Как подгрузить курс валюты с сайта Tinkoff?

Question

[phil1514]

Требуется подгрузить со страницы https://www.tinkoff.ru/about/exchange/ курс обмена рублей на доллары(около 140 сейчас) в google таблицы.
Насколько понял, это можно сделать с помощью IMPORTXML("https://www.tinkoff.ru/about/exchange"; "/html/body/div[1]/div/div/div[2]/div/div[3]/div[2]/div[3]")
Но почему-то формула ничего не выдает.

Answer 1

[Григорий Боев]

Его просто нет в исходном HTML, оно получается скриптом
Смотрите в сторону этого запроса:

fetch("https://api.tinkoff.ru/v1/currency_rates?from=USD&to=RUB", {
  "headers": {
    "accept": "*/*",
    "accept-language": "ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7",
    "content-type": "application/x-www-form-urlencoded",
    "sec-ch-ua": "\" Not A;Brand\";v=\"99\", \"Chromium\";v=\"99\", \"Google Chrome\";v=\"99\"",
    "sec-ch-ua-mobile": "?0",
    "sec-ch-ua-platform": "\"Windows\"",
    "sec-fetch-dest": "empty",
    "sec-fetch-mode": "cors",
    "sec-fetch-site": "same-site"
  },
  "referrer": "https://www.tinkoff.ru/",
  "referrerPolicy": "strict-origin-when-cross-origin",
  "body": null,
  "method": "GET",
  "mode": "cors",
  "credentials": "omit"
});

В ответе:

{"trackingId":"WEE285K7UR","resultCode":"OK","payload":{"lastUpdate":{"milliseconds":1646839634102},"rates":[{"category":"C2CTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"OPSRateGroup","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"DebitCardsTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G13CO","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CUTransfersPremium","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G11","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"DepositPayments","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"SMETransferAbove100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CreditCardsOperations","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"CUTransferAbove100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"PrepaidCardsOperations","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"SMETransferAbove100G13","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G13","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G12","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G11","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G13CO","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CUTransferFrom10To100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferAbove100G12","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferAbove100G13CO","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferAbove100G11","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"PrepaidCardsTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"DebitCardsOperations","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"SavingAccountTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G12","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CUTransfersPro","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CreditCardsTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G13","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95}]}}

Comments

[phil1514]

function dollar(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Прайс");
var row = sheet.getRange(2,2);
var response = UrlFetchApp.fetch("https://api.tinkoff.ru/v1/currency_rates?from=USD&...");
var data = JSON.parse(response);
var usdBuy = data[????].????;
row.setValue(usdBuy);

};

Cделал такой скрипт

Подскажите, что поставить вместо ???? чтобы получить нужную информацию

[Григорий Боев]

phil1514,
data.payload.rates[0].buy

[phil1514]

Спасибо огромное, все работает!

Последний вопрос, этот скрипт будет обновлять курс только при обновлении страницы? Возможно ли настроить обновление автоматическое курса раз в минуту например?

[R111-svg]

Привяжите скрипт к тригерру по времени и укажите необходимый временной отрезок для автоматического обновления