This post is following my previous one -> How to turn Postman API's request into Apps Script code?

I'm trying to convert 3 API requests into a single piece of code via Google Apps Script. The purpose is to export automatically a set of data from my CRM platform into Google Sheet.

Based on my CRM platform documentation, I need to do 3 API requests in order to get the final set of data I want:

• Creating an export job (using a POST request)
• Retrieve the job status from previous request (using GET request)
• Retrieve the exported data (using GET request)

Below is the code for the 1st API request (kindly provided by Tanaike):

function exportjob() {
  var url = 'https://api.intercom.io/export/content/data';
  var options = {
    "method": "post",
    "headers": {
      "Authorization": "Bearer 123456789",
      "Accept": "application/json",
    },
    "contentType": "application/json",
    "payload": JSON.stringify({
      "created_at_after": 1654041600,
      "created_at_before": 1656547200
    })
  }
  var response = UrlFetchApp.fetch(url, options);
  console.log(response.getContentText())
}

The result of this request is the following

Info {"job_identifier":"5gf58ty4y5y45229r", "status":"pending", "download_url":"", "download_expires_at":""}

Next step is to add the 2 other API requests in my script (Retrieve the job status and retrieve the data). However I have a couple of issues:

For the 2nd API request

• How do I include the job_identifier ID in my code? (given in the 1st request)

For the 3rd API request

• How do I retrieve the data with the URL provided in the 2nd API request?
• The data comes as ZIP file, how do I convert it to CSV so Google Sheet can open it?
• How do get the final set of data to be exported in a google sheet?

Apologies for the long post, I'm trying to summarize the documentation from my CRM platform as much as I can. Any help on how to retrieve the job POST and retrieve the set of data to a google sheet via CSV would be highly appreciated.

Thank you

CodePudding user response：

1. About For the 2nd API request

If this is for Retrieve a job status, it seems that the sample curl command is as follows.

curl https://api.intercom.io/export/content/data/v5xsp8c19ji0s82 \
-X GET \
-H 'Authorization:Bearer <Your access token>'
-H 'Accept: application/json'

• It seems that the value of job_identifier can be retrieved from your 1st request shown in your question. Ref

• When this request is done for the first time, it seems that status returns pending. By this, it seems that until status is changed to complete, it is required to wait. Ref

2. About For the 3rd API request

If this is for Retrieve the exported data, it seems that the sample curl command is as follows.

curl https://api.intercom.io/download/content/data/xyz1234 \
-X GET \
-H 'Authorization:Bearer <Your access token>' \
-H 'Accept: application/octet-stream’

In this case, the document says Your exported message data will be streamed continuously back down to you in a gzipped CSV format.. I thought that in this case, the returned data might be able to be ungzipped with Utilities.ungzip, and the ungzipped data might be able to be parsed with Utilities.parseCsv.

3. Using your 3 requests, how about the following sample script?

function exportjob2() {
  var accessToken = "###your access token###"; // Please set your access token.

  // 1st request: This is from https://stackoverflow.com/a/73032528
  var url1 = 'https://api.intercom.io/export/content/data';
  var options1 = {
    "method": "post",
    "headers": {
      "Authorization": "Bearer "   accessToken,
      "Accept": "application/json",
    },
    "contentType": "application/json",
    "payload": JSON.stringify({
      "created_at_after": 1654041600,
      "created_at_before": 1656547200
    })
  }
  var response1 = UrlFetchApp.fetch(url1, options1);
  var { job_identifier } = JSON.parse(response1.getContentText());

  // 2nd request <--- Modified
  var url2 = 'https://api.intercom.io/export/content/data/'   job_identifier;
  var options2 = {
    "headers": {
      "Authorization": "Bearer "   accessToken,
      "Accept": "application/json",
    },
  }
  var response2 = UrlFetchApp.fetch(url2, options2);
  // console.log(response2.getContentText()); // for debug.
  var { download_url, status } = JSON.parse(response2.getContentText());
  while (status == "pending") {
    Utilities.sleep(5000); // Please adjust this value. The current wait time is 5 seconds.
    response2 = UrlFetchApp.fetch(url2, options2);
    // console.log(response2.getContentText()); // for debug.
    var obj = JSON.parse(response2.getContentText());
    status = obj.status;
    download_url = obj.download_url;
  }
  if (!download_url) {
    throw new Error("download_url has no value.");
  }

  // 3rd request
  var options3 = {
    "headers": {
      "Authorization": "Bearer "   accessToken,
      "Accept": "application/octet-stream",
    },
  }
  var response3 = UrlFetchApp.fetch(download_url, options3);
  var blob = response3.getBlob().setContentType("application/zip");
  var csvs = Utilities.unzip(blob);

  // Create a new Spreadsheet and put the CSV data to the 1st tab.
  var ss = SpreadsheetApp.create("sample Spreadsheet");
  csvs.forEach((b, i) => {
    var ar = Utilities.parseCsv(b.getDataAsString());
    var sheet = i == 0 ? ss.getSheets()[i] : ss.insertSheet("sample"   (i   1));
    sheet.getRange(1, 1, ar.length, ar[0].length).setValues(ar);
  });
}

• I'm not sure whether the downloaded the gzip file has the correct mimeType. So I added the mimeType like var blob = response3.getBlob().setContentType("application/x-gzip").

Note:

• When this script is run, the flow of your showing question is done. But, I cannot test this API because I have no account. So, when an error occurs, please check each value and your access token again. And, please provide the error message. By this, I would like to confirm it.

• I thought that the value of download_url returned from the 1st request might be the same with the value of download_url returned from 2nd request. But, I cannot test this API because I have no account. So, please check it, and when my understanding is correct, you can modify the above script.

• This sample script creates a new Spreadsheet. But, if you want to put the CSV data to the existing Spreadsheet, please modify the above script.

Reference:

• fetch(url, params)