How to import JSON in Google Sheets | 2 min easy guide

Reading Time: 3 minutes

Import JSON in Google Sheets

Table of Contents

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

Figure 1: Open Apps Script Editor

Step 2: Erase the placeholder code

You will see a screen like this.

Figure 2: Erase the placeholder code

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.

Figure 3: Copy and paste the script

Step 4: Save and Name your script

Name your project and click on “Save Project”.

Figure 4: Save and Name the Script

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.

Figure 5: ImportJSON function

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

=ImportJSON(url)

Url: The URL you want to get data from, should be enclosed in Strings

The final function would look something like this.

=ImportJSON(“dummy.restapiexample.com/api/v1/employees”)

Figure 6: JSON url

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.

Figure 7: Import the JSON data

Conclusion

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.

See Also

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