Convert a Google Spreadsheet to JSON-Formatted Text

Overview

This article provides a step-by-step guide on how to convert data from a Google Sheet into JSON-formatted text. The author explains the importance of JSON as a lightweight and easy-to-understand file format used for data interchange. They then walk through creating a Google Sheets document with sample data, freezing the title row, and adding an Apps Script to export the spreadsheet’s content as JSON.

The article includes:

• An overview of JSON syntax rules
• A brief explanation of why converting data to JSON is necessary
• Step-by-step instructions on how to create a Google Sheet document, add an Apps Script, grant permissions, and export the spreadsheet data as JSON
• The author provides examples throughout the guide, including creating a sample sheet with music album information and using the script to convert it into JSON format. They also discuss the potential limitations of this method.

Overall, this article is aimed at users who work with Google Sheets and need to convert their data into JSON format for use in development or configuration purposes.

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:

{"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:

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).

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

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).

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).

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.

JSON Best Practices FAQ

Q: What is the primary purpose of using JSON?

A: The primary purpose of using JSON (JavaScript Object Notation) is to exchange data between systems, applications, or languages in a lightweight and easy-to-understand format.

Q: How do I write valid JSON code?

A: To write valid JSON code:

• Use curly braces {} to define objects
• Separate key-value pairs with commas
• Use double quotes ” to enclose strings
• Use square brackets [ ] to define arrays

Example:

Copy code

{

  “name”: “John Doe”,

  “age”: 30,

  “address”: {

“street”: “123 Main St”,

“city”: “Anytown”

  }

}

Q: What is the difference between single quotes and double quotes in JSON?

A: In JSON, both single quotes ” and double quotes “” can be used to enclose strings. However, it’s recommended to use double quotes consistently throughout your code.

Q: Can I use spaces or tabs in my JSON code?

A: Yes, you can use spaces or tabs in your JSON code for readability purposes. However, make sure to remove any unnecessary whitespace when using tools that parse JSON.

Q: How do I represent numbers and dates in JSON?

A: You can represent numbers as integers (42) or floating-point numbers (3.14). For dates, use the ISO 8601 format (e.g., 2022-07-25T14:30:00Z).

Example:

{

  “score”: 100,

  “date”: “2022-07-25”

}

Q: Can I nest objects in JSON?

A: Yes, you can nest objects within each other using the dot notation (e.g., object.property). This is useful for representing hierarchical data.

Example:

{

  “user”: {

“name”: “John Doe”,

“address”: {

   “street”: “123 Main St”,

   “city”: “Anytown”

}

  }

}

Q: How do I handle arrays in JSON?

A: You can represent arrays using square brackets [ ]. Each element within the array is separated by a comma.

Example:

{

  “colors”: [“red”, “green”, “blue”]

}

Q: Can I use comments in my JSON code?

A: No, you cannot include comments directly in your JSON code. However, some tools may support parsing and processing of comment-like data structures (e.g., /* */ comments) when used with certain encoding schemes.

Q: How do I validate or parse JSON data?

A: There are several libraries and tools available for validating and parsing JSON data, such as:

• JavaScript’s built-in JSON.parse() function
• Third-party libraries like jsonschema, joi, or fast-json-parser
• Online validators like jsonlint or json-codesniffer

These tools can help ensure that your JSON data conforms to the expected format and structure.

(Editor’s note: This post, which has been updated, originally ran on May 6, 2022.)