Привіт, друзі. Сьогодні я хочу поділитися ще однією напрацюванням з великого списку регламентів та інструкцій нашої студії «АлаичЪ і Ко». Серед моїх колег є фанат роботи з Гугл Таблицями – це Олексій Степанов, спільно з яким ми готували для вас минулу публікацію про написання seo-текстів та підготовки ТЗ для копірайтерів. Впевнений, що і серед читачів блогу багато тих, хто використовує Гугл Таблиці замість Екселю. Сьогоднішня публікація для вас, Олексій підготував без моєї допомоги, тому без довгих вступів я відразу передаю йому слово.

У цій статті ми розглянемо на прикладах, як парсити вміст сайтів за допомогою Таблиць Google. Зокрема, розглянемо функції importHTML і importXML. Першу функцію дуже зручно використовувати якщо дані знаходяться в якийсь таблиці або списку, а друга функція є універсальною та практично не має обмежень.

Функція importHTML

За допомогою функції importHTML можна настроїти імпорт даних з таблиці або списку на сторінці сайту.

Синтаксис

=IMPORTHTML(посилання; запит; індекс)

=IMPORTHTML(посилання; запит; індекс)

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

Приклад:

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

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

Змінні можна розмістити у клітинках, тоді формула зміниться так:

IMPORTHTML(A2; B2; C2)

IMPORTHTML(A2; B2; C2)

Приклади використання importHTML

1. Вивантаження будь-яких табличних даних

Одного разу мені знадобилося зібрати список мінус-слів по містах Росії в іменнику відмінку. Перші пару нагугленных файлів з мінус-слова були неповними, а в інших мене не влаштувала словоформа. Тому я просто вирішив спарс сторінку Вікіпедії, що зайняло близько двох хвилин, включаючи час на відкриття Гугл Таблиці, пошук потрібної сторінки Вікіпедії і налаштування формули. З цього прикладу я і почну.

Завдання: вивантажити список всіх міст Росії з сторінки https://ru.wikipedia.org/wiki/Список_городов_России

Щоб вивантажити дані з цієї таблиці нам потрібно вказати у формулі її порядковий номер в коді сторінки. Щоб цей номер дізнатися, потрібно відкрити код сайту (в нормальних браузерах це поєднання Ctrl+U, або клавіша F12, відкриває панель розробника). А далі пошуком за кодом визначити порядковий номер:

В даному випадку цільова таблиця є першою в коді.

Складаємо формулу:

=IMPORTHTML(“https://ru.wikipedia.org/wiki/Список_городов_России”;”table”;1)

=IMPORTHTML(“https://ru.wikipedia.org/wiki/Список_городов_России”;”table”;1)

Результат:

2. Вивантаження даних зі списку

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

Завдання: вивантажити всі пункти меню, оформленого тегами

.

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

Складаємо формулу:

=IMPORTHTML(“https://tools-markets.ru/”;”list”;3)

=IMPORTHTML(“https://tools-markets.ru/”;”list”;3)

Результат:

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

Функція importXML

За допомогою функції importXML можна настроїти імпорт даних з різних джерел у форматі XML, HTML, CSV, TSV, а також RSS і ATOM XML.

Порівняно з importHTML у даної функції набагато більш широке застосування. Вона дозволяє збирати практично будь-яку інформацію зі сторінки/документа, від часткових фрагментів до повного її змісту. Нижче будуть описані приклади парсинга HTML-сторінок сайту. Парсинг файлів розглядати не буду, оскільки така задача виникає рідко і більшість з вас з цим не зіткнеться ніколи. Але якщо що, принцип роботи скрізь однаковий, головне зрозуміти суть.

Синтаксис

IMPORTXML(посилання; “//XPath запит”)

IMPORTXML(посилання; “//XPath запит”)

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

Приклад:

IMPORTXML(“https://en.wikipedia.org/wiki/Moon_landing”; “//a/@href”)

IMPORTXML(“https://en.wikipedia.org/wiki/Moon_landing”; “//a/@href”)

Значення змінних можна зберігати в осередках, тоді формула буде такою:

IMPORTXML(A2; B2)

IMPORTXML(A2; B2)

Приклади використання importХML

1. Імпорт мета-тегів і заголовків зі сторінок

Найпростіша і поширена ситуація, яка може бути — дізнатися мета-теги або заголовки просуваються сторінок. Для цих цілей краще підходить парсинг сайту спеціалізованим софтом типу Comparser’a. Але бувають і виключення, наприклад, якщо сайт дуже великий і це зробити важко, або у вас немає під рукою програми. Або якщо ви плануєте надалі використовувати отримані дані в таблицях для моніторингу змін мета-тегів і звірки цільових і фактичних мета-тегів на сторінках.

Формули прості.

Отримуємо title сторінки:

=importxml(A3;”//title”)

=importxml(A3;”//title”)

Отримуємо заголовки h1:

=importxml(A3;”//h1″)

=importxml(A3;”//h1″)

Отримуємо description:

=importxml(A3;”//meta[@name=’description’]/@content”)

=importxml(A3;”//meta[@name=’description’]/@content”)

Остання формула вийшла складніше оскільки нас цікавить значення не самого тега, а його атрибута:

  • шукаємо тег meta //meta
  • у якого є атрибут name=’description’ [@name=’description’]
  • і парсим вміст другого атрибута content /@content

2. Визначаємо наявність тексту на сторінці і його довжину в символах

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

Розглянемо ситуацію на прикладі нашого блогу http://alaev.info, а конкретно на сторінці http://alaev.info/blog/post/6202. Якщо заглянути в код, то ми побачимо, що контент сторінки розташований у тегу
з класом entry.

Формула:

=LEN(concatenate(IMPORTXML(A1;”//div[@class=’entry’]”)))

=LEN(concatenate(IMPORTXML(A1;”//div[@class=’entry’]”)))

У комірці А1 у нас посилання на статтю, в запиті XPath ми отримуємо вміст тега div, у якого є клас entry, тобто парсим весь текст сторінки: =IMPORTXML(A1;”//div[@class=’entry’]”)

Функція CONCATENATE (в російській варіанті ЗЧЕПИТИ) потрібна, щоб об’єднати всі абзаци в один шматок контенту. Без неї ми порахуємо тільки обсяг першого абзацу.

Функція LEN (в російській варіанті LEN) вважає кількість символів.

3. Вивантажуємо актуальні ціни

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

На щастя, варіант з Гугл доками виявився підходящим і вже до кінця дня я все налаштував так, що дані підтягувалися автоматично. Від мене нічого не потрібно, окрім як надіслати посилання на документ клієнту і спокійно займатися своїми справами.

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

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

На даному сайті нам треба парсити вміст тега з id textspan7.

Формула:

=importxml(A2;”//span[@id=’textspan7′]”)

=importxml(A2;”//span[@id=’textspan7′]”)

У таблиці посилання на товари вкажемо в стовпці А, а формулу вставимо в стовпець Ст.

Результат:

Поясню:

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

    //div[@class=’textspan7′]

  • Автоматизуємо далі.

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

    Підключивши логіку і інші формули, можна хакнуть рутину. Посилання на всі моделі присутні в головному меню. А якщо є посилання, то швидше за все їх теж можна спарс.

    І дійсно, заглянувши в код, ми побачимо, що посилання на товарні сторінки розташовуються в теге

    c класом CMtext4:

    Формула:

    =IMPORTXML(A8;”//p[@class=’CMtext4′]/a/@href”)

    =IMPORTXML(A8;”//p[@class=’CMtext4′]/a/@href”)

    • A8 – в цій комірці посилання на сайт,
    • p[@class=’CMtext4′] – тут ми шукаємо вміст тега

      з класом CMtext4,

    • /a/@href – а в цій частині формули ми уточнюємо, що з вмісту

      хочемо дістати вміст вкладеного тега , а якщо ще точніше, то ту частину, яка прописана в href=””.

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

    Ми отримали посилання на всі товари, які нас цікавлять. Залишилося налаштувати парсинг цін за цими адресами. Але посилання на сторінки відносні, а нам обов’язково потрібні абсолютні посилання, що містять ім’я домену. Щоб підставити у посилання домен використовуємо функцію CONCATENATE.

    Формула:

    =IMPORTXML(concatenate(“http://centermotors.lada.ru”;A9);”//span[@id=’textspan7′]”)

    =IMPORTXML(concatenate(“http://centermotors.lada.ru”;A9);”//span[@id=’textspan7′]”)

    Або за умови, що у нас в осередку А8 знаходиться адресу домену:

    =IMPORTXML(concatenate(A$8;A9);”//span[@id=’textspan7′]”)

    =IMPORTXML(concatenate(A$8;A9);”//span[@id=’textspan7′]”)

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

    Результат:

    Поясню, якщо хтось ще не розібрався:

    • 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(IMPORTXML(A3;”//div[@class=’title’]”))

    =COUNTA(IMPORTXML(A3;”//div[@class=’title’]”))

    Результат:

    COUNTA потрібна, щоб підрахувати число значень, якби ми не використали цю функцію, то результат був би таким:

    Як і в прикладі вище, можна автоматизувати процес, щоб посилання на всі категорії парсились автоматично і їх не доводилося додавати вручну.

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

    Я дуже рідко зустрічаю сайти, де постійно відвалюється, але все ж буває. У разі необхідності можна швидко перевірити всі просувні сторінки на доступність і коректну відповідь сервера.

    Зазвичай я такі речі моніторю з допомогою скриптів без використання формул, але з допомогою даної функції можна реалізувати. Цей спосіб, мабуть, єдиний виняток із загального списку прикладів, тому що я до нього не вдавався ні разу, але впевнений, що комусь із вас він стане в нагоді.

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

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

    Формула:

    =importxml(concatenate(A$4;A6);”//div[@id=’otv’]/b”)

    =importxml(concatenate(A$4;A6);”//div[@id=’otv’]/b”)

    Результат:

    • A$4 — адреса клітинки з незмінною частиною URL,
    • A6 — комірка з адресою сторінки, код відповіді, якої хочемо перевірити.

    Функцією 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, то формула буде така:

    =importxml(CONCATENATE(“ href=“https://yandex.ru/search/?text=site%3A%22;A15”>https://yandex.ru/search/?text=site%3A”;>A15);”//div[@class=’serp-adv__found’]”)

    =importxml(CONCATENATE(“https://yandex.ru/search/?text=site%3A”;A15);”//div[@class=’serp-adv__found’]”)

    Результат:

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

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

    Але є нюанс. Закінчення змінюється в залежності від результату, наприклад, може виглядати як [Знайшовся 1 результат], тому такі варіації теж треба буде замінити.

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

    =SUBSTITUTE(посилання;”Знайшлося “;””)

    =SUBSTITUTE(посилання;”Знайшлося “;””)

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

    =SUBSTITUTE(SUBSTITUTE(посилання;”Знайшлося “;””);”тис.”;”000″)

    =SUBSTITUTE(SUBSTITUTE(посилання;”Знайшлося “;””);”тис.”;”000″)

    Зробимо теж саме для інших варіантів і замість слова [посилання] пропишемо функцію імпорту. Кінцева формула буде такою:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(importxml(CONCATENATE(“https://yandex.ru/search/?text=site%3A”;A10);”//div[@class=’serp-adv__found’]”);”Знайшлося “;””);” результатів”;””);” результату”;””);” “;””);”Знайшлася”;””);”результат”;””);”Знайшовся”;””);”тис.”;”000″)

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(importxml(CONCATENATE(“https://yandex.ru/search/?text=site%3A”;A10);”//div[@class=’serp-adv__found’]”);”Знайшлося “;””);” результатів”;””);” результату”;””);” “;””);”Знайшлася”;””);”результат”;””);”Знайшовся”;””);”тис.”;”000″)

    Трохи страшно, правда? Можливо, є більш елегантні рішення, але так як даний варіант універсальний і повністю робочий, то я зупинився на ньому. Якщо ви знайдете більш красиве рішення, то поділіться в коментарях, мені буде цікаво побачити інші можливі побудови формули.

    Обмеження

    Мінусом використання даних функцій є те, що великі обсяги даних обробити не вийде. Існують обмеження на кількість вихідних запитів, і якщо вам потрібно, наприклад, послати 1000 запитів, то ви зіткнетеся з ситуацією, коли в більшості осередків у вас буде перебувати напис «Loading». Точних цифр Google не призводить, але за особистим спостереженням — за один раз можна відправити близько 100 запитів, після чого відбувається таймаут приблизно на 1 годину до відправлення наступної партії запитів. Тому вам підійде цей функціонал тільки в тому випадку, якщо не планується обробка великих обсягів даних.

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

    Для мене можливості даних функцій незамінні для виконання простих завдань. Сподіваюся, що і для вас ця інформація виявилася корисною і дозволить перемогти рутину в повсякденних завданнях.

    Якщо у вас є на замітці цікаві приклади, обов’язково діліться у коментарях!

    Дякую за увагу. До зв’язку!

    З повагою, Олександр Алаєв