使用Apps Script将Google Sheets单行多列数据拆分为多行格式

一、内置函数方案（无脚本，中小数据集首选） #

无需编写代码，直接用FLATTEN+QUERY组合公式实现。假设源数据（Table A）在A1:D4（A列姓名，B-D列依次为数学、阅读、科学），在新工作表的A1单元格输入以下公式：

=QUERY(
  FLATTEN(
    A2:A&"|"&B1:D1&"|"&B2:D,
    A2:A&"|"&B1:D1&"|",
    A2:A&"|"&B1:D1&"|"
  ),
  "SELECT 
    SPLIT(Col1, '|')[OFFSET(0)],
    IF(SPLIT(Col1, '|')[OFFSET(1)]='数学', SPLIT(Col1, '|')[OFFSET(2)], ''),
    IF(SPLIT(Col1, '|')[OFFSET(1)]='阅读', SPLIT(Col1, '|')[OFFSET(2)], ''),
    IF(SPLIT(Col1, '|')[OFFSET(1)]='科学', SPLIT(Col1, '|')[OFFSET(2)], '')
   WHERE SPLIT(Col1, '|')[OFFSET(2)] IS NOT NULL
   LABEL 
    SPLIT(Col1, '|')[OFFSET(0)] '姓名',
    IF(SPLIT(Col1, '|')[OFFSET(1)]='数学', SPLIT(Col1, '|')[OFFSET(2)], '') '数学',
    IF(SPLIT(Col1, '|')[OFFSET(1)]='阅读', SPLIT(Col1, '|')[OFFSET(2)], '') '阅读',
    IF(SPLIT(Col1, '|')[OFFSET(1)]='科学', SPLIT(Col1, '|')[OFFSET(2)], '') '科学'
  ", 0
)

• 核心逻辑：用FLATTEN把每行的科目-分数对拆成独立行，再通过QUERY拆分拼接字符串，按科目匹配填充对应列，其余列留空
• 优势：实时同步源数据，无需额外操作；缺点：超万行数据集可能出现卡顿

二、Apps Script批量处理方案（大数据集最优） #

通过批量读写+内存数组操作替代逐行循环，解决大数据集运行慢的问题：

function convertTableFormat() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("TableA"); // 替换为你的源表名称
  const targetSheet = ss.getSheetByName("TableB"); // 替换为你的目标表名称
  
  // 一次性读取所有源数据（跳过表头）
  const sourceData = sourceSheet.getDataRange().getValues().slice(1);
  const headers = ["姓名", "数学", "阅读", "科学"];
  
  // 内存中构建目标数据数组
  const targetData = [];
  sourceData.forEach(row => {
    const name = row[0];
    // 生成数学分数行
    targetData.push([name, row[1], "", ""]);
    // 生成阅读分数行
    targetData.push([name, "", row[2], ""]);
    // 生成科学分数行
    targetData.push([name, "", "", row[3]]);
  });
  
  // 批量写入目标表（避免频繁读写单元格）
  targetSheet.clearContents();
  targetSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  targetSheet.getRange(2, 1, targetData.length, targetData[0].length).setValues(targetData);
}

• 优化点：一次性读写所有数据，内存数组循环比单元格循环快100倍以上，十万级数据可在几秒内完成
• 使用方式：在脚本编辑器中粘贴代码，修改表名后运行，或绑定按钮一键执行

三、通用逆透视自定义函数（灵活扩展） #

如果需要适配更多科目/列，可添加自定义UNPIVOT函数：

function UNPIVOT(sourceRange, idColCount, headerRowNum) {
  const values = sourceRange.getValues();
  const headers = values[headerRowNum - 1].slice(idColCount);
  const idRows = values.slice(headerRowNum).map(row => row.slice(0, idColCount));
  
  const result = [];
  idRows.forEach((idRow, idx) => {
    const dataRow = values[headerRowNum + idx].slice(idColCount);
    headers.forEach((header, colIdx) => {
      const newRow = [...idRow];
      headers.forEach(h => newRow.push(h === header ? dataRow[colIdx] : ""));
      result.push(newRow);
    });
  });
  return result;
}

• 使用方法：在目标单元格输入=UNPIVOT(TableA!A1:D4,1,1)，参数依次为：源数据范围、ID列数量（姓名列数）、表头行号

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