Import JSON data into Google Sheets

json google-apps-script google-sheets
joejoeson picture joejoeson · Apr 17, 2010 · Viewed 47.5k times · Source

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?

Answer

Anh-Kiet Ngo picture Anh-Kiet Ngo · Aug 25, 2011

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.

Related questions

How to send a JSON payload with UrlFetchApp service?

I'm trying to POST to a web service that is expecting to get JSON as payload using Google Apps Script. I'm using the following code: var options = { "method" : "post", "contentType" : "application/json", "headers" : { "Authorization" : "Basic <Base64 of user:password&…

Read More
Using Google Apps Script to Post JSON Data

I am trying to post JSON data to the URL from google script but getting the above error: Server response : HTTP Status 415 - Unsupported Media Type My code: function myFunctionpost() { var url = "http://abc.xyz.org/jira/rest/api/2/issue"; …

Read More
Slack API chat.update returns 'not_authed' error

I have a google script running as a webapp to handle the backend of a slack app. The app has been Authenticated and I have the OAUTH token from this. I can currently post to a channel with button actions …

Read More