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.
If I'm following correctly you are calling the apps script function using 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 function call inside a cell formula.
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 function (when placed in a cell as an automatically executed formula) can do, is return either a string or array, which will then be displayed in the sheet.