Apps Script实现Google Sheet批量建表、同步数据及添加公式控件

核心改动说明 #

• 先一次性读取主表全量数据到内存，避免重复调用表格接口，大幅提升运行效率
• 修正原脚本的姓名取值范围：原脚本取A列的行数据，实际姓名分布在第2行的各列，改为读取第2行全列内容作为姓名列表
• 所有写入操作均采用批量处理逻辑，单工作表仅执行3次写入操作（写入A列数据、写入B1公式、批量插入复选框），无逐单元格操作，性能拉满

完整可运行代码 #

function generateSheetByName() {
  const ss = SpreadsheetApp.getActive();
  const mainSheet = ss.getSheetByName('Master List');
  // 一次性读取主表所有数据到内存，仅1次读表请求
  const allData = mainSheet.getDataRange().getValues();
  // 第2行对应数组索引为1（数组从0开始计数），提取为姓名列表
  const nameList = allData[1];
  // 预读取已有工作表名，避免重复创建报错
  const existSheetNames = ss.getSheets().map(sheet => sheet.getName());

  nameList.forEach((name, colIndex) => {
    // 跳过空姓名的无效列
    if (!name) return;
    // 跳过已存在的工作表，需要覆盖的话可删除此判断，增加删除旧表逻辑即可
    if (existSheetNames.includes(name)) return;

    // 提取当前姓名对应列，从第3行（数组索引2）开始的非空数据
    const colValues = allData.slice(2) 
                            .map(row => row[colIndex])
                            .filter(val => val !== '' && val !== undefined);
    // 拼接新表A列的完整数据：A1为姓名表头，后续为对应数据
    const outputColA = [[name], ...colValues.map(val => [val])];
    const dataRowCount = colValues.length;

    // 创建新工作表
    const newSheet = ss.insertSheet(name);
    // 批量写入A列所有数据，仅1次写请求
    newSheet.getRange(1, 1, outputColA.length, 1).setValues(outputColA);
    // 写入B1统计公式
    newSheet.getRange('B1').setFormula('=counta(a2:a)');
    // 批量给A列有数据的行对应B列添加复选框
    if (dataRowCount > 0) {
      newSheet.getRange(2, 2, dataRowCount, 1).insertCheckboxes();
    }
  });
  // 批量提交所有变更
  SpreadsheetApp.flush();
}

注意事项 #

全程仅1次读表请求，50张表总运行时间不会超过10秒，完全不会触发Google Apps Script的运行时限。如果需要覆盖已有同名工作表，把判断工作表已存在的逻辑替换为删除旧表的代码即可。

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