Blog
>
Tutorials

Import JSON to Google Sheets (Easiest Way in 2023)

In this article we will show you how to import JSON to Google Sheets in just a few clicks. Simply follow the steps below.

Import JSON to Google Sheets

Enter your API URL in the box below to fetch your JSON data and display it in a table format.

1. Open Google Apps Script

You can open Google Apps Script by clicking Extensions in the main menu, then selecting Apps Script.

json to google sheets, click extensions, select apps script

A new tab will be loaded for Apps Script. The right side of the screen is where we will insert a special function to insert JSON files to Google Sheets.

json to google sheets, google apps script page

2. Copy the ImportJSON Script from Github

We then copy the script from paulgambill’s Github:

https://gist.github.com/paulgambill/cacd19da95a1421d3164

Copy all of the code from line 1 to 392. Hold the left key of your mouse and drag it across all of the code, then either press Ctrl+C or right-click then select Copy

Github script for use to import json to google sheets

3. Paste the script to Google Apps Script

Go back to the Google Apps Script tab. Click on the code area, press Ctrl+A to highlight the default code, then press Delete on your keyboard. That clears the area.

Clear google apps script area to insert custom code to import json google sheets

Afterwards, press Ctrl+V or right-click again then select Paste. This will insert the github code we copied into Google Apps Script

custom code to import json google sheet inserted

4. Save and name the script

Save the script by clicking the Save project icon near the top of the page. 

google spreadsheet json save project to enable function

Afterwards, you can also name the script by clicking the default title Untitled project.

google spreadsheet json, click title to rename project name 

A small pop-up box will appear where you can rename the project. Type the name you want to give, then click Rename.

Rename project for script to import json google sheets

The script is now ready to be used!

script to import json google sheets saved and renamed

5. Use the script as =IMPORTJSON(“url”) in your sheet

Google Sheets automatically detects the function set in Google Apps Script as a legitimate function to be used in the sheets. When you type =IMPORTJSON, the custom script will appear as a function:

json google sheets function detected when adding the function inside the cell

The IMPORTJSON function has the following syntax:

=IMPORTJSON(url, query, options)

Where

Url: is the URL to a public JSON feed

query: is a comma-separated lists of paths to import

options: is a comma-separated list of options that alter processing of the data

For this tutorial we will import an entire json file. The sample json file will be from the following link:

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

You can view the json files through your browser. Some browsers, such as Firefox, automatically read the file and format it to a more readable format: 

google spreadsheets json sample to import

Type the formula into a cell:

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

google sheet import json function with sample url added

You can also do the following:

1. Copy the link

2. Go back to Google Sheets

3. Type =importjson(“

4. Paste the link

5. Type “) 

6. Press Enter

The JSON file has been imported.

google sheet import json function output

FAQs

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

It is possible to filter the JSON you import into Google Sheets. One way is to check the API of the source database for filtering options. These will be appended to the link to the JSON file.

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.

google sheets import json, data to filter with query


We therefore set up the formula as

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

The output becomes

google sheets import json, select columns to import, output

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:

google sheets import json, filter entries output

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.