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

Solution 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

Solution 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

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

Solution 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.

Solution 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.

View more solutions

Comments

[Muhammad Gelbana]

Is there an official source for these information ?

[nanusdad]

Removing @OnlyCurrentDoc also fixed this issue for me

[1owk3y]

Best I can offer you in terms of an offical source is to do this: Open the sheet, click Tools > 'script editor'. The page you are taken to is for that sheet's "Add-on" functions. This option should not work if you made a Blank Project (but you will also lose access to things like onEdit using that approach)

[k.b]

well, I do not have @OnlyCurrentDoc tag in my app's script, yet I am getting this error.

[PhilHibbs]

I removed @OnlyCurrentDoc and it did NOT fix this issue for me!

[Ko Ga]

this helped me solve my issue with, need to add the scope it's complaining about to appsscript.json in the project root. If you don't see this file, you'll need to go to Project Settings (in the left navver) and check the 'Show "appsscript.json" manifest file in editor' checkbox.