Google Sheets: Find All Cells Using a Named Range

Question

I need to find all references to a Named Range (which is a single cell) in any cell in a sheet. Any method (or script) to do this?

Context: I'm calculating the lift forces on a boat sail at different wind strengths. The equation uses a "Coefficient of Lift" value (CL), which is my NamedRange. However, I now need to change that so at different wind speeds, a different CL is used, and scrap the NamedRange.

I can do that using nested IF() statements, no problem, but first I have to find all cells that currently use the NamedRange. MTIA!

Answer 1

I believe your goal as follows.

• You want to retrieve the cells which have the formulas using NamedRange.
• In order to achieve your goal, you can use Google Apps Script.

In this answer, I would like to propose the following flow using Google Apps Script.

1. Retrieve all named ranges.
2. Using TextFinder, retrieve the cells which have the formulas using NamedRange.

Please copy and paste the following script to the script editor of the container-bound script of the Spreadsheet. And run the function myFunction1 or myFunction2(). By this, you can see the result values at the log.

Sample script 1:

In this sample script, the A1Notations of cells which use the formulas with the NamedRanges are returned as an array.

function myFunction1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");  // Please set the sheet name.
  
  // 1. Retrieve all named ranges.
  const namedRanges = ss.getNamedRanges().map(e => e.getName());

  // 2. Using TextFinder, retrieve the cells which have the formulas using NamedRange.
  const values = namedRanges.reduce((ar, e) => {
    sheet.createTextFinder(e).matchCase(true).matchFormulaText(true).findAll().forEach(f => ar.push(f.getA1Notation()));
    return ar;
  }, []);
  const res = [...new Set(values)];

  console.log(res)
}

The sample output value is as follows.

["A1", "B1",,,]

Sample script 2:

In this sample script, the A1Notations of cells which use the formulas with the NamedRanges are returned as an JSON object.

function myFunction2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");  // Please set the sheet name.

  // 1. Retrieve all named ranges.
  const namedRanges = ss.getNamedRanges().map(e => ({name: e.getName(), range: `'${e.getRange().getSheet().getSheetName()}'!${e.getRange().getA1Notation()}`}));
  
  // 2. Using TextFinder, retrieve the cells which have the formulas using NamedRange.
  const res = namedRanges.reduce((o, {name, range}) => {
    sheet.createTextFinder(name).matchCase(true).matchFormulaText(true).findAll().forEach(f => {
      const r = f.getA1Notation();
      const temp = {namedRange: name, rangeOfNamedRange: range};
      o[r] = o[r] ? o[r].concat(temp) : [temp];
    });
    return o;
  }, {});

  console.log(res)
}

The sample output value is as follows. The A1Notation of cell which uses the NamedRange is used as the key. And the NamedRange and the range of NamedRange are used as the value. The values can be also retrieved from the cell which uses multiple NamedRanges.

{
  "A1":[{"namedRange":"sample1","rangeOfNamedRange":"'Sheet2'!A1"}],
  "B1":[{"namedRange":"sample1","rangeOfNamedRange":"'Sheet2'!A1"},{"namedRange":"sample2","rangeOfNamedRange":"'Sheet2'!B1"}],
  "C1":[{"namedRange":"sample3","rangeOfNamedRange":"'Sheet3'!A1"}],
  ,
  ,
  ,
}

References:

• getNamedRanges()
• Class TextFinder

Added:

I would need to run it for just one Named Range, as it looks like all cells for ALL Named Ranges have been logged, so I don't now which is which?

From your above replying, I thought that the pattern 1 might be suitable. So when above script is modified, it becomes as follows.

Modified script:

function myFunction1() {
  const sheet = ss.getSheetByName("Sheet1");  // Please set the sheet name.
  const namedRange = "###";  // Please set the name of namedRange.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const res = sheet.createTextFinder(namedRange).matchCase(true).matchFormulaText(true).findAll().map(f => f.getA1Notation());
  console.log(res)
}