SpreadsheetApp.openByUrl and openById give errors

Question

So I have encountered a problem in the application I am writing using google spreadsheets and app-script. I have put together a step by step guid to reproduce it.

Summary: any script that uses SpreadsheetApp.openByUrl() and SpreadsheetApp.openById() works OK when called from inside the script editor but gives errors when called from inside even the same spreadsheet that has that script inside it.

Error Messages:

Error
    You do not have permission to perform that action. (line ?).
    

Steps to reproduce it:

1. Make a new Google Spreadsheet
2. Open its Script Editor ( Tools menu >> Script Editor... )
3. Choose Blank Project

4. Paste in this code

   function demo1() {
     return "cat";
   }
   
   function demo2() {
     return [["bob", "fred"], ["x", "y"]];
   }
   
   function demo3() {
     Logger.log("demo3-1")
     ss = SpreadsheetApp.openByUrl("[==URL==]")
     name = ss.getName()
     Logger.log("demo3-2 " + name)
     return name
   }
   
   function demo4() {
     Logger.log("demo4-1")
     ss = SpreadsheetApp.openById("[==id==]")
     name = ss.getName()
     Logger.log("demo4-2 " + name)
     return name
   }
   

5. Change the "[==URL==]" and "[==id==]" to refer to your own document

6. Save the script

7. From the Run menu choose demo1. This will trigger a question about privileges. Grant it the privileges it asks for. ( asks for access to your Google Drive )

8. Go back to the spreadsheet itself

9. Set the following cells to have these formulae:

   A1 : =demo1()
   C2 : =demo2()
   F1 : =demo3()
   h1 : =demo4()
   

Note that the first 2 work, so the spreadsheet is seeing the script project and able to run code from it ( so it has permission to run code from that script ) but the last two do not work and complain that it does not have permission to perform that action.

So how do I fix this? Am I doing something wrong or is there an underlying problem ?

Solution 1

This error is due to a change Google recently rolled out prohibiting the use of SpreadsheetApp.openByUrl() (or SpreadsheetApp.openById() in custom functions.  You can still use this method from other contexts like a menu item, trigger, etc. Google had to roll out this change for security reasons and they won't be able to revert back to the old behavior. So the only possible fix may very well be to rewrite the function as a 'normal' GAS-function and NOT a custom function.

The issue is listed in the issue tracker.

Solution 2

I actually discovered a nice and easy fix for this yesterday. Hopefully, this workaround works out for you.

Reading through the SpreadsheetApp documentation, I found a function that opens a spreadsheet by providing the corresponding File object.

Here is the section:
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#open(File)

The example shows that you can easily search for a file type in Google Drive (Spreadsheets in this case). However, instead of searching your entire Drive library, you could just use the ID of the file/spreadsheet by using DriveApp.getFileById(ID).

Here is the documentation for that function: https://developers.google.com/apps-script/reference/drive/drive-app#getFileById(String)

All you need to do is this:

1. Find the link of the spreadsheet in drive by right clicking the file and selecting "Get Link"
2. Copy the link, which should look like this docs.google.com/spreadsheets/d/{ID}/edit#gid=0
3. Get that ID portion of the URL
4. Use this line SpreadsheetApp.open(DriveApp.getFileById(ID)) to retrieve your Spreadsheet object

You can do other processing after that once you have the object. Let us know if this helped.

Solution 3

I had a similar issue. I switched to using an installed trigger to run my script, instead of using a simple trigger.

https://developers.google.com/apps-script/guides/triggers/installable