Google Sheets Script: Function to Find Text Including in Formulas

0
votes

As I use a lot of formulas in an Analysis spreadsheet, I need to be able to search the formulas in all cells in all sheets for any cell with the specified search text, preferably case-insensitive.

For example, search for "ce-z", and find all cells that has the formula: "=vlookup(round(D29,0),Sails_Sheet, match("CE-z",Sails_Sheet_Headers, 0),false)"

Unfortunately, Class TextFinder does not do this.

Is there a way to loop through every cell in every sheet and examine it's function?

2
google-apps-scriptgoogle-sheets
what do you mean by: search for "ce-z", and find all cells that has the formula: "=vlookup(round(D29,0),Sails_Sheet, match("CE-z",Sails_Sheet_Headers, 0),false)" . You want to search for ce-z or for this formula? – soMario
Sorry, I wanted to find the string "CE-z" in the formula. However, as I posted as an answer, all I had to do was add ".matchFormulaText(true)" to creatTextFinder. Thanks anyway. – maxhugen

2 Answers

0
votes

Found a method in Class TextFinder that does exactly what I wanted: matchFormulaText(boolean). Full function follows, which calls a dialog from my custom menu, as posted by @Tanaike, with my one addition:

function c_FindText() {
// Author: Tanaike : tanaikech.github.io/2020/06/26/search-dialog-sample-using-textfinder-with-google-apps-script/
// Date: 20102-12-10
// Purpose: Find text in all sheets, eg a NamedRange, cell reference (eg 'BF133'), custom function, etc
  const htmlStr = `
  <input type="text" id="searchText" name="searchText">
    <button id="previous" onclick="googleScript(c > 0 ? c - 1 : 0)">PREVIOUS</button>
    <button id="next" onclick="googleScript(++c)">NEXT</button>
    <script>
      let c = -1;
  const googleScript = (i) =>
    google.script.run.withSuccessHandler(cc => c = cc).searchText(document.getElementById("searchText").value, i);
  </script>
`;
  const htmlObj = HtmlService.createHtmlOutput(htmlStr)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .setWidth(350)
    .setHeight(50);
  SpreadsheetApp.getUi().showModelessDialog(htmlObj, "Find Text");
}
function searchText(searchValue, c) {
  const ranges = SpreadsheetApp.getActiveSpreadsheet()
    .createTextFinder(searchValue)
    .matchFormulaText(true)         // MH: added to also search Formulas
    .findAll();
  if (c < ranges.length) {
    ranges[c].activate();
    return c;
  }
  return --c;
}
0
votes

You can simply check each cell from every sheet for the value you want. For this, you can use something similar to this:

Snippet

function checkCells() {
    var spreadsheet = SpreadsheetApp.openById('SPREADSHEET_ID');
    var sheets = spreadsheet.getSheets();
    for (let i = 0; i < sheets.length; i++) {
        let range = sheets[0].getDataRange();
        for (let j = 0; j < range.getLastRow(); j++)
            for (let k = 0; k < range.getLastColumn(); k++)
                if (range[j][k].getValue() == "VALUE" || range[j][k].getFormula() == "A1_NOTATION_FORMULA")
                    // do things 

    }

}

Explanation

The script above loops through all the cells from all the sheets of a spreadsheet. In order to get all the sheets the getSheets() method has been used. As for checking the contents of each cell this is done by getting the value of a cell by using:

  • getValue() - in case you need a specific value of a cell;

  • getFormula() - in case you need the formula for a cell;

Reference