JSON to Google Sheets JSON + Google Sheets to JSON Google Spreadsheets =

KPIBees Google Add-on.

How to import JSON to Google Sheets

What is JSON?

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!

Import JSON to Google Sheets

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. 

Step 1: 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.

 

Google Sheet Add-on location toolbar.

Step 4: Click on the JSON integration.

Select mysql integration to google sheets

Step 5:  Add a Name to the query and add the json data source url in the Data Url field.

Add the json to google sheet data source

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.

 

Add JSON file to Google Sheets

 

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. Here are the possible display types:

  • Default
  • Inner array flattener
  • Cartesian Most common object
  • Reduce heaviest object
  • Longest array
  • Each object in a row

 

JSONPath to Google Sheets

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.

 

Automatically import JSON API to Google Sheets

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. 

 

Automatically add json api to google sheets

 

Combine JSON data in spreadsheet reports with: