Cell coloring in google-spreadsheet fails if it is called from a cell, but works ok when called from the script.

Question

I have created the following simple function:

function test(r,c) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(r,c).setBackground("red");
  return 1;
}

---

In the spreadsheet, I write "=test(row(),column())

This results in ERROR with the following message: Error: You do not have permission to call setBackground (line 3).

It is NO problem if I create another function call in the script as follows:

function test_the_test(){
  test(5,4); 
}

Why can't I call the test function from the spreadsheet cell?

Thank you in advance

Answer 1

As it is clearly explained in the documentation, Custom functions return values, but they cannot set values outside the cells they are in. In most circumstances, a custom function in cell A1 cannot modify cell A5. That is of course also true for other methods such as setBackground etc.

Answer 2

It's not possible to call anything which sets content from cell, but it is possible to call it from buttons.

Actually is quite easy. Definitely it's not true that you can't change others cell content.

The trick is to not call the function from cell but mount it into drawing/image.

1. Click insert -> drawing
2. Draw a image and save it (you should see your image in spreadsheet)
3. Click it by right mouse button - in top right corner, there is a little triangle opening options
4. Click Assign script and type name of your script without parenthesis (like "test" not "test()" ) and confirm
5. Click the button. A pop-up window asking for privileges to access spreadsheet appears.
6. Confirm it, if problem with refresh occurs just refresh it manually (F5)
7. Now you can click the button and you can edit any cell you like

This code work fine when mounted to button.

function test() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(3,5).setBackground("red");
}