26
votes

Problem: When I run the script, Google tells me,

You do not have permission to call openById

I had copied a script from another one of my Google spreadsheets and changed the target_ssKey variable's cell reference and created properly-sized Named Ranges in both the Source and Target spreadsheets.

Google Apps Script documentation says nothing about reasons why it might not be working:

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById%28String%29

Another Google Apps Script documentation says that it should work for me because I invoke it from a custom menu:

https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services

The second link above says:

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

I tried putting the function into a "Custom Functions" project and then into an "Add-on" project, but still got the same error message.

Any ideas on what I am doing wrong and how to make this work?

Here is my exact code:

function exportData_SStoSS() {
    //  Get the source data.
    var source_ss = SpreadsheetApp.getActiveSpreadsheet();
    var data = source_ss.getRangeByName("exportData").getValues();

    //  Identify the target.
    var controls_sh = source_ss.getSheetByName("Controls");
    var target_ssKey = controls_sh.getRange('C2').getValue();
    var target_ss = SpreadsheetApp.openById(target_ssKey);

    //  Paste the data to the target.
    target_ss.getRangeByName("importData").setValues(data);
};
5
The question doesn't include enough details to reproduce the problem specially when the function is called from a custom menu and from an add/on (custom functions can't execute methods that require permissions)Rubén
Possible duplicate No permission to call msgBox in Google Apps Scripting (while the error message is not exactly the same the cause is and that question is older)Rubén

5 Answers

25
votes

I thought that I would throw in a similar issue that I had which brought me to this question, where I received the error You don't have permission to call by openById.

In my case I was trying to call functions from translate.gs which I copied from this example:

https://developers.google.com/apps-script/quickstart/docs

Note that at the top of translate.gs

/**
 * @OnlyCurrentDoc
 *
 * The above comment directs Apps Script to limit the scope of file
 * access for this add-on. It specifies that this add-on will only
 * attempt to read or modify the files in which the add-on is used,
 * and not all of the user's files. The authorization request message
 * presented to users will reflect this limited scope.
 */

The culprit here is the @OnlyCurrentDoc comment. See here for reference:

https://developers.google.com/apps-script/guides/services/authorization

Removing @OnlyCurrentDoc fixed this issue for me

13
votes

I found this official note which I believe clears up what caused the issue.

If your function is a custom function, that is one which can be used like a regular spreadsheet function in the sheet itself, then it has limited access to things and cannot open other spreadsheets.

The same script can however open other spreadsheets from a menu button or similar.

Link: Documentation at developers.google.com

4
votes

I could resolved this issue with this autorization guide of google developers.

https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes

This entry It's necesary in json file.

 "oauthScopes": [
      "https://www.googleapis.com/auth/spreadsheets.readonly",
      "https://www.googleapis.com/auth/userinfo.email",
      "https://www.googleapis.com/auth/spreadsheets"
  ],
2
votes

The method openById can be called from a "Blank Project" but not a "Custom Functions in Sheets" nor a "Google Sheets Add-on" project.

I thought a "Blank Project" would create a project that was not connected to my spreadsheet, but I was wrong. The Blank Project is connected to my spreadsheet. The other types of projects that I tried to use seem to be limited-scope versions of script projects, not able to carry out some GAS methods.

0
votes

Had this same issue and came to share my solution. In my case I had two spreadsheets, call them A and B. Both used scripts bound to each respective spreadsheet. Spreadsheet B was able to write data to a tab of spreadsheet A. But Spreadsheet A kept getting the "You do not have permission to call openById" error when trying to read from spreadsheet B. I then tried adding it as a custom menu item but still the same issue.

The solution in my case turned out to be really simple. I created a new unbound script in script.google.com that calls both spreadsheets using openById. The first time running put a smile on my face as it asked for authorization. Thereafter smooth sailing.