How To Use IMPORTJSON in Google Sheets

Last Updated on October 31, 2023 by Jake Sheridan

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.