You do not have permission to call openById

Question

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);
};

Answer 1

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

Answer 2

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

Answer 3

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"
  ],

Answer 4

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.

Answer 5

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.