How to export your data from Google Sheets to JSON

#json #javascript #googlesheets #webdev

Many times I have the need to use the data I've been working with in Google Sheets in other tool. For example, last week a non technical colleague sent me a data collection in tabular format inside a Google Sheet. Part of my job is automating the integration of this data with other tools. I didn't want to copy and paste everything, so I wondered if there was a way to export this data easily. JSON is the de-facto standard for data interexchange on the Internet and a format almost of the tools I work with are able to understand. I decided then to solve the problem once and for all. Do you want to know how? Keep reading.

The Solution

Step 1

For the solution we are going to implement to work correctly, we will need to freeze the first row or header from our data. So, select the top row and then click on View > Freeze > 1 Row. If you don't do that, the script will throw an error.

Step 2

In this case, we are going to use the Apps Script tool that Google Sheet has integrated in its interface. This allows us to include Javascript code to add new functionalities to our spreadsheet.

Access into this tool doing click in Extensions > Apps Script:

Step 3

In the opened window, paste the following script found in this Gist* and change the title to "Export JSON".

Then, save this script by clicking Save and run the code with the corresponding button.

Google may require additional permissions in order to do this correctly so accept them.

Step 4

Go back to the Google Sheets tab and refresh, and voilà, a new option will appear in the spreadsheet to export your data to JSON format. Congrats!

If you click this new option you got this 💪

Things to improve

Some of the next steps would be:

Try to adapt this process to an addon rather than a script.
Investigate whether this process could be adapted to all spreadsheets. Currently, if we want to export data to JSON, we have to repeat all these steps, over and over again.

Conclusion

Thanks to Google's Apps Script tool, we can add new options and functionalities to our spreadsheet. In this case, we have managed to automate the process of exporting data to JSON format, as I said before, a de-facto standard for data interexchange on the Internet.

In cases with a lot of data, this new option is very helpful, because we don’t have to pass the data manually to other tools doing copy and paste.

*Thanks to Pamela Fox for share the script

Top comments (4)

Nathan Tarasiuk • Dec 22 '23

There's a chrome extension that might be useful for this:
chromewebstore.google.com/detail/s...

It adds a "JSON" button in the toolbar and handles everything for you

Jason Nazario • Sep 12 • Edited

Thanks for this, I added a line to allow '' (underscore) in the key or id column.
char == '' ||

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum_(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
char == '' ||
isDigit(char);
}