Google Apps Script：点击按钮新建工作表并自动添加带公式的目录行

function createSheetAndUpdateIndex() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const indexSheetName = "目录"; // 可自定义目录工作表名称
  const indexSheet = ss.getSheetByName(indexSheetName);
  
  // 弹出UI输入框获取新工作表名称
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt("新建工作表", "请输入工作表名称：", ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() !== ui.Button.OK) return;
  
  let newSheetName = response.getResponseText().trim();
  if (!newSheetName) {
    ui.alert("工作表名称不能为空！");
    return;
  }
  
  // 处理特殊字符：单引号转成双单引号，判断是否需要包裹单引号
  const escapedSheetName = newSheetName.replace(/'/g, "''");
  const isSpecialName = /[\s'!@#$%^&*()_+\-=\[\]{};:"\\|,.<>\/?]+/.test(newSheetName);
  const sheetRef = isSpecialName ? `'${escapedSheetName}'!` : `${escapedSheetName}!`;
  
  // 创建新工作表
  const newSheet = ss.insertSheet(newSheetName);
  
  // 定位目录下一行并插入新行
  const lastRow = indexSheet.getLastRow();
  const targetRow = lastRow + 1;
  indexSheet.insertRowsAfter(lastRow, 1);
  
  // 批量设置公式（用R1C1格式，避免拼接A1字符串，高效且减少操作）
  const formulas = [
    [`=${sheetRef}R1C1`, `=${sheetRef}R3C2`, `=IF(${sheetRef}R5C4>=0,"Yes","No")`, `=COUNTA(${sheetRef}R21C2:R123C2)`]
  ];
  indexSheet.getRange(targetRow, 1, 1, 4).setFormulasR1C1(formulas);
  
  ui.alert(`工作表「${newSheetName}」已创建，目录已更新！`);
}

核心功能拆解 #

1. 特殊工作表名称处理 #

• 单引号转义：把名称里的单个单引号'替换成两个''，这是Google Sheets公式中对单引号的转义规则，确保像It's Tuesday这样的名称能被正确引用为'It''s Tuesday'!
• 自动判断包裹单引号：通过正则表达式检测名称是否包含空格、特殊符号，若是则用单引号包裹整个工作表名，避免公式报错

2. 高效公式批量设置 #

• 使用setFormulasR1C1一次性设置整行公式，替代循环调用setValue，大幅减少与电子表格的交互次数
• R1C1格式的优势：不用手动拼接A1样式的单元格地址，直接用行号列号定位，逻辑更清晰，不易出错

3. 目录行定位与插入 #

• 通过getLastRow()获取目录的最后一行，仅统计有内容的行，避免空行干扰
• 用insertRowsAfter插入新行，确保目录结构连续

4. UI交互优化 #

• 输入框做了空值校验，避免创建空名称的工作表
• 操作完成后弹出提示，明确反馈执行结果

使用说明 #

1. 打开你的Google表格，点击「扩展程序」→「Apps Script」
2. 粘贴上述代码，保存项目（可自定义项目名称）
3. 返回表格，点击「插入」→「绘图」，绘制一个按钮，右键按钮选择「点击时执行脚本」，选择createSheetAndUpdateIndex
4. 点击按钮即可触发操作

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