javascript - How to create webhook from Google Apps Script using the "exec" url rather than "dev&

admin管理员组

文章数量:1342623

Been attempting to set-up a webhook in Google Sheets Apps Script that will respond when changes are made in Trello. To do this, it's suggested to deploy the script as a web app (requiring doGet and doPost functions that return HTML) to create a URL destination for receiving post and get requests.

The web app makes two URLs, one ends in /dev and is for development purposes (doesn't respond to post requests) and the other ends in /exec for normal access.

I've deployed the web app, both URL's are accessible and responsive in my browser (can view exec in incognito without sign-in), app is published to execute as "me" and be accessible by "anyone, even anonymous."

I have been able to successfully use the below code to create web-hooks for the /dev URL but not the /exec URL.

function create() {
  var url = '/?key=a211f4aca7fb3e521d652730dd231cb6'

  var payload = { 
    "key": "xxxxxxxxxxx",
    "token" : "xxxxxxxxxxxxx",
    "callbackURL": "",
    "idModel":"xxxxxxxx",
    "description": "GW Test"  
  }
  var options = {"method" : "post",
                 "payload" : payload,
                 "muteHttpExceptions": true
                 };

  var response = UrlFetchApp.fetch(url,options);
}

When using the /dev URL, I can see that a new web-hook is added in my Trello, but the /exec URL returns the following error:

{"message":"URL () did not return 200 status code, got 403","error":"ERROR"}

So Trello was expecting a "200 Status Code" returned to verify that the script ran properly, but received 403 instead. What confuses me is why this doesn't happen when using /dev? How can I get it to accept my /exec URL?

More Info:

I can get my code to respond to put and get requests when I use link or load the URL directly in my browser. It responds by filling the first six (6) cells in the spreadsheet.

I have republished as a new version and verified that the code still works, but I still get the error message from Trello saying that they received a 403 rather than 200 code. And I only receive this error when using /exec not /dev. And since /dev can't receive post requests, it isn't going to be helpful :(

Below is the remainder of an example:

function doSomething() {
  var values = [11,12];
  SpreadsheetApp.getActiveSheet().getRange("A1:B1").setValue(values);
}


function create() {
  var url = '/?key=a211f4aca7fb3e521d652730dd231cb6'  
  var payload = { 
    "key": "xxxxxxxxxxxxxxxxxx",
    "token" : "xxxxxxxxxxxxxxxxxxx": "",
    "idModel":"xxxxxxxxxxxxxxxxxxxxxxxx",
    "description": "GW Test"  
  }
  var options = {"method" : "delete",
                 "payload" : payload,
              // "muteHttpExceptions": true
                 };

  var response = UrlFetchApp.fetch(url,options);           // creates webhook
}


// function that fires when the webapp receives a GET request
function doGet(e) {
  doSomething();
  var values = [21,22];
  SpreadsheetApp.getActiveSheet().getRange("A2:B2").setValue(values);
  return HtmlService.createHtmlOutput("something Get-ed");
}

function doPost(e) {
  doSomething();
  var values = [31,32];
  SpreadsheetApp.getActiveSheet().getRange("A3:B3").setValue(values);
  return ContentService.createTextOutput("something Posted");
  //var params = JSON.stringify(e);
}

Been attempting to set-up a webhook in Google Sheets Apps Script that will respond when changes are made in Trello. To do this, it's suggested to deploy the script as a web app (requiring doGet and doPost functions that return HTML) to create a URL destination for receiving post and get requests.

The web app makes two URLs, one ends in /dev and is for development purposes (doesn't respond to post requests) and the other ends in /exec for normal access.

I've deployed the web app, both URL's are accessible and responsive in my browser (can view exec in incognito without sign-in), app is published to execute as "me" and be accessible by "anyone, even anonymous."

I have been able to successfully use the below code to create web-hooks for the /dev URL but not the /exec URL.

function create() {
  var url = 'https://api.trello./1/tokens/ae6ebe60b45abcd2d4aa945c9ab4c4571bd6b6f7856b1df0cd387fbffc649579/webhooks/?key=a211f4aca7fb3e521d652730dd231cb6'

  var payload = { 
    "key": "xxxxxxxxxxx",
    "token" : "xxxxxxxxxxxxx",
    "callbackURL": "https://script.google./macros/s/AKfycbw51TYGWHe95hKdcAs4l7E2eg0AtBi8e48lf_iafKYI/dev",
    "idModel":"xxxxxxxx",
    "description": "GW Test"  
  }
  var options = {"method" : "post",
                 "payload" : payload,
                 "muteHttpExceptions": true
                 };

  var response = UrlFetchApp.fetch(url,options);
}

When using the /dev URL, I can see that a new web-hook is added in my Trello, but the /exec URL returns the following error:

{"message":"URL (https://script.google./macros/s/AKfycbxo90ucgXXz7MG6Z3wb2KD-PJn3akGKoelXLJ2mkg/exec) did not return 200 status code, got 403","error":"ERROR"}

So Trello was expecting a "200 Status Code" returned to verify that the script ran properly, but received 403 instead. What confuses me is why this doesn't happen when using /dev? How can I get it to accept my /exec URL?

More Info:

I can get my code to respond to put and get requests when I use link or load the URL directly in my browser. It responds by filling the first six (6) cells in the spreadsheet.

I have republished as a new version and verified that the code still works, but I still get the error message from Trello saying that they received a 403 rather than 200 code. And I only receive this error when using /exec not /dev. And since /dev can't receive post requests, it isn't going to be helpful :(

Below is the remainder of an example:

function doSomething() {
  var values = [11,12];
  SpreadsheetApp.getActiveSheet().getRange("A1:B1").setValue(values);
}


function create() {
  var url = 'https://api.trello./1/tokens/ae6ebe60b45abcd2d4aa945c9ab4c4571bd6b6f7856b1df0cd387fbffc649579/webhooks/?key=a211f4aca7fb3e521d652730dd231cb6'  
  var payload = { 
    "key": "xxxxxxxxxxxxxxxxxx",
    "token" : "xxxxxxxxxxxxxxxxxxx": "https://script.google./macros/s/AKfycbw51TYGWHe95hKdcAs4l7E2eg0AtBi8e48lf_iafKYI/dev",
    "idModel":"xxxxxxxxxxxxxxxxxxxxxxxx",
    "description": "GW Test"  
  }
  var options = {"method" : "delete",
                 "payload" : payload,
              // "muteHttpExceptions": true
                 };

  var response = UrlFetchApp.fetch(url,options);           // creates webhook
}


// function that fires when the webapp receives a GET request
function doGet(e) {
  doSomething();
  var values = [21,22];
  SpreadsheetApp.getActiveSheet().getRange("A2:B2").setValue(values);
  return HtmlService.createHtmlOutput("something Get-ed");
}

function doPost(e) {
  doSomething();
  var values = [31,32];
  SpreadsheetApp.getActiveSheet().getRange("A3:B3").setValue(values);
  return ContentService.createTextOutput("something Posted");
  //var params = JSON.stringify(e);
}

• javascript
• google-apps-script
• google-sheets
• webhooks
• trello

Share Improve this question Follow edited Aug 9, 2019 at 2:17 Chuck's Keg asked Aug 9, 2019 at 0:57 Chuck's KegChuck's Keg 7111 gold badge11 silver badge33 bronze badges 5

• In your situation, the endpoint of /dev works. But the endpoint of /exec doesn't work. From such situation, I thought that the latest script might not be reflected to Web Apps. So how about redeploying Web Apps as new version? By this, please test it again. If this was not the direct solution of your issue, I apologize. If this didn't work, can you provide a script of Web Apps? – Tanaike Commented Aug 9, 2019 at 1:09
• I can get my code to respond to put and get requests when I use link or load the URL directly in my browser. It responds by filling the first six (6) cells in the spreadsheet. I have republished as a new version and verified that the code still works, but I still get the error message from Trello saying that they received a 403 rather than 200 code. And I only receive this error when using /exec not /dev. And since /dev can't receive post requests, it isn't going to be helpful :( – Chuck's Keg Commented Aug 9, 2019 at 2:10
• Can you try it in a new script file? It might be a concurrent issue. Publish a new script to call trello api from the new script in a new project to call the old published url? i.e., make a calling project and published project different. Also, try JSON stringifying payload.. – TheMaster Commented Aug 9, 2019 at 6:27
• I proposed a workaround as an answer. Could you please confirm it? If I misunderstood your question and this didn't resolve your issue, I apologize. – Tanaike Commented Aug 9, 2019 at 8:21
• @Chuck's Keg Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. – Tanaike Commented Aug 14, 2019 at 2:36

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 9

How about this answer?

Issue:

From your question, the following error is returned from API.

{"message":"URL (https://script.google./macros/s/AKfycbxo90ucgXXz7MG6Z3wb2KD-PJn3akGKoelXLJ2mkg/exec) did not return 200 status code, got 403","error":"ERROR"}

About the reason of above error, at first, I thought that the latest script might not be reflected to Web Apps. But from your reply ment, it was found that the latest script was reflected to Web Apps. From this situation, I experimented for retrieving the error of 403 from the deployed Web Apps.

Preparation:

As the preparation, Web Apps was deployed with "Execute the app as" and "Who has access to the app" as Me and Anyone, even anonymous, respectively. The sample script for Web Apps is as follows.

function doGet(e) {return ContentService.createTextOutput("GET: Done.")}

Experiment:

Using Google Apps Script:

At first, I checked the status code using Google Apps Script, when it requests to Web Apps. The script is as follows.

function myFunction() {
  var url_exec = "https://script.google./macros/s/###/exec";
  var url_dev = "https://script.google./macros/s/###/dev";
  var res = UrlFetchApp.fetchAll([{url: url_exec}, {url: url_dev}]);
  res.forEach(function(e) {
    Logger.log(e.getResponseCode());
  });
}

In this case, the status code of 200 was obtained for both endpoints of exec and dev. Using Google Apps Script, the status code of 403 couldn't be retrieved.

Using Curl:

In order to retrieve the status code with curl, curl -s -o /dev/null -w "%{http_code}" http://www.example/ is used. This is from this thread. Here, the status code was investigated using the curl mand. Because the curl can access by 2 kinds of request by the options as showing below.

1. --include: Include the HTTP response headers in the output. The HTTP response headers can include things like server name, cookies, date of the document, HTTP version and more...

2. --head: (HTTP FTP FILE) Fetch the headers only! HTTP-servers feature the mand HEAD which this uses to get nothing but the header of a document. When used on an FTP or FILE file, curl displays the file size and last modification time only.

Using above options, the following 4 patterns were investigated.

1. Request to the endpoint of exec using the option --include.
   • curl -sL --include -o /dev/null -w "%{http_code}" "https://script.google./macros/s/###/exec"
   • 200 was returned.
2. Request to the endpoint of dev using the option --include.
   • curl -sL --include -o /dev/null -w "%{http_code}" "https://script.google./macros/s/###/dev"
   • 200 was returned.
3. Request to the endpoint of exec using the option --head.
   • curl -sL --head -o /dev/null -w "%{http_code}" "https://script.google./macros/s/###/exec"
   • 403 was returned.
4. Request to the endpoint of dev using the option --head.
   • curl -sL --head -o /dev/null -w "%{http_code}" "https://script.google./macros/s/###/dev"
   • 200 was returned.

As the result, it was found that when the Web Apps of the endpoint of exec was requested with the option --head, the status code of 403 was obtained.

Result and discussions:

When the option --head is used for the curl mand, from the document, this means that it requests only header and doesn't request the body. By this, it was found that the status code of 403 was returned.

Here, why was the status code of 200 returned for both options of --include and --head when it requests to the endpoint of dev? It is considered that the reason of this is due to that the login screen was returned. When the endpoint of dev is accessed, it is required to use the access token. When the access token is not used, the login screen is returned. In this case, the status code of 200 is returned. As the test case, when the access token is used for the endpoint of dev using below curl mand,

curl -sL --head -H "Authorization: Bearer ###" -o /dev/null -w "%{http_code}" "https://script.google./macros/s/###/dev"

The status code of 403 was returned. From this result, the following results were obtained.

• When only the header is retrieved under that the Web Apps works fine, 403 is returned.
• When the login screen is returned, 200 is returned.

As the result, it is considered that the reason of error of {"message":"URL (https://script.google./macros/s/AKfycbxo90ucgXXz7MG6Z3wb2KD-PJn3akGKoelXLJ2mkg/exec) did not return 200 status code, got 403","error":"ERROR"} is due to above situation.

Workaround:

From above results, when the Web Apps is deployed with "Execute the app as" and "Who has access to the app" as Me and Only myself, respectively, when it requests to the endpoint of exec using the option --head, it is found that the status code of 200 will be returned. Because at this time, the login screen is displayed.

Using this situation, how about the following flow as a workaround?

1. When the endpoint of Web Apps is registered to the API, please deploy the Web Apps with "Execute the app as" and "Who has access to the app" as Me and Only myself, respectively.

2. After the registration was pleted, please deploy Web Apps with "Execute the app as" and "Who has access to the app" as Me and Anyone, even anonymous, respectively.

   • By this, the Web Apps can be accessed.

By above flow, only when the endpoint is registered, the status code of 200 is returned. But in this workaround, it supposes that the API you want to use might check the requested header. If my guess was not correct, this workaround cannot be used. In that case, I have to apologize.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script
• curl.haxx.se

Added:

In my environment, I could confirm that the endpoint of exec of Web Apps could be registered with the trello API using above workaround. The script I used is as follows.

• Before you run the script, please set the Web Apps as follows.
  • "Execute the app as" and "Who has access to the app" are Me and Only myself, respectively.
• After the response of like {"id":"###","description":"sample","idModel":"###","callbackURL":"https://script.google./macros/s/###/exec","active":true} was retrieved, please set the Web Apps as follows.
  • "Execute the app as" and "Who has access to the app" are Me and Anyone, even anonymous, respectively.

By this flow, the webhook can be used.

Sample script:

var url = 'https://api.trello./1/tokens/###/webhooks/?key=###'
var payload = { 
  "callbackURL": "https://script.google./macros/s/###/exec",
  "idModel":"###",
  "description": "sample"
}
var options = {method: "post", payload: payload};
var res = UrlFetchApp.fetch(url,options);
Logger.log(res.getContentText())

本文标签：