JSON is a powerful file format based on Javascript object syntax that can store a wealth of complex 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 scrapes 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 get a set of data from JSON and analyze it in a comprehensible table? The first step is to get yourself setup with our ImportJSON Google sheets add-on, which is as simple as getting the add-on in your GoogleSheets.
Here’s the 2-minute process:
- Simply, install the add-on into your Google Sheets from the Google Workspace Marketplace
- From there, open a new spreadsheet and activate the add-on from the menu Extensions
- 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
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 this 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 (aka url)
This is so simple that it will literally blow your mind. Once you have the ImportJSON add-on activated 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(A1)
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(A1)
The data will instantly appear, ready for you to manipulate in a table format.
From a 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 parse your JSON directly from Google Sheets!
=IMPORTJSON("{'prefix':'roller','dict':'0','res':{'sug':['roller','roller quad','rollerblade','roller fender','rollers','rollerball','rollergames nes','roller enfant','roller fille','roller skating'],'categories':[[115180,'Articles de skate, de roller et de trottinette'],[7278,'Stylos haut de gamme']]},'savedser':{}}")
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 – you guessed it – 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(A1)
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 represented 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.
This means that you might end up with some unnecessary information in the process. To ensure that you’re only getting what you need, you can also use filters within your formulas to save you from sifting through the whole table.
This will look something like:
=IMPORTJSON(A1,filter)
If you want to apply more than one filter, separate each with a comma and hit enter. Then marvel at all of the information that matters to you.
Making this data mining technique more useful for you
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 what industry you work in, 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 post 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!
