CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Aitch
Aitch

Reputation: 942

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

Is there a way to count the number of formulae in a complex Google sheets doc (multi tabbed)

I have reworked an old template using functions like ARRAYFORMULA and so on and i would really like to determine the reduction in complexity - number of formulae being one proxy for this. There were many thousands of row-by-row, column-by-column copy/paste calcs originally.

Upvotes: 3

Views: 298

Answers (3)

Tanaike
Tanaike

Reputation: 201358

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:

Upvotes: 2

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

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;  
}

Upvotes: 2

TheMaster
TheMaster

Reputation: 50445

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
  );

Upvotes: 1

Related Questions