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 ?

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