Google Sheets – Fix Errors Using Custom Formula with Script (getRange)

formulasgoogle sheets

I have a google Sheet where i want to get the background colors of the specify range (Cells) using custom formula with script.

within the Cell A1, i put the custom function: =gh(B1:D1)

In script:
function gh(Cells) {
var Hoja = SpreadsheetApp.getActive().getSheetByName('PROGRAMA SST');
const Colores = Hoja.getRange(Cells).getBackgrounds();
return Colores;
};

however, i get error "Exception Range not found".
Note: I know if i put =gh("B1:D1") the script works but i want to avoid putting the quotes.

Best Answer

You can convert a range reference to a text string in A1 notation with a named function, and use it in your custom function formulas like this:

=gh( GETRANGEA1(B1:D1) )

To create the named function, choose Data > Named functions and use these parameters:

Function name: GETRANGEA1

Function description: Converts a range reference to a text string in A1 notation.

Argument placeholders: range

Formula definition:

=substitute( 
  cell("address", range) 
  & ":" & 
  reduce( 
    _, range, 
    lambda( 
      result, lastCell, 
      cell("address", lastCell) 
    ) 
  ), 
  "$", ""  
)
Related Topic