Содержание

Использование функций importHTML и importXML в Google Docs для парсинга и других SEO целей

В этой статье мы рассмотрим на примерах, как парсить содержимое сайтов с помощью Google Таблиц. В частности, рассмотрим функции importHTML и importXML. Первую функцию очень удобно использовать если данные находятся в какой-то таблице или списке, а вторая функция является универсальной и практически не имеет ограничений.

Функция importHTML

С помощью функции importHTML можно настроить импорт данных из таблицы или списка на странице сайта.

Синтаксис

ссылка — ссылка на веб-страницу, включая протокол (http:// или https://),
запрос — значения «table» или «list», смотря, что нужно парсить (таблицу или список),
индекс – порядковый номер списка или таблицы (отсчет начинается с 1).

IMPORTHTML(«http://ru.wikipedia.org/wiki/Население_Индии»; «table»; 4)

Переменные можно разместить в ячейках, тогда формула изменится так:

Примеры использования importHTML

Выгрузка любых табличных данных

Задача: выгрузить список всех городов России со страницы https://ru.wikipedia.org/wiki/Список_городов_России

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

В данном случае целевая таблица является первой в коде.

Выгрузка данных из списка

Как-то я работал с интернет-магазином у которого была обширная структура и очень неудобное меню. Надо было бегло оценить текущую структуру сайта, чтобы понять ассортимент и возможные очевидные проблемы, типа дублирования пунктов меню. Меню было реализовано в виде списка, чем я и воспользовался. Меньше чем через минуту полная иерархия сайта отображалась у меня в Гугл Табличке.

Как и в первом примере для формулы понадобится определить порядковый номер списка в коде сайта. В данном случае — четвертый.

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

Функция importXML

С помощью функции importXML можно настроить импорт данных из источников в формате XML, HTML, CSV, TSV, а также RSS и ATOM XML.

Синтаксис

ссылка – адрес веб-страницы с указанием протокола (http:// или https://). Значение этого параметра должно быть заключено в кавычки или представлять собой ссылку на ячейку, содержащую URL страницы.
//XPath запрос – то, что будем импортировать. Ниже мы разберем основные примеры запросов (а тут подробнее про XPath).

Значения переменных можно хранить в ячейках, тогда формула будет такой:

Примеры использования importХML

Пример 1. Импорт мета-тегов и заголовков со страниц

Последняя формула получилась сложнее т.к. нас интересует значение не самого тега, а его атрибута:

Пример 2. Определяем наличие текста на странице и его длину в символах

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

Рассмотрим ситуацию на примере нашего блога http://alaev.info, а конкретно на странице https://alaev.info/blog/post/6202. Если заглянуть в код, то мы увидим, что контент страницы расположен в теге с классом entry.

В ячейке А1 у нас ссылка на статью, в запросе XPath мы получаем содержимое тега div, у которого есть класс entry, то есть парсим весь текст страницы: =IMPORTXML(A1;»//div[@class=’entry’]»)

Функция CONCATENATE (в русском варианте СЦЕПИТЬ) нужна, чтобы объединить все абзацы в один кусок контента. Без нее мы посчитаем только объем первого абзаца.

Функция LEN (в русском варианте ДЛСТР) считает количество символов.

Пример 3. Выгружаем актуальные цены

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

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

Я решил рассмотреть похожую ситуацию на случайном сайте. Итак, допустим, мы хотим мониторить цены на автомобили и, скажем, выгружать актуальные цены с сайта http://centrmotors.lada.ru/. Вот пример товарной карточки http://centrmotors.lada.ru/ds/cars/granta/sedan/.

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

На данном сайте нам надо парсить содержимое тега с id textspan7.

В таблице ссылки на товары укажем в столбце А, а формулу вставим в столбец В.

A2 — номер ячейки из которой берется адрес страницы,
//span[@id=’textspan7′] — блок из которого будем выводить информацию.
Если бы у //div[@class=’textspan7′]

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

Подключив логику и другие формулы, можно хакнуть рутину. Ссылки на все модели присутствуют в главном меню. А если есть ссылки, то скорее всего их тоже можно спарсить.

И действительно, заглянув в код, мы увидим, что ссылки на товарные страницы располагаются в теге

c классом CMtext4:

A8 –
с классом CMtext4,
/a/@href – а в этой части формулы мы уточняем, что из содержимого
хотим достать содержимое вложенного тега , а если еще точнее, то ту часть, которая прописана в href=»».

После применения формулы в документе получится такой список:

Мы получили ссылки на все товары, которые нас интересуют. Осталось настроить парсинг цен по этим адресам. Но ссылки на страницы относительные, а нам обязательно нужны абсолютные ссылки, включающие имя домена. Чтобы подставить в ссылку домен используем функцию CONCATENATE.

Либо при условии, что у нас в ячейке А8 находится адрес домена:

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

A$8 — ячейка, в которой указан домен сайта. Знаком $ фиксируем строку, чтобы это значение не изменилось, когда мы начнем протягивать формулу вниз.
A9 — это первый URL товарной страницы, при перетаскивании формулы значение автоматически меняется, т.е. в 10 строке у нас вместо A9 будет A10, в 11 строке A11 и т.д.

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

Пример универсальный и имеет применение не только в рабочих целях. Например, можно мониторить цены на товары в ожидании скидок. А если еще немного потрудиться, то и настроить оповещение на почту при изменении цены (или любого другого значения) ниже/выше определенного значения.

Пример 4. Узнать количество товаров в категориях

Очень полезный прием для тех, кто хоть раз продвигал интернет-магазины без товаров. Нет, это не шутка! Мне доводилось пару раз, как бы это смешно ни звучало.

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

Но с помощью парсинга легко узнать на каких страницах есть беда с ассортиментом.

Для примера возьмем сайт http://bz2.ru. Категории с товарами выглядят вот так http://bz2.ru/katalog/generatory-dizelnye/dizelnye-generatory-100-kvt/.

Чтобы посчитать количество товаров идем в код сайта и смотрим какими тегами оформлены товары.

Товар оформлен в теге с классом product, заголовок товара оформлен в теге с классом title. Я решил посчитать заголовки:

COUNTA нужна, чтобы подсчитать число значений, если бы мы не использовали эту функцию, то результат был бы таким:

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

Пример 5. Парсим код ответа сервера

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

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

В сети много сервисов, которые проверяют страницы на код ответа сервера. Суть идеи в том, чтобы парсить данные с такого сервиса. Для примера я решил взять самый популярный — http://www.bertal.ru/ — если мы проверим в нем страницу, то он отобразит результат по URL вида https://bertal.ru/index.php?a4789763/alaev.info/blog/post/6202#h.

Я не знаю, что означают символы a4789763, но на результат они не влияют. Смотрим:

Функцией CONCATENATE склеиваем наши куски в один URL с которого и парсим данные.

Пример 6. Узнаем количество страниц в индексе ПС

Если мы введем в поисковик запрос типа [site:http://alaev.info], то узнаем сколько всего страниц данного сайта находится в выдаче.

Результаты будут выведены на странице типа https://yandex.ru/search/?text=site%3Ahttp%3A%2F%2Falaev.info&lr=213, где после [https://yandex.ru/search/?text=site%3A] следует адрес сайта и необязательный параметр региона поиска [&lr=213].

Все, что нам нужно — это сформировать URL, по которому поисковик отдаст нам ответ, и определить из каких тегов парсить эту инфу.

В данном случае данные лежат в теге с классом serp-adv__found.

Если мы поместим ссылку на сайт в ячейку A15, то формула будет такая:

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

Для этих целей будем использовать функцию SUBSTITUTE (в русском варианте ПОДСТАВИТЬ). Суть идеи в том, чтобы убрать слово [Нашлось: ] и заменить надпись [тыс. результатов] на [000], чтобы в итоге надпись выглядела как просто число 2000.

Но есть нюанс. Окончание меняется в зависимости от результата, например, может выглядеть как [Нашёлся 1 результат], поэтому такие вариации тоже надо будет заменить.

Не буду вас утомлять, поэтому ближе к делу: сначала заменим надпись [Нашлось: ] на пустоту вот так:

Дальше заменим [тыс. результатов] на [000] для этого добавим еще одну аналогичную функцию, и формула будет выглядеть так:

Проделаем тоже самое для остальных вариантов и вместо слова [ссылка] пропишем функцию импорта. Конечная формула будет следующей:

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

Ограничения

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

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

Если у вас есть на заметке интересные примеры, обязательно делитесь в комментариях!

Вот уже 16 лет я профессионально занимаюсь созданием и продвижением сайтов и 12 лет с удовольствием пишу в любимый блог. Создал заслужившие доверие в seo-сообществе программы FastTrust и ComparseR и известный сервис для проверки траста сайтов CheckTrust.ru.

В 2014 основал веб-студию «АлаичЪ и Ко» в Краснодаре: 1 место в рейтинге агентств интернет-маркетинга (Рейтинг Рунета), 2 место в рейтинге SEO-компаний (CMSmagazine), 12 место в рейтинге известность бренда SEO-компаний в России (SeoNews).

[expert_bq id=»1570″]Если щелкнуть на желтом квадрате со стрелкой, он превращается в зеленый флажок, указывающий, что данные этой таблицы будут импортированы. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Когда вы выберете файл, мы захватим первые 100 строк для просмотра ниже. Этот «Предел строк предварительного просмотра» определяет, сколько строк вы можете использовать для проверки ИМПОРТА по мере прохождения мастера. Вы можете увеличить его, но это потребует больше ресурсов, так что не сходите с ума.

Excel. Получение информации с веб-страницы

A2 — номер ячейки из которой берется адрес страницы,
//span[@id=’textspan7′] — блок из которого будем выводить информацию.
Если бы у //div[@class=’textspan7′]

У вас может быть файл Excel со 100 столбцами, но ваша таблица имеет только 30. Здесь вы даете команду SQL Developer, какие столбцы должны использоваться для импорта. Вы также можете изменить порядок столбцов, что может сделать следующий шаг немного проще.

Как я могу экспортировать таблицы в Excel с веб-страницы [закрыто]

Хотите улучшить этот вопрос? Обновите вопрос, чтобы на него можно было ответить с помощью фактов и цитат, отредактировав этот пост .

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

@user — здесь есть как минимум две разные проблемы: 1) форматирование данных, чтобы они правильно отображались в Excel, и 2) экспорт данных с использованием Javascript, чтобы он правильно установил тип mime, предлагая пользователю сохранить файл . Вы пытаетесь решить обе эти проблемы?

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

DataTables полностью написан на Javascript. Просто элемент TableTools использует Flash, и он крошечный. Я НИКОГДА не буду использовать Flash в своих продуктах!

Я понимаю и согласен. Но все же — пусть даже и крохотный — там есть объект .swf, и он не может работать без Flash.

Здравствуйте, вы можете показать полный пример, я слишком новичок, чтобы заставить его работать без примера!

@PramodGaikwad, нет, Datatables заменит NG-table. По сути, они обладают той же функциональностью, но Datatables намного более зрелый и имеет гораздо больше функций. Есть дополнительный продукт Datatables, созданный специально для Angular: l-lin.github.io/angular-datatables/#/welcome

Давным-давно я обнаружил, что Excel откроет HTML-файл с таблицей, если мы отправим его с типом содержимого Excel. Рассмотрим документ выше:

Во всяком случае, в документе выше я добавил кнопку, которая теоретически загружала бы весь документ в виде файла Excel:

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

Это простое решение отлично работает. Посмотрите на этот повторяющийся вопрос, чтобы иметь возможность установить имя файла, а также установить имя рабочего листа. Одинаковый тип решения; stackoverflow.com/questions/17126453/…

Это больше не работает в Office 365 из-за более строгих мер безопасности. Файл Excel должен быть ИСТИННЫМ документом Excel, иначе при открытии будет выдана ошибка.

Откройте Excel и создайте рабочий лист с желаемым форматированием и цветами.
Сохраните книгу Excel как «Таблица XML 2003 (* .xml)».
Откройте полученный файл в текстовом редакторе, например в блокноте, и скопируйте значение в строку в своем приложении.
Предполагая, что вы используете подход на стороне клиента с URI данных, код будет выглядеть так:
Затем вы можете использовать замену строк, чтобы создать коллекцию строк, которые будут вставлены в шаблон рабочего листа.

После сбора информации создайте последнюю строку и откройте новое окно, используя URI данных.

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

Вам также может потребоваться выполнить кодирование base64 для содержимого URI данных, для чего может потребоваться библиотека js , а также добавить строку ‘; base64’ после типа mime в URI данных.

Хотя использовать OpenXML приятно, это решение не будет работать с таблицами с colspans или rowspans без большой работы над генератором javascript

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

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

Веб-запрос в основном запускает HTTP-запрос непосредственно из Excel и копирует некоторые или все полученные данные (и, при необходимости, форматирование) в рабочий лист.

После того, как вы определили веб-запрос, вы можете обновить его в любое время, даже не выходя из Excel. Таким образом, вам не нужно фактически «экспортировать» данные и сохранять их в файл — вы лучше обновите данные, как из базы данных.

Вы даже можете использовать параметры URL-адреса, если Excel предложит вам определенные критерии фильтрации и т. Д.

Он действительно работает с базовой аутентификацией, а также с аутентификацией на основе форм, но с последней вам, возможно, придется нажать на «редактировать запрос», чтобы повторно временем

Mozilla по-прежнему поддерживает базовые 64 URI. Это позволяет динамически составлять двоичный контент с помощью javascript:

если ваш файл excel не очень навороченный (без диаграмм, формул, макросов), вы можете копаться в формате и составлять байты для своего файла, затем кодировать их с помощью base64 и вставлять в href

На самом деле это проще, чем вы думаете: «просто» скопируйте HTML-таблицу (то есть HTML-код для таблицы) в буфер обмена. Excel умеет декодировать HTML-таблицы; он даже попытается сохранить атрибуты.

Жесткая часть — «скопировать таблицу в буфер обмена», поскольку стандартного способа доступа к буферу обмена из JavaScript не существует. См. Это сообщение в блоге: Доступ к системному буферу обмена с помощью JavaScript — Святой Грааль?

Теперь все, что вам нужно, это таблица в формате HTML. Предлагаю jQuery и метод html () .

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

Я попытался использовать этот код, он действительно открыл таблицу в Excel, но неправильный формат выглядит так, как будто он просто скопировал код html в таблицы. вот так:

SARTIN, DAN BALAEZ, BARBARA

Есть предложения?

Это потому, что он использовал innerHTML. Элемент, который он получает, ЯВЛЯЕТСЯ таблицей, поэтому он должен быть externalHTML. Я внес правку

Я получаю сообщение об ошибке: «Сервер автоматизации не может создать объект» при создании ActiveXObject. Как я могу это исправить?

преобразование должно выполняться на стороне клиента

код python, который открывает окно ie и имеет к нему доступ: переменная url содержит URL-адрес (‘http: //’)

Примечание: если страница недоступна напрямую, но войдите в систему, вам нужно будет обработать это, введя данные формы и эмулируя действия пользователя с помощью python.

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

затем, когда данные находятся в переменной python, вы можете открыть экран Excel аналогичным образом, используя python:

только совет: используйте AppleScript — он имеет простой и похожий на API win32com.client Dispatch

java.awt.Robot может работать для этого, у него есть щелчок, нажатие клавиши (можно использовать горячие клавиши), но ни один API для Linux, о котором я знаю, не может работать так же просто, как AppleScript

[expert_bq id=»1570″]Этот код является только IE, поэтому он полезен только в ситуациях, когда вы знаете, что все ваши пользователи будут использовать IE например, в некоторых корпоративных средах. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если вы выберете « Таблица» , опция «Существующая рабочая таблица» будет отключена, будет выбрана опция « Новая рабочая таблица», и Excel создаст столько таблиц, сколько будет импортировано таблиц из базы данных. Таблицы Excel отображаются в этих таблицах.

Как я могу экспортировать таблицы в Excel с веб-страницы закрыто

Откройте Excel и создайте рабочий лист с желаемым форматированием и цветами.
Сохраните книгу Excel как «Таблица XML 2003 (* .xml)».
Откройте полученный файл в текстовом редакторе, например в блокноте, и скопируйте значение в строку в своем приложении.
Предполагая, что вы используете подход на стороне клиента с URI данных, код будет выглядеть так:
Затем вы можете использовать замену строк, чтобы создать коллекцию строк, которые будут вставлены в шаблон рабочего листа.

Рис. 1. Использование диалогового окна Создание веб-запроса для указания данных, которые следует импортировать; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Я попытался использовать этот код, он действительно открыл таблицу в Excel, но неправильный формат выглядит так, как будто он просто скопировал код html в таблицы. вот так:

SARTIN, DAN BALAEZ, BARBARA

Есть предложения?

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции