How do I get REST API JSON data and extract them to Google Spreadsheet as a table?

Question

We use AskNicely to identify our NetPromoter Score by sending out surveys and measuring our performance using the data that we get from the responses. We then pull the responses out and get the sample JSON data below:

{
  "success": true,
  "total": "14836",
  "totalpages": "2968",
  "pagenumber": "1",
  "pagesize": "50000",
  "since_time": "0",
  "data": [
    {
      "response_id": "20352",
      "person_id": "11007",
      "name": "Mark Williams",
      "email": "markwilliams_23@hotmail.com",
      "answer": "5",
      "answerlabel": "5",
      "data": null,
      "comment": "Response time is too long. But was able to resolve my issue",
      "note": null,
      "status": "",
      "dontcontact": null,
      "sent": "2018-10-03 08:53:21",
      "opened": "2018-10-03 08:53:21",
      "responded": "2018-10-03 09:21:30",
      "lastemailed": "2018-10-03 08:53:21",
      "created": "2018-10-03 08:53:02",
      "segment": "",
      "published": "",
      "publishedname": "-",
      "publishedavatar": null,
      "customernumber_c": "171502",
      "companyname_c": "Williams, Mark",
      "agentname_c": "MICHAEL S",
      "city_c": "RIDGEHAVEN",
      "segment_b_c": null,
      "state_c": "SA",
      "workflow_custom_alerts_c": null,
      "workflow_check_for_publish_c": null,
      "workflow_detractor_alerts_c": null,
      "company_c": null,
      "segment_a_c": null,
      "deliverymethod": "email",
      "dashboard": "recommend",
      "email_token": "568e62f94c"
    },
    {
      "response_id": "20351",
      "person_id": "11006",
      "name": "Susie White",
      "email": "Whites61@bigpond.net.au",
      "answer": "9",
      "answerlabel": "9",
      "data": null,
      "comment": "Best service i've gotten so far!",
      "note": null,
      "status": "",
      "dontcontact": null,
      "sent": "2018-10-03 08:53:21",
      "opened": "2018-10-03 08:53:21",
      "responded": "2018-10-03 08:53:21",
      "lastemailed": "2018-10-03 08:53:21",
      "created": "2018-10-03 08:53:02",
      "segment": "",
      "published": "",
      "publishedname": "-",
      "publishedavatar": null,
      "customernumber_c": "171382",
      "companyname_c": "White, Susie",
      "agentname_c": "SAM N",
      "city_c": "Magill",
      "segment_b_c": null,
      "state_c": "SA",
      "workflow_custom_alerts_c": null,
      "workflow_check_for_publish_c": null,
      "workflow_detractor_alerts_c": null,
      "company_c": null,
      "segment_a_c": null,
      "deliverymethod": "email",
      "dashboard": "recommend",
      "email_token": "e257aab272"
    },

I am able to successfully pull the data by using the script below:

function callAPI() {

// Call the Numbers API for random math fact
var response = UrlFetchApp.fetch("insert URL");
var a = response.getContentText();
Logger.log(a);   
}

But that's the extent of my knowledge. What I need is to pull each item inside "Data" and make the label as table headers in row 1, then their value being pulled and pasted per row. I have been searching stackoverflow and google but so far, what i've seen are scripts to just get 1 layer of data. Can anyone route me to the correct resource please?

For starters, you'll have to iterate JSON object and then paste the data in spreadsheet row. You can do this operation by pasting one row at a time or dumping an array at once. Refer to the links below 1. https://stackoverflow.com/a/19324092/3157062 2. https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange() — Parag Jadhav, Oct 03 '18 at 05:06

score 0 · Accepted Answer · answered Oct 03 '18 at 22:57

0

I was able to find a code that works perfectly. It's from ImportJSON by Trevor Lohrbeer

But the one i used was the older version. Not sure why it's the one that worked for me though: Version 1.1

answered Oct 03 '18 at 22:57

Michael Banawa

181
4
18

How do I get REST API JSON data and extract them to Google Spreadsheet as a table?

1 Answers1