如何用App Script将多工作表数据逆透视至单个工作表？

修改后的完整脚本 #

function unpivotMultipleSheets(sourceSheetNames, sourceRangeA1Notation, targetSheetName) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const unpivotedData = [];

  // 遍历所有源工作表
  sourceSheetNames.forEach(sheetName => {
    const sourceSheet = spreadsheet.getSheetByName(sheetName);
    if (!sourceSheet) {
      console.log(`未找到工作表：${sheetName}，跳过处理`);
      return;
    }

    const sourceRange = sourceSheet.getRange(sourceRangeA1Notation);
    const sourceValues = sourceRange.getDisplayValues();

    // 提取表头（假设第二行为表头）
    const headers = sourceValues[1];
    // 从第三行开始遍历数据行
    for (let row = 2; row < sourceValues.length; row++) {
      // 从第11列（索引10）开始遍历需要逆透视的列
      for (let col = 10; col < headers.length; col++) {
        // 跳过空值行（可选，根据需求调整）
        if (!sourceValues[row][1] || !headers[col] || !sourceValues[row][col]) continue;
        unpivotedData.push([sourceValues[row][1], headers[col], sourceValues[row][col]]);
      }
    }
  });

  // 获取或创建目标工作表
  let targetSheet = spreadsheet.getSheetByName(targetSheetName);
  if (!targetSheet) {
    targetSheet = spreadsheet.insertSheet(targetSheetName);
  }

  // 清空目标表并写入表头
  targetSheet.clear();
  targetSheet.getRange(1, 1, 1, 3).setValues([["Name", "Date", "Schedule"]]);

  // 写入所有合并后的逆透视数据
  if (unpivotedData.length > 0) {
    targetSheet.getRange(2, 1, unpivotedData.length, 3).setValues(unpivotedData);
  }
}

// 使用示例：处理"Jan 2024"和"Feb 2024"两个工作表，范围A1:AK255，目标表"Contoh"
function runUnpivot() {
  unpivotMultipleSheets(["Jan 2024", "Feb 2024"], "A1:AK255", "Contoh");
}

关键修改点说明 #

• 支持多工作表批量处理：将单个源表参数改为数组，通过遍历逐个处理每个工作表
• 复用传入参数：移除原脚本中硬编码的表名和范围，直接使用函数参数，提升脚本灵活性
• 统一累加数据：所有工作表的逆透视数据存入同一个数组，避免多次写入覆盖之前的内容
• 容错处理：添加工作表不存在的判断，跳过无效表并打印日志，防止脚本中断
• 可选空值过滤：增加空值判断，跳过无有效数据的行/列（不需要可直接删除该判断）
• 独立执行入口：新增runUnpivot函数作为执行入口，修改数组内的表名即可快速调整处理范围

使用方法 #

1. 打开目标Google表格，进入脚本编辑器
2. 替换原脚本为上述代码
3. 修改runUnpivot函数中的工作表名数组（比如添加"Mar 2024"等）
4. 运行runUnpivot函数，完成授权后即可自动合并多表逆透视数据

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