google sheets script: display cell addresses of color filled cells

Question

i don't know much coding/javascript so was hoping i could get some help.

i'd like help to script a function that would allow me to find the cell addresses of all cells with grey fill (#dbd9d9) and display the result as a list. this is close to what i need, but it only displays the cell address of first and last filled cells of a specified row.

the following works (sorry, i didn't save the link of where i found it), but it returns the hex of each cell in the range:

function getBgColor(input) {
var bgs = SpreadsheetApp.getActiveSheet().getRange("C1:Z6").getBackgrounds(),
colors = [];
for(var i = 0; i < bgs.length; i++){
colors.push(bgs[i]);
}
return colors;
}

could this be adapted to return cell addresses instead?

thanks for reading.

Answer 1

I believe your goal is as follows.

• You want to retrieve the cell coordinate as A1Notation by searching the background color from the cells "C1:Z6".

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor and save the script.

When you use this script, please put a custom function of =getBgColor("#dbd9d9"). By this, the cell coordinates of a1Notation are returned as an array.

function getBgColor(input = "#dbd9d9") {
  // Ref: https://stackoverflow.com/a/53678158
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : "";

  const range = SpreadsheetApp.getActiveSheet().getRange("C1:Z6");
  const offsetX = range.getColumn();
  const offsetY = range.getRow();
  const backgrounds = range.getBackgrounds();
  const a1Notations = backgrounds.reduce((ar, r, i) => {
    r.forEach((c, j) => {
      if (c == input) {
        ar.push(`${columnIndexToLetter_(offsetX + j - 1)}${offsetY + i}`); // or ar.push([offsetX + j, offsetY + i]);
      }
    });
    return ar;
  }, []);
  return a1Notations;
}

Testing:

When this script is run, the following result is obtained.

Note:

• From your question, I guessed that your cell addresses might be a1Notation. If your cell addresses is the cell numbers of columns and rows, please modify the above script as follows. By this, the result including the cell numbers of column and row is returned.

  • From

      ar.push(`${columnIndexToLetter_(offsetX + j - 1)}${offsetY + i}`);
    

  • To

      ar.push([offsetX + j, offsetY + i]);