Google Sheets 固定前两列转置表单单行多组数据方案咨询

方案说明 #

你需要将谷歌表单提交的单行多组请求数据，拆分为每组请求对应独立行的标准化结构，固定前两列（时间戳、邮箱）重复，后续每3列为一组拆分。以下提供两种可直接落地的实现方案：

方案1：Google Apps Script实现（优先推荐） #

该方案支持两种运行模式：新提交响应自动追加到目标表、非工作时段定时全量重算所有数据，适配你的业务需求。

完整代码 #

// 配置项：可根据实际表名、参数调整
const CONFIG = {
  SOURCE_SHEET_NAME: 'Form Responses 1', // 表单同步的源数据表名
  TARGET_SHEET_NAME: 'Normalized Responses', // 输出的目标表名
  FIXED_COL_COUNT: 2, // 固定重复的列数：时间戳、邮箱共2列
  GROUP_COL_COUNT: 3 // 每组请求的列数
}

// 全量重算所有数据（用于定时触发器调用）
function fullRefreshNormalizedData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(CONFIG.SOURCE_SHEET_NAME);
  const targetSheet = ss.getSheetByName(CONFIG.TARGET_SHEET_NAME) || ss.insertSheet(CONFIG.TARGET_SHEET_NAME);
  
  // 读取源表所有数据，跳过表头行
  const sourceData = sourceSheet.getDataRange().getValues().slice(1);
  const output = [];
  // 先写入目标表表头
  output.push(['Timestamp', 'Email Address', 'Attribute 1', 'Attribute 2', 'Attribute 3']);
  
  // 遍历每行源数据拆分
  sourceData.forEach(row => {
    const fixedCols = row.slice(0, CONFIG.FIXED_COL_COUNT);
    // 按每组3列拆分剩余字段
    for(let i = CONFIG.FIXED_COL_COUNT; i < row.length; i += CONFIG.GROUP_COL_COUNT) {
      const group = row.slice(i, i + CONFIG.GROUP_COL_COUNT);
      // 跳过未填写的空请求组
      if(group.join('').trim() !== '') {
        output.push([...fixedCols, ...group]);
      }
    }
  });
  
  // 清空目标表旧数据写入新数据
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
}

// 增量追加新提交的数据（用于表单提交触发器调用）
function appendNewResponse(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheet = ss.getSheetByName(CONFIG.TARGET_SHEET_NAME) || ss.insertSheet(CONFIG.TARGET_SHEET_NAME);
  const newRow = e.values; // 触发器返回的新提交行数据
  
  const fixedCols = newRow.slice(0, CONFIG.FIXED_COL_COUNT);
  const output = [];
  // 拆分新行的请求组
  for(let i = CONFIG.FIXED_COL_COUNT; i < newRow.length; i += CONFIG.GROUP_COL_COUNT) {
    const group = newRow.slice(i, i + CONFIG.GROUP_COL_COUNT);
    if(group.join('').trim() !== '') {
      output.push([...fixedCols, ...group]);
    }
  }
  
  // 追加到目标表末尾
  if(output.length > 0) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, output.length, output[0].length).setValues(output);
  }
}

配置步骤 #

• 打开你的谷歌表格，点击顶部菜单「扩展程序」-「Apps Script」进入脚本编辑器
• 替换默认代码为以上代码，保存项目
• 按需配置触发器：
  • 如需表单提交后自动追加新数据：添加触发器，选择appendNewResponse函数，事件源为「电子表格」，事件类型为「表单提交时」
  • 如需每日非工作时段全量重算：添加触发器，选择fullRefreshNormalizedData函数，事件源为「时间驱动」，设置你需要的每日执行时段即可

方案2：数组公式实现（轻量无代码） #

如果不需要增量触发，仅需要实时自动计算结果，可以直接在目标表的A1单元格输入以下公式：

=LET(
  source, 'Form Responses 1'!A2:Z,
  fixed_cols, 2,
  group_cols, 3,
  total_groups, ROUNDUP((COLUMNS(source)-fixed_cols)/group_cols,0),
  row_map, FLATTEN(MAKEARRAY(ROWS(source), total_groups, LAMBDA(r,c, r))),
  filterd_row_map, FILTER(row_map, INDEX(source, row_map, fixed_cols + (MOD(SEQUENCE(ROWS(row_map))-1, total_groups)*group_cols)+1)<>""),
  fixed_data, BYROW(filterd_row_map, LAMBDA(r, INDEX(source, r, 1)&"|"&INDEX(source, r, 2))),
  group_index, MOD(SEQUENCE(ROWS(filterd_row_map))-1, total_groups)*group_cols + fixed_cols + 1,
  attr1, BYROW(SEQUENCE(ROWS(filterd_row_map)), LAMBDA(i, INDEX(source, INDEX(filterd_row_map,i,1), INDEX(group_index,i,1)))),
  attr2, BYROW(SEQUENCE(ROWS(filterd_row_map)), LAMBDA(i, INDEX(source, INDEX(filterd_row_map,i,1), INDEX(group_index,i,1)+1))),
  attr3, BYROW(SEQUENCE(ROWS(filterd_row_map)), LAMBDA(i, INDEX(source, INDEX(filterd_row_map,i,1), INDEX(group_index,i,1)+2))),
  result, BYROW(SEQUENCE(ROWS(filterd_row_map)), LAMBDA(i, {SPLIT(INDEX(fixed_data,i,1),"|"), INDEX(attr1,i,1), INDEX(attr2,i,1), INDEX(attr3,i,1)})),
  VSTACK({"Timestamp","Email Address","Attribute 1","Attribute 2","Attribute 3"}, result)
)

可根据实际源表名、列数调整开头的source、fixed_cols、group_cols参数，公式会自动读取源表所有提交数据，实时生成拆分后的标准化结构，无需手动刷新。

内容的提问来源于stack exchange，提问作者Andrew_Barnes