Sudden permissions problem with SpreadsheetApp.openByID

2
votes

The code is supposed to copy some data from another spreadsheet and then I manipulate it.

This code worked yesterday, then I tried adding a library today and now it is still throwing this error even after removing the library and rolling back my code to the version yesterday.

Error Message:

You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets (line 21, file "Import")

Code:

 data = SpreadsheetApp.openById('1-SyJRflR1YYYYDRo3e7xnDhYYYY6hIGDBKODl9XXXXX').getSheetByName("g1.1").getDataRange().getValues();

I am NOT using "//@OnlyCurrentDoc" anywhere in either of my documents so that is not my problem.

Google sheets API is turned on for this project, as checked from the resources menu.

The library function I was attempting to use was a simple time one (nothing to do with sheets), and it is not currently being called anywhere in my project and the library has been detached.

1
google-apps-scriptgoogle-sheets
What does you manifest file look like? – Chris
You may need to reauthorize the permissions. From the code editor, choose a function from the drop down list, and run the function. If the code is missing a required authorization, you should be prompted with the dialog box to accept the permissions. If the scopes have been manually set in the appsscript.json file, and the scope for opening a spreadsheet is not present, then you'll never be prompted to authorize the permission, and that error will go on forever. If you are in an enterprise account, like GSuite, make sure that the domain admin hasn't blocked Spreadsheet services. – Alan Wells

1 Answers

6
votes

Possible Issues:

  1. If your library included //@OnlyCurrentDoc and you authorized after adding the library, your permissions might've just included the current document.

  2. If you had explicitly set scopes1, then those scopes might not have included https://www.googleapis.com/auth/spreadsheets

Possible Solutions:

  1. If you want to still use the library, add the //@NotOnlyCurrentDoc in your main script and re-run the function. If not remove the library and re-run your function.

    An opposing annotation, @NotOnlyCurrentDoc, is available if your script includes a library that declares @OnlyCurrentDoc, but the master script actually requires access to more than the current file.

  2. Removing the explicit scopes in your manifest will trigger the normal authorization flow.

  3. If all else fails, visit your account, find your project and remove all access. Now, re-running any function in your script will trigger normal authorization flow.