如何通过Google Apps Script高效将Data表行数据映射到Template表非连续单元格？

原代码的核心问题是频繁调用getRange()和setValue()，每一次调用都是对Google Sheets API的请求，50次操作会产生大量IO开销，导致效率低下。下面是基于映射关系表+批量读写的优化方案，能大幅减少API调用次数：

优化后的完整代码 #

const ss = SpreadsheetApp.getActiveSpreadsheet();
const templateS = ss.getSheetByName("Template");
const dataS = ss.getSheetByName("Data");

// 定义Data表列与Template表单元格的映射关系
// 格式：{ dataCol: 数据列索引, templateCell: 目标单元格地址 }
const CELL_MAPPING = [
  { dataCol: 1, templateCell: "D3" },   // Client ID
  { dataCol: 2, templateCell: "B1" },   // 称谓
  { dataCol: 3, templateCell: "B2" },   // 姓氏
  { dataCol: 4, templateCell: "E2" },   // 名字
  { dataCol: 5, templateCell: "B4" },   // 地址
  // 在这里继续添加剩下的45个映射关系即可
];

function searchRecord() {
  const searchValue = templateS.getRange("B6").getValue();
  if (!searchValue) {
    SpreadsheetApp.getUi().alert("请输入要搜索的Client ID");
    return;
  }

  // 查找匹配的记录
  const recordFound = dataS.getRange("A:A")
    .createTextFinder(searchValue)
    .matchCase(true)
    .matchEntireCell(true)
    .findNext();

  if (!recordFound) {
    SpreadsheetApp.getUi().alert("未找到对应Client ID的记录");
    return;
  }

  const row = recordFound.getRow();
  // 一次性读取Data表中该行的所有数据（减少API调用）
  const rowData = dataS.getRange(row, 1, 1, dataS.getLastColumn()).getValues()[0];

  // 准备要设置的单元格列表和对应的值列表
  const templateCells = CELL_MAPPING.map(item => item.templateCell);
  const values = CELL_MAPPING.map(item => [rowData[item.dataCol - 1]]); // 数组索引从0开始，列索引从1开始

  // 批量设置Template表的单元格（仅2次API调用：getRangeList + setValues）
  templateS.getRangeList(templateCells).setValues(values);
}

关键优化点说明 #

1. 映射关系表：

   • 用CELL_MAPPING数组统一管理Data列和Template单元格的对应关系，后续新增/修改字段只需修改这个数组，无需重复编写大量赋值代码，维护性大幅提升。

2. 批量读取数据：

   • 用getRange(row, 1, 1, dataS.getLastColumn()).getValues()[0]一次性读取匹配行的所有数据，替代原代码中多次dataS.getRange(row, col).getValue()的调用，把API调用从50次降到1次。

3. 批量设置单元格：

   • 通过getRangeList(templateCells)获取所有目标单元格的RangeList，再用setValues(values)批量赋值，把API调用从50次降到1次。整个流程的API调用次数从100+次降到了4次左右（读取搜索值、查找记录、读取行数据、批量设置），效率提升非常明显。

4. 异常处理：

   • 增加了空搜索值和未找到记录的判断，避免代码运行报错，提升用户体验。

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