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