Google Apps Script改造：复选框触发脚本及大表提速需求

需求1：复选框触发跨表格脚本的实现 #

跨表格读写需要授权，简单onEdit触发器权限不足，必须用可安装的编辑触发器，具体实现如下：

1. 创建可安装触发器 #

运行这段代码创建触发器，确保表格编辑时触发指定函数：

function createCheckboxTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 清理旧触发器，避免重复触发
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "handleCheckboxTrigger") {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  // 创建可安装onEdit触发器
  ScriptApp.newTrigger("handleCheckboxTrigger")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

2. 编写触发逻辑 #

处理函数中判断是否为目标复选框（Summary工作表C36）被勾选，触发原数据同步逻辑：

function handleCheckboxTrigger(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  // 校验触发源：Summary表C36复选框被勾选
  if (sheet.getName() === "Summary" && range.getA1Notation() === "C36" && range.getValue()) {
    // 执行原有的数据同步函数
    firstExtractFromBdd();
    // 可选：执行后重置复选框，防止重复触发
    range.setValue(false);
  }
}

注意事项 #

• 首次运行createCheckboxTrigger时需完成授权，确保脚本获得跨表格读写权限
• 可安装触发器触发延迟在几秒内，权限覆盖跨表格操作

需求2：大体积外部表格的提速优化 #

针对外部表格B接近1000万单元格导致的300秒运行时长，从以下维度优化：

1. 一次性读取外部数据，用内存映射替代VLOOKUP #

避免逐行调用VLOOKUP（每次都会重新读取外部表格），一次性读取数据并转为键值对映射：

function getExternalLookupMap() {
  const externalSS = SpreadsheetApp.openById("外部表格B的ID");
  const dataSheet = externalSS.getSheetByName("目标工作表名");
  // 一次性读取所有数据到内存
  const allRows = dataSheet.getDataRange().getValues();
  // 构建匹配键到目标值的映射
  const lookupMap = {};
  allRows.forEach(row => {
    const matchKey = row[0]; // 匹配键所在列，按需调整
    lookupMap[matchKey] = row[1]; // 要获取的值所在列，按需调整
  });
  return lookupMap;
}

后续查询直接从lookupMap取值，效率提升显著。

2. 批量读写当前表格数据 #

• 一次性读取「Datas from xxxx」的所有数据到内存，批量完成匹配计算
• 处理完成后，一次性将结果写入「Mat & Comp Quick Search」工作表，减少SpreadsheetAppAPI调用次数（API调用是耗时核心）

3. 拆分外部表格B #

如果数据可按业务维度拆分（如类别、日期），拆分为多个小表格，每次仅读取目标拆分表的数据，降低单次读取的数据量。

4. 过滤无效数据 #

处理时跳过空行、无效行，减少不必要的计算。

5. 使用Sheets API提升读写速度 #

在脚本编辑器中开启「资源」→「高级Google服务」中的「Sheets API」，使用Sheets.Spreadsheets.Values.batchGet和batchUpdate接口，比原生SpreadsheetApp读写速度更快。

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