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:
1 2 3 4 5 | {“colors”: [ {“colorname” : “Black”, “hex” : “000000”}, {“colorname” : “White”, “hex” : “FFFFFF”}, {“colorname” : “Red”, “hex” : “FF0000”} ]} |
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).
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).
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).
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).
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.
