如何将卡顿的Google Sheets函数转换为Google Script优化性能

你当前使用的嵌套数组公式卡顿的核心原因是，公式对A2:A10000区间的每一行都执行了两次SUMIF条件匹配和区间遍历运算，整体运算复杂度是O(n²)，即使数据量只有几千行也会产生大量重复计算，拉高表格负载。

可以通过Google Apps Script实现完全一致的功能，运算复杂度仅为O(n)，不会触发工作表频繁重算，能彻底解决卡顿问题。

方案1：自定义函数用法 #

直接在单元格调用即可，使用逻辑和原生工作表函数一致：

function RUNNING_BALANCE() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 读取G1初始余额
  const initial = ss.getRange("G1").getValue();
  // 仅读取B列有数据的行，避免遍历无效空行
  const validRowCount = ss.getRange("B:B").getValues().filter(String).length;
  const data = ss.getRange(2, 1, validRowCount, 2).getValues();
  
  let currentBalance = initial;
  const result = [];
  // 单次遍历完成所有累计计算
  data.forEach(row => {
    const type = row[0];
    const amount = row[1];
    if(type === "Received") currentBalance += amount;
    if(type === "Given") currentBalance -= amount;
    result.push([currentBalance]);
  })
  return result;
}

使用步骤：

• 打开对应Google表格，点击顶部菜单栏「扩展程序」>「Apps Script」进入脚本编辑器
• 删除编辑器默认的空白代码，粘贴上述代码后点击保存，自定义项目名称后关闭编辑器
• 回到表格，在你原本放置数组公式的单元格输入=RUNNING_BALANCE()即可自动输出所有行的累计结果

方案2：手动触发更新（更省资源） #

如果数据更新频率不高，建议用该方案，仅在你需要的时候才重算数据，完全不占用表格实时运算资源：

// 打开表格时自动生成自定义菜单
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('自定义工具')
    .addItem('更新累计余额', 'updateBalance')
    .addToUi();
}

// 手动更新余额并直接写入指定列
function updateBalance() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const initial = ss.getRange("G1").getValue();
  const validRowCount = ss.getRange("B:B").getValues().filter(String).length;
  const data = ss.getRange(2, 1, validRowCount, 2).getValues();
  
  let currentBalance = initial;
  const result = [];
  data.forEach(row => {
    const type = row[0];
    const amount = row[1];
    if(type === "Received") currentBalance += amount;
    if(type === "Given") currentBalance -= amount;
    result.push([currentBalance]);
  })
  // 结果默认写入C2开始的列，可自行修改getRange的第二个参数调整输出列
  ss.getRange(2, 3, result.length, 1).setValues(result);
}

保存脚本后重新打开表格，顶部菜单栏会出现「自定义工具」选项，需要更新数据时点击「更新累计余额」即可。

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