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

If you have a JSON file(most probably named appscript.json) in your Google app script files list/directory, then it is worth checking the file as well. For me, the json in the file had a key-value(key-outhscopes, value- an array of scope where the script the script is allowed to run) pair, and my manually executing the script wasn't covered in the scopes so it was showing me the error. I removed the oauth part and it is working fine for me.

Answer 2

I found this official note about Using Google Apps Script services which I believe clears up what caused the issue.

Custom functions can call certain Google Apps Script services to perform more complex tasks. Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, specifically the following:

Spreadsheet | Read only (can use most get*() methods, but not set*()). Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl()).

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.

Answer 3

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.

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

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 6

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