0

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.

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.