JSON is a standard protocol for sending data between applications.
In this tutorial, Quantrimang.com will show you how to import JSON data into Google Sheets using a free open source script available on Github.
Then at the end, this article will show you an easier way to import JSON data into Google Sheets with more advanced features.
Using the ImportJSON script
1. Open Google Sheets
Open a new or existing Google Sheets page.
Tip: If you want to create a new Google Sheet, you can visit sheet.new in your browser
2. Open the script editor
From the top menu in Google Sheets, click Extensions > Apps Script.
3. In the script editor, remove the placeholder content
If you entered any placeholder code in the script editor, you can delete it.
4. Copy and paste open source code
Visit: https://gist.github.com/paulgambill/cacd19da95a1421d3164
Copy all the code from “import_json_appsscript.js” and paste the code into the Google Sheets script editor.
This is a free open source script created by someone to import JSON into Google Sheets.
If you’re looking for other free importjson scripts, they are also available online.
5. Save and name the script
After copying and pasting all the scripts into the editor, click the save button and give the project a name.
You can name the project whatever you want, but a name like “ImportJSON” will make it easier to remember.
6. Return to Google Sheets
With your project saved in the script editor, you can now close the script editor tab in your browser and return to your Google Sheets.
Now, in any cell of your Google Sheets, start typing “=import” (without the quotes) and you will see a popup with “ImportJSON” as an option. This is the script you save in the editor.
Click the “ImportJSON” option.
7. Add JSON API URL
After opening the JSON input formula, you need to add the JSON API URL.
If you would like to use the free JSON API URL for testing, you can use one of the following URLs:
Chuck Norris Jokes: https://api.chucknorris.io/jokes/random
Ethereum current price: https://api.coinbase.com/v2/prices/ETH-USD/buy
Enter your URL like this:
=ImportJSON(“https://api.example.com”)
There should be “double quotes” around the URL and single quotes around the quotes.
In the URL, you’ll specify the endpoint and add any other parameters to get the data you need.
8. Import JSON data
After entering the JSON URL into your Google Sheets, the final step in entering JSON is to press the Enter button on your keyboard and wait for the JSON data to populate the spreadsheet.