List all functions used on a Google Sheet using Apps Script

Context

Recently, I had the need to detect all the functions being used on a spreadsheet and after looking for a solution online, found a VBA Macro to run on the Excel version of the file and after having done so on my MacBook Pro 2019, ran into a bit of a snag where I eventually had to force quit the application.

Also couldn't find a simple Google Apps Script code that could help with that and figured, I'd just give it a shot (assuming others might find it handy someday 🤞🏾)

References

• getFormulas() Apps Script method
• RegEx to parse spreadsheet functions from a cell. Spoiler alert: It's \b(\w+)( (ref: https://regex101.com/r/ADA42H/1)

Code

I created 2 functions and added 1 variation like so —

1. getFunctionsPerSheet method which lists a unique set of functions being used per page & getFunctions which simply fetches a unique list of functions being used across the entire workbook
2. I've also added a toggle to write the list of functions on a new sheet too via ADD_DATA_TO_SHEET variable — default is set to false so you'll need to manually change it to true in case you wish to have the data being produced by the script written on a new sheet

Here's the getFunctions method —

const ADD_DATA_TO_SHEET = false;

function getFunctions() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let functions = [];
  sheets.forEach(sheet => {
    let formulas = sheet.getDataRange().getFormulas();

    // flatten 2D array
    formulas = [].concat(...formulas).filter(Boolean);

    formulas.forEach(el => {
      const values = el ? el.match(/\b(\w+)\(/gm) : null;
      if (values) {

        // new Set() is used to remove duplicates
        [...new Set(values.toString().replace(/\(/gm, "").split(","))]
          .forEach(func => functions.push(func));
      }
    })
  })

  functions = [...new Set(functions)];
  console.log(functions);

  if (ADD_DATA_TO_SHEET) {
    const rowData = [];
    functions.forEach(func => rowData.push([func]));
    ss.insertSheet().setName(`Functions | ${new Date().toLocaleString()}`)
      .appendRow(["Function"])
      .getRange(2, 1, rowData.length, 1).setValues(rowData);
  }
}

And here's the getFunctionsPerSheet method —

const ADD_DATA_TO_SHEET = false;

function getFunctionsPerSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  let functions = [];
  sheets.forEach(sheet => {
    const sheetName = sheet.getSheetName();
    let formulas = sheet.getDataRange().getFormulas();

    // flatten 2D array
    formulas = [].concat(...formulas).filter(Boolean);

    formulas.forEach(el => {
      const values = el ? el.match(/\b(\w+)\(/gm) : null;
      if (values) {

        // new Set() is used to remove duplicates
        [...new Set(values.toString().replace(/\(/gm, "").split(","))]
          .forEach(func => functions.push([func, sheetName]));
      }
    })
  })

  // https://www.kirupa.com/javascript/removing_duplicate_arrays_from_array.htm
  functions = Array.from(new Set(functions.map(JSON.stringify)), JSON.parse);
  console.log(functions);

  if (ADD_DATA_TO_SHEET) {
    ss.insertSheet().setName(`Functions | ${new Date().toLocaleString()}`)
      .appendRow(["Function", "Sheet name"])
      .getRange(2, 1, functions.length, 2).setValues(functions);
  }
}

New learnings

1. Learnt that one could filter empty elements from an array by just using BOOLEAN within the .filter() array method 🤯
2. S/O to folks at Kirupa.com for this amazing article about Removing Duplicate Arrays from an Array of Arrays (can also be referred as 2D arrays, maybe?)