There is IMPORTXML function to import data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.

Is there any similar solution to import data in JSON format?


3 Answers 3


Currently there is no any Add-on which offer this functionality, so you have to create or import custom functions by Script Editor available in Tools menu (for Spreadsheet). Then re-paste the content of ImportJSON.gs from bradjasper's GitHub page and save it.

After that, you can use ImportJSON function, for example:

=ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders")

Here is another example:

=split(concatenate(ImportJSON("https://api.binance.com/api/v1/klines?symbol=BTCUSDT&interval=1m&limit=1")), ",")

Read more:

  • 1
    is there a way to do this in new google sheets? how do i get this running?
    – rcphq
    May 31, 2015 at 19:12
  • 1
    is there a way to paste the json object in the argument, instead the url?
    – catch22
    Oct 18, 2017 at 10:22
  • I've tested these steps today (as of 2018) and they still work in the latest Sheets (Tools->Script editor) and import the functions.
    – kenorb
    Jul 30, 2018 at 20:11

If you want to paste a JSON object you can use http://www.convertcsv.com/json-to-csv.htm to convert it to a CSV and then import that into Google Sheets.

If you want to rotate the data from columns to rows you can use http://www.convertcsv.com/transpose-csv.htm

I found this easier than messing with an addon script.


I wanted to fetch JSON from an API endpoint and then pluck out a particular value into a cell (via formula) in Google Sheets.

So I created this custom function in Apps script:

 * Inspired by https://thisdavej.com/consuming-json-web-data-using-google-sheets/
 * @customfunction
function SKLIMA(address) {
  const url = `https://api.zapper.fi/v1/protocols/klima/balances?addresses%5B%5D=${address}&network=polygon`;
  const response = UrlFetchApp.fetch(url);
  const object = JSON.parse(response.getContentText());
  return object[address].products[0].assets[0].tokens[0].balance;

The @customfunction CURRENTWEATHER at https://thisdavej.com/consuming-json-web-data-using-google-sheets/ was helpful for me.

Not the answer you're looking for? Browse other questions tagged or ask your own question.