Google Script向Google Sheets插入数据处理时间过长的优化求助

优化方案 #

你的脚本耗时波动大的核心原因是：写入过滤器单元格时，看板内所有依赖这些单元格的计算公式会实时触发全量重算，两次写入操作会触发至少两次重算，数据量越大重算耗时越高。以下是可落地的优化方案，调整后基本可以做到1秒内完成操作：

核心优化思路 #

1. 写入值之前先暂停电子表格的自动重算，所有值写入完成后再恢复计算，避免中途重复重算的无效开销
2. 规范API调用写法，减少不必要的I/O请求
3. 可选关闭撤销历史存储，进一步降低额外开销

优化后代码 #

默认值按钮函数

function Filters() {
  // 先获取当前电子表格全局实例
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheet = ss.getActiveSheet();
  // 保存原有的计算设置，后续恢复
  const originalCalcSetting = ss.getCalculationEngine().getCalculationSetting();
  
  try {
    // 暂停自动重算
    ss.getCalculationEngine().setCalculationSetting(SpreadsheetApp.CalculationSetting.MANUAL);
    // 不需要保留操作撤销可以保留这行，否则删掉即可
    SpreadsheetApp.disableUndoRedo();

    // 跨表取值（直接从全局实例获取，效率更高）
    const values_1 = ss.getRange('\'Aux AM\'!A11:A17').getValues();
    const values_2 = ss.getRange('\'Aux AM\'!A20:A22').getValues();

    // 写入当前表
    currentSheet.getRange('C2:C8').setValues(values_1);
    currentSheet.getRange('E4:E6').setValues(values_2);

    // 确保所有写入操作完成
    SpreadsheetApp.flush();
  } catch (e) {
    console.error('操作失败：' + e.message);
  } finally {
    // 无论是否出错都恢复原有设置
    ss.getCalculationEngine().setCalculationSetting(originalCalcSetting);
    SpreadsheetApp.enableUndoRedo();
  }
}

复制取值按钮函数

// 建议修改原test函数名，更方便识别功能
function copyFilterValues() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const currentSheet = ss.getActiveSheet();
  const originalCalcSetting = ss.getCalculationEngine().getCalculationSetting();
  
  try {
    ss.getCalculationEngine().setCalculationSetting(SpreadsheetApp.CalculationSetting.MANUAL);
    SpreadsheetApp.disableUndoRedo();

    const values_1 = ss.getRange('\'AM Metrics - Orders\'!C2:C8').getValues();
    const values_2 = ss.getRange('\'AM Metrics - Orders\'!E4:E6').getValues();

    currentSheet.getRange('C2:C8').setValues(values_1);
    currentSheet.getRange('E4:E6').setValues(values_2);

    SpreadsheetApp.flush();
  } catch (e) {
    console.error('操作失败：' + e.message);
  } finally {
    ss.getCalculationEngine().setCalculationSetting(originalCalcSetting);
    SpreadsheetApp.enableUndoRedo();
  }
}

额外优化建议 #

如果调整后还是有偶尔耗时高的情况，可以做如下调整：

• 把过滤器的默认值直接存在脚本的常量里，不需要每次从Aux AM表读取，减少一次跨表读请求
• 如果你的看板有很多使用IMPORTRANGE/QUERY/FILTER这类重负载公式，可以临时把这些公式所在的范围先清空，写完过滤器值之后再恢复公式（适合公式固定的场景）

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