Find Formulas Referencing Sheet

findFormulasReferencingSheet(sheetName) walks every tab in the active spreadsheet and returns each cell whose formula mentions that tab, in both the quoted ('Archive'!A1) and unquoted (Archive!A1) forms. Each hit is {sheet, address, formula}, ready to write into an audit tab.

When to use

  • Checking what would break before you delete or rename a tab
  • Finding leftover references after you archive a sheet
  • Reviewing cross-tab formulas before a restructure

How to use this snippet

/** * Find every formula in the active spreadsheet that mentions a tab. * Matches both quoted ('Sheet'!A1, with '' escaping apostrophes in the * name) and unquoted (Sheet!A1) references, case-insensitively. * Bare A1 references on the same tab are not included — they do not * mention the tab by name. Constructed names (INDIRECT of pieces) are * not detected. * * @param {string} sheetName * @return {{sheet: string, address: string, formula: string}[]} */ function findFormulasReferencingSheet(sheetName) { var name = String(sheetName == null ? '' : sheetName); if (!name) throw new Error('findFormulasReferencingSheet: sheetName required'); var ss = SpreadsheetApp.getActive(); var quoted = "'" + name.replace(/'/g, "''") + "'!"; var unquoted = name + '!'; function esc(s) { return s.replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); } // Sheet references are case-insensitive in Sheets. var reQuoted = new RegExp(esc(quoted), 'i'); var reUnquoted = new RegExp('(^|[^A-Za-z0-9_\'])' + esc(unquoted), 'i'); function colName_(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; } var out = []; ss.getSheets().forEach(function (sheet) { var lastRow = sheet.getLastRow(); var lastCol = sheet.getLastColumn(); if (!lastRow || !lastCol) return; var formulas = sheet.getRange(1, 1, lastRow, lastCol).getFormulas(); var tab = sheet.getName(); for (var r = 0; r < formulas.length; r++) { for (var c = 0; c < formulas[r].length; c++) { var f = formulas[r][c]; if (!f) continue; reQuoted.lastIndex = 0; reUnquoted.lastIndex = 0; if (reQuoted.test(f) || reUnquoted.test(f)) { out.push({ sheet: tab, address: colName_(c + 1) + (r + 1), formula: f }); } } } }); return out; }

Example

function auditArchiveRefs() { var hits = findFormulasReferencingSheet('Archive'); var rows = [['Sheet', 'Cell', 'Formula']].concat( hits.map(function (h) { return [h.sheet, h.address, h.formula]; }) ); var ss = SpreadsheetApp.getActive(); var out = ss.getSheetByName('Tab audit') || ss.insertSheet('Tab audit'); out.clear(); out.getRange(1, 1, rows.length, 3).setValues(rows); }

Notes

  • Sheet references in Google Sheets are case-insensitive; the search is too.
  • ArchiveBackup!A1 does not match a tab named Archive. The unquoted form requires a non-name character (or the start of the formula) before the name.
  • Names that contain spaces, punctuation, or apostrophes appear in formulas as 'My Sheet'!A1 or 'O''Brien'!A1. Both forms are covered.
  • This reads getFormulas() once per tab. It does not evaluate INDIRECT or look at chart ranges, named ranges, or data validation that might also point at the tab.
  • For a compact inventory of distinct formulas, see Formula Inventory For Prompt.

Happy Coding!

NitroGAS Chrome Extension

Want this snippet handy inside the Apps Script editor? NitroGAS gives you free snippets — plus optional Co-Pilot when you want a boost (Day Pass — $3, or 1-week, 1-month, or yearly passes — no subscription). Happy Coding!

Get the Extension