How To Export Google Sheet As JSON, XML

This tutorial explains how to export Google Sheets as JSON and XML files. There might be some scenarios where you would have wanted to export or save your Google Sheet as JSON or XML file format but unfortunately Google Sheets simply don’t support both of these file extensions for export and import. The method explained in this tutorial incorporates a Google Sheet add-on which simply gives you the option to export your Google Sheet with any number of sheets as a JSON or XML file. This add-on offers a number of different options to format the exported JSON and XML file and choose to export cell values as JSON arrays or objects. For XML, you can specify the root element as well.

Other useful Google Sheets tutorials that you can check:

• How to convert a Google Sheet as Google Doc.
• How to delete or hide multiple blank rows in Google Sheets.

How to export Google Sheet data as JSON and XML:

As I mentioned, you can properly format the JSON or XML file which will have your Google Sheets data. The best part is that it can export data from all sheets or selected sheets in JSON and XML format. All you have to do is run this add-on and specify the formatting options of the exported file. It then compiles the Google Sheet into the desired file format and save it in your Google Drive folder i.e. in the same folder where your Google Sheet is kept. Another exciting feature is that you can view the JSON or XML file before exporting the Google Sheet data into them.

While exporting to a JSON file format, you get the options to choose whether you want to export each cell as a JSON array, entire sheet content as an array of JSON objects, or export the entire Google Sheet as a single JSON object, and many other options. So, this add-on can help you to export Google Sheet data to a specific JSON format as per your needs. You get the similar types of options to format the structure of the output XML file as you can choose to export column cells as child elements, choose a character to replace illegal characters in Google Sheet to XML, etc.

Let’s now see the steps to export a Google Sheet as a JSON or XML file:

Step 1: Install Export Google Sheet add-on from the link at the end of this tutorial and then open the Google Sheet you want to export. This add-on will then ask permission for you to run, allow it to run and after that you can start using this Google Sheets add-on to export your Google Sheet data as a JSON or XML file. Below is the screenshot of the Google Sheet which I will be exporting as an XML and JSON file.

Step 2: Now, open the interface of  Export Sheet Data add-on from Add-ons > Export Sheet Data > Open Sidebar.

This will open up a sidebar, as shown in the screenshot below from where you can choose the desired file format and customize formatting options to export the sheet data as per your requirements. Select a file format i.e. JSON or XML from the drop-down list at the top and the specific sheets that you want to export. As per your selected file format, you will see the options to format the structure of the output file. I have already mentioned most of the useful formatting options above. So, you can select these options so as to export the sheet data as per the intended format. To select these options, hover your mouse at end of the label string and then enable the checkbox that appears. That is quite an unusual way to enable users to select options, it took around 10 minutes to figure out how to use the formatting options. It would have been lot better if the checkboxes were made visible without hovering mouse for better user experience.
If you want to inspect the data file to be exported to JSON or XML format, click on the “Visualize” button which will show you the preview of the output file. At last, click on the Export button to compile the Google Sheet into the selected file format.

You can then download the exported file or find it in the same Drive folder of your Google Sheet. So, in this way, you can export a Google Sheet to XML or JSON file.

The screenshot below displays the exported XML file.

After that, I exported the same Google Sheet as JSON file, which is shown in the screenshot below.

Verdict:

This tutorial explains how to export Google Sheets data as JSON and XML file by using a free Google Sheets add-on. The method just involves a couple of steps to export Google Sheets to JSON or XML format with proper structure.

Get Export Sheet Data.