google apps script - UrlFetch getting 404 error from spreadsheet URL

Question

I am consistently getting a 404 - Requested entity not found response when I use UrlFetchApp to export a spreadsheet as a PDF. If I put the same URL into a browser, the PDF download initiates properly.

What can I do to get this example script to work?

Here's an example PDF export URL, for a simple shared (read-only) spreadsheet: https://docs.google.com/spreadsheets/d/133KPZyxAATG3AAY9iCSFWabefyFvQnENkHh0dyqh7MI/export?exportFormat=pdf&format=pdf&gid=0&size=letter&portrait=true&fitw=true&sheetnames=false&printtitle=false&pagenumbers=false&gridlines=false&fzr=false

If you click on that URL in your browser, it GETs a PDF Blob, which typically launches the browser's file download dialog. (This is a public sheet, but in production the sheet is private.)

function notFunctioning() {
  var options = {
    headers: {
      Authorization:"Bearer "+ScriptApp.getOAuthToken()
    },
    muteHttpExceptions : true        /// Get failure results
  }

  url = "https://docs.google.com/spreadsheets/d/133KPZyxAATG3AAY9iCSFWabefyFvQnENkHh0dyqh7MI/export?exportFormat=pdf&format=pdf&gid=0&size=letter&portrait=true&fitw=true&sheetnames=false&printtitle=false&pagenumbers=false&gridlines=false&fzr=false";
  var response = UrlFetchApp.fetch(url, options);
  
  var status = response.getResponseCode();
  var result = response.getContentText();  
  debugger; // status:404  8'(
}

The content of the result string is:

Sorry, the file you have requested does not exist.

Make sure that you have the correct URL and that the owner of the file hasn't deleted it.

Of course, the file does exist, etc. I'm assuming that there is some authentication or authorization step that I've missed. Note that this code is essentially the same as in Convert all sheets to PDF with Google Apps Script, which worked once upon a time...

Investigations:

• There is a reported Issue 5417 that pertains to the Execution API, but as it also involved file transfers and Google's URLs, I thought the advice it contained might apply. That advice (not from Google, mind) was to ensure the "script is associated with the same Google Developer console project that you used for authentication." I created a new dev console project, and changed the Apps Script application to use it. While that triggered a new authorization cycle, it did not ultimately fix the 404 - Requested entity not found.

• A Google Docs editors URL format change was announced a year ago. I'm using a consumer account, not a domain account, so it seems that this should not apply. Further, the script has worked since that time. (Still, I wonder if it does affect things, and whether the browser is being redirected while the GAS servers are not.)

Answer 1

While this technique does not directly use the Advanced Drive Service, it is those permissions that are needed for the script to access your Google Drive files via their URLs.

A note on Issue 3579: Converting spreadsheet as PDF fails with new Sheets provides the hint:

Keep in mind that solution only works if Drive or DriveApp appears somewhere in the script, so that the Drive OAuth scopes are requested and can be passed along. Check to ensure this is true for all scripts.

It's sufficient to add a dummy function containing a DriveApp function call:

/**
 * Dummy function for API authorization only.
 */
function forAuth_() {
  DriveApp.getFileById("Just for authorization"); // https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579#c36
}

Then enable the Advanced Drive Service through "Resources > Advanced Drive Services...", and the developer console. (See this for more info.)

---

In my case, I had a script that had been working, and then stopped. I believe that when I was originally writing the script, I'd attempted to use the Advanced Drive Service export links (which don't work, btw), so I'd been through enabling the service. But recently, I'd had to reauthorized the script due to other changes, and I think that removed the authorization for Drive at the same time.