В Google Таблицах есть хороший набор функций для импорта данных из внешних источников. Некоторые из них включают IMPORTRANGE, IMPORTFEED, а также IMPORTDATA, IMPORTXML и IMPORTHTML.

В этом руководстве мы поговорим, в частности, о функции IMPORTHTML в Google Таблицах. Мы обсудим, что делает функция, каков ее синтаксис, а также рассмотрим несколько простых примеров того, как ее можно использовать.

Что делает функция IMPORTHTML в Google Таблицах?

Функция IMPORTHTML — это полезная функция очистки данных, которая помогает нам извлекать таблицы и списки с веб-страниц. Функция сканирует HTML-страницу (по заданному URL-адресу) на предмет таблиц или списков и копирует определенный список или таблицу, которые нам нужны, со страницы в рабочую книгу Google Таблиц.

Все таблицы в файле HTML заключены в теги <table> </table>, а все списки заключены в теги <ul> </ul> (для неупорядоченных списков) или теги <ol> </ol> для упорядоченных списков. Это позволяет функции IMPORTHTML очень легко обнаруживать таблицы и списки на веб-странице.

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

Синтаксис функции IMPORTHTML в Google Таблицах

Синтаксис функции IMPORTHTML в Google Таблицах следующий:

=IMPORTHTML(url, query_type, index)

Здесь,

  • Параметр url указывает URL-адрес веб-страницы, из которой мы хотим извлечь список или таблицу. Это должно включать протокол («http: //» или «https: //»).
  • Параметр запроса указывает, хотим ли мы извлечь с веб-страницы таблицу или список.
  • Параметр index указывает индекс таблицы или списка, который нам нужен на веб-странице. Индексы всегда начинаются с 1, поэтому, если мы хотим извлечь первую таблицу с веб-страницы, мы указываем этот параметр как 1.

Как использовать функцию IMPORTHTML для импорта списка с веб-страницы

Давайте рассмотрим пример, чтобы понять, как использовать функцию IMPORTHTML для извлечения определенного списка с веб-страницы.

Допустим, мы хотим извлечь список «10 лучших библиотек машинного обучения Python» со страницы по URL-адресу https://www.edureka.co/blog/python-libraries/. Вот так список выглядит на исходной веб-странице:

Чтобы получить этот список, нам сначала нужно знать индекс этого конкретного списка на всей веб-странице. Если вы посетите веб-сайт по этому URL-адресу, вы заметите, что на странице есть несколько разных списков. Итак, как мы узнаем, каков индекс этого конкретного списка?

Как получить индекс списка с веб-страницы

Чтобы получить индекс списка с веб-страницы, нам нужно написать небольшой фрагмент кода javascript в консоли браузера. Не волнуйтесь, вам не нужно много делать, просто скопируйте приведенный ниже код в консоль:

 var index = 1;

[].forEach.call(document.getElementsByTagName(“table”), function(elements) { console.log(“Index: ” + index++, elements); });

Вот шаги, чтобы получить индекс нашего необходимого списка с данной веб-страницы:

  • Откройте инструменты разработчика вашего браузера. Если вы используете ПК с Windows, нажмите клавишу F12 на клавиатуре. Это работает в Firefox, Google Chrome или Microsoft Edge. Если вы используете Mac, нажмите Cmd + Opt + J, чтобы открыть Google Chrome, и Cmd + Opt + C, чтобы открыть Safari (сначала обязательно включите «Меню разработки»).
  • Когда откроется ваш инструмент разработчика, перейдите на вкладку «Консоль».

  • Вставьте или введите указанный выше код в область сценария консоли.

  • Щелкните «Выполнить».

  • В области вывода вы должны увидеть ряд индексов, которые выглядят следующим образом:

  • Если вы наведете указатель мыши на каждый индекс, вы увидите список на своей веб-странице, который соответствует этому индексу выделенным. Это поможет вам понять, какой индекс соответствует нужному списку.

Как видно из приведенного выше снимка экрана, список, который мы хотим извлечь, находится в индексе 18 веб-страницы.

Использование функции IMPORTHTML для импорта списка с веб-страницы

Как только вы узнаете индекс списка, который хотите извлечь, вы можете продолжить и использовать его в функции IMPORTHTML следующим образом:

=IMPORTHTML("https://www.edureka.co/blog/python-libraries/","list",18)

Приведенная выше формула импортирует список номер 18 с веб-страницы по URL-адресу https://www.edureka.co/blog/python-libraries/.

Чтобы вставить список в лист Google Таблиц, просто введите указанную выше формулу в пустой лист, нажмите клавишу возврата и подождите несколько секунд, пока список не появится в электронной таблице.

Как использовать функцию IMPORTHTML для импорта таблицы с веб-страницы

Помимо списков, вы также можете импортировать таблицы с веб-страниц с помощью функции IMPORTHTML.

Допустим, мы хотим импортировать текущие рыночные цены на все криптовалюты с веб-сайта CoinMarketCap. URL-адрес страницы: https://coinmarketcap.com/all/views/all/

Вот как таблица выглядит на исходной веб-странице:

Чтобы получить эту таблицу, нам снова нужно знать ее индекс на веб-странице.

Как получить указатель таблицы с веб-страницы

Тот же код javascript можно использовать для поиска индекса нужной таблицы, но с небольшими изменениями. Вместо использования querySelectorAll для извлечения всех элементов «ul» и «ol» мы используем функцию getElementsByTagName для извлечения всех элементов «table» с веб-страницы. Вот код, который вы можете скопировать и вставить в консоль:

var index = 1;

[].forEach.call(document.getElementsByTagName ("table"),function(elements) {console.log("Index: "+index++,elements);});

Вот как вывод вышеприведенного кода выглядит в консоли:

Как видно из приведенного выше снимка экрана, таблица, которую мы хотим извлечь, находится в индексе 3 веб-страницы.

Использование функции IMPORTHTML для импорта таблицы с веб-страницы

Как только вы узнаете индекс таблицы, которую хотите извлечь, вы можете продолжить и использовать его в функции IMPORTHTML следующим образом:

=IMPORTHTML("https://coinmarketcap.com/all/views/all/","table",3)

Приведенная выше формула импортирует таблицу номер 3 с веб-страницы по URL-адресу «https://coinmarketcap.com/all/views/all/».

Вот как таблица выглядит на вашем листе Google Таблиц:

Объединение функции IMPORTHTML с функциями QUERY для извлечения определенных частей таблицы

Теперь давайте посмотрим, как можно точно настроить извлечение, чтобы получать только определенные столбцы или строки из таблицы.

Мы можем объединить функцию ImportHTML с функцией QUERY,  так что мы можем запросить вывод и извлечь конкретно , что нам нужно.

Извлечение определенных столбцов таблицы с веб-страницы

Предположим, из таблицы криптовалют вам нужны только имя и цена каждой криптовалюты. В таких случаях вы можете указать это в своей функции QUERY. Столбцы Name и Price — это 2-й и 5-й столбцы в таблице. Поэтому мы указываем это в функции QUERY следующим образом:

=QUERY(IMPORTHTML("https://coinmarketcap.com/all/views/all/","table",3), "SELECT Col2, Col5")

Здесь функция IMPORTHTML сначала получит вашу таблицу номер 3 с веб-страницы coinmarketcap, а затем функция QUERY возьмет эту таблицу и извлечет из нее только второй и пятый столбцы.

Извлечение определенных строк таблицы с веб-страницы

Давайте теперь еще больше уточнить наш поиск и сузить наше окно. Допустим, мы хотим видеть только те строки, в которых значение Price превышает 1000 долларов.

Мы можем указать это условие в функции QUERY, добавив предложение WHERE следующим образом:

=QUERY(IMPORTHTML("https://coinmarketcap.com/all/views/all/","table",3), "SELECT Col2, Col5 WHERE Col5>1000")

Поскольку цена находится в столбце 5, мы указали, что хотим выбрать только те строки столбцов Col2 и Col5, где Col5> 1000.

Вот результат:

Автоматическое обновление функции IMPORTHTML через заданный интервал

В большинстве случаев мы хотели бы видеть самую свежую версию данных, которые мы импортируем с веб-страницы. Функция IMPORTHTML автоматически обновляет импортированные данные каждый час.

Однако, если вы хотите ускорить или замедлить интервал обновления, вы можете сделать это, добавив дополнительную строку обновления, как показано ниже:

=IMPORTHTML("https://coinmarketcap.com/all/views/all/?refresh=1","table",3)

Обратите внимание, что мы добавили условие «? Refresh-1» в конец строки url.

Приведенная выше формула заставляет функцию IMPORTHTML обновляться каждую минуту.

В этом руководстве мы подробно рассмотрели функцию IMPORTHTML в Google Таблицах. Мы рассмотрели, как работает функция, ее синтаксис и как использовать в разных случаях. Мы надеемся, что это руководство было полезным и простым в использовании.