How to import JSON data into a Google spreadsheet?

Question

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?

Answer 1

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:

• The ImportJSON library at Fast Fedora
• Import JSON data into Google Spreadsheet at Stackoverflow
• Custom Functions in Google Sheets at Google Developers

Answer 2

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.

Answer 3

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.