code to find cell address for script - google sheets

Question

I am using Google Sheets.

I have a spreadsheet with a column 'B' containing data which is conditionally formatted with background colors.

I need to place the hex code corresponding to the color in each cell of 'B' into the neighboring cell in 'C'.

I have the following code, which works:

function GetBackColorCode(cell) 
{
return (SpreadsheetApp.getActiveSheet().getRange(cell).getBackground());
}

As you can see, however, this code requires manually inserting the proper column and row number in each and every cell. Thus, if I want the hex. code for the color in B14, I insert the following in C14:

=GetBackColorCode("b14")

This method would require me to manually imput about three hundred cells. Is there no way to automatically pick up the cell reference, with, for example, a 'this.someFunction()' call? Such that I can paste the call '=GetBackColorCode(this.someFunction())' into all the cells in column 'C' and each will automatically furnish the necessary reference to the cell to its immediate left?

JPV JPV · Accepted Answer · 2019-12-27T08:12:50

Change your script to

function getHexCodes(range) {
  return SpreadsheetApp.getActiveSheet()
 .getRange(range).getBackgrounds();
}

then enter in C2 (or whatever your 'start row' will be)

=getHexCodes("B2:B")

code to find cell address for script - google sheets

1 Answers