In this tutorial, you will learn how to use IMPORTJSON in Google Sheets.
How To Use IMPORTJSON in Google Sheets
Quick Navigation
JSON data is a popular format for storing information and is the most common output when working with external APIs. However, Google Sheets currently does not have a built-in function for reading JSON data.
Luckily, we can set up a custom formula for reading JSON data through Apps Script. Apps Script is a programming language that allows you to write your own functions.
In this guide, we will show you how to import JSON data into Google Sheets using a custom function made through Apps Script.
How To Import JSON Data in Google Sheets
Here’s how to import JSON data in Google Sheets.
Step 1
First, head to the Google Sheets document you want to import JSON data into. In the Extension menu, select the Apps Script option.
Step 2
A new tab will appear with the Apps Script code editor already open.
Copy and paste the following code into the editor:
/**
* Imports JSON data to your spreadsheet
* @param url URL of JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
try{
var res = UrlFetchApp.fetch(url);
var content = res.getContentText();
var json = JSON.parse(content);
var patharray = xpath.split(“/”);
for(var i=0; i<patharray.length; i++){
json = json[patharray[i]];
}
if(typeof(json) === “undefined”){
return “Not Available”;
} else if(typeof(json) === “object”){
var tempArr = [];
for(var obj in json){
tempArr.push([obj,json[obj]]);
}
return tempArr;
} else if(typeof(json) !== “object”) {
return json;
}
}
catch(err){
return “Error retrieving data”;
}
}
The script above defines a custom function called IMPORTJSON. The function will accept two arguments: the URL that will return the JSON data and the xpath string which will determine what part of the JSON data to return.
Step 3
Select an empty cell where you want to import your JSON data.
Step 4
Type the string “=IMPORTJSON(“ to start the IMPORTJSON function.
Step 5
Next, type the URL that will return JSON data. This URL will typically be a link to some API endpoint.
In this example, we want to return the price of Bitcoin using the CoinDesk API.
The URL returns a JSON-formatted text string as seen above. From this data, we want to access the data under “bpi” then “USD” then finally “rate”.
Step 6
We can return the price of bitcoin in USD by specifying the xpath “bpi/USD/rate”
Step 7
Hit the Enter key to evaluate the custom IMPORTJSON function.
Summary
This guide should be everything you need to use IMPORTJSON in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.