How to Import JSON in Google Sheets [Complete Guide]

This article will teach How to Import JSON in Google Sheets. A powerful feature in Google Sheets lets you import data from multiple sources, including JSON files. A popular lightweight data-sharing format for data communication between a server and a web application is JSON (JavaScript Object Notation). For tasks like evaluating API answers, processing data from web services, or simply dealing with JSON data files, importing JSON into Google Sheets can be highly beneficial. This article will take you step-by-step through the procedure, guaranteeing a smooth transaction.

Importance of using JSON data in Google Sheets

For several compelling reasons, learning how to import JSON into Google Sheets is essential in current times. In current web applications and APIs, JSON, or JavaScript Object Notation, is a common data format. Its prevalence is expected to continue, so being able to manage JSON data effectively is crucial.

The ability to master this skill enables people to easily incorporate data from a variety of sources into their spreadsheets, positioning them to successfully traverse the changing data landscape.

How to Import JSON in Google Sheets?

In this article, we will show you the step-by-step procedure of the Easiest Google Sheets Import JSON. Following are the steps to Import JSON in Google Sheets;

STEP 1

Open your Google Sheet

If you need to import a JSON file into a Google Sheet, open the sheet in concern.

You may also type sheets to rapidly open a new sheet. on your browser.

STEP 2

Go to the Extensions Menu

Go to the Main menu on Google Sheets and Click on “Extensions”.

STEP 3

Click on Apps Script

Now, in the extension click on Apps script.

STEP 4

Delete the placeholder content on the script editor

Any placeholder code that was supplied in the script editor can be removed.

STEP 5

Copy the open-source script and paste it into the Editor

Go to the following link then copy the entire “import_json_appsscript.js” script.

https://docs.google.com/document/d/1GVuWhC7NxvFs8HntpBMkJAVyxZ9IG3Np4iTW7ooKgXA/edit

And paste it into your Google Sheet’s script editor.

STEP 6

Save and specify a name for the Project

Use the save option and give your project a name after pasting the complete script into the script editor.

To save your project click on the Save Project button

STEP 7

Now Switch back to your Google Sheet

After saving your project there, you may now exit the script editor tab in your browser and go back to your Google Sheet. Choose any cell, go to the function area, and type the following:

Import JSON Syntax

STEP 8

Add JSON API URL

You must provide your JSON API URL after launching your Import JSON formula.

You can pick one of these to test with if you wish to use a free JSON API URL:

·       https://api.chucknorris.io/jokes/random

·       https://api.coinbase.com/v2/prices/ETH-USD/buy

·       https://api.nobelprize.org/v1/laureate.json

Type the URL in the following format:

=importjson(“https://api.nobelprize.org/v1/laureate.json”)

STEP 9

Import the JSON data

After Adding the JSON API URL to your Google Sheet, press the enter key on your keyboard and wait for the JSON data to fill your Google Sheet.

Your JSON data has been successfully imported into Google Sheets.

Frequently Asked Questions

What if my JSON is not accessible via a URL?

If your JSON data is not publicly accessible, you may need to host it somewhere or consider using Google Apps Script’s ‘UrlFetchApp’ to fetch the data.

Can I import nested JSON data?

Yes, you can modify the script to handle nested JSON structures. However, you’ll need to customize the script according to your specific JSON format.

How can I refresh the data?

Currently, this script doesn’t have automatic refresh functionality. You would need to manually trigger the “IMPORTJSON” function by entering the formula again.

Can you Filter the JSON File Data you Import into Google Sheets?

You can filter the JSON data you import into Google Sheets, and one method involves inspecting the source database’s API for filtering capabilities. These filtering parameters can then be added to the JSON file’s URL to refine your data extraction.

If you don’t want to read the API documentation, you can combine “IMPORTJSON” with the QUERY function.

Here are some examples:

Select Only Certain Fields or Columns

‍=QUERY(IMPORTJSON(“URL”),”select ColN1, ColN2″)

Where ‘ColN1’ and ‘ColN2’ are the column numbers to only import. For our example, we only want to import the name columns. They are in the 2nd and 3rd columns.

We therefore set up the formula as

=query(importjson(“https://api.nobelprize.org/v1/laureate.json”),”select Col2, Col3″)

The output becomes

Filter Results by Value

=QUERY(IMPORTJSON(“url”),”where ColN = value”)

Where ColN is the column that we look for the value. For our example, we only want to list entries that won the Nobel Prize in 1935. We set up the formula as

=query(importjson(“https://api.nobelprize.org/v1/laureate.json”),”where Col13 = ‘1935’”)

The output is as follows:

Can I import data from APIs?

Yes, you can use the same ‘UrlFetchApp.fetch(url)’ method to fetch data from APIs. Just replace the ‘URL’ with the API endpoint.

Is there a size limit for the JSON file?

There might be some limitations depending on the Google Apps Script quotas, but for most regular use cases, you should be fine.

Conclusion

In Summary, today we learned how to import JSON in Google Sheets because it is a valuable method for handling structured data in a spreadsheet format. By following the instructions outlined in this article, you can effortlessly import JSON data into your sheets, which in turn facilitates analysis and visualization. Gaining proficiency in this skill enhances efficiency and aids in making informed decisions when dealing with data, making it an asset in your skills collection. Thanks for reading this guide. Keep learning with Office Demy.