In this article, we are going to show you how to easily import JSON into Google Sheets with just a few clicks. Follow the steps below to get started.
Import JSON into Google Sheets with Lido (the easiest method)
Importing JSON into a spreadsheet has never been easier thanks to the IMPORTJSON function from Lido.
Lido is a spreadsheet software similar to Google Sheets, but with the ability to import data from anywhere! You can import your JSON into a Lido spreadsheet in less than a minute. Just follow these steps:
1. Create a free Lido account
To use the IMPORTJSON function from Lido, start by signing up on the website here.
2. Write your IMPORTJSON formula
Once you have created a Lido account, open a new Lido spreadsheet.
To import your JSON into Lido, use the following formula:
=IMPORTJSON(url, output_cell)- The url argument corresponds to the URL of the JSON you want to import. You can either write the URL directly in the formula or reference it from a cell in your spreadsheet.
- The output_cell argument corresponds to the cell in the spreadsheet where you want to start displaying the JSON.
In our example above, our formula is:
=IMPORTJSON(A1, D5)With just one formula, your JSON data is now imported into 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 definitely switched from Google Sheets to Lido after seeing how easy it is to import data and automate repetitive tasks on a Lido spreadsheet.
Method 2: Using Google Apps Script (the more difficult method)
1. Open Google Apps Script
You can open Google Apps Script by clicking on “Extensions” in the main menu and selecting “Apps Script”.
A new page will open for “Apps Script”. The right side of the screen is where we will insert a special function to insert JSON files into Google Sheets.
2. Copy the ImportJSON script from GitHub
Next, copy the script from the GitHub repository by paulgambill here.
Copy all the code from line 1 to line 392. Hold down the left mouse button and drag the cursor over the entire code, then press “Ctrl+C” or right-click and select “Copy”.
3. Paste the script into Google Apps Script
Go back to the “Google Apps Script” tab. Click inside the code area, press “Ctrl+A” to select the default code, then press “Delete” on your keyboard to clear the area.
Next, press “Ctrl+V” or right-click and 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 on the “Save project” icon near the top of the page.
Then, you can also name the script by clicking on the default title “Untitled project”.
A small popup box will appear where you can give the project a name. Enter the desired name and click “Rename”.
The script is now ready to be used!
5. Use the script as =IMPORTJSON(“url”) in your spreadsheet
Google Sheets automatically recognizes the function defined in Google Apps Script as a legitimate function to use in spreadsheets. When you type =IMPORTJSON, the custom script will appear as a function.
The IMPORTJSON function has the following syntax:
=IMPORTJSON(url, query, options)- url: corresponds to the URL of a public JSON feed.
- query: corresponds to a comma-separated list of paths to import.
- options: corresponds to a comma-separated list of options that modify the data processing.
For this tutorial, we will be importing a complete JSON file. The example JSON file can be found here.
You can view the JSON file in your browser. Some browsers, like Firefox, automatically read the file and format it into a more readable format.
Type the formula in a cell:
=importjson("https://api.nobelprize.org/v1/laureate.json")You can also do the following:
- Copy the link.
- Go back to Google Sheets.
- Type
=importjson(". - Paste the link.
- Type
"). - Press
Enter.
The JSON file has been imported.
FAQ
Is it possible to filter the data from the imported JSON in Google Sheets?
Yes, it is possible to filter the JSON data that you import into Google Sheets. One way to do this is by consulting the API of the source database to see the filtering options. These will be added to the URL of 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 specific columns
=QUERY(IMPORTJSON("url"),"select ColN1, ColN2")Where ColN1 and ColN2 are the column numbers to import. For our example, we only want to import the name columns, which are in the 2nd and 3rd columns.
So we have the following formula:
=query(importjson("https://api.nobelprize.org/v1/laureate.json"),"select Col2, Col3")Filter results by value
=QUERY(IMPORTJSON("url"),"where ColN = value")Where ColN corresponds to the column where we are looking for the value. For our example, we only want to list the entries that won the Nobel Prize in 1935. We have the following formula:
=query(importjson("https://api.nobelprize.org/v1/laureate.json"),"where Col13 = '1935'")We hope this article has helped you and given you a better understanding of how to import JSON into Google Sheets. You may also enjoy our articles on how to integrate WhatsApp with Google Sheets and how to export Airtable to Google Sheets.
To optimize your workflow, we recommend checking out our guide on creating an email mailing list in Google Sheets and trying out our deadline tracking software.
For more information, visit our website at Crawlan.com.
