当前位置： 文江博客话题详情

Google Apps脚本urlfetchapp返回未经授权错误401

发布于 2025-01-24 17:15:00 字数 3617 浏览 2 评论 0原文

因此，我使用的是一个Google电子表格，该电子表格持有受保护的范围，因此可以完成排序行，从表格中调用Unbound Web应用程序：

Google Sheet XYZ -> Unbound Web App (sort code) -> Google Sheet XYZ

Unbound Web应用程序在我的帐户下运行，并使用Sheet Xyz与用户共享。 完美工作。

现在，我试图使用onsubmit从Google表单中调用同一Web应用程序：

Google Form ABC -> Unbound Web App (sort code) -> Google Sheet XYZ

但是，EN例外Si从Web应用程序中返回：

{ [Exception: Request failed for https://script.google.com returned code 401. Truncated server response: <HTML>
<HEAD>
<TITLE>Unauthorized</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Unauthorized</H1>
<H2>Error 401</H2>
</BODY>
</HTML>
 (use muteHttpExceptions option to examine full response)] name: 'Exception' }

奇怪的是，尽管我都是表单，Web应用程序，和表所有者....还尝试将其他范围添加到表单脚本中。

这是在表单的onsubmit中运行的代码：

function callWebApp(){
  var queryString = "?action=sort&sheetID="+contactListSSID+"&sortType=byName&sheetName="+S_PARENTS_SHEET_NAME+"&firstDataRow="+S_PARENTS_FIRST_DATA_ROW+"&colUserName="+S_PARENTS_NAME_SURNAME_COLUMN+"&classColSection="+null+"&colDOB="+null; // null because unused for this sort type
  var baseUrl ="https://script.google.com/a/macros/ourdomain.com/s/xxxxxxx/exec"
  var url = encodeURI(baseUrl + queryString).replace("#","%23");
  var params = {method: "get", headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}};
  
  try {
      var request = UrlFetchApp.fetch(url,params)
      Logger.log(request);
  } catch (error) {
    console.error(error);
    return error;
  }
  return request;
}

未结合的Web应用程序代码（不包括代码中列出的所有操作）：

function doGet(e) {
  var param = e.queryString;
  var parameters = param.split("&") 
  var validActions = ["sort", "duplicate", "delete"];
  if (param == null){
    return "Missing parameters";
  }
  param = e.parameter;
  const matches = parameters.filter(s => s.includes('action'));
  if (matches.length < 1 || validActions.indexOf(param.action) < 0){
    return "Missing or bad action";
  }
  var ret;
  switch(param.action){
    case "sort":
      ret = sortSheet(e);
      break;
    case "duplicate":
      ret = duplicateSheet(e);
      break;
    case "delete":
      ret = deleteSheet(e);
      break;  
  }

  return ContentService.createTextOutput(ret);
}

function sortSheet (e){
  param = e.parameter;
  var sheetId = param.sheetID;
  var name = param.sheetName;
  var sortType = param.sortType;
  var COL_STUDENT_NAME = Number(param.colUserName);
  var S_CLASS_COL_SECTION = Number(param.classColSection);
  var COL_DOB = Number(param.colDOB);
  var FIRST_DATA_ROW = Number(param.firstDataRow);
  try{  
    var ss = SpreadsheetApp.openById(sheetId)  
    var sheet = ss.getSheetByName(name)
    var dataRange = sheet.getRange(FIRST_DATA_ROW,1,(sheet.getLastRow() - FIRST_DATA_ROW + 1),sheet.getMaxColumns()); 
    var a1 = dataRange.getA1Notation();
    
    switch (sortType){
      case "byName":
        dataRange.sort({column: COL_STUDENT_NAME, ascending: true});
        break;
      case "bySection":
        dataRange.sort([{column: S_CLASS_COL_SECTION, ascending: true}, {column: COL_STUDENT_NAME, ascending: true}]);
        break;  
      case "byDOB":
        dataRange.sort([{column: COL_DOB, ascending: true}, {column: COL_STUDENT_NAME, ascending: true}]);
        break;   
    }
  }
    catch (err){
    return err;
  }
  return a1;
}

当从电子表格中运行上述代码时，则可以使用。一旦以表单运行相同的代码，它将引发异常。

原文

So, I am using a Google Spreadsheet which holds protected ranges, therefore, to accomplish sorting rows, an unbound web app is invoked from the sheet:

Google Sheet XYZ -> Unbound Web App (sort code) -> Google Sheet XYZ

Unbound Web App runs under my account and is shared with users using Sheet XYZ.
Works perfectly.

Now, I am attempting to invoke the same Web App from a Google Form using OnSubmit:

Google Form ABC -> Unbound Web App (sort code) -> Google Sheet XYZ

however en exception si returned from the web app:

{ [Exception: Request failed for https://script.google.com returned code 401. Truncated server response: <HTML>
<HEAD>
<TITLE>Unauthorized</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Unauthorized</H1>
<H2>Error 401</H2>
</BODY>
</HTML>
 (use muteHttpExceptions option to examine full response)] name: 'Exception' }

Strange to say this occurs although I am both the form, web app, and sheet owner.... Also tried to add other scopes to the Form script.

This is the code running in the form's OnSubmit:

function callWebApp(){
  var queryString = "?action=sort&sheetID="+contactListSSID+"&sortType=byName&sheetName="+S_PARENTS_SHEET_NAME+"&firstDataRow="+S_PARENTS_FIRST_DATA_ROW+"&colUserName="+S_PARENTS_NAME_SURNAME_COLUMN+"&classColSection="+null+"&colDOB="+null; // null because unused for this sort type
  var baseUrl ="https://script.google.com/a/macros/ourdomain.com/s/xxxxxxx/exec"
  var url = encodeURI(baseUrl + queryString).replace("#","%23");
  var params = {method: "get", headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}};
  
  try {
      var request = UrlFetchApp.fetch(url,params)
      Logger.log(request);
  } catch (error) {
    console.error(error);
    return error;
  }
  return request;
}

Unbound Web App code (does not include all actions listed in the code):

function doGet(e) {
  var param = e.queryString;
  var parameters = param.split("&") 
  var validActions = ["sort", "duplicate", "delete"];
  if (param == null){
    return "Missing parameters";
  }
  param = e.parameter;
  const matches = parameters.filter(s => s.includes('action'));
  if (matches.length < 1 || validActions.indexOf(param.action) < 0){
    return "Missing or bad action";
  }
  var ret;
  switch(param.action){
    case "sort":
      ret = sortSheet(e);
      break;
    case "duplicate":
      ret = duplicateSheet(e);
      break;
    case "delete":
      ret = deleteSheet(e);
      break;  
  }

  return ContentService.createTextOutput(ret);
}

function sortSheet (e){
  param = e.parameter;
  var sheetId = param.sheetID;
  var name = param.sheetName;
  var sortType = param.sortType;
  var COL_STUDENT_NAME = Number(param.colUserName);
  var S_CLASS_COL_SECTION = Number(param.classColSection);
  var COL_DOB = Number(param.colDOB);
  var FIRST_DATA_ROW = Number(param.firstDataRow);
  try{  
    var ss = SpreadsheetApp.openById(sheetId)  
    var sheet = ss.getSheetByName(name)
    var dataRange = sheet.getRange(FIRST_DATA_ROW,1,(sheet.getLastRow() - FIRST_DATA_ROW + 1),sheet.getMaxColumns()); 
    var a1 = dataRange.getA1Notation();
    
    switch (sortType){
      case "byName":
        dataRange.sort({column: COL_STUDENT_NAME, ascending: true});
        break;
      case "bySection":
        dataRange.sort([{column: S_CLASS_COL_SECTION, ascending: true}, {column: COL_STUDENT_NAME, ascending: true}]);
        break;  
      case "byDOB":
        dataRange.sort([{column: COL_DOB, ascending: true}, {column: COL_STUDENT_NAME, ascending: true}]);
        break;   
    }
  }
    catch (err){
    return err;
  }
  return a1;
}

When the above code is run from the spreadsheet, it works. Once the same code is run in the form, it will throw an exception.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

泅渡 2025-01-31 17:15:00

问题：

为了访问非公开应用脚本Web应用程序，需要驱动器范围。

解决方案：

添加这两个范围中的任何一个都可以工作：

您可以添加它们在示例中明确 oauthscopes：

"oauthScopes": [
  "https://www.googleapis.com/auth/script.external_request", 
  "https://www.googleapis.com/auth/script.scriptapp",
  "https://www.googleapis.com/auth/forms",
  "https://www.googleapis.com/auth/drive.readonly"
]

或者，更容易，您可以使用需要此范围的方法添加一个注释的行，例如：

//DriveApp.getFileById()

脚本会注意到这一点，因此script> scriptapp.getoauthtoken（）>将获得此范围的授权。

相关：

Issue:

A drive scope is needed in order to access non-public Apps Script web apps.

Solution:

Adding any of these two scopes will work:

You can either add them explicitly in the manifest, using the field oauthScopes:

"oauthScopes": [
  "https://www.googleapis.com/auth/script.external_request", 
  "https://www.googleapis.com/auth/script.scriptapp",
  "https://www.googleapis.com/auth/forms",
  "https://www.googleapis.com/auth/drive.readonly"
]

Or, more easily, you can add a commented line with a method that requires this scope, for example:

//DriveApp.getFileById()

The script will notice this and so ScriptApp.getOAuthToken() will be authorized with this scope.

Related:

回复 原文
~没有更多了~

关于作者

人生百味

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文