How to export your data from Google Sheets to JSON
Tags: json, javascript, googlesheets, webdev
Published: 2022-09-09 12:40:55


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bdkt64giz9wy2y31d605.png)

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g61gak5kxn7uznldox1u.png)

### Step 3
In the opened window, paste the following script found in this [Gist](https://gist.githubusercontent.com/pamelafox/1878143/raw/6c23f71231ce1fa09be2d515f317ffe70e4b19aa/exportjson.js)* 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k5hljk5l85heh4wrfghk.png)

Google may require additional permissions in order to do this correctly so accept them.
![Accept permissions](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ojgiikp4blj510lfq8dp.png)

### 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n1w4l2w4lspdbbzzdy6q.png)

If you click this new option you got this 💪

![Export JSON](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6xb4rwgs8pttjy52e2gb.png)


## 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](https://github.com/pamelafox) for share the script