How to import JSON data into a table in GoogleSheets

JSON is a powerful file format based on Javascript object syntax that can store a wealth of complex and public information that’s waiting to be extracted. Luckily, our ImportJSON tool makes this intricate process much simpler, giving you access to an abundance of API data that may otherwise go untapped. 

One of the main difficulties of working with this kind of data is the complicated tree formatting of JSON that can be confusing to manipulate. This is exactly why ImportJSON converts JSON data into a table in GoogleSheets, making your approach to the information digestible. 

Read along to learn exactly how to get data from JSON into a table or spreadsheet using this simple process. 

Retrieving JSON data into a table in GoogleSheets

So you’d like to take complex data from a JSON file and analyze it in a comprehensible table? The first step is to get yourself setup with our ImportJSON tool, which is as simple as getting the add-on in your GoogleSheets. 

Here’s the 2-minute process:

  1. Simply, install the tool into your Google Sheets from the Google Workspace Marketplace following the step-by-step instructions indicated by the add-on. 
  2. From there, you can access the tool by clicking “Add-ons” and activating ImportJSON within any given spreadsheet. 
  3. Within the tool, you’ll have access to guidance that can give you further insights into how to leverage the tool within your web data research. 

From there, you’re fully ready to access powerful JSON data to enrich your work with information. Now, let’s talk about how you get data from JSON into a table within GoogleSheets using our tool. 

How to extract JSON data into a digestible table

The first thing to note about JSON data is that there are a number of places where it can be found. And we’d like to address how to approach each one here. 

From an API (url)

This is so simple that it will literally blow your mind. Once you have the ImportJSON add-on configured in your GoogleSheets, all you need to do is add a URL to a cell within your spreadsheet, then input the cell name into the function…

=IMPORTJSON(url cell name) 

Hit enter and watch as the tool instantly pulls the information directly into an intelligible table format – rather than a complicated tree of information.

From a JSON file saved in Google Drive

If you have a specific file that you’d like to extract from, add it to your GoogleDrive. It doesn’t matter what file extension it has – but it is important that it contains a valid JSON object. 

Make sure the file is shareable in your drive settings, then copy only the ID section of the document’s link (tip: this means the part that comes after /d).

Paste the ID into a cell in your spreadsheet, then input the cell name into the function, as we did previously…

=IMPORTJSON(link cell name) 

The data will instantly appear, ready for you to manipulate in a table format. 

From text in a cell

Are we starting to see a pattern? Yes, ImportJSON is a really easy tool to use for taking information from JSON and putting it into a table or spreadsheet. Including if you want to simply parse a JSON into a cell or range of cells. 

Using the same function, all you have to do is add the JSON formatted data in a cell within your spreadsheet. Insert this into our trusty function, and it will separate the information into the table format so that you can manipulate it with filters, column ordering, and more. This will look like…

=IMPORTJSON(cell)

From a cURL

While more and more APIs are being made accessible with just a URL, using a cURL format enables you to access the complex requests that require more information. And ImportJSON, again, makes this simple for you. In fact, no technical knowledge is required. 

Like always, just copy and paste the cURL request into a cell and add this cell name into our trusty function…

=IMPORTJSON(cURL request cell name) 

ImportJSON will process all of this information for you in seconds so that you can focus on getting the information you need rather than wasting time on logistics. 

Filtering your JSON data to get even more out of it

As previously stated, JSON data can be imagined like a tree of information. However, spreadsheets are two dimensional. That’s why ImportJSON is designed to retrieve this data and flatten out the branches of the tree so that the information exists on the same plane.

But because you’re scraping the entire JSON tree, this might mean ending up with some unnecessary information in the process. For example, imagine that you’re looking for the total population of all EU countries. A JSON archive with demographic and geographical data will have this information – and a lot more, as well. 

Filters allow you to weed out what you don’t need to ensure that you’re only getting useful information to your research, saving you from sifting through a complex table. 

This will look something like…

=IMPORTJSON(url, filter) 

If you want to apply more than one filter, separate each with a comma and hit enter. Then marvel as it pulls only information that matters to you. 

Making this data mining technique more useful to your work

If you’re not sure how this function will be useful to you, we’re happy to inform you that there’s a huge amount of public APIs that hold a wealth of information within a number of fields. And with ImportJSON, you can have access to them at a moment’s notice. 

So no matter your industry, this tool can help you enrich your work with relevant and public online data. Here’s a really helpful resource that will help you discover what kind of information is available to you. You can also check out this blogpost where we take a deeper look at how we can utilize information within APIs. 

Has this inspired you to start tapping JSON data with the ImportJSON tool? We’re always looking for ways to help our customers successfully use our products, so please let us know if you have any remaining questions or concerns!