In this article:

Import JSON to Google Sheets (Easiest Way in 2024)

May 8, 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

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:


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:

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)


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:

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:


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


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(""),"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(""),"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.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->