如何用Google Apps Script合并多Sheet指定列并实现条件列替换

需求说明 #

• 目标：将3个Sheet的指定列合并到「Sheet Final」，最终结构为 [id, alliance]
  • Sheet1：提取「Column Needed A」作为id，「Alliance Mapped」作为alliance
  • Sheet2：提取「identifier」作为id；「Alliances」作为alliance，若值为「International」则用「trash column 2」的值替代
  • Sheet3：提取「number」作为id，「organization」作为alliance

现有代码（仅处理Sheet2） #

function copySheet() { 
 var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
 var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet Final");

 var columns_to_be_copied = [4,1];

 for (var i = 0; i < columns_to_be_copied.length; i++) {
  var sourcerange = sourceSheet.getRange(2, columns_to_be_copied[i], sourceSheet.getLastRow()-1,1)
  sourcerange.copyTo(destSheet.getRange(1,(i+1)))
 }
}

完整实现脚本 #

function mergeSheetsToFinal() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const finalSheet = ss.getSheetByName("Sheet Final");
  
  // 清空目标Sheet原有数据（保留表头可选，这里直接清空）
  finalSheet.clearContents();
  // 写入表头
  finalSheet.getRange(1, 1, 1, 2).setValues([["id", "alliance"]]);
  
  // 处理Sheet1
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet1Data = sheet1.getDataRange().getValues();
  // 跳过表头，从第2行开始
  const sheet1Rows = sheet1Data.slice(1).map(row => [
    row[getColumnIndex(sheet1, "Column Needed A") - 1], // id列
    row[getColumnIndex(sheet1, "Alliance Mapped") - 1]  // alliance列
  ]);
  // 写入Sheet1数据
  if (sheet1Rows.length > 0) {
    finalSheet.getRange(finalSheet.getLastRow() + 1, 1, sheet1Rows.length, 2).setValues(sheet1Rows);
  }
  
  // 处理Sheet2
  const sheet2 = ss.getSheetByName("Sheet2");
  const sheet2Data = sheet2.getDataRange().getValues();
  const sheet2Rows = sheet2Data.slice(1).map(row => {
    const id = row[getColumnIndex(sheet2, "identifier") - 1];
    let alliance = row[getColumnIndex(sheet2, "Alliances") - 1];
    // 条件替换：如果是International，用trash column 2的值替代
    if (alliance === "International") {
      alliance = row[getColumnIndex(sheet2, "trash column 2") - 1];
    }
    return [id, alliance];
  });
  // 写入Sheet2数据
  if (sheet2Rows.length > 0) {
    finalSheet.getRange(finalSheet.getLastRow() + 1, 1, sheet2Rows.length, 2).setValues(sheet2Rows);
  }
  
  // 处理Sheet3
  const sheet3 = ss.getSheetByName("Sheet3");
  const sheet3Data = sheet3.getDataRange().getValues();
  const sheet3Rows = sheet3Data.slice(1).map(row => [
    row[getColumnIndex(sheet3, "number") - 1],       // id列
    row[getColumnIndex(sheet3, "organization") - 1]  // alliance列
  ]);
  // 写入Sheet3数据
  if (sheet3Rows.length > 0) {
    finalSheet.getRange(finalSheet.getLastRow() + 1, 1, sheet3Rows.length, 2).setValues(sheet3Rows);
  }
}

// 辅助函数：根据列名获取列索引（从1开始）
function getColumnIndex(sheet, columnName) {
  const headers = sheet.getDataRange().getValues()[0];
  return headers.indexOf(columnName) + 1;
}

关键逻辑说明 #

• 辅助函数getColumnIndex：通过列名匹配索引，替代硬编码列号，后续列位置变动时只需修改列名即可，维护更方便
• 清空目标Sheet：每次运行先清空「Sheet Final」内容，避免重复累加数据
• Sheet2条件替换：遍历每行数据时判断Alliances列值，若等于「International」则替换为「trash column 2」对应的值
• 批量写入优化：使用setValues批量写入数据，比逐行操作效率更高，适合处理大量数据
• 表头处理：所有Sheet均跳过第一行表头，仅提取有效数据写入目标Sheet

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