Import JSON data into Google Sheets

Question

I am pulling data from a web service and it is formatted as JSON. I am writing a Google Apps Script for Google Sheets that will populate the data for me. My problem is, I can't seem to get it to parse out.

Doing:

var dataset = myJSONtext;
Browser.msgbox(dataset.item[0].key);

errors out, saying:

item[0] is not defined.

Is there some built in way I should be doing this?

the accepted answer's solution is/is going to be deprecated. I suggest @Ahnkiet's answer be accepted. — antony.trupe, Feb 12 '13 at 15:10
Related: [How to import JSON data into Google Spreadsheets?](http://webapps.stackexchange.com/q/73252/22759) at WebApps — kenorb, Jan 28 '15 at 11:20

score 56 · Answer 1 · edited Feb 03 '13 at 17:35

56

JSON.parse

For those who are seeing this in 2011+, as pointed out by Henrique Abreu at the Google support forum, Utilities.jsonParse is/will be deprecated. As you can see from the thread, there's a bug with this function that it does not work when your keys are numbers, ie "1234".

As suggested, you should be using JSON.stringify/parse.

edited Feb 03 '13 at 17:35

antony.trupe

10,640
10
57
84

answered Aug 25 '11 at 02:52

Anh-Kiet Ngo

2,151
1
14
11

2

[JSON.parse](https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/JSON/parse) – Photodeus Oct 09 '12 at 09:14

score 18 · Answer 2 · answered Apr 23 '13 at 19:05

A 2013 update -- Check out the ImportJSON library at

http://blog.fastfedora.com/projects/import-json

"ImportJSON imports data from public JSON APIs into Google Spreadsheets. It aims to operate similarly to how the native Google Spreadsheet functions ImportData and ImportXML work."

Code available here and he has submitted it to the Script Gallery: https://raw.github.com/fastfedora/google-docs/master/scripts/ImportJSON/Code.gs

Example usage: After putting the code in your Google spreadsheet's Script Editor, then paste this in cell A1 of the sheet:

=ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content",               "noInherit,noTruncate,rawHeaders")

If you just want JSON data in a Google Spreadsheet (leaving aside Google App Engine) this is a good option. — duozmo, Jul 11 '13 at 00:48
use `noHeaders` instead of `rawHeaders` to skip the header row — FloatingRock, Jan 24 '16 at 07:36

score 15 · Accepted Answer · edited Mar 24 '15 at 01:25

15

Apps script is (pretty much) just Javascript; plain-old JSON.parse is your best option for parsing JSON into an object representation.

You can also use JSON.stringify to serialize an object into a string representation.

edited Mar 24 '15 at 01:25

joshperry

41,167
16
88
103

answered Oct 18 '10 at 04:45

Tim McNamara

18,019
4
52
83

4

+1 The `jsonParse()` method [was added in August 2010](http://code.google.com/googleapps/appsscript/release_notes.html) and it should be used instead of eval() or copy/pasted scripts. – Alex Jasmin Oct 18 '10 at 22:05
Thanks! Its about time they added that. – joejoeson Nov 11 '10 at 22:45
suggest that for sake of Google's juicy-ness this comment needs to be promoted as the accepted answer. – justSteve Jun 03 '11 at 01:18
10

The other answer is the better one, today (in 2012) we use [JSON.parse](https://developer.mozilla.org/en-US/docs/JavaScript/Reference/Global_Objects/JSON/parse) – Photodeus Oct 09 '12 at 09:11
1

In fact, the Google documentation at https://developers.google.com/apps-script/reference/utilities/utilities now says on jsonParse() that it is deprecated: "As of November 2013, replaced by JSON.parse()" – Max Horn Jul 28 '14 at 14:16

score 1 · Answer 4 · edited May 20 '14 at 17:00

1

Use this gist : https://gist.github.com/varun-raj/5350595a730a62ca1954

Replace

http://example.com/feeds?type=json

with your JSON url

Add your entities here

rows.push([data.id, data.name,data.email]);

edited May 20 '14 at 17:00

Wahyu Kristianto

8,719
6
43
68

answered May 20 '14 at 16:41

user3657546

11
1

Import JSON data into Google Sheets

4 Answers4

Linked