OAuth error when exporting Sheet as XLS in Google Apps Script

Question

I had a Google Apps Script to take appointments from my Google Calendar, copy them into a Google Sheet, convert it to XLS and email it. It was working fine until this week.

The initial problem was a 302 error, probably caused by the new version of Sheets. This has been discussed here: Export (or print) with a google script new version of google spreadsheets to pdf file, using pdf options

I got the new location of the file by muting the HTTP exceptions and adjusting the URL accordingly. I also updated the OAuth scope to https://docs.google.com/feeds/ as suggested.

The program is failing with an "OAuth error" message. When muteHttpExceptions is set to true, the message is "Failed to authenticate to service: google".

I guess this is a scope problem but I really can't see what I've done wrong. Naturally, I've tried a few other possibilities without luck.

I've included the code below. Commented code is the instruction that worked until this week.

function getSSAsExcel(ssID)
{
  var format = "xls";

  //var scope = "https://spreadsheets.google.com/feeds/";
  var scope = "https://docs.google.com/feeds/";
  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=" + scope);
  oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");

  var requestData = {
    //"muteHttpExceptions": true,
    "method": "GET",
    "oAuthServiceName": "google",
    "oAuthUseToken": "always"
  };

  //var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=" + ssID
  var url = "https://docs.google.com/spreadsheets/d/" + ssID 
      + "/feeds/download/spreadsheets/Export?"
      + "&size=A4" + "&portrait=true" +"&fitw=true" + "&exportFormat=" + format;

  var result = UrlFetchApp.fetch(url , requestData);
  var contents = result.getContent();

  return contents; 
}

Thanks for your help!

Answer 1

Instead of using OAuthConfig (which must be auth'ed in the Script Editor) you can pass an OAuth2 token instead, retrievable via ScriptApp.getOAuthToken().

The code snippet below uses the Advanced Drive service to get the export URL, but if you hand construct the URL you'll need to ensure that the Drive scope is still requested by your script (simply include a call to DriveApp.getRootFolder() somewhere in your script code).

function exportAsExcel(spreadsheetId) {
  var file = Drive.Files.get(spreadsheetId);
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];
  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  return response.getBlob();
}