20

Google Sheets has an IMPORTXML function to import data from various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.

Is there a similar way to import JSON formatted data?

1
  • 3
    you can also you =importdata("url to json") Apr 2, 2016 at 17:23

3 Answers 3

19

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:

3
  • 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
4

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.

0

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.