Google Sheets: "getrange is not a function"

Question

Sorry, I am a beginner in script development. Just tried to access the background color of a cell (returned as color string) and being stuck with the error message "TypeError: getrange is not a function" in line 4.

function GetCellColor(input) 
{ 
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var cell = ss.getrange(input); 
var result = cell.getBackground(); 
return result 
}

I want to use it in a spreadsheet with reference to any other cell like

=GetCellColor(D24)

Methods are case sensitive. getRange() not getrange(). Also getRange() accepts a stringSo use : =GetCellColor("D24") — TheMaster
Thanks, works. Can I somehow change the script to work with =GetCellColor(A1) instead =GetCellColor("A1")? I would like to be able to use copy-paste with Google sheets automatically changing the cell reference accordingly (i.e. in conditional formatting). — HJay
Possible. There's a roundabount way: get the active cell, get the formula in the active cell as a string, use regex to parse out the string A1 in the formula. There are some similar scripts here- search long and you may find some. Alternatively, DIY by referencing official documentations like MDN. — TheMaster
Also, I don't think you can use a custom formula in custom formatting. — TheMaster

Aerials Aerials · Accepted Answer · 2020-12-29T13:16:44

You are using the method Sheet.getRange(a1Notation) which takes a string as a parameter. So to answer the question from your comment you can't. But you could build the R1C1 notation string using other methods and concatenation which you could the use for copying and pasting.

Example:

=GetCellColor("R"&ROW()&"C"&COLUMN())

Google Sheets: "getrange is not a function"

1 Answers

Example: