JSON, also known as JavaScript Object Notation, is an open standard and data interchange format that empowers much of communications done today by modern software applications.
It’s an incredibly compact format that requires a lot less storage space than XML and it’s even a lot more easier to read! But even in a beautified form, when we try to read something is missing – you got it, it’s not in a spreadsheet!
The Add-on can be installed and you can connect spreadsheets to your mysql database in just a few clicks – no other setup needed!
One easy way to import JSON to Google Sheets is with the Google Sheets Add-on KPIBees. With just a few clicks, JSONs can be imported into Google Sheets.
Go to the KPIBees Gsuite Marketplace listing and install the Google Sheets Add-on.
STEP 2
Grant access to KPIBees on your Google account – we are GDPR compliant!
STEP 3
Open any google spreadsheet and go to Add-ons in the page menu and click on Launch.
STEP 4
Click on the JSON integration.
STEP 5
Add a Name to the query and add the json data source url in the Data Url field.
STEP 6
Add a Name to the query and add the JSON data source url in the Data Url field. For simple queries you can already hit the save button and import the JSON data into the spreadsheet.
STEP 7 (optional)
If your JSON url is behind a login wall it won’t be accessible to KPIBees directly. To test whether your url is accessible, open it in an incognito browser and check if your JSON is displayed. To grant access, fill in the Request Headers parameters.
STEP 8
Run the query and convert the JSON into Google Sheets. The JSON query result will be written starting with your selected spreadsheet.
STEP 9 (optional)
JSONs vary in the contained data structure, therefore KPIBees offers multiple strategies to display your JSON. If you’re not happy with the Default Display Strategy, consider selecting a different Display Strategy and running your query again.
JSONPath is a query language used for selecting node from a JSON document. If you only need to retrieve a certain part of the JSON, you can use JSONPath to retrieve by filling the JSON Path field. To find out more about JSONPath syntax, check this resource.
A lot of times when you’re working with a JSON API, you might have the need to view the data in a better format. With KPIBees, you can automatically import JSON api to Google Sheets and trigger automatic refresh times. With every performed refresh, there’s a possibility to set email or slack notifications with the data in pdf format.
Combine JSON data in spreadsheet reports with:
Here are all the Google Sheets integrations that KPIBees supports:
Google Spreadsheets
