How To Use IMPORTJSON in Google Sheets

In this tutorial, you will learn how to use IMPORTJSON in Google Sheets.

How To Use IMPORTJSON in Google Sheets

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.