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!A1does not match a tab namedArchive. 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'!A1or'O''Brien'!A1. Both forms are covered. - This reads
getFormulas()once per tab. It does not evaluateINDIRECTor 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!
