How to count the Quantity of formulae in a Google Sheets Document?

I believe you want to retrieve the number of cells including the formulas on all sheets in a Google Spreadsheet. If my understanding is correct, I would like to propose to use Google Apps Script. So how about this sample script?

I thought that when various approaches are proposed, they might be useful for users. So I would like to propose the following 2 sample scripts.

Pattern 1:

In this pattern, all sheets are retrieved from the active Spreadsheet, and the number of cells including formulas are retrieved from each sheet. In order to count the number of elements, the 2 dimensional array is flatten.

Sample script:

function myFunction() {
  const res = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()
    .reduce((n, s) => n += s.getDataRange().getFormulas().flat().filter(String).length, 0);
  console.log(res)
}

Pattern 2:

In this pattern, the formulas are retrieved using TextFinder. In this case, all formulas can be retrieved from all sheets in the active Spreadsheet without using the loop process. The detail process for searching is run at the internal server of Google side. In my environment, it is found that the process cost of this pattern was lower than that of pattern 1. Under the same condition, the cost of pattern 2 was about 1/5 of the cost of pattern 1.

Sample script:

function myFunction() {
  const res = SpreadsheetApp
    .getActiveSpreadsheet()
    .createTextFinder("^\\=")
    .matchFormulaText(true)
    .useRegularExpression(true)
    .findAll().length;
  console.log(res)
}

Note:

• Both patterns return the same result.
• Please run the script with enabling V8.

References:

• reduce()
• getFormulas()
• flat()
• filter()
• Class TextFinder

Answer:

You can get all formulae in all cells in all Sheets, and add one to a counter if the cell starts with the = character.

Code:

function getAllFormulae() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var count = 0;
  
  for (var i = 0; i < ss.length; i++) {
    var currSheetValues = ss[i].getDataRange().getFormulas();
    for (var j = 0; j < currSheetValues.length; j++) {
      for (var k = 0; k < currSheetValues[j].length; k++) {
        if (currSheetValues[j][k].startsWith("=") == 1) {
          count++
        }
      }
    }
  }  
  return count;  
}

Use getFormulas to get all formulas and count all elements that are not empty:

const getTotalFormulasCount = () =>
  console.log(getNumFormulas_(SpreadsheetApp.getActive().getSheets()));

/**
 * @param {GoogleAppsScript.Spreadsheet.Sheet[]|string[][]|string[]} arr Array of sheets or 1D or 2D array of formulas
 * @returns {number} count of formulas present in arr
 */
const getNumFormulas_ = arr =>
  arr.reduce(
    (count, el) =>
      count +
      (Array.isArray(el) //if el is array, recurse
        ? getNumFormulas_(el)
        : el.getRange //if el is sheet, recurse over it's formulas
        ? getNumFormulas_(el.getDataRange().getFormulas())
        : Number(el !== '')),
    0
  );