在Google Apps脚本中将工作表导出为XLS时出现OAuth错误 [英] OAuth error when exporting Sheet as XLS in Google Apps Script

问题描述

我有一个Google Apps脚本，可以从我的Google日历中取得约会，将它们复制到Google表格中，将其转换为XLS并通过电子邮件发送。直到本周它仍然正常工作。

最初的问题是302错误，可能是由新版本的表格造成的。这里已经讨论过了：使用pdf选项将Google电子表格的新版本导出（或打印）为PDF文件

我得到了文件的新位置通过静音HTTP例外并相应地调整URL。我还根据建议将OAuth范围更新为 https://docs.google.com/feeds/ 。

该程序因OAuth错误消息而失败。当muteHttpExceptions设置为true时，消息是无法通过服务验证：google。

我认为这是一个范围问题，但我真的看不到我做错了。当然，我已经尝试了一些其他的可能性，但没有幸运。

我已经包含下面的代码。

 函数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=+范围）; 
 oauthConfig.setAuthorizationUrl（https://www.google.com/accounts/OAuthAuthorizeToken）; 
 oauthConfig.setConsumerKey（anonymous）; 
 oauthConfig.setConsumerSecret（anonymous）; 
 
 var requestData = {
 //muteHttpExceptions：true，
method：GET，
oAuthServiceName：google，
oAuthUseToken：永远
}; 
 
 // 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（）; 
 
返回内容; 
} 
  

感谢您的帮助！

ScriptApp.getOAuthToken（ ）。

以下代码段使用高级云端硬盘服务以获取导出网址，但如果您手动构建网址，则需要确保您的脚本仍需要Drive范围（只需包含对 DriveApp的调用即可）。

  function exportAsExcel（spreadsheetId）{$ b $ getRootFolder（） b 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（）; 
} 
  

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!

解决方案

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

这篇关于在Google Apps脚本中将工作表导出为XLS时出现OAuth错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！