Converting JSON to Google Sheet

Converting JSON to Google Sheet
I have worked on a few projects recently where the data pull was in JSON format and not the spreadsheet-ready database pulls that I am used to from my SQL queries against a database such as MySQL, Vertica, Postgres, etc. I am not a programmer and the JSON format is not something that I am used to working with comfortably yet, so I set out to investigate ways to pull JSON formatted data and get it into a working format (in this case a spreadsheet). I am planning to write soon on techniques to pull and parse data in RStudio.

My hope is that this post may help some people get to work with their data quickly without having to wrestle too much with parsing the data into an analysis-friendly format.

What is JSON?
JSON (JavaScript Object Notation) is described by http://json.org/ as a lightweight data-interchangeable format that is easy for humans to read and write and easy for machines to generate. Apparently JSON is an ideal data interchange language as it is programming language independent but uses conventions familiar to programmers of the C-family of languages.

My recent experiences pulling JSON formatted data have been via a pull from an application, such as Keen IO, or by using cURL and a URL provided by a developer to pull the data needed for analysis.

If you want to see an example of JSON formatted data, take a look at the data on Lady Gaga from graph.facebook.com: http://graph.facebook.com/ladygaga

How to pull JSON data into Google Sheets:
You will need to set up Google Sheets with the functionality to pull the data, the steps are as follows:

  1. Open Google Docs and go to Tools > Script editor
  2. Select “Create script for” > “Custom functions in sheets”
  3. Delete the existing code
  4. Paste in the import_json_appsscript.js code (Thank you Chris Keller for making this code! available on your GitHub repository)
    1. Tip: click the “Raw” button at the upper-right on the GitHub repository as this will allow you to select all and copy the code more easily.
  5. Rename the script, I used ImportJSON.gs

Now you should be able to type =ImportJSON() into a cell in a Google Sheet, including the URL/path to pull and format the JSON data.

IMPORTANT NOTE: I noticed that in Google sheets the =ImportJSON() command does not show up in the list of functions that automatically populates when you begin to type in the function, so you will need to type in the entire =ImportJSON() command in the cell (make sure to wrap the URL/path within quotes inside the parenthesis).

Let’s test it out:
Click here to see a clip of my test to generate some JSON data and pull it into Google Sheets.

I want to acknowledge the articles and that I pulled from to write this post:

Advertisements

One thought on “Converting JSON to Google Sheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s