多Google Sheets单单元格求和JS公式故障排查与解决方案咨询

Hey there, let's break down why your custom function stopped working and get it back to running smoothly!

Why Your Original Script Failed #

The core issue with your initial code is that it makes a separate API call to getRange() and getValue() for every single worksheet. As your number of sheets grows, this adds up fast—Google Sheets custom functions have a strict execution time limit (typically around 30 seconds), so you end up hitting a timeout. The "unrecognized function" error might also pop up if repeated interrupted executions caused caching or permission glitches.

Fixed Optimized Script #

This version cuts down API calls drastically by fetching all target cells in one batch, which is far more efficient and avoids timeouts. It also handles non-numeric values (like empty cells or text) gracefully to prevent errors:

function sumCells(cell) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();

  // Create a list of range strings for all sheets (e.g., "Sheet1!B5", "Sheet2!B5")
  const targetRanges = allSheets.map(sheet => `${sheet.getName()}!${cell}`);

  // Fetch all values in one go (way more efficient than individual calls)
  const cellValues = ss.getRangeList(targetRanges).getValues();

  // Calculate the total, ignoring non-numeric values
  return cellValues.reduce((total, [value]) => {
    const numericValue = Number(value);
    return total + (isNaN(numericValue) ? 0 : numericValue);
  }, 0);
}

Quick Fixes for "Unrecognized Function" #

If the function still doesn't load properly:

• Save the script in the Apps Script editor, then refresh your Google Sheet.
• Run the function once manually in the Apps Script editor (this triggers a permission prompt if access expired).
• Clear your browser cache or open the sheet in incognito mode to rule out caching issues.

Alternative No-Script Solution (Limited Use Case) #

If you'd rather avoid scripts and your worksheets follow a consistent naming pattern (e.g., "Sheet1", "Sheet2"), you can use a built-in formula combo:

=SUMPRODUCT(ARRAYFORMULA(VALUE(INDIRECT("Sheet"&SEQUENCE(10)&"!B5"))))

Adjust SEQUENCE(10) to match your total number of sheets, and replace B5 with your target cell. Note this won't work for custom-named sheets (like "Sales2024" or "Q1Data"), so the script remains the better choice for dynamic, arbitrarily named sheets.

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