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?

Read More:   DataOps and the Problem with ‘Ops’ Terminology – InApps 2022

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:

titlebandreleaselabel
RushRush1974Moon
Fly by NightRush1975Mercury
Caress of SteelRush1975Mercury
2112Rush1976Mercury
A Farewell To KingsRush1977Mercury
HemispheresRush1978Mercury
Permanent WavesRush1980Mercury
Moving PicturesRush1981Mercury
SignalsRush1982Mercury
Grace Under PressureRush1984Mercury
Power WindowsRush1985Mercury
Hold Your FireRush1987Mercury
PrestoRush1989Atlantic
Roll The BonesRush1991Atlantic
CounterpartsRush1993Atlantic
Test For EchoRush1996Atlantic
Vapor TrailsRush2002Atlantic
Snakes & ArrowsRush2007Atlantic
Clockwork AngelsRush2012Roadrunner

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.

Read More:   Update OASIS Combines Open Source and Open Standards with Open Projects

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.