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!

Here's a similar question with an up-to-date answer. stackoverflow.com/questions/31809987/… — Christiaan Westerbeek
– Christiaan Westerbeek, Commented Oct 8, 2015 at 15:06

Riyafa Abdul Hameed · Accepted Answer · 2016-02-27 18:23:09Z

2

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();
}

edited Feb 27, 2016 at 18:23

Riyafa Abdul Hameed

8,0438 gold badges45 silver badges59 bronze badges

answered May 1, 2014 at 18:46

Eric Koleda

12.8k1 gold badge35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scallions Over a year ago

The reference for other export types is [link] {developers.google.com/drive/web/…}. Instructions for enabling the Drive API are [link] {developers.google.com/apps-script/guides/services/advanced}.

Mickey D Over a year ago

Thanks @Eric Koleda, 3 years old, may I ask, how does that get called (or impelemented)? I don't see the function actually exporting. I see it returning a blob.

Collectives™ on Stack Overflow

OAuth error when exporting Sheet as XLS in Google Apps Script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related