A #REF! on tab 9 can sit there for weeks until someone presents the dashboard. findErrorCells(sheetOrSpreadsheet) scans one tab or every tab and returns [{ sheet, a1, error, formula }] for each cell that's showing a formula error (#REF!, #N/A, #VALUE!, #DIV/0!, #NAME?, #NUM!, #NULL!, #ERROR!, #SPILL!). It reads each tab with one getDisplayValues() and one getFormulas() call, so it's fast even on big spreadsheets.
When to use
- A “find broken formulas” menu item before a client meeting
- Nightly checks that email you when a new error appears
- Cleaning up a spreadsheet you've just inherited
How to use this snippet
/**
* List every cell displaying a formula error.
* One getDisplayValues() + one getFormulas() read per tab.
*
* @param {Sheet|Spreadsheet} sheetOrSpreadsheet
* @return {{sheet: string, a1: string, error: string, formula: string}[]}
*/
function findErrorCells(sheetOrSpreadsheet) {
var ERR = /^#(REF!|N\/A|VALUE!|DIV\/0!|NAME\?|NUM!|NULL!|ERROR!|SPILL!)/;
var sheets = sheetOrSpreadsheet.getSheets ? sheetOrSpreadsheet.getSheets() : [sheetOrSpreadsheet];
var out = [];
function a1(row, col) {
var s = '';
while (col > 0) { var m = (col - 1) % 26; s = String.fromCharCode(65 + m) + s; col = Math.floor((col - 1) / 26); }
return s + row;
}
sheets.forEach(function (sh) {
var range = sh.getDataRange();
if (range.getNumRows() === 0 || range.getNumColumns() === 0) return;
var shown = range.getDisplayValues();
var formulas = range.getFormulas();
for (var r = 0; r < shown.length; r++) {
for (var c = 0; c < shown[r].length; c++) {
var m = ERR.exec(shown[r][c]);
if (m && formulas[r][c]) {
out.push({ sheet: sh.getName(), a1: a1(r + 1, c + 1), error: m[0], formula: formulas[r][c] });
}
}
}
});
return out;
}
Example
function logBrokenFormulas() {
var errors = findErrorCells(SpreadsheetApp.getActive());
errors.forEach(function (x) {
console.log(x.sheet + '!' + x.a1 + ' ' + x.error + ' ' + x.formula);
});
console.log(errors.length + ' error cell(s)');
}
// Dashboard!C4 #REF! =SUM(Old!B2:B40)
// Invoices!H12 #DIV/0! =G12/F12
Tips:
- Only cells with a formula are reported, so a typed “#N/A” label doesn't count.
- An
ARRAYFORMULAthat errors shows up once, on the cell holding the formula. #N/Afrom a lookup is sometimes expected (“not found”). Wrap those inIFNAor filter them out of the results.- The
a1value plus the tab's gid makes a jump link:ss.getUrl() + '#gid=' + sh.getSheetId() + '&range=' + a1.
Tip: NitroGAS Co-Pilot can turn this into a menu sweep that writes an Errors tab with jump links. Paste your onOpen.
Happy Coding!
