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.
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:
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.
