Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google App Script To Fetch Data From JSON Webservice and Write them to google spreadsheet.
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);
}
@GeorgeRhinelander

This comment has been minimized.

Copy link

GeorgeRhinelander commented Aug 23, 2014

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

@varun-raj

This comment has been minimized.

Copy link
Owner Author

varun-raj commented May 5, 2015

Why it doesn't work ?

@Gallion

This comment has been minimized.

Copy link

Gallion commented May 11, 2015

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

@samvignoli

This comment has been minimized.

Copy link

samvignoli commented Jul 15, 2015

Multidimensional and generic plz =)

@tarata

This comment has been minimized.

Copy link

tarata commented Jan 6, 2016

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")
...

@rmeekers

This comment has been minimized.

Copy link

rmeekers commented Jan 12, 2016

How can I output an array which contains multiple levels?

@aelkz

This comment has been minimized.

Copy link

aelkz commented Mar 1, 2016

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

@JimIngramDC

This comment has been minimized.

Copy link

JimIngramDC commented Apr 6, 2016

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?

@glenntmorrison

This comment has been minimized.

Copy link

glenntmorrison commented Sep 16, 2016

Yes, never post your api keys.

@superstrong

This comment has been minimized.

Copy link

superstrong commented Nov 19, 2016

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

@drewrwilson

This comment has been minimized.

Copy link

drewrwilson commented Jun 1, 2017

Nice! Thanks for sharing!

@evansmwendwa

This comment has been minimized.

Copy link

evansmwendwa commented Jun 5, 2017

Simple and straight forward.

Using it to sync some rest data to a spreadsheet

@henriquemeloo

This comment has been minimized.

Copy link

henriquemeloo commented Jul 28, 2017

Will it work with a Gzip encoded JSON?

@jenishlad40

This comment has been minimized.

Copy link

jenishlad40 commented Aug 7, 2017

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

@sudheer82

This comment has been minimized.

Copy link

sudheer82 commented Oct 26, 2017

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

@SIFAR786

This comment has been minimized.

Copy link

SIFAR786 commented Jun 24, 2018

@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": "no.name@noname.com"
}, {
"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": "no.name@noname.com"
}, {
"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": "no.name@noname.com"
}
]
}

@N-neville

This comment has been minimized.

Copy link

N-neville commented Jan 2, 2019

@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?

@bigmamainthemud

This comment has been minimized.

Copy link

bigmamainthemud commented Apr 4, 2019

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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.