Import JSON data into Google Spreadsheet

Question

I am pulling data down from a webservice and it is formated as JSON. I am writing a google apps script for google spreadsheet that will populate the data form 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?

Any help would be apreciated.

Answer 1

myJSONText apparently contains a text representation of the dataset. You have to deserialize that text in order to easily access the items it contains.

You can do so using the eval function this way:

var dataset = eval("("+myJSONtext+")");

eval() invokes the JavaScript interpreter to evaluate the text. This is dangerous if text is not trusted as it could end up running executable code inside your spreadsheet.

It's safer to use a JSON library for this task:

I suggest you copy/paste the contents of http://www.json.org/json2.js (except for the first line) at the bottom of your script. This will provide a safer JSON.parse() function that can be used this way:

var dataset = JSON.parse(myJSONtext);
Browser.msgBox(dataset.item[0].key);

Note: This answer is outdated! Please read Tim McNamara's answer.

I would delete that one but I can't delete an accepted answer

Answer 2

Try Utilities.jsonParse.