Fix Errors using Custom Formula in google Sheet with Script (getRange)

Question

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.

Answer 1

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) 
    ) 
  ), 
  "$", ""  
)