How to convert a Google spreadsheet to JSON formatted text – The new stack

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

  • Data are key-value pairs.
  • Data is separated by commas.
  • Objects are placed in curly brackets.
  • Arrays are stored in 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 is so widespread that it can be found almost everywhere. Open a Linux app configuration and you will find JSON. Create a container manifest… there’s JSON!

Writing JSON isn’t too difficult either. For example, take this snippet of JSON code:

Pretty easy to understand. Each entry above is of the form a key value Pair. You can write these all day, right? But what if you already have a collection of data that you want to convert to JSON format? Suppose you have a Google Sheets document that is in a format that can be converted to JSON. Is it possible to then export this data to JSON formatted text?

Why, yes it is.

let me show you

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

Ready? let’s go to work

Create your table

I’m going to show off a bit of fandom here by creating a Google Sheets Doc for Rush Albums. The data in the table looks like this:

title tape release etiquette
hurry hurry 1974 moon
night flight hurry 1975 mercury
Steel caress hurry 1975 mercury
2112 hurry 1976 mercury
A Farewell to Kings hurry 1977 mercury
hemispheres hurry 1978 mercury
permanent waves hurry 1980 mercury
Moving pictures hurry 1981 mercury
signals hurry 1982 mercury
grace under pressure hurry 1984 mercury
power windows hurry 1985 mercury
Do not shoot hurry 1987 mercury
Presto hurry 1989 Atlantic
Roll the bones hurry 1991 Atlantic
counterparts hurry 1993 Atlantic
test for echo hurry 1996 Atlantic
contrails hurry 2002 Atlantic
Snakes & Arrows hurry 2007 Atlantic
Clockwork Angel hurry 2012 road runner

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

Okay, after putting your data in the spreadsheet, the next step is to create one apps script, a Google Cloud JavaScript tool for integrating and automating tasks. To do this, click on Extensions > Apps Script. In the resulting window, paste the following script found in this essential.

After pasting the script, click Untitled Document and then name it something like JSON EXPORT. Then click on the Save on computer Button to save your previous work. After saving, click the Run button (illustration 1).

Illustration 1: The Run button is the small right-pointing arrow just to the left of Debug.

When you click Run, you will be prompted that the script needs permissions to continue (figure 2).

Authorization popup.

Figure 2: Permissions are always an issue.

Make sure you are passing the correct permissions for the account in question. When you exit this process, you’ll get a warning that Google hasn’t verified the app. Proceed and agree to this by clicking Advanced and then Go JSON (unsafe). Exit Permissions and you will be taken back to the Apps Script window.

Now when you return to the spreadsheet and reload it, you should see a new menu item labeled Export JSON (figure 3).

Google Sheets JSON tool.

Figure 3: Our new menu item for converting to JSON.

Click Export JSON and then select Export JSON for this sheet. The script will do its job and upon completion, a popup window will appear with your JSON formatted text (figure 4).

Conversion to JSON.

Figure 4: Our Rush discography has been converted into a convenient JSON format.

Copy the output, paste it into the popup and use it anywhere you need this JSON formatted code.

One of the nice things about this script is that it allows you to keep adding to the table. So you could create your data, export it to JSON, return to the table, add more data and export it to JSON again 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 stays linked to the spreadsheet).

The only caveat is that when you reopen the spreadsheet, it may take a few seconds for the Export JSON menu to appear. If it doesn’t appear right away, wait for it and it will appear in the toolbar.

Conclusion

If you’re working with Google Sheets to store data and you need to (eventually) convert that data into a document in JSON format, this is one of the best ways to do it. Plus, this is just a cool way to demonstrate how developer-friendly Google apps can be. Try this script and see if you don’t use it to create better JSON code for your development or configuration needs.

About Willie Ash

Check Also

Don’t commit yourself! Here are solid alternatives for Apple’s weaker software

One of the best things about Apple’s Macintosh computers is that they come with a …