Simple Step to import JSON in Google Sheets
In this article we will see how to import JSON in Google Sheets using the Apps Script Editor.
Step 1: Open Apps Script Editor
Apps Script editor is a function that allows you to write scripts to perform almost everything. We will be using Apps Script Editor because you can’t import JSON in Google Sheets. To open Apps Script Editor follow these steps.
In the top bar, go to Extensions>Apps Script
Step 2: Erase the placeholder code
You will see a screen like this.
In Code.gs erase the code that is already present.
Step 3: Copy and paste the script into the Apps Script Editor
Go to the open-sourced project https://gist.github.com/paulgambill/cacd19da95a1421d3164.
Copy the entire code and paste it into the app script editor. It is one of the many free-to-use scripts that you can use to import JSON into your Google Sheets.
Your console would look something like this.
Step 4: Save and Name your script
Name your project and click on “Save Project”.
Step 5: Go back to Google Sheets
After doing all the above-mentioned steps, you should be ready to go. Close the App Script tab and then head back to your Google Sheets.
Now, on any cell type “=ImportJSON”, you should see a suggestion. This is the function that was implemented in the Apps Script the steps above. If you didn’t get the suggestion, then recheck the code and try again.
Step 6: Enter the URL for the JSON data
For demonstration purposes, we will be using the following URL.
List of employees: http://dummy.restapiexample.com/api/v1/employees
The syntax for the function is
Url: The URL you want to get data from, should be enclosed in Strings
The final function would look something like this.
Step 7: Import the JSON data to Google Sheets
Using the above formula, you would be able to import JSON from a URL. The final result might look something like this.
We saw how to import JSON in Google Sheets using Apps Script Editor. You can also check out some other third-party extensions like apipheny which can help you with the same.
Google Sheets: Hlookup | Quick Guide: Introduction to Hlookup in Google Sheets
Using Vlookup and Hlookup together: Learn how to perform 2-D lookups by combining Vlookup and Hlookup