如何为按钮分配具有基本授权的UrlFetchApp?

问题描述

限时删除！！

我在Google Apps脚本中创建了函数，当我在Google Apps脚本中运行该函数时效果很好.输出数据返回到Google表格.

I created function in Google Apps Script, that works well when I run it in Google Apps Script. Output data returns to Google Sheets.

function testFunction11() {
  var rng = SpreadsheetApp.getActiveRange();
  var encodedAuthInformation = Utilities.base64Encode("username:key");
  var headers = {"Authorization" : "Basic " + encodedAuthInformation};
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': headers
  };
  var res = UrlFetchApp.fetch("https://api.apiservice.com/api/v1/xxx?fields=somefields", params);
  Logger.log(res.getContentText());
  rng.setValue(res);
}

单元格中的输出:

[
  {
    "id": xxx,
    "createdDate": "2019-02-01T04:54:00Z",
    "reference": "XXX"
  },
etc

然后我将脚本分配给按钮"testFunction11".当我单击按钮时，它会返回

Then I assign script to button, 'testFunction11'.And when I click button, it returns

{
  "message": "An error has occurred."
}

看起来像来自API服务器的响应.

It looks like response from API server.

我唯一的假设是Google工作表的按钮添加了一些标头，User-Agent或content-type来请求，这在API服务器中是不允许的.经过一些搜索，我想我无法在请求中重新分配User-Agent.那是对的还是我做错了?

My only hypothesis is that google sheet's button adds some headers, User-Agent or content-type to request, which not allowed in API server. And after some search, I guess I can't reassign User-Agent in request. Is that something right or I do it wrong?

每种情况console.log(UrlFetchApp.getRequest(url, params))的标题:在电子表格中单击按钮时:

Headers for each case console.log(UrlFetchApp.getRequest(url, params)):When clicking button in spreadsheet:

{headers={Authorization=Basic XXXXXXXXQVU6MWVhODlmZmFkN2U3NGNjOGJkOTc1YTE1ZjVhNTE3MzE=, X-Forwarded-For=178.xx.my.ip}, method=get, payload=, followRedirects=true, validateHttpsCertificates=true, useIntranet=false, contentType=null, url=https://api.apisite.com/api/v1/SalesOrders?fields=Id,Createddate,Reference&where=Createddate%3E2019-02-01T00:00:00Z}

对于脚本:

{headers={Authorization=Basic XXXXXXXXQVU6MWVhODlmZmFkN2U3NGNjOGJkOTc1YTE1ZjVhNTE3MzE=}, method=get, payload=, followRedirects=true, validateHttpsCertificates=true, useIntranet=false, contentType=null, url=https://api.apisite.com/api/v1/SalesOrders?fields=Id,Createddate,Reference&where=Createddate%3E2019-02-01T00:00:00Z}

因此该按钮仅添加X-Forwarded-For.

当我尝试手动添加X-Forwarded-For: 'unknown'时，会出现这样的错误

When I tried manually add X-Forwarded-For: 'unknown' there are error like this

There are attribute with impossible value: Header:X-Forwarded-For

俄语错误文本，对不起，翻译不正确.这很有趣，因为当我以相同的方式添加Test: unknown时，没有错误，但显然不起作用.看起来Google不允许更改此值.

Text of error in russian, so sorry for maybe not accurate translating. It's fun, because when I added Test: unknown in same way, there are no error, but obviously not working. Looks like google don't allow to change this value.

将尝试在邮递员中使用不同的标头，并可能确认此标头是导致错误的原因.谢谢@TheMaster

Will try different headers in postman and maybe confirm that this header is the cause of the error. Thank you @TheMaster

我通过邮递员尝试了不同的标题.因此，结果是当我使用任何值将其添加到标头X-Forwarded-For键时，它返回"message": "An error has occurred."当我不添加此键时，它会很好地工作.

I tried different headers via Postman. So, result is when I add to headers X-Forwarded-For key with any value, it return "message": "An error has occurred."When I don't add this key, it works well.

因此，问题是通过Google Apps脚本禁用添加此标头的任何方法.好像没有.

So, the question is any way to disable adding this header via Google Apps Script. It seems like not.

推荐答案

• 根据您的情况，通过电子表格上的按钮运行UrlFetchApp.fetch()时，X-Forwarded-For会自动添加到标题中.
• 通过将X-Forwarded-For添加到标题中，会出现An error has occurred.错误.
• 另一方面，标头中未使用X-Forwarded-For，不会发生错误.
• In your situation, when UrlFetchApp.fetch() is run from a button on Spreadsheet, X-Forwarded-For is automatically added to the header.
• By added X-Forwarded-For to the header, the error of An error has occurred. occurs.
• On the other hand, X-Forwarded-For is not used in the header, no error occurs.

如果我的理解是正确的，该解决方法如何?我认为可能有几种解决方法.因此，请仅将此视为其中之一.在这种解决方法中，Web Apps用作包装器功能.

If my understanding is correct, how about this workaround? I think that there might be several workarounds. So please think of this as just one of them. In this workaround, Web Apps is used as a wrapper function.

首先，请复制并粘贴以下脚本.并将testFunction11()设置为按钮.运行testFunction11()时，testFunction11()向Web Apps(doGet())请求，而Web Apps向https://api.apiservice.com/api/v1/xxx?fields=somefields请求.这样，X-Forwarded-For不会用于请求的标头.然后，返回Web Apps的结果，并将值放入电子表格中. 在运行脚本之前，请先部署Web Apps.

At first, please copy and paste the following script. And please set testFunction11() to the button. When testFunction11() is run, testFunction11() requests to Web Apps (doGet()), and Web Apps requests to https://api.apiservice.com/api/v1/xxx?fields=somefields. By this, X-Forwarded-For is not used to the header of request. Then, the result of Web Apps is returned and put the value to the spreadsheet. Please deploy Web Apps, before you run the script.

function testFunction11() {
  var rng = SpreadsheetApp.getActiveRange();
  var url = ScriptApp.getService().getUrl();
  var params = {method: "get", headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}};
  var res = UrlFetchApp.fetch(url, params);
  Logger.log(res.getContentText());
  rng.setValue(res);
}

function doGet() {
  var url = "https://api.apiservice.com/api/v1/xxx?fields=somefields";
  var encodedAuthInformation = Utilities.base64Encode("username:key");
  var headers = {"Authorization" : "Basic " + encodedAuthInformation};
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': headers
  };
  var res = UrlFetchApp.fetch(url, params);
  return ContentService.createTextOutput(res.getContentText());
}

部署Web应用程序:

在运行此脚本之前，请先部署Web Apps.

Deploy Web Apps:

Before you run this script, please deploy Web Apps.

• 在脚本编辑器上
• 发布->部署为Web App
• On the Script Editor
• Publish -> Deploy as Web App
1. 创建新的项目版本
2. 在执行应用"上，选择我"
3. 在有权访问该应用的用户"中，
   • 如果该功能仅由您使用，请选择仅我自己".
   • 如果该功能已被多个用户使用，请选择任何人".
1. Create new Project version
2. At "Execute the app a"s, select "Me"
3. At "Who has access to the app",
   • if the function is used by only you, select "Only myself".
   • if the function is used by several users, select "Anyone".
• 修改脚本后，请重新部署Web Apps作为新版本.这样，最新的脚本就会反映到Web Apps.这很重要.
• 这是一个简单的示例脚本.因此，请根据您的情况进行修改.
• When you modified the script, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. This is an important point.
• This is a simple sample script. So please modify to your situation.
• 网络应用
• 利用Google的Web Apps优势Apps脚本
• Web Apps
• Taking advantage of Web Apps with Google Apps Script

这篇关于如何为按钮分配具有基本授权的UrlFetchApp?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..