DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 963,274 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
oschertar
oschertar

Posted on

How to export your data from Google Sheets to JSON

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.

Freezing top row

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:

How to access 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.

Save and execute the script

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

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!

New option

If you click this new option you got this πŸ’ͺ

Export JSON

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 (0)

What image format should you use in your next project? πŸ€”