Google Script does not autorhize me to use function of API even accept popup

Question

I got a basic function (I keep only relevant code) :

function list() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getRange("Analyse!B1").setBackground("red");  
}

I'm calling the function in a cell like this : =list()

Error : You're not authorize to call function list

I got the popup above, asking me for authorization, I accepted it, mais nothing change always an arror:

Please update the question to indicate how you are triggering the function. Are you entering it into a cell as a custom function? (EG =list() ) or running it from a menu, etc. — Cameron Roberts, Jan 22 '18 at 21:36

score 0 · Accepted Answer · answered Jan 22 '18 at 21:44

You cannot call functions like setBackground() from within a custom function.

When you run Apps Script code as a custom function it runs with a limited set of "safe" permissions, this is why you are not prompted to authorize the script.

The only way a custom function may interact with the calling sheet is by returning either a single value or a two dimensional array of data, which will be written from the location of the calling cell.

See my response on this similar question: How to change format of cell that runs function - Google Sheets Script Editor

If you must change the background color or perform other kinds of actions, you'll need to trigger the functionality another way, such as from a menu option or sidebar, which will trigger the authorization flow and run with full permissions.

If i trigger the function with a timer every minute, will it work ? — azro, Jan 22 '18 at 21:46
I set a trigger as "after modification" and it works, thanks — azro, Jan 22 '18 at 21:50
that's a good solution if you don't need to distribute the function to other users. glad I could help! — Cameron Roberts, Jan 22 '18 at 21:59

score 0 · Answer 2 · answered Jan 23 '18 at 07:23

0

This is a known bug and likely affects scripts where the user is logged into multiple Google accounts in the same browser?

Open Google Chrome, go to the menu and choose New Incognito Window. Here open the same Google sheet and run the script again.

answered Jan 23 '18 at 07:23

Amit Agarwal

10,910
1
32
43

Google Script does not autorhize me to use function of API even accept popup

2 Answers2