How to change format of cell that runs function -

Question

If I have the following user defined function I can return the text "foo"...

function myFunction(input){
   return "You wrote: "+ input;
}

How can I assign a red background to the cell through code? Even though it's a simple example, my script is more complex and I want to assign formatting through code (I don't want to use the Format > Conditional Formatting... option; I need to have everything configured in a single script.

Is there something in the lines of this that could work?

function myFunction(input){
   setBackground('red');
   return "You wrote: "+ input;
}

getActiveCell doesn't work for me because that triggers the clicked (or activated) cell and not necessarily the cell that contains the formula.

Thanks in advance!

Answer 1

If I'm following correctly you are calling the function using Custom Formula syntax, by putting =myFunction() in a cell in your sheet.

In this case, there is no script based method to apply formatting to the cell containing the formula. Not only are you not automatically given a reference to the parent cell in your function, you also do not have permission to call set methods (such as Range.setBackground()) from within a custom formula call.

This is noted in the documentation under the Using Apps Script services heading:

Spreadsheet: Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

https://developers.google.com/apps-script/guides/sheets/functions#advanced

The only thing your custom formula can do is return either a string or array, which will then be displayed in the sheet.