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.
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.
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.
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.
Afterwards, press Ctrl+V or right-click again then select Paste. This will insert the github code we copied into Google Apps Script.
4. Save and name the script
Save the script by clicking the Save project icon near the top of the page.
Afterwards, you can also name the script by clicking the default title Untitled project.
A small pop-up box will appear where you can rename the project. Type the name you want to give, then click Rename.
The script is now ready to be used!
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:
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:
Type the formula into a cell:
=importjson("https://api.nobelprize.org/v1/laureate.json")
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.
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.
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:
