How to get a field from external API using Google Scripts UrlFetchApp.fetch?

Question

How do I get the field: dataMain in external API using Google Apps Script UrlFetchApp.fetch?

Two days on this with no results. The assistance from customer support for this API is a link to https://pestroutes.api-docs.io/3.1/welcome/how-to-use-the-pestroutes-api which does not seem to help. This seems like it should be a very simple thing to do, but apparently not.

External API Docs: https://pestroutes.api-docs.io/3.1/import/importmainobject

Sample Code (Google Apps Script):

function runTest() {
   var at = 'authenticationToken=<myToken>'
   var ak = 'authenticationKey=<myKey>'
   var url ='https://<myCompanyDomain>.pestroutes.com/api/import/main?' + at +'&' + ak;

   var data = [
      {
        'CustomerID':'9854poiu', 
        'CustomerName':'Fred Blair',
        'SquareFt': 3500,
        'Lead':'Fred Blair'
      }
   ];   

   var options = {  
     'method':'post',
     'contentType': 'application/json', 
     'payload': JSON.stringify(data)
   };  

   var response = UrlFetchApp.fetch(url, options);  
   var responseCode = response.getResponseCode()
   var responseBody = response.getContentText()

   if (responseCode === 200) {
      var responseJson = JSON.parse(responseBody);
      Logger.log(responseJson);
   } else {
      Logger.log(Utilities.formatString("Request failed. Expected 200, got %d: %s", responseCode, 
      responseBody))
   }
};

Result:

{
 endpoint=import, 
 params={endpoint=import,
 authenticationToken=<myToken>,
 authenticationKey=<myKey>, 
 dataMain=[],
 action=main}, 
 tokenUsage={requestsReadInLastMinute=2, requestsReadToday=0, requestsWriteToday=105,
 requestsWriteInLastMinute=0}, count=0.0, tokenLimits={limitWriteRequestsPerMinute=60.0,
 limitReadRequestsPerMinute=60.0, limitWriteRequestsPerDay=3000.0, limitReadRequestsPerDay=3000.0}, 
 requestAction=main, processingTime=153 milliseconds, dataIssueDetails=false, success=false
}

As you can see, the dataMain=[] array should have one object according to the API instructions, but always returns empty?

Is this a problem with my code and am misunderstanding how the API works, or is this an issue with the API? Any help or clues would be very appreciated!

PS. And yes, I have replaced my key and token with myKey and myToken for security.

I think that the script of your request is correct. So, for example, when you tested your request with Try it out, will you be able to get the values you expect? Ref — Tanaike
@Tanaike, with regards to the 'Try it Out' feature, i don't know how to replace subdomain with my domain in the url: subdomain.pestroutes.com/api/import/main, thus it comes back as incorrect authentication. ?? — iamtoc
Thank you for replying. I thought that it might be required to confirm whether the result is changed by the domain at the request side. So I proposed it. But from your replying, I could understand that Try it out cannot be used. Thank you for testing it. As other confirmation, for example, when you check Logger.log(response.getContentText()), what result will you get? — Tanaike
@Tanaike, that result comes back as {"params":{"endpoint":"import","action":"main","authenticationToken":"myToken","authenticationKey":"myKey","dataMain":[]},"tokenUsage":{"requestsReadToday":"0","requestsWriteToday":"111","requestsReadInLastMinute":"2","requestsWriteInLastMinute":"0"},"tokenLimits":{"limitReadRequestsPerMinute":60,"limitReadRequestsPerDay":3000,"limitWriteRequestsPerMinute":60,"limitWriteRequestsPerDay":3000},"requestAction":"main","endpoint":"import","success":false,"dataIssueDetails":false,"processingTime":"149 milliseconds","count":0}. — iamtoc
@OlegValter, the response code is 200. Also, i have emailed the API support team and am awaiting a response. Thanks for looking into this. — iamtoc

Michael Benjamin Michael Benjamin · Accepted Answer · 2020-06-23T20:04:29

It looks like this is a question about using the PestRoutes import/main endpoint for import new customers and subscriptions! I am the PestRoutes developer in charge of the API and I would happy to help you <3.

Here is an example for posting a customer to our demo environment:

Abstract:

apiModule.call('import','main',{
    "dataMain":[
        {
            "CustomerID": "PestRoutesOverflow1111",
            "Branch": "Demo Pest Control",
            "CustomerName": "PestRoutes Testing01",
            "CustomerAddress": "Walt Disney World Resort, Orlando, FL 32830",
            "CustomerCity": "Orlando",
            "CustomerState": "FL",
            "CustomerZipCode": "32830",
            "CustomerPhone1": "4428675309",
            "CustomerPhone2": "4438675309",
            "CustomerEmail": "",
            "CustomerStatus": "Act"

            //"Frequency": 90,              //These three fields
            //"ServiceType": 'Quarterly',   // are the minimum to
            //"Price": '111',               // create a subscription

            //"Lead": 'No',                 //Set Yes to revert subscription to a LEAD
            //"LastService": '4/26/18',     // If this is set it will override Lead to No
            //"InitialService": '1/26/18',  // If this is set it will override Lead to No
        }
    ]
});

here is what that looks like as a curl:

curl 'https://demo.pestroutes.com/api/import/main' \
-H 'Connection: keep-alive' \
-H 'Accept: application/json, text/javascript, */*; q=0.01' \
-H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.106 Safari/537.36' \
-H 'Content-Type: application/x-www-form-urlencoded; charset=UTF-8' \
-H 'Origin: null' \
-H 'Sec-Fetch-Site: cross-site' \
-H 'Sec-Fetch-Mode: cors' \
-H 'Sec-Fetch-Dest: empty' \
-H 'Accept-Language: en-US,en;q=0.9' \
--data-raw 'dataMain%5B0%5D%5BCustomerID%5D=PestRoutesOverflow1111&dataMain%5B0%5D%5BBranch%5D=Demo+Pest+Control&dataMain%5B0%5D%5BCustomerName%5D=PestRoutes+Testing01&dataMain%5B0%5D%5BCustomerAddress%5D=Walt+Disney+World+Resort%2C+Orlando%2C+FL+32830&dataMain%5B0%5D%5BCustomerCity%5D=Orlando&dataMain%5B0%5D%5BCustomerState%5D=FL&dataMain%5B0%5D%5BCustomerZipCode%5D=32830&dataMain%5B0%5D%5BCustomerPhone1%5D=4428675309&dataMain%5B0%5D%5BCustomerPhone2%5D=4438675309&dataMain%5B0%5D%5BCustomerEmail%5D=&dataMain%5B0%5D%5BCustomerStatus%5D=Act&authenticationKey=88492884d8154febd1057372867c2e34b371d8fb&authenticationToken=6915e71f53708f17dba090febd2df4f9d79364d7' \
--compressed

As a fetch:

fetch("https://demo.pestroutes.com/api/import/main", {
"headers": {
    "accept": "application/json, text/javascript, */*; q=0.01",
    "accept-language": "en-US,en;q=0.9",
    "content-type": "application/x-www-form-urlencoded; charset=UTF-8",
    "sec-fetch-dest": "empty",
    "sec-fetch-mode": "cors",
    "sec-fetch-site": "cross-site"
},
"referrerPolicy": "no-referrer-when-downgrade",
"body": "dataMain%5B0%5D%5BCustomerID%5D=PestRoutesOverflow1111&dataMain%5B0%5D%5BBranch%5D=Demo+Pest+Control&dataMain%5B0%5D%5BCustomerName%5D=PestRoutes+Testing01&dataMain%5B0%5D%5BCustomerAddress%5D=Walt+Disney+World+Resort%2C+Orlando%2C+FL+32830&dataMain%5B0%5D%5BCustomerCity%5D=Orlando&dataMain%5B0%5D%5BCustomerState%5D=FL&dataMain%5B0%5D%5BCustomerZipCode%5D=32830&dataMain%5B0%5D%5BCustomerPhone1%5D=4428675309&dataMain%5B0%5D%5BCustomerPhone2%5D=4438675309&dataMain%5B0%5D%5BCustomerEmail%5D=&dataMain%5B0%5D%5BCustomerStatus%5D=Act&authenticationKey=88492884d8154febd1057372867c2e34b371d8fb&authenticationToken=6915e71f53708f17dba090febd2df4f9d79364d7",
"method": "POST",
"mode": "cors",
"credentials": "omit"
}).then( r=>r.json()).then(console.log);

First run result:

{
    "params": {
        "endpoint": "import",
        "action": "main",
        "dataMain": [],
        "authenticationKey": "88492884d8154febd1057372867c2e34b371d8fb",
        "authenticationToken": "6915e71f53708f17dba090febd2df4f9d79364d7"
    },
    "tokenUsage": {
        "requestsReadToday": "0",
        "requestsWriteToday": "1",
        "requestsReadInLastMinute": "0",
        "requestsWriteInLastMinute": "0"
    },
    "tokenLimits": {
        "limitReadRequestsPerMinute": 1000,
        "limitReadRequestsPerDay": 1000,
        "limitWriteRequestsPerMinute": 1000,
        "limitWriteRequestsPerDay": 1000
    },
    "requestAction": "main",
    "endpoint": "import",
    "success": true,
    "customersImported": [
        {
            "CustomerID": "PestRoutesOverflow1111",
            "PestRoutesCustomerID": "21257",
            "Action": "Created"
        }
    ],
    "processingTime": "1199 milliseconds",
    "count": 0
}

Second run result:

{
    "params": {
        "endpoint": "import",
        "action": "main",
        "dataMain": [],
        "authenticationKey": "88492884d8154febd1057372867c2e34b371d8fb",
        "authenticationToken": "6915e71f53708f17dba090febd2df4f9d79364d7"
    },
    "tokenUsage": {
        "requestsReadToday": "0",
        "requestsWriteToday": "2",
        "requestsReadInLastMinute": "1",
        "requestsWriteInLastMinute": "0"
    },
    "tokenLimits": {
        "limitReadRequestsPerMinute": 1000,
        "limitReadRequestsPerDay": 1000,
        "limitWriteRequestsPerMinute": 1000,
        "limitWriteRequestsPerDay": 1000
    },
    "requestAction": "main",
    "endpoint": "import",
    "success": true,
    "customersImported": [
        {
            "CustomerID": "PestRoutesOverflow1111",
            "PestRoutesCustomerID": "21257",
            "Action": "Updated"
        }
    ],
    "processingTime": "1036 milliseconds",
    "count": 0
}

Note that while the second request with the same CustomerID will update, usage of this endpoint for customer updates is very dangerous and not recommended. (Email me for alternative workflows)

Note that the dataMain object in the result is always empty. For most PestRoutes endpoints this parameter array will return the payload exactly as received, but this is a legacy endpoint and outputs a little strangely; so you can disregard the emptiness of that field as a symptom of error for this particular endpoint.

As for the encoding content-type; application/x-www-form-urlencoded and multipart/form-data are accepted by the server.

Please reach out to me via email at michael ~at~ pestroutes.com for further assistance :)

How to get a field from external API using Google Scripts UrlFetchApp.fetch?

2 Answers