“(perhaps it was deleted?)” Spreadsheet Error in Google Scripts

Question

I have a small script that grabs some data from a spreadsheet and displays it on a Google Scripts html page - for testing I'm just displaying the sheet name.

It works fine for me but other users get the error : [Error] ScriptError: Document 1sTltxpKjSqgaFjc9-3EQzgtvyErkut2qETsn7SdThLE is missing (perhaps it was deleted?)

The script and Spreadsheet are both public and the user can access the spreadsheet directly.

Link to Spreadsheet

Link to Script page

Google Scripts Code:

function getInfo() {
    var ss = SpreadsheetApp.openById("1sTltxpKjSqgaFjc9-3EQzgtvyErkut2qETsn7SdThLE");
    var ssName = ss.getName();
    var sheet = ss.getSheets()[0];

     return 'Doc: ' + ssName + '. Sheet: ' + sheet.getName();
 }

The javascript in the html is simply:

  function getSheetInfo(data) {
      $('p.data').append(data);
  }

  google.script.run.withSuccessHandler(getSheetInfo)
     .getInfo();

Can anyone shed any light why this sheet isn;t accessible to other users but they can view the sheet? Both are public.

Thanks Rob

Is your script running "as the user accessing the app"? If so, do they have access to the spreadsheet? If this is the case, then this is the expected behavior and the solution is to either share the file to anyone with the link can view or publish the script running as you, the developer. — Henrique G. Abreu
Thanks Henrique. yes, I'm running "as the user accessing the app" as I need to get some personal details of the logged in user. (so can't run as me) And yes, they can access the spreadsheet directly no problems. I'll try with the spreadsheet access settings changes and post back. Thanks for your help. — rob_was_taken
Thanks Henrique that worked. So is there no way to run this without making the sheet publicly accessible? I need to have sensitive data on there. Any way you know? — rob_was_taken
I've made it into a proper answer, since it's quite lengthy. Hope it helps — Henrique G. Abreu

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2014-05-28T22:54:27

If you've deployed your script to run "as the user accessing the app" the spreadsheet must be shared with such user, so that a script running as him can access it. If you have a "general" audience, you'll have to share this spreadsheet with "anyone with the link can view". Unless of course you can change the deployment option to execute the app as you, the developer.

Note that this does not necessarily expose your spreadsheet, because you don't have to handle your users the link, the script code (which can be private only to you) access it on their behalf, and since your code runs from a Google server the spreadsheet link is never leaked.

The only way that the spreadsheet could be leaked is from your manual access to it. E.g. if you open it from a coffee shop or other unsecure network, where someone sniffing or the network admin himself can see the url of sites you navigate to, therefore being able to open the file themselves (since it's shared to anyone with the link).

If your info is really sensitive and you don't want to take any chances, a solution is to save this information somewhere else. For example, if it fits (due to size limitations) you may use the Properties services, or cloud-sql or any other cloud storage.

Another solution is to use a secondary script, published anonymously and running as you, that the "user" script can call using UrlFetch to receive the data from it. In this case there's still a "link-only" security feature, but now your script published link instead of the files, which is way less likely to leak since no one access it but the other script, which is a Google server to server communication. And if someone can hack Google servers communication your data is not secured anyway.

Lastly, probably the best solution, is to use the Drive SDK directly via UrlFetch, on which you can manage the oAuth2 credentials, passing your's instead of your users'. But you'd need to do the oAuth dance in your script, which is not trivial (although there's plenty of code on the internet to help you). Also, there's the advanced Drive service which might help a little, but I'm not sure if it allows you to deal with the oAuth2 yourself.

“(perhaps it was deleted?)” Spreadsheet Error in Google Scripts

4 Answers