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.
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.
Access into this tool doing click in Extensions > Apps Script:
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.
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 💪
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.
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