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

问题描述

我有一个 Google Apps 脚本，可以从我的 Google 日历中接受约会，将它们复制到 Google 表格中，将其转换为 XLS 并通过电子邮件发送.直到本周它都运行良好.

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.

最初的问题是 302 错误，可能是新版表格引起的.这已经在这里讨论过:使用 pdf 选项，使用 google 脚本将新版本的 google 电子表格导出(或打印)为 pdf 文件

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

我通过静音 HTTP 异常并相应地调整 URL 获得了文件的新位置.我还按照建议将 OAuth 范围更新为 https://docs.google.com/feeds/.

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.

程序失败并显示OAuth 错误"消息.当 muteHttpExceptions 设置为 true 时，消息为无法对服务进行身份验证:google".

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

感谢您的帮助！

推荐答案

代替使用 OAuthConfig(必须在脚本编辑器中进行身份验证)，您可以传递 OAuth2 令牌，可通过 ScriptApp.getOAuthToken().

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

下面的代码段使用高级云端硬盘服务来获取导出网址，但如果您手动构建 URL，则需要确保 Drive 范围仍由您的脚本请求(只需在脚本代码的某处包含对 DriveApp.getRootFolder() 的调用).

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屋！