Курс Таблицы Google → Как получить данные из JSON в Google Sheets

Решение: https://github.com/bradjasper/ImportJSON

Подробная инструкция есть по ссылке, опишу максимально коротко.

Как настроить

В Google Sheets открываем меню Tools -> Script Editor.

В текстовую область вставляем всё содержимое файла:
https://raw.githubusercontent.com/bradjasper/ImportJSON/master/ImportJSON.gs

Нажимаем Сохранить проект (иконка дискеты).

Возвращаемся к вашей таблице.

Теперь вам доступна функция =ImportJSON(ссылка на json, список полей, доп опции)

Как использовать

Разберем на примере JSON от НБУ.
Ссылка на JSON файл: https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?valcode=USD&date=20210115&json

Структура ответа:

[{ 
"r030":840,"txt":"Долар США","rate":28.0609,"cc":"USD","exchangedate":"15.01.2021"
 }]

Чтобы получить только курс (rate):

=ImportJSON("https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?valcode=USD&date=20210115&json","/rate","noHeaders")

Если нужно запарсить несколько полей:

=ImportJSON("https://bank.gov.ua/NBUStatService/v1/statdirectory/exchange?valcode=USD&date=20210115&json","/rate,/exchangedate","noHeaders")

Доп. параметры и другие подробности:

/**
 * Imports a JSON feed and returns the results to be inserted into a Google Spreadsheet. The JSON feed is flattened to create 
 * a two-dimensional array. The first row contains the headers, with each column header indicating the path to that data in 
 * the JSON feed. The remaining rows contain the data. 
 * 
 * By default, data gets transformed so it looks more like a normal data import. Specifically:
 *
 *   - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values 
 *      of the rows representing their parent elements.
 *   - Values longer than 256 characters get truncated.
 *   - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case. 
 *
 * To change this behavior, pass in one of these values in the options parameter:
 *
 *    noInherit:     Don't inherit values from parent elements
 *    noTruncate:    Don't truncate values
 *    rawHeaders:    Don't prettify headers
 *    noHeaders:     Don't include headers, only the data
 *    allHeaders:    Include all headers from the query parameter in the order they are listed
 *    debugLocation: Prepend each value with the row & column it belongs in
 *
 * 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")
 * 
 * @param {url}          the URL to a public JSON feed
 * @param {query}        a comma-separated list of paths to import. Any path starting with one of these paths gets imported.
 * @param {parseOptions} a comma-separated list of options that alter processing of the data
 * @customfunction
 *
 * @return a two-dimensional array containing the data, with the first row containing headers
 **/