Modal Title
Data / Data Science

How to Convert a Google Spreadsheet to JSON-Formatted Text

Make a bridge between JSON and Google Sheets
May 6th, 2022 11:00am by
Featued image for: How to Convert a Google Spreadsheet to JSON-Formatted Text

JSON stands for JavaScript Object Notation and is an incredibly important open standard file/data interchange format that is lightweight and easy to understand. The JSON syntax rules are quite simple:

  • Data is key-value pairs.
  • Data is separated by commas.
  • Objects are placed within braces.
  • Arrays are stored within brackets.

JSON has numerous use cases and can be found in container manifests, configuration files, public/frontend/internal APIs, NoSQL databases, data exports, and much more. JSON has become so prevalent that it’s just about everywhere. Open a Linux app configuration and you’ll find JSON. Create a container manifest… there’s JSON!

Writing JSON isn’t all that challenging either. Take, for instance, this snippet of JSON code:


Pretty simple to understand. Each entry above is in the form of a key:value pair. You can write those all day, correct? But what if you already have a collection of data that you want to convert to JSON format. Say, for example, you have a Google Sheets document that is laid out in a format that can convert to JSON. Is it possible to then export that data into JSON-formatted text?

Why, yes, it is.

Let me show you.

The only thing you’ll need for this is a Google account.

Ready? Let’s get to work.

Creating Your Spreadsheet

I’m going to show a bit of fandom here, in that I’ve created a Google Sheets document for Rush albums. The data in the spreadsheet looks like this:

title band release label
Rush Rush 1974 Moon
Fly by Night Rush 1975 Mercury
Caress of Steel Rush 1975 Mercury
2112 Rush 1976 Mercury
A Farewell To Kings Rush 1977 Mercury
Hemispheres Rush 1978 Mercury
Permanent Waves Rush 1980 Mercury
Moving Pictures Rush 1981 Mercury
Signals Rush 1982 Mercury
Grace Under Pressure Rush 1984 Mercury
Power Windows Rush 1985 Mercury
Hold Your Fire Rush 1987 Mercury
Presto Rush 1989 Atlantic
Roll The Bones Rush 1991 Atlantic
Counterparts Rush 1993 Atlantic
Test For Echo Rush 1996 Atlantic
Vapor Trails Rush 2002 Atlantic
Snakes & Arrows Rush 2007 Atlantic
Clockwork Angels Rush 2012 Roadrunner

You can create a Sheets doc that contains any type of data. But once you’ve created your spreadsheet, it’s crucial that you freeze the title row. So after crafting your spreadsheet, select the top row and then click View > Freeze > 1 Row. If you don’t do this, the export will error out.

Okay, now that you’ve added your data into the spreadsheet, the next step is to create an Apps Script, a Google Cloud  JavaScript tool to integrate and automate tasks. To do this, click Extensions > Apps Script. In the resulting window, paste the following script found in this Gist.

After pasting the script, click Untitled Document and then name it something like JSON EXPORT. Next, click the Save button to save your work so far. Once it’s saved, click the Run button (Figure 1).

Apps script

Figure 1: The run button is the small right-pointing arrow directly to the left of Debug.

When you click Run, you’ll be prompted that the script needs permissions to continue (Figure 2).

Authorization popup.

Figure 2: Permissions are always an issue.

Make sure you walk through handing over the proper permissions for the account in question. Curing this process you’ll get a warning that Google hasn’t verified the app. Go ahead and okay that by clicking Advanced and then Go to JSON (unsafe). Finish up the permissions and you’ll be directed back to the Apps Script window.

If you now go back to the spreadsheet and reload it, you should see a new menu entry, labeled Export JSON (Figure 3).

Google Sheets JSON tool.

Figure 3: Our new menu entry for the conversion to JSON.

Click Export JSON and then select Export JSON for this sheet. The script will do its thing and, when it completes, a pop-up will appear with your JSON-formatted text (Figure 4).

Conversion to JSON.

Figure 4: Our Rush discography has been converted to a handy JSON format.

Copy and paste the output in the pop-up and use it wherever you need that JSON-formatted code.

One of the nice things about this script is that it allows you to keep adding to the spreadsheet. So you could build your data, export it to JSON, come back to the spreadsheet, add more data, and again export it to JSON and the new data will be included. Even better, you can close the spreadsheet, come back to it later, add more data, and export it as JSON (the Apps Script remains associated with the spreadsheet).

The one caveat is that when you re-open the spreadsheet, it might take a few seconds for the Export JSON menu to appear. If it doesn’t show up immediately, wait for it and it’ll pop into the toolbar.

Conclusion

If you work with Google Sheets to house data, and you need to (at some point) work that data into a JSON-formatted document, this is one of the best ways to do it. On top of which, this is just a cool way to demonstrate how developer-friendly Google apps can be. Give this script a whirl and see if you don’t start using it to create better JSON code for your development or configuration needs.

Group Created with Sketch.
THE NEW STACK UPDATE A newsletter digest of the week’s most important stories & analyses.