Google apps script - get A1 cell notation of each element in an array

Question

I have a function that I want to search through a given sheet and return an array of cells containing the custom function "POPULATE". The function currently returns a string array of each instance of "=POPULATE".

function formulaSearch() {
  var sheet = spreadsheet.getSheetByName('test');
  var range = sheet.getDataRange();
  var values = range.getValues();
  var formulas = range.getFormulas();

  // Search for all cells with the POPULATE function and put them into array
  var cells = [];
  for (var row in formulas) {
    for (var col in formulas[row]) {
      if (formulas[row][col].indexOf("=POPULATE") > -1 ) {
        cells.push(formulas[row][col]);
        Logger.log(cells); 
      } 
    }
  }
  return cells;
}

How can I return an array of cells in A1 notation of each cell that contains the formula? I know of the .getA1Notation() method but I can't convert the elements in the returned array to the Range data type.

formulas[row][col] is just a String containing the cell's formula, not the actual cell. — Bobbyrogers

Bobbyrogers Bobbyrogers · Accepted Answer · 2015-07-06T18:00:33

One way is to get the cell from the range variable and then get the A1 notation from that:

for (var row in formulas) {
  for (var col in formulas[row]) {
    if (formulas[row][col].indexOf("=POPULATE") > -1 ) {
      cell = range.getCell(row+1, col+1).getA1Notation();
      cells.push(cell);
      Logger.log(cells); 
    } 
  }
}

EDIT: Ranges are not 0 indexed. Added 1 to row and col in getCell(). Thanks for pointing that out @tsc333!

Google apps script - get A1 cell notation of each element in an array

1 Answers