google-apps-scriptgoogle-sheetsgoogle-sheets-custom-function

How to change format of cell that runs function


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.


Solution

  • 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.