In this article:

Import JSON to Google Sheets (Easiest Way in 2024)

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 with Lido (Easiest Method)

It has never been easier to import JSON to a spreadsheet thanks to Lido's IMPORTJSON function.

Lido is a spreadsheet software just like google sheets but with the ability to import data from anywhere! You can mport your JSON into lido spreadsheet in under a minute. Simply follow the steps below:

1. Create a FREE Lido Account

In order to use Lido's IMPORTJSON function, first sign up for an account at https://www.lido.app/go/signup

2. Write your IMPORTJSON Formula


Open a new Lido spreadsheet once you have made and account.

To import your JSON to Lido we will use the following formula:

=IMPORTJSON(url, output_cell)

Url will be the url you want to import the Json from. You can write the url path in the formula or reference it from a cell in your spreadsheet.

Output_cell will be the cell in the spreadsheet where you want to begin the JSON output.

In our example images above, our formula is:

```=IMPORTJSON(A1,D5)```

And with 1 simple formula your JSON data has now been imported to a Lido spreadsheet.

If you need to use google sheets you can simply copy the data and paste it back into google sheets. However, many users who have tried our IMPORTJSON function have permanently made the switch from google sheets to Lido after seeing how easy it is to import data and automate repetitive spreadsheet tasks wit Lido.

Method 2: Using Apps Script (Hardest Method)

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

We hope this article has helped you and given you a better understanding of how to import JSON to Google Sheets. You might also like our articles on how to integrate WhatsApp with Google Sheets and how to export Airtable to Google Sheets.

To optimize your workflow, we recommend reading our guide on how to create a Google Sheets email list and trying our software for tracking due dates.

Schedule a free automation consult
Learn more

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started