I have an Apps Script function that returns the background color of a cell, however it requires the argument to be in quotes, ex: COLOR("A1").
I would like it to accept arguments outside of quotes, so that I can perform functions within the COLOR() function.
Here is the script for my function:
/**
* Returns the background color of a cell
*
* @customfunction
*/
function COLOR(input) {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
const bcolors = sh.getRange(input).getBackgrounds();
return bcolors;
}
I attempted to pass it functions within the quotes also, however that did not work. I was expecting to be able to change the cell that my function finds the color of, but if it’s in quotes ex: COLOR(“A1”) I cannot change A1 to something else assuming there is something like an xlookup function in place of “A1”.
Here is an example function I would like to use:
=COLOR(XLOOKUP(AY35, 'Raw Data'!CO:CO, 'Raw Data'!CQ:CQ))
1
=COLOR(XLOOKUP(AY35, 'Raw Data'!CO:CO, 'Raw Data'!CQ:CQ)) * see note at end
Your formula above uses XLOOKUP which finds and returns a value. Assuming you intend to return the cell reference as a A1 notation string for the target value you will need to instead use INDEX and MATCH and then either ADDRESS or CELL to return the cell reference as a string.
To find the
ADDRESS
For example, using the ADDRESS function:
=COLOR(
ADDRESS(
[row_adjustment] + [formula_01_returns_row_number],
[formula_02_returns_col_number],4))
Example with your data
Since your ranges start in row 1 you would not need [row_adjustment]:
=COLOR(
ADDRESS(
MATCH(AY35, 'Raw Data'!CO:CO, 0),
COLUMN('Raw Data'!CQ:CQ))) // you could also use 95 or CO's column + 2
Example with an adjustment
If your ranges didn’t start in row 1 you would need to adjust the row number. In the example below this is done by adding the the range’s starting row to the result of the MATCH and subtracting 1:
=LET(range,'Raw Data'!CO6:CO,
COLOR(
ADDRESS(
ROW(INDEX(range,1))-1 + MATCH(AY35,range,0),
COLUMN(range)+2))
CELL
The Cell function behaves a bit differently as you can simply supply the indexed cell from your formula result instead of separately specifying row and column numbers. You do however need to strip out unnecessary characters which REGEXREPLACE does in the formula below:
=REGEXREPLACE(
CELL("address", [indexed_cell],
"(.*!)|($)","")
Example with your data
=REGEXREPLACE(
CELL("address",
INDEX('Raw Data'!CQ:CQ, MATCH(AY35, 'Raw Data'!CO:CO, 0))),
"(.*!)|($)","")
Functions Used in Formulas
ADDRESS CELL COLUMN INDEX MATCH REGEXREPLACE ROW VLOOKUP XLOOKUP
NOTE
* If you use VLOOKUP instead of XLOOKUP, your range can include the target column and use an index argument to specify the column (as opposed to retyping the sheet name and range):
=XLOOKUP(AY35, 'Raw Data'!CO:CO, 'Raw Data'!CQ:CQ)
=VLOOKUP(AY35, 'Raw Data'!CO:CQ, 2, 0)