Google App Script To Fetch Data From JSON Webservice and Write them to google spreadsheet.

pullJSON.js

function pullJSON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();


  var url="http://example.com/feeds?type=json"; // Paste your JSON URL here
  
   var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText()); //
  var dataSet = dataAll;
  
  
  var rows = [],
      data;
  

  for (i = 0; i < dataSet.length; i++) {
    data = dataSet[i];
    rows.push([data.id, data.name,data.email]); //your JSON entities here
  }

  dataRange = sheet.getRange(1, 1, rows.length, 3); // 3 Denotes total number of entites
  dataRange.setValues(rows);

}

Yes, never post your api keys.

Thanks for posting this! Used it to scratch an itch with the Asana and BaseCRM APIs, now posted here:

• https://gist.github.com/superstrong/b8d7413724ce311d11e672ad5d2c57c7
• https://gist.github.com/superstrong/bc60593321d46fa06774310b18ed41d0

Nice! Thanks for sharing!

Simple and straight forward.

Using it to sync some rest data to a spreadsheet

Will it work with a Gzip encoded JSON?

Hi varun-raj,

I modified the code:

for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
rows.push([data.format,data.name,data.value,data.type]); //your JSON entities here
}

LINE no: 21: dataRange = sheet.getRange(1, 1, rows.length, 4); // 3 Denotes total number of entites
dataRange.setValues(rows);

But, google generate en error at while I'm executing

Error:

"The coordinates or dimensions of the range are invalid. (line 21, file "Code")"

Hi varun-raj
is it possible to get this script working for a JSON data returned from for example https://finance.google.com/finance/data?dp=mra&output=json&catid=all&cid=2451462

appreciate any help on the changes needed.
also dataRange.setValues(rows); is failing throwing a permission error

@varun-raj, how do i parse a JSON like this? Your code doesn't work with this JSON. Can you help urgently?

{
"result":
[
{
"sg_event_id": "92-OndRfTs6fZjNdHWzLBw",
"timestamp": 1529618395,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:59:55.000Z",
"created_at": "2018-06-21T22:00:28.532Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 38,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "[email protected]"
}, {
"sg_event_id": "bjMlfsSfRyuXEVy8LndsYA",
"timestamp": 1529618349,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:59:09.000Z",
"created_at": "2018-06-21T21:59:39.491Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 36,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "[email protected]"
}, {
"sg_event_id": "fru_s2s1RtueuqBMNoIoTg",
"timestamp": 1529618255,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:57:35.000Z",
"created_at": "2018-06-21T21:58:20.374Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 29,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "[email protected]"
}
]
}

@varun-raj, your code is not working in my case

I am trying to grab the data from a webhook(data is in JSON) and post it to my Google sheet.

In the code, the first line won't work for me as I am deploying the script as a web app(which would catch the webhook)

 var response = UrlFetchApp.fetch(url); // get feed
  var dataAll = JSON.parse(response.getContentText()); //
  var dataSet = dataAll;

In place of line 2 I am trying with these but I have not got any results.

//1// var myObj = e.postData.getDataAsString(); 

//2//var myObj2=JSON.parse(e.getContentText());

//3// var myObj3= JSON.parse(e);

None of these is giving me any results.(sheet is blank)
but when i use this :
sheet.getRange(lastRow+1, 1).setValue(JSON.stringify(e));
I am getting the raw data into a single cell.
This the page containing references of what is coming as the webhook payload:

https://help.shopify.com/en/api/reference/events/webhook

Could you please help me?

This is great and useful - any tips on bringing in a lot of data? I've got ~50k lines of data w 8 data points and I get only very slow responses and timeouts. Thanks!