如何使用Apps Script跨表复制指定列数据到各条目工作表末行

前置逻辑说明 #

• 数据源为database工作表，需要提取每行的H、L、M、N、O、P列数据
• 匹配规则：默认以database表A列的内容作为对应独立工作表的表名，逐行匹配后将提取的数据追加到目标工作表的最后一行下方
• 自动跳过空行、找不到对应工作表的异常行，不会中断整体执行

完整实现代码 #

function appendSpecifiedColumnsToSheets() {
  const currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 读取数据源工作表
  const databaseSheet = currentSpreadsheet.getSheetByName('database');
  if (!databaseSheet) throw new Error('未找到名为database的工作表，请确认表名拼写');

  // 拉取数据源全量有效数据，数组下标从0开始计数
  const allDatabaseRows = databaseSheet.getDataRange().getValues();
  // 配置需要提取的列下标：H=7、L=11、M=12、N=13、O=14、P=15
  const extractColumnIndexes = [7, 11, 12, 13, 14, 15];
  // 表名所在列下标，默认A列为0，如果你用其他列存工作表名，修改这个值即可
  const sheetNameColumnIndex = 0;
  // 数据起始行下标，默认第2行开始是数据（跳过第1行表头，下标为1），如果表头行位置不同自行修改
  const dataStartRowIndex = 1;

  // 逐行处理数据
  for (let i = dataStartRowIndex; i < allDatabaseRows.length; i++) {
    const currentRow = allDatabaseRows[i];
    const targetSheetName = currentRow[sheetNameColumnIndex]?.toString().trim();
    // 表名为空直接跳过
    if (!targetSheetName) continue;

    const targetSheet = currentSpreadsheet.getSheetByName(targetSheetName);
    // 找不到对应工作表打印日志后跳过
    if (!targetSheet) {
      console.warn(`第${i+1}行匹配失败：不存在名为【${targetSheetName}】的工作表`);
      continue;
    }

    // 提取当前行需要追加的字段
    const rowToAppend = extractColumnIndexes.map(colIndex => currentRow[colIndex]);
    // 定位到目标表最后一行的下一行写入数据
    const targetLastRow = targetSheet.getLastRow();
    targetSheet.getRange(targetLastRow + 1, 1, 1, rowToAppend.length).setValues([rowToAppend]);
  }
}

使用步骤 #

• 打开目标谷歌表格，点击顶部菜单栏「扩展程序」→「Apps Script」，进入脚本编辑器
• 把编辑器里默认的空函数删掉，粘贴上面的代码，点击保存按钮给项目随便命个名
• 根据自己的实际表格结构调整两个配置项：
  • 如果存工作表名称的不是A列，修改sheetNameColumnIndex的值（B列填1、C列填2，以此类推）
  • 如果表头不在第1行，数据不是从第2行开始，修改dataStartRowIndex的值（比如数据从第3行开始就填2）
• 点击运行按钮，第一次执行会弹出谷歌账号授权提示，按流程完成授权即可，执行完成后可以在执行日志里查看匹配失败的条目
• 后续需要同步数据的时候，重新运行这个函数就行

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