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?
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:
- Open Google Docs and go to Tools > Script editor
- Select “Create script for” > “Custom functions in sheets”
- Delete the existing code
- Paste in the import_json_appsscript.js code (Thank you Chris Keller for making this code! available on your GitHub repository)
- 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.
- 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:
- How to import JSON data into Google Spreadsheets in less than 5 minutes: My blog post here is essentially my personal write up of going through the steps in this article while adding my own findings and examples.
- ImportJSON at Fast Fedora
- Chris Keller’s awesome GitHub repository
- JSON Generator