CODEWITHSUNDEEP
google-apps-script
Jordando
Jordando

Reputation: 356

How to let others use a script I wrote in google sheets that is shared

I have a google sheet that is shared with many users. I wrote several scripts which create a toolbar with buttons to allow easy use of the sheet. I would like others to be able to use the toolbar, but when others try they get an error message, saying they need permission to do this.

After working with another user, on their account, I found that if they manually ran the the script (entered script editor --> selected function --> pressed run) they were prompted with the usual "This script would like to access your..." message. After we accepted that, they had no more problems running the scripts as intended, from the toolbar.

So my question is this: how can other users who share a common spreadsheet all use the scripts which reside on that sheet? Is there something I need to do? Or do they each have to manually run the script once to accept authorization?

Thanks!

P.S. I have searched a lot on stack overflow for the exact same problem, and have found similar things, but not this exact one. Thanks again.

Upvotes: 1

Views: 1901

Answers (1)

Serge insas
Serge insas

Reputation: 46812

You have to create an onOpen function that creates a menu, from that menu call any function, even a dummy one.

When a new user will call that function he (she) will be asked to authorize the script with the usual Google popup...

But

Yes, there is always a "but" somewhere...

You can't have a global variable in your script that uses a Google service that needs authorization or this procedure won't work.

(this is simply because global calls are executed each time any function is called and that will generate an error message)

Note that when run from the script editor this restriction will not apply ! (why make it simple if we can make it complicated ? :-)

An example to make it clear :

this code works :

function onOpen() {   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: "Import Agendas", functionName: "Cal_to_sheetM"} 
  ];  
  ss.addMenu("import Agendas", menuEntries);  
}

this one does not :

var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();// global var definition

function onOpen() {   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    {name: "Import Agendas", functionName: "Cal_to_sheetM"} 
  ];  
  ss.addMenu("import Agendas", menuEntries);  
}

Final comment : sometimes I don't have this issue, sometimes I have... I didn't figure out yet why or when is happens but I'm still searching. It seems that my Google Apps accounts are more sensitive than my free gmail account but I'm not 100% sure.

Upvotes: 2

Related Questions