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

}

I'd like to run the script every minute, but the trigger in Google Sheets doesn't work. Do you a solution?

Why it doesn't work ?

What exactly is the point of this specific line?
var dataSet = dataAll;
Can't you just use dataAll?

Multidimensional and generic plz =)

Hi script work... Great!!
I modify script in......
....
for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
rows.push([data.ID,data.Tempo_Server,data.Tempo_Locale,data.sensorId,data.value]); //your JSON entities here
}

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

}
...

Google send me an email error on execution of this code on trigger time:
...
...coordinate o dimensioni dell'intervallo non valide. (riga 23, file "ImportJSON")
...

How can I output an array which contains multiple levels?

working fine here.
To avoid filling into the first row (generally the header), you can put the value 2 on getRange method call:

dataRange = ss.getRange(2, 1, rows.length, 1);

This 'pullJSON" script has been very useful to me as an absolute beginner.

My objective is to obtain via an API provided by GetSWift.co...a web based app that dispatches deliveries data about 'deliveries' and 'drivers'.'

This utilization of the script to pull in JSON formatted data on drivers works perfectly...it adds a row to a spreadsheet each time the trigger event occurs

function pullJSON() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet = ss.getActiveSheet();
Logger.log(sheets)
var url="https://app.getswift.co/api/v2/drivers?ApiKey=4be189b2-abf5-442d-ab73-b965dec74aa0&Filter=all"; // Paste your JSON URL here

var response = UrlFetchApp.fetch(url); // get feed
var dataAll = JSON.parse(response.getContentText()); //
var dataSet = dataAll;
Logger.log(dataSet)
var rows = [],
data;
Logger.log(rows)

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

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

This utlization of this script causes this message: "The coordinates or dimensions of the range are invalid. (line 24, file "pullJSON") "

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

var url="https://app.getswift.co/api/v2/deliveries/4f7491a1-0907-4b1f-916e-1725586fb016"; // Paste your JSON URL here

var response = UrlFetchApp.fetch(url); // get feed
var dataAll = JSON.parse(response.getContentText()); //
var dataSet = dataAll;
Logger.log(dataSet);
var rows = [],
data;
Logger.log(rows);

for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
rows.push([data.Created,data.Id,data.Reference,data.PickupLocation.name,data.PickupLocation.address,data.PickupLocation.phone,data.DropoffLocation.name,data.DropoffLocation.address,data.DropoffLocation.phone
,data.LastUpdated,data.CurrentStatus,data.Driver.identifier,data.Driver.name,data.Driver.phone,data.Driver.photurl,data.Items.pickupTime,data.Items.dropoffTime,data.Items.earliestTime,data.Items.latestTime,data.DeliveryInstructions,data.trackingUrls.api,data.trackingUrls.url]
)}

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

This is the execution transcript:

[16-04-06 14:21:30:565 PDT] Starting execution
[16-04-06 14:21:30:571 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[16-04-06 14:21:30:861 PDT] Spreadsheet.getSheets() [0.289 seconds]
[16-04-06 14:21:30:861 PDT] Spreadsheet.getActiveSheet() [0 seconds]
[16-04-06 14:21:30:863 PDT] Logger.log([Sheet, []]) [0 seconds]
[16-04-06 14:21:31:077 PDT] UrlFetchApp.fetch([https://app.getswift.co/api/v2/deliveries/4f7491a1-0907-4b1f-916e-1725586fb016]) [0.213 seconds]
[16-04-06 14:21:31:077 PDT] HTTPResponse.getContentText() [0 seconds]
[16-04-06 14:21:31:077 PDT] Logger.log([{currentStatus=Completed, created=2016-03-04T14:35:44.713Z, dropoffLocation={address=Rue 571, Cotonou, Benin, phone=+22922226678, name=Francoise}, pickupLocation={address=Rue 576, Cotonou, Benin, phone=+2290001010, name=giles}, dro={=, =}, ={=, =}, =null, =, =, =, ={=, =, =, =}, =, =[]}, []]...) [0 seconds]
[16-04-06 14:21:31:078 PDT] Logger.log([[], []]) [0 seconds]
[16-04-06 14:21:31:078 PDT] Sheet.getRange([1, 1, 0, 22]) [0 seconds]
[16-04-06 14:21:31:081 PDT] Execution failed: The coordinates or dimensions of the range are invalid. (line 24, file "pullJSON") [0.508 seconds total runtime]

Essentially the issue..it appears to me..is that the attempt to get the length of the rows returns zero instead of 1

Can anyone suggest a course of action for me?

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!