Importxml() — парсим данные из интернета. И еще обновляем.

Недавно меня попросили помочь с получением данных из интернета по стоимости ЕТФ фондов.

Первое, что пришло в голову — конечно же googlefinance()

Однако ТКСных ЕТФок там не оказалось. Данную проблему можно решить двумя способами — через регулярные выражения (и о них я поговорю в следующей статье) и стандартным способом — через встроенную гугл функцию importxml(). Взвесив все за и против, я решил пойти путём наименьшего сопротивления.

Почему так? Очень просто — кастом-функция по regExp через фетч, воспринимается как скрипт и, как следствие, подлежит квотированию со стороны гугла. Вообще тема квотирования, как и кэширования, для меня (и гугла) — больная и о ней я тоже как-нибудь напишу отдельно большой «возмущения пост» 🙂

В общем — importxml()

Importxml принимает в себя два текстовых аргумента:

IMPORTXML(ссылка; запрос_xpath)

  1. ссылка – адрес веб-страницы с указанием протокола (например, http://).
  2. запрос_xpath – запрос XPath для поиска данных.

И если с ссылкой все понятно, то с запросом XPath может возникнуть сложность.

Вообще, если глубоко погружаться в XPath, то рекомендую почитать здесь: https://msiter.ru/tutorials/xpath.

Однако, есть более простой способ решить проблему понимания дерева xpath для тех, кому надо всё, сразу, здесь и сейчас.

XPath в хроме

Вернёмся к задаче, потому что на практическом примере это понять намного проще

У меня был список требуемых ЕТФ:

И, как писал ранее, таких тикеров в googlefinance нет.

Хорошо, перехожу на сайт — https://www.tinkoff.ru/invest/etfs/TSPX/ — и вижу стоимость:

Мне нужно получить в гугл таблицу 0,1199$

Для этого подвожу мышь к цене 0,1199 и перехожу в «Просмотреть код«

Стоимость находится тут:

Уже здесь можно скопировать путь Xpath:

и дальше просто вставить его в пустую ячейку таблицы.

Итого: у меня есть url и есть Xpath

Самое время посмотреть что получу через importxml:

Цена есть, правда знак доллара уехал.

Те, кто был чуть повнимательнее, заметили выше &nbsp — неразрывный пробел ( в данном случае их два) который как раз и разносит содержимое.

Что делать?

Подняться на уровень (а в случае сайта тинькофф — на два уровня) выше и импортировать все содержимое span класса со всеми переносами, пробелами, спец символами и так далее и тому подобное:

Получив в результате:

Победа? Ну… почти.

Все дело в том, что результат импорта «0,1199 $» — это текст и для дальнейших вычислений он не пригоден.

Из текста в цифру

Через стандартные функции:

  1. Удаляю пробелы (если они там есть) — Trim: =TRIM(A4)

2. Заменяю спецсимволы на пустоту — Subsitute: =SUBSTITUTE(B4;" ";"")

3. Заменяю знак доллара на пустоту — Subsitute: =SUBSTITUTE(C4;"$";"")

4. Привожу результат к числовому значению — Value: =VALUE(D4)

Так как тикеры из списка не только долларовые, но и рублёвые, необходимо так же через substitute сделать замену «₽» на пустоту. Если в итоге объединить всё в одну формулу в рамках одной ячейки, получится примерно следующий «вложенный монстр»:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(K2);" ";"");"$";"");"₽";""))

Далее, дело за малым — распространить importxml и формулу выше на все тикеры.

Что же, первую часть задачи я решил. У меня есть цена на каждый указанный тикер.

Но это только пол дела. Цену надо ещё и обновлять.

И вот тут в дело вступают google apps scripts.

Переключатель ссылки (или просто Switcher)

Все дело в том, что гугл кэширует результаты выполнения importxml и обновляет их по одним гуглу известным алгоритмам. Я перерыл тонны вариантов решения этой проблемы где только можно и нельзя и самым «изящным» и приемлемым стал следующий: при обновлении ссылки (как аргумента importxml) гугл воспринимает ее как новую и заново перевыполняет функцию importxml.

То есть мне надо обновлять первый аргумент функции, при неизменном втором — xpath. Вот тут в действие и вступает так называемый «свитчер».

Это «условный» переключатель, который будет меняться с 1 на 0 скриптом.

Что все это значит? Написанный далее скрипт будет менять значение ячейки B1 c единицы на ноль (и обратно) и далее в зависимости от значения ячейки «свитчера» (1 или 0), будет меняться ссылка в importxml.

Чтобы она менялась на новую, но вела на тот же сайт за теми же значениями, я добавляю несуществующий параметр сайта ?Param=1

Скрипт

Перехожу в Apps script

и пишу следующий скрипт:

const ss = SpreadsheetApp.getActiveSpreadsheet()
const paramsWs = ss.getSheetByName("Params")
const SWITCHER_CELL = "B1"

function switcher() {

  const switcherCell = paramsWs.getRange(SWITCHER_CELL)
  const switcherValue = switcherCell.getValue()

  if(switcherValue == 1){
    switcherCell.setValue(0)
  } else {
    switcherCell.setValue(1)
  }

}

const ss = SpreadsheetApp.getActiveSpreadsheet() // обращаюсь к книге
const paramsWs = ss.getSheetByName("Params") // далее обращаюсь к листу с параметрами, где хранится значение переключателя
const SWITCHER_CELL = "B1" // имя ячейки где хранится 1 или 0
const switcherCell = paramsWs.getRange(SWITCHER_CELL) // получаю диапазон (в данном случае ячейку)
const switcherValue = switcherCell.getValue() // получаю значение ячейки с 1 или 0
if(switcherValue == 1){ // если значение равно 1
switcherCell.setValue(0) // устанавливаю значение, равное 0
} else { // иначе
switcherCell.setValue(1) // устанавливаю значение, равное 1
}
}

Ну и под конец….

Триггер — запуск по таймеру

Перехожу в «Триггеры«

В правом нижнем углу нажимаю

Выбираю свитчер (он у меня один)

Выбираю «триггер по времени»

Тип триггера — по минутам и далее — интервал

Сохраняю 🙂

Теперь гугл по триггеру будет сам включать свитчер. Свитчер будет менять свое значение (0 или 1) и, как следствие, вызывать пересчет importxml.

Небольшой комментарий. Как показала практика, при списках — т.е. где надо импортировать не одну запись, а целую таблицу записей, промежуток времени лучше ставить либо 15, либо 30 минут.

Послесловие к этому посту, спустя больше года публикации

Уважаемые читатели! Ведение этого сайта — хобби вне основной работы, нацеленное на поднятие уровня образованности в использовании гугл таблиц.

За весь срок мне поступило (и продолжает поступать) достаточно много вопросов, как сюда, так и в почту по поводу парсинга:

  • других ресурсов;
  • изменения кода расписанных здесь вариантов;
  • нерабочего своего кода;
  • нерабочих своих формул в книгах.

Суть таких вопросов примерно одна и та же — дать конкретный совет (с формулами и кодами) как сделать так чтобы все работало у конкретного читателя под конкретную задачу, либо вообще написать отлаженный и работающий код за читателя. То есть мне надо потратить свое время, чтобы решить вашу проблему — в обычном мире это платная услуга: консультация или разработка.

В связи с этим прошу посмотреть в интернете примерные расценки часа работы программиста с опытом от года и иметь это в виду, прежде чем описывать конкретные задачи, присылать ссылки не неработающие скрипты или таблицы с просьбой совета или помощи.

Спасибо и надеюсь на понимание!

Ваше мнение важно и может улучшить блог

Я хочу услышать ваше мнение и ваши идеи о том, как сделать этот сайт еще лучше. Примите участие в опросе, чтобы поделиться вашими пожеланиями, предложениями и замечаниями. Пройдите опрос сейчас и помогите сделать этот сайт более полезным для вас!

Метки: , , , , , , , , , ,

У этой записи 32 комментариев

  1. Алексей 10.03.2022 Ответить

    Огромное тебе спасибо. Все настолько просто. 3 часа не мог сделать. После прочтения данной статьи сделал за 2 минуты.

  2. Flyekb 05.05.2022 Ответить

    Нолик пропал, выходит 1,102

  3. Кирилл 29.05.2022 Ответить

    А как тоже самое проделать с bibance p2p, делаю все тоже самое но в таблице пишет «нет данных»

    1. Dmitrii 09.06.2022 Ответить

      Добрый день, расскажите более подробно пожалуйста что именно нужно вытянуть. Возможно в данном случае придется пользоваться fetch + regexp.

  5. Александр 01.07.2022 Ответить

    Здравствуйте,
    https://p2p.binance.com/ru/trade/Tinkoff/USDT?fiat=RUB
    по вот этой ссылке нужно вытащить первую цену из списка в рублях,
    выше человек тоже видимо хотел это сделать, через =importxml не поулчается (пишет #Н/Д

  6. Игорь 23.08.2022 Ответить

    прописал свитчер: =if(Switcher=1;»https://www.tinkoff.ru/invest/stocks/GAZP/»&C2;»https://www.tinkoff.ru/invest/stocks/GAZP/»&C2&»?Param=1/»)
    но google docs его не распознает слово «switcher» в формуле не активно, ячейка выдает ошибку #NAME (Неизвестное название диапазона: SWITCHER)
    следовательно скрипт не запускается, в чем может быть проблема? Спасибо!

    1. Dmitrii 24.08.2022 Ответить

      «Switcher» это именованный диапазон — ячейка в которой стоит значение «1»
      Вам надо встать на нужную ячейку, выбрать меню «Данные», далее «настроить именованные диапазоны»
      Либо просто написать Switcher в поле под значком «отменить (Ctrl+Z)» — это так же именует диапазон.
      Либо вообще вместо свитчера дать ссылку на ячейку, то есть вместо =if(Switcher=1 указать =if(B1=1

  7. Илья 05.09.2022 Ответить

    Здравствуйте, а как сделать аналогичную функцию, но чтобы каждое новое значение после свитча записывалось в новую ячейку последующую ячейку? Цель: создать историю изменений, чтобы можно было строить диаграммы из них. Спасибо

  8. Artem 06.09.2022 Ответить

    Привет!
    Вопрос в том что нужно вытащить данные с защищенного сайта. Делаю формулу выдаёт Н/Д. «Ошибка Нет данных для импорта.» Как быть?

    1. Dmitrii 12.11.2022 Ответить

      Добрый день. Хороший вопрос, честно говоря не знаю 🙂
      1. Стандартная функция importXml не работает.
      2. Через гугл скрипты и urlFetchApp тоже
      3. Через чистый js у меня так же не получилось: https://codepen.io/DmitriiBeattle/pen/poKPppP
      Посему, оставлю эту запись тут, может кто-то более опытный и умный подскажет!

  12. Alexey 03.12.2022 Ответить

    Подскажите пожалуйста. В гугл таблицах через импортхмл хотел спарсить данные с коинмаркеткапа и бинанса. Половина монет прасится нормально, половина Н/Д. Бывает захожу и все ок, все данные загружены, а бывает так, что 95% таблицы — Н/Д. Не могу понять в чем причина, может кто-то сталкивался ?

      1. Александр 13.04.2023 Ответить

        Подскажите, а как в таком случае надо поправить шаблон, если требуется например получить цену пары ETH/BTC?
        Ввожу такой шаблон: (.*?)<\/
        В результате получаю такой результат: 0,00005859 BTC

    1. Dmitrii 04.03.2023 Ответить

      Иван, добрый день.
      К сожалению, стандартными средствами google sheets / google apps scripts даже с подключением cheerio как сторонней библиотеки это сделать никак нельзя. Проблема в том, что структура сайта не позволяет дожидаться загрузки всего контента, как если бы это был обычный html сайт.
      Если вы все же хотите получать эти данные, советую посмотреть в сторону связки node.js + puppeteer + cheerio, но к сожалению, к сервисам гугла это никакого отношения не имеет.

  14. MAKS 15.07.2023 Ответить

    А можно ссылку на эту таблицу? ато есть сложности с обновлением данных

  15. Анатолий 23.07.2023 Ответить

    Дмитрий, добрый вечер! Очень долго и много где искал то, о чем вы здесь пишете. Это что-то гениальное!
    В последнее время пользуюсь гугл таблицами, углубленно изучаю, нахожу для себя функции, о которых ранее и не подозревал.
    Для получения в таблицу цены на криптовалюту ETH использую функцию importxml, где первым ее аргументом является ссылка «https://coinmarketcap.com/exchanges/binance/» , находящаяся в ячейке B1, вторым аргументом — xPath «//*[@id=»__next»]/div[2]/div[1]/div[2]/div/div[4]/div[1]/div/table/tbody/tr[3]/td[4]/p», находящийся в ячейке В2, в ячейке В3 цена, прописана функция =IMPORTXML(B1,B2).
    Цена появляется и все. Но ведь курс токена меняется, и ваше решение здесь как нельзя кстати. Скрипт я более-менее по вашим указаниям написал, но не знаю, в какую ячейку написать =if(B1=1; ………. и как правильно написать . Пожалуйста, подскажите.

  16. Анатолий 23.07.2023 Ответить

    Дмитрий, дополнение.
    Пытаюсь что-то сам сделать с =if(B1=1; ……… по вашей аналогии, но в результате пишет, что синтаксическая ошибка.

    1. Дмитрий 24.07.2023 Ответить

      Добрый вечер. Если ошибка синтаксическая — значит где-то в самом написании функции ошибка. Посмотрите пример по ссылке, там свитчер работает нормально
      https://docs.google.com/spreadsheets/d/1NWnB4BmiydHw_KxPtWnDcuUWAHs1P0vzXH4_wpp7xsg/edit#gid=0
      На второй вкладке — params — как раз свитчер
      На первой — корректное написание формулы.
      В самом тексте поста так же поправил код.

  17. Анатолий 25.07.2023 Ответить

    Добрый вечер, Дмитрий! Спасибо, что ответили, без вас я ни за что не разберусь. Смотрел вашу таблицу и многое не понял.
    Каким образом свитчер на второй вкладке связан с вкладкой «Лист1»?
    Каким образом свитчер переключает базовый линк?

    По функции IF вообще ничего толкового найти невозможно, а как вы этому так хорошо научились?
    Посмотрите, пожалуйста, выдает ошибку.
    https://docs.google.com/spreadsheets/d/1Zvh12S-_IiMf2WL00cZubQQrAInRsm8kHewHMReQ-FY/edit?usp=sharing

    С уважением, Анатолий

    1. Дмитрий 25.07.2023 Ответить

      Связан что ни на есть напрямую. На первом листе в формуле if она срабатывает как раз в зависимости от состояния свитчера.
      Сам свитчер — это код в скриптах, который меняет значение ячейки с 0 на 1 по запускаемому в этих же скриптах триггеру.
      Дерзайте! 🙂
      P.S. Ваша таблица недоступна для чтения.
      P.P.S по IF есть хорошее описание на самом гугле = https://support.google.com/docs/answer/3093364?hl=ru

  18. Анатолий 25.07.2023 Ответить

    Дмитрий, добрый вечер!
    «Связан что ни на есть напрямую». Эта связь — не очевидна. А у меня в самой формуле ошибка, никак не могу понять, как она должна быть составлена.
    Ниже ссылка, все должно открываться с правами редактора.
    https://docs.google.com/spreadsheets/d/1Zvh12S-_IiMf2WL00cZubQQrAInRsm8kHewHMReQ-FY/edit?usp=sharing
    Спасибо.

  19. Анатолий 28.07.2023 Ответить

    Здравствуйте, Дмитрий! Вы говорили выше, что листы таблицы в вашем примере связаны напрямую. Для меня эта связь не видна. Плз, объясните «чайнику» каким образом связаны ваши листы Params и List.
    Да формула с «ЕСЛИ» куда-то уехала. Повторно даю ссылку.
    https://docs.google.com/spreadsheets/d/1Zvh12S-_IiMf2WL00cZubQQrAInRsm8kHewHMReQ-FY/edit?usp=sharing
    Дмитрий, если я вам докучаю, то дайте знать, беспокоить более не буду.
    Всех благ!

Добавить комментарий