Convert Google Spreadhseet data to JSON

Google spreadhseets have emerged as a reliable analytics tool for a variety of purposes. Start-ups use the full suite of google tools as an essential back-end for their product and Google is developing a host of tools to support this direction. Enter Google Apps Script. It is a simple scripting language (reminiscent of Javascript) that allows us to build add-ons on top of products like :

Google apps script support

In a recent project, we were using Google spreadsheets to analyze and play-around with data points. However, data on our back-end server had to be stored in form of JSON as we were using MongoDb. There was a clear disconnect and redundancy too in the work flow. Data metrics and inputs were conceptualized and stored on spreadsheets. That data was painstakingly written to a JSON file (manually Yes :P) and then imported into the relevant MongoDb collection. I decided to create a simple Google Apps script that gave me finer control on the spreadsheet data and made the process of generating JSON files, essentially, automatic. The code can be easily augmented to suit specific needs.

There are three ways to publish a Google Apps Script project (from the docs):

  • Standalone – The script file is not tied with any Spreadsheet or doc and can run without a context
  • Bundled scripts – The script file is bound to a specific Google App like a spreadhseet or a drive for instance.
  • Web Apps – Script files can be hosted as Web Apps and can serve as API’s for services too.

We are gonna create a script file bundled with out spreadsheet/workbook (for excel enthusiasts).

Create an empty google spreadsheet or navigate to the one you want to convert into JSON. Select the “Script Editor..” option under “Tools“.

Script editor

You should be presented with a window like below (choose “Script file” under “New” dropdown)

New Script file

Script files have an extension “.gs“. The file we’ll now be working with is  main.gs . This file would contain the initialization code and UI (if any).

The  onOpen() method is called as soon as the SpreadSheet is opened and the function creates a Menu and adds it to our spreadsheet. It should appear something like this after everything is configured properly :

Menu appearance

The menu items are bound to specific functions which are called when they are clicked. So the   doGet()  method is called when the menu item is clicked. We get a handle on the ActiveSpreadsheet there then select a specific sheet that we would want to work with, through the  book.getSheetByName() method. All that is left now is to define the  convertSheet2Json() method and our template file  index.html .

I have split the code into three simple for loops for the sake of clarity. Otherwise the JSON array can be filled in the second for loop too where we populate the  rowValues array using the  range.getValues() method. This function returns properly formatted  JSONArray .

Finally, we create the template file to display the generated JSON.

The magic happens in the highlighted line where we inject the JSON array returned from   convertSheet2Json() call into the template file. The rest are some utility functions for syntax highlighting and copying of the generated JSON.

That’s pretty much it. We should now have another tool in our scripting arsenal that allows us to fiddle around with spreadsheet data as JSON.

Improving this further:

  • Automatic import of this data to a remote MongoDB instance from the spreadsheet. (Wow)
  • May be a file download option that allows a user to download the JSON file as data could be large in some use cases.

Any insights or suggestions to imporve this workflow further are welcome 🙂