A sheet with 4,000 formula cells usually has about six distinct formulas, copied down. formulaInventoryForPrompt(range) groups cells by their R1C1 formula (identical logic, different rows), and returns each distinct formula with its cell count and one example A1 cell. That's a short list you can paste into Co-Pilot instead of the whole sheet.
When to use
- Asking Co-Pilot to explain or rewrite a sheet's formulas
- Seeing which cells break the pattern (a count of 1 in a column of 500)
- Documenting a spreadsheet before you hand it off
How to use this snippet
/**
* Group a range's formulas by R1C1 and count them.
*
* @param {Range} range
* @return {{r1c1: string, formula: string, count: number, example: string}[]}
* most common first; `formula` is the A1 form at `example`
*/
function formulaInventoryForPrompt(range) {
var r1c1 = range.getFormulasR1C1();
var a1 = range.getFormulas();
var row0 = range.getRow();
var col0 = range.getColumn();
var groups = {};
var order = [];
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;
}
for (var r = 0; r < r1c1.length; r++) {
for (var c = 0; c < r1c1[r].length; c++) {
var f = r1c1[r][c];
if (!f) continue;
if (!groups[f]) {
groups[f] = { r1c1: f, formula: a1[r][c], count: 0, example: colName(col0 + c) + (row0 + r) };
order.push(f);
}
groups[f].count++;
}
}
return order.map(function (f) { return groups[f]; })
.sort(function (x, y) { return y.count - x.count; });
}
Example
function logFormulaInventory() {
var sh = SpreadsheetApp.getActive().getSheetByName('Pipeline');
formulaInventoryForPrompt(sh.getDataRange()).forEach(function (f) {
console.log(f.count + ' cells, e.g. ' + f.example + ': ' + f.formula);
});
}
// 412 cells, e.g. F2: =D2*E2
// 1 cells, e.g. F87: =D87*E87*0.9
// 1 cells, e.g. H1: =ARRAYFORMULA(IF(A2:A="",,VLOOKUP(A2:A,Rates!A:B,2,0)))
Tips:
- A count of 1 sitting next to a count of 400 in the same column is usually a hand-edited cell. Check it first.
- Results contain formulas only, not values, so no customer data goes into the prompt.
- Large ranges cost two reads (
getFormulasR1C1andgetFormulas), not one per cell.
Tip: paste the output into NitroGAS Co-Pilot and ask "explain each of these in one line and flag the odd ones out."
Happy Coding!
