为何Google Sheets自定义函数extractFormulas自动触发执行？

我有一个extractFormulas函数，原本是作为单元格自定义公式（=extractFormulas(parm)）使用的，后来改成通过菜单点击触发，遍历目标行并用setValue(result)写入结果。但现在这个函数会多次自动触发，甚至不需要点击工作表中的菜单项，工作表似乎仍把它当作单元格自定义函数执行。

相关截图 #

相关代码 #

function onOpen() {    //create menu item
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Reconcile_Bank')
    .addItem('Get amounts from bank', 'extractFormulas')
    .addToUi();
}

function extractFormulas() {
  Logger.log('*********Starting extractFormulas()************');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jan");
  const lrow = sheet.getLastRow();
  const receiptsRange = sheet.getRange("C5:C" + lrow);
  var budgetEntries = receiptsRange.getValues();
  var budgetFormulas = receiptsRange.getFormulas();
  var result = '';
  var amount = 0;
  var idx = '';

  for (j=29; j<lrow; j++) {
    result = '';
    amount = sheet.getRange(j,12).getValue();
    Logger.log('row: '+ j + ' amount: '+ amount);
    if (amount = 0) continue;
    if (amount > 0) 
      result = 'deposit';
    else {
      idx = budgetFormulas.findIndex(([c]) => [...c.matchAll(/\b[.\d]*/g)].some(([e]) => e == -amount.toString()));
      if (idx > -1) {
        result = receiptsRange.offset(idx, 0, 1, 1).getA1Notation()+"|"+receiptsRange.offset(idx, -1, 1, 1).getValue();
        Logger.log('Formula found: '+result);
        continue;    //refers to outer loop for (j=29; j<lrow; j++) {
      }
      else  //not in a formula
        for (var i = 0; i<budgetEntries.length; i++) {
          if (budgetEntries[i][0].toString() == -amount.toString()) {
            result = 'C'+(i+5)+'|'+sheet.getRange(i+5,2).getValue();
            Logger.log('non-Formula found '+ result + 'matched to '+budgetEntries[i][0].toString());
            break;  //refers to inner loop for (var i = 0; i<budgetEntries.length; i++) {
          }
        }
    } 
    if (result == '') 
      result = 'not found';
    sheet.getRange(j,9).setValue(result);
  }
  return;
}

1. 清理残留的自定义公式引用 #

工作表中大概率还残留着之前作为自定义函数使用时的=extractFormulas(...)公式，这些公式会在工作表数据变化时自动触发函数执行：

• 按Ctrl+F打开查找框，搜索=extractFormulas，定位所有包含该公式的单元格。
• 删除这些单元格里的公式，替换为静态值或直接清空。

2. 重命名函数（彻底规避缓存问题） #

如果担心有隐藏的公式残留或Google Sheets的函数缓存问题，直接给函数改名：

• 将原函数extractFormulas改为新名称，比如extractBankAmounts。
• 同步修改onOpen中的菜单调用代码：

  ui.createMenu('Reconcile_Bank')
    .addItem('Get amounts from bank', 'extractBankAmounts')
    .addToUi();
  

• 保存后刷新工作表，旧的自定义公式会因找不到函数报错，方便你定位遗漏的清理点。

3. 修复代码中的逻辑与性能问题 #

你的代码存在两个明显问题，可能加重异常触发：

• 循环里的if (amount = 0)是赋值操作，不是相等判断，应改为if (amount === 0)。
• 循环中多次调用getRange和setValue会触发大量API请求，不仅慢还可能导致不必要的函数触发，建议改成批量读写：

  function extractBankAmounts() {
    Logger.log('*********Starting extractBankAmounts()************');
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jan");
    const lrow = sheet.getLastRow();
    const receiptsRange = sheet.getRange("C5:C" + lrow);
    var budgetEntries = receiptsRange.getValues();
    var budgetFormulas = receiptsRange.getFormulas();
    
    // 批量获取需要处理的金额数据
    var amountRange = sheet.getRange(29, 12, lrow - 29, 1);
    var amounts = amountRange.getValues();
    // 准备结果数组
    var results = [];
  
    for (let j=0; j<amounts.length; j++) {
      let result = '';
      let rowNum = 29 + j;
      let amount = amounts[j][0];
      Logger.log('row: '+ rowNum + ' amount: '+ amount);
      
      if (amount === 0) {
        results.push(['']);
        continue;
      }
      
      if (amount > 0) {
        result = 'deposit';
      } else {
        const targetAmount = (-amount).toString();
        // 查找公式中的匹配项
        let idx = budgetFormulas.findIndex(([c]) => 
          [...c.matchAll(/\b\d+(\.\d+)?\b/g)].some(([e]) => e === targetAmount)
        );
        if (idx > -1) {
          result = receiptsRange.offset(idx, 0, 1, 1).getA1Notation()+"|"+receiptsRange.offset(idx, -1, 1, 1).getValue();
          Logger.log('Formula found: '+result);
        } else {
          // 查找静态值中的匹配项
          let matchIdx = budgetEntries.findIndex(([val]) => val.toString() === targetAmount);
          if (matchIdx > -1) {
            result = 'C'+(matchIdx+5)+'|'+sheet.getRange(matchIdx+5,2).getValue();
            Logger.log('non-Formula found '+ result + 'matched to '+targetAmount);
          } else {
            result = 'not found';
          }
        }
      }
      results.push([result]);
    }
    // 批量写入结果
    sheet.getRange(29, 9, results.length, 1).setValues(results);
  }
  

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