如何用Google Apps Script基于模板批量生成Google Sheet并同步母表数据

1. 需求可行性与原有代码问题 #

你的需求完全可以通过Google Apps Script实现，原有代码的问题集中在以下几点：

• 参数传递错误：ss.getSheetByName(['NOL'])中方法要求传入字符串参数，你多写了数组括号，应该改为ss.getSheetByName('NOL')
• 目标表格对象获取错误：创建新表格ssNew后，你写的var sheet = SpreadsheetApp.getActiveSheet()获取的仍然是母表的激活工作表，模板内容被复制到了母表而非新表，自然新生成的表格是空白的
• 变量名冲突：外层已经用sheet变量存储Justin's Analysis工作表，循环内重复定义同名变量会导致后续母表操作逻辑异常
• 逻辑冗余：同时使用range.copyTo和sheet.copyTo两种复制模板的逻辑，重复执行无意义，且未完成对应行经费数据填入新模板的步骤
• 范围不匹配：当前读取范围仅为A1:P23，要处理250行数据需要调整读取范围和循环边界

修正后的生成独立Google Sheet的代码示例：

function generateIndependentNOLSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName("Justin's Analysis");
  // 调整为实际250行的范围，这里假设表头占1行，数据从第2行到251行
  const dataRange = masterSheet.getRange("A2:P251");
  const nolTemplateSheet = ss.getSheetByName('NOL');
  const allData = dataRange.getValues();

  for (let i = 0; i < allData.length; i++) {
    const rowData = allData[i];
    const clubName = rowData[1];
    const newFileName = `NOL${i+1}_${clubName}`;
    // 创建新表格
    const newSS = SpreadsheetApp.create(newFileName);
    // 复制模板到新表格
    const copiedTemplate = nolTemplateSheet.copyTo(newSS);
    // 删除新表格自带的空白默认表
    newSS.deleteSheet(newSS.getActiveSheet());
    // 填写对应行的经费数据到模板，这里假设模板中对应数据填入位置为B2到B7，可根据实际模板调整
    copiedTemplate.getRange("B2").setValue(rowData[9]); // registration
    copiedTemplate.getRange("B3").setValue(rowData[10]); // subscription
    copiedTemplate.getRange("B4").setValue(rowData[11]); // incentive
    copiedTemplate.getRange("B5").setValue(rowData[12]); // food
    copiedTemplate.getRange("B6").setValue(rowData[13]); // other
    copiedTemplate.getRange("B7").setValue(rowData[14]); // total
    // 回填链接到母表，P列对应索引15
    allData[i][15] = newSS.getUrl();
  }
  // 批量写入更新后的数据回母表
  dataRange.setValues(allData);
}

2. 更简便的实现方案 #

你提到的不生成独立文件，改为在现有表格中新增250个工作表的方案完全可行，相比生成独立文件优势明显：无需跨文件操作、权限统一管理、查找汇总更方便，执行效率也更高。

同文件新增工作表的代码示例：

function generateNOLSheetsInSameFile() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName("Justin's Analysis");
  const dataRange = masterSheet.getRange("A2:P251");
  const nolTemplateSheet = ss.getSheetByName('NOL');
  const allData = dataRange.getValues();

  for (let i = 0; i < allData.length; i++) {
    const rowData = allData[i];
    const clubName = rowData[1];
    const newSheetName = `NOL${i+1}_${clubName}`;
    // 复制模板为新工作表
    const newSheet = nolTemplateSheet.copyTo(ss);
    newSheet.setName(newSheetName);
    // 填写经费数据，调整目标位置匹配你的模板
    newSheet.getRange("B2").setValue(rowData[9]);
    newSheet.getRange("B3").setValue(rowData[10]);
    newSheet.getRange("B4").setValue(rowData[11]);
    newSheet.getRange("B5").setValue(rowData[12]);
    newSheet.getRange("B6").setValue(rowData[13]);
    newSheet.getRange("B7").setValue(rowData[14]);
    // 回填工作表跳转链接，gid参数为新工作表ID
    allData[i][15] = `${ss.getUrl()}#gid=${newSheet.getSheetId()}`;
  }
  dataRange.setValues(allData);
}

如果你的使用场景只是需要给不同社团查看对应NOL数据，还可以进一步简化：仅做一个查询页，通过输入社团名称自动匹配对应行数据填充到NOL模板，不需要生成250个工作表。

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