google-apps-scriptintercom

Convert API requests to Google Apps Script

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:

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

For the 3rd API request

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

Solution

  • 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'

    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);
  });
}

    Note:

    Reference: