如何在Google Script中循环执行Google Sheets转置公式批量插入多工作表

纯Google Sheets工作表函数无法实现跨表循环写入公式的需求，你可以用内置的Google Apps Script完成操作，步骤如下：

• 打开目标表格，点击顶部菜单栏「扩展程序」>「Apps 脚本」，进入代码编辑页面，清空默认生成的代码。
• 将下方代码粘贴到编辑框中，根据你的实际需求调整参数后，点击运行按钮，授权脚本权限后即可完成批量插入。

function batchInsertFormulas() {
  // 配置区：按需修改以下参数
  const FORMULA = `=transpose(query({Planning!A3:DT3};"Select "&ArrayFormula(join(", ";text(query(transpose({column(Planning!BM3:DT3);mod(column(Planning!BM3:DT3)-column(Planning!BM3);2)});"Select Col1 where Col2=0");"Col0"))))`;
  // 6个月对应的目标单元格位置，可自行补充剩余4个月的位置
  const TARGET_CELLS = ["B4", "C4", "B42", "C42"];
  // 自动获取所有名称不是Planning的工作表作为目标表
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheets = ss.getSheets().filter(sheet => sheet.getName() !== "Planning");
  // 如果需要手动指定工作表，替换成下面的写法：
  // const targetSheets = ["Peter", "John", "Albert", ...其余32个工作表名称].map(name => ss.getSheetByName(name));

  // 执行批量写入
  targetSheets.forEach(sheet => {
    TARGET_CELLS.forEach(cell => {
      sheet.getRange(cell).setFormula(FORMULA);
    })
  })
  SpreadsheetApp.getUi().alert("公式批量插入完成");
}

注意事项 #

• 若你的Google表格公式分隔符使用逗号而非分号，需要将公式中的所有;替换为,再使用
• 可在TARGET_CELLS数组中补充剩余4个月对应的单元格地址，即可一次性完成6个月的公式插入
• 若后续需要更新公式，只需修改FORMULA变量的内容重新运行即可

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