优化Google Sheets脚本：批量添加下拉菜单与公式

云原生阿岚

2026-6-28

优化Google Sheets脚本性能：批量处理下拉菜单与公式

我正在开发一款Google Sheets脚本，需要根据特定条件动态更新表格，为多行添加下拉菜单并应用指定公式。当前代码可正常运行，但处理大量行或多列下拉菜单时，逐个添加下拉的操作速度较慢，寻求批量操作等优化策略以缩短耗时，开发环境为具备Google Sheets API访问权限的Google Apps Script。

当前核心代码（简化版）

maintainedProcesses.forEach((process, index) => {
  const rowIndex = index + 2; // 适配表头行
  if (!process['Destination Unit']) {
    process['Destination Unit'] = `=IFERROR(VLOOKUP(C:C;'Data Validations'!A:B,2,0);""`;
  }
  if (!process['Responsible Manager']) {
    process['Responsible Manager'] = `=IFERROR(IF(E${rowIndex}="Screening";AA${rowIndex};"");"")`;
  }
});

await sheetManager.updateSheet(maintainedProcesses);

// 获取验证数据
const validationData = await sheetManagerValidations.readToJson();
const judicialActions = [...new Set(validationData.map(item => item['Judicial Action']))];
const subjects = [...new Set(validationData.map(item => item['Subject']))];
// 其他下拉选项处理...

// 设置下拉菜单
await sheetManager.setDropdownMenu(judicialActions, 'Judicial Action');
await sheetManager.setDropdownMenu(subjects, 'Subject');
// 其他下拉菜单设置...

setDropdownMenu方法实现

/**
 * 为指定列设置下拉菜单数据验证
 * 若选项数量超过500，使用辅助表存储选项（因验证规则限制）
 * @param {Array} options - 下拉选项数组
 * @param {string} columnName - 目标列名称
 */
async function setDropdownMenu(options, columnName) {
  if (!Array.isArray(options)) throw new TypeError('参数"options"必须是数组');
  if (typeof columnName !== 'string') throw new TypeError('参数"columnName"必须是字符串');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const header = sheet.getDataRange().getValues()[0];
  const columnId = header.indexOf(columnName) + 1;
  const lastRow = sheet.getLastRow();

  if (options.length <= 500) {
    // 选项数量在限制内的常规处理
    const rule = SpreadsheetApp.newDataValidation().requireValueInList(options, true).build();
    sheet.getRange(2, columnId, lastRow - 1, 1).setDataValidation(rule);
  } else {
    // 大量选项的处理逻辑
    const dropdownSheetName = "DropdownLists";
    let dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);
    if (!dropdownSheet) {
      dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(dropdownSheetName);
    }

    // 找到空行插入新选项
    const startRow = dropdownSheet.getLastRow() + 1;
    const optionsColumn = options.map(option => [option]);
    dropdownSheet.getRange(startRow, 1, options.length, 1).setValues(optionsColumn);

    // 更新数据验证为范围引用
    const validationRange = `${dropdownSheetName}!A${startRow}:A${startRow + options.length - 1}`;
    const rule = SpreadsheetApp.newDataValidation().requireValueInRange(dropdownSheet.getRange(validationRange), true).build();
    sheet.getRange(2, columnId, lastRow - 1, 1).setDataValidation(rule);
  }
}

SheetManager.updateSheet方法实现

/**
       * @summary 用新数据覆盖表格内容
       * @async
       * @example
       * const sheetManager = new SheetManager(sheetName);
       * const data = [
       *  {'ID': '1029', 'DATE': '09/20/2023'},
       *  {'ID': '1030', 'DATE': '09/22/2023'}
       * ]
       * sheetManager.updateSheet(data)
       *
       * @param {Array<Object>} data 对象数组
       * @param {Object} columnOptions 列配置选项
       * @param {Array} columnOptions.extraColumns 需要额外添加的列
       * @param {String} columnOptions.individualSheetType 工作表类型：SCREENING, ACTION
       * @param {Object} clearOptions 表格清除选项
       * @param {Boolean} clearOptions.commentsOnly 仅清除批注
       * @param {Boolean} clearOptions.contentsOnly 仅清除内容
       * @param {Boolean} clearOptions.formatOnly 仅清除格式
       * @param {Boolean} clearOptions.validationsOnly 仅清除验证规则
       * @param {boolean} force 强制执行覆盖操作
       * @returns {Promise<void>}
       */
      async updateSheet(data, columnOptions = {
        extraColumns: null,
        individualSheetType: null
      }, clearOptions = {
        commentsOnly: false,
        contentsOnly: false,
        formatOnly: false,
        validationsOnly: false
      }, force = false) {
        const onlyReadIds = [this.configs.getConfigurationSpreadsheetId(), this.configs.getMasterSpreadsheetId(), this.configs.getLayoutSpreadsheetId()];
        if (onlyReadIds.includes(this.spreadsheetId) && !force) {
          console.error(`尝试覆盖只读表格ID ${this.spreadsheetId}，请检查异步函数是否正确使用await`);
          throw new Error(`尝试覆盖只读表格ID ${this.spreadsheetId}`);
        }
        if (!data) throw new TypeError('参数"data"必填');
        if (!Array.isArray(data)) throw new TypeError('参数"data"必须是Object类型的数组');
        if (data.length > 0 && data.some(element => !this.isObject(element))) throw new TypeError('数组元素必须是Object类型');
    
        // 初始化表头管理器，通过布局表格填充表头
    const headerManager = new HeaderManager(new SheetManager(this.sheetName, this.configs.getLayoutSpreadsheetId(), this.oauth2Client));

    // 获取当前工作表的表头
    let newHeader = await headerManager.get(columnOptions);

    // 写入无格式表头
    headerManager.setStandardHeader(this.sheet, newHeader);

    // 清除当前工作表内容
    const lastRow = this.sheet.getLastRow();
    const lastColumn = this.sheet.getLastColumn();
    const dataRange = this.sheet.getRange(2, 1, lastRow, lastColumn);
    dataRange.clear(clearOptions);
    dataRange.removeCheckboxes();

    // 将对象数组转换为二维数据矩阵
    const updatedDataMatrix = await this.jsonArrayToMatrix(data);

    // 写入带标准格式的表头
    headerManager.setStandardHeader(this.sheet, newHeader, true);

    // 获取数据内容（不含表头）
    const dataContent = updatedDataMatrix.slice(1);

    // 无数据则返回
    if (!dataContent || !dataContent[0]) return;

    // 覆盖工作表内容
    this.sheet.getRange(2, 1, dataContent.length, dataContent[0].length).setValues(dataContent);

    // 在指定列填充复选框
    this.setCheckboxOnQuestionMark(updatedDataMatrix);
    this.setNotesOnSpecificColumns(updatedDataMatrix)

    // 根据列类型格式化数据（日期、货币等）
    headerManager.setColumnFormat(newHeader, this.sheet);
  }

性能优化策略

1. 批量创建下拉验证规则

避免逐个调用setDropdownMenu，改为一次性收集所有下拉配置，批量处理选项写入与规则设置，减少Spreadsheet API调用次数：

async function setBatchDropdownMenus(dropdownConfigs) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const header = sheet.getDataRange().getValues()[0];
  const lastRow = sheet.getLastRow();
  const dropdownSheetName = "DropdownLists";
  let dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropdownSheetName);
  if (!dropdownSheet) {
    dropdownSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(dropdownSheetName);
  }
  let currentRow = dropdownSheet.getLastRow() + 1;

  const rangeRules = [];

  for (const config of dropdownConfigs) {
    const { options, columnName } = config;
    const columnId = header.indexOf(columnName) + 1;
    if (columnId === 0) continue; // 找不到目标列则跳过

    let rule;
    if (options.length <= 500) {
      rule = SpreadsheetApp.newDataValidation().requireValueInList(options, true).build();
    } else {
      // 批量写入选项到辅助表
      const optionsColumn = options.map(option => [option]);
      dropdownSheet.getRange(currentRow, 1, options.length, 1).setValues(optionsColumn);
      const validationRange = dropdownSheet.getRange(currentRow, 1, options.length, 1);
      rule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange, true).build();
      currentRow += options.length;
    }
    rangeRules.push({
      range: sheet.getRange(2, columnId, lastRow - 1, 1),
      rule: rule
    });
  }

  // 批量应用验证规则
  rangeRules.forEach(item => {
    item.range.setDataValidation(item.rule);
  });
}

// 调用方式
await setBatchDropdownMenus([
  { options: judicialActions, columnName: 'Judicial Action' },
  { options: subjects, columnName: 'Subject' },
  // 其他下拉配置项
]);

2. 公式批量写入，替换循环赋值

原代码中循环修改每个process对象的方式效率低下，改为在生成数据矩阵时批量填充公式：

// 先获取表头列索引（假设从headerManager获取newHeader）
const destinationUnitColIndex = newHeader.indexOf('Destination Unit');
const responsibleManagerColIndex = newHeader.indexOf('Responsible Manager');

// 批量生成带公式的行数据
const dataContent = data.map((process, index) => {
  // 基础行数据转换逻辑，根据process对象生成数组
  const row = Object.values(process);
  const rowIndex = index + 2;

  if (!process['Destination Unit']) {
    row[destinationUnitColIndex] = `=IFERROR(VLOOKUP(C:C;'Data Validations'!A:B,2,0);""`;
  }
  if (!process['Responsible Manager']) {
    row[responsibleManagerColIndex] = `=IFERROR(IF(E${rowIndex}="Screening";AA${rowIndex};"");"")`;
  }
  return row;
});

// 一次性写入表格
sheet.getRange(2, 1, dataContent.length, dataContent[0].length).setValues(dataContent);

3. 减少重复API调用

提前一次性获取表头列索引映射，避免在每个下拉设置中重复调用sheet.getDataRange().getValues()[0]；
复用辅助表的行指针，避免每次都重新获取getLastRow()；
使用RangeList批量处理多个单元格范围的操作，进一步减少API调用次数。

4. 启用Google Sheets API批量更新

如果已开启Google Sheets API权限，使用batchUpdate方法将多个操作打包为单个请求，大幅减少网络往返时间。例如可以将下拉规则设置、公式写入、格式调整等操作合并为一个批量请求。

5. 优化辅助表使用逻辑

对重复的下拉选项集合，检查辅助表中是否已存在，复用已有范围而非重复写入；
使用命名范围管理辅助表中的选项集合，便于后续维护与验证规则引用。

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