Работаем в Google Таблицах

И в это статье рассмотрим 9 функций в Google Таблицах, которые помогут SEO-специалистам (и не только) автоматизировать и упростить повседневные задачи.

Парсинг данных

<product id="00000000040" sort="1" series="ISDFP" language="ru" name="Секционные противопожарные ворота с пределом огнестойкости EI60">

Формула (где, url страницы для парсинга записан в ячейке A1, например, https://domain.ru/data/products/00000000053.xml):

=IMPORTXML(A1;"//product[@id='00000000053']/@name")

или так (с любыми атрибутами):

=IMPORTXML(A1;"//product[@*]/@name")|

Уместить результат формулы в одну ячейку

=JOIN(CHAR(10);IMPORTXML("https://yandex.ru/search/?text=test";"//h2/text()"))

Если заголовки умещаются в одну строку, то запрос xPath можно сделать и без /text()

xPath примеры

<root xmlns:foo="http://www.foo.org/" xmlns:bar="http://www.bar.org">
    <employees>
        <employee id="1">Johnny Dapp</employee>
        <employee id="2">Al Pacino</employee>
        <employee id="3">Robert De Niro</employee>
        <employee id="4">Kevin Spacey</employee>
        <employee id="5">Denzel Washington</employee>
    </employees>
    <foo:companies>
        <foo:company id="6">Tata Consultancy Services</foo:company>
        <foo:company id="7">Wipro</foo:company>
        <foo:company id="8">Infosys</foo:company>
        <foo:company id="9">Microsoft</foo:company>
        <foo:company id="10">IBM</foo:company>
        <foo:company id="11">Apple</foo:company>
        <foo:company id="12">Oracle</foo:company>
    </foo:companies>
</root>

Выбрать узел документа
```
/
```
Выбрать корневой элемент /root
Выбрать все элементы employee, которые являются прямыми дочерними элементами элемента employees
```
/root/employees/employee
```
Select all 'company' elements regardless of their positions in the document.
```
//foo:company
```
Select the 'id' attributes of the 'company' elements regardless of their positions in the document.
```
//foo:company/@id
```
Select the textual value of first 'employee' element.
```
//employee[1]/text()
```
Select the last 'employee' element.
```
//employee[last()]
```
Select the first and second 'employee' elements using their position.
```
//employee[position() < 3]
```
Select all 'employee' elements that have an 'id' attribute.
```
//employee[@id]
```
Select the 'employee' element with the 'id' attribute value of '3'.
```
//employee[@id='3']
```
Select all 'employee' nodes with the 'id' attribute value lower or equal to '3'. //employee[@id<=3]
Select all the children of the 'companies' node.
```
/root/foo:companies/*
```
Select all the elements in the document.
```
//*
```
Select all the 'employee' elements AND the 'company' elements.
```
//employee|//foo:company
```
Select the name of the first element in the document.
```
name(//*[1])
```
Select the numeric value of the 'id' attribute of the first 'employee' element.
```
number(//employee[1]/@id)
```
Select the string representation value of the 'id' attribute of the first 'employee' element.
```
string(//employee[1]/@id)
```
Select the length of the first 'employee' element's textual value.
```
string-length(//employee[1]/text())
```
Select the local name of the first 'company' element, i.e. without the namespace.
```
local-name(//foo:company[1])
```
Select the number of 'company' elements.
```
count(//foo:company)
```
Select the sum of the 'id' attributes of the 'company' elements.
```
sum(//foo:company/@id)
```

Пример

<block sort="4" type="3" showcaption="true" caption="Основные технические характеристики">
<elements>
<element sort="1">
<table>
<cells>
<object sort="1" datatype="string" value=""/>
<object sort="2" datatype="string" value=""/>
<object sort="3" datatype="string" value=""/>
<object sort="4" datatype="string" value=""/>
<object sort="5" datatype="string" value=""/>
<object sort="6" datatype="string" value=""/>
</cells>
<rows>
<row sort="1">
<object sort="1" datatype="string" value="Высота конструкции"/>
<object sort="2" datatype="string" value="до 100 м"/>
<object sort="3" datatype="string" value=""/>
<object sort="4" datatype="string" value=""/>
<object sort="5" datatype="string" value=""/>
<object sort="6" datatype="string" value=""/>
</row>
<row sort="2">
<object sort="1" datatype="string" value="Ширина конструкции"/>
<object sort="2" datatype="string" value="определяется заказчиком, без ограничений"/>
<object sort="3" datatype="string" value=""/>
<object sort="4" datatype="string" value=""/>
<object sort="5" datatype="string" value=""/>
<object sort="6" datatype="string" value=""/>
</row>
<row sort="3">
<object sort="1" datatype="string" value="Толщина заполнения"/>
<object sort="2" datatype="string" value="от 4 до 55 мм"/>
<object sort="3" datatype="string" value=""/>
<object sort="4" datatype="string" value=""/>
<object sort="5" datatype="string" value=""/>
<object sort="6" datatype="string" value=""/>
</row>
</rows>
</table>
</element>
</elements>
</block>

Формула (где, ссылка страницы для парсинга записана в ячейке A2):

=ЕСЛИОШИБКА(IMPORTXML(A2;"//block[@caption='Основные технические характеристики']/elements/element/table/rows/row[@sort='1']/object[@sort='1']/@value"))

Выведет:

Высота конструкции

Удалить разрывы строк

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

=CLEAN(A1)

Если ошибка

=ЕСЛИОШИБКА(A1; "Ошибка в ячейке A1")

или

=ЕСЛИОШИБКА(A2)

ImportHTML — импорт таблиц и списков из веб-страницы

В качестве параметров надо указать адрес веб-страницы, тип данных («table» или «list»), и порядковый номер таблицы, данные которой импортируются.

Как узнать порядковый номер таблицы? Ответ нам даст исходный код страницы и инструменты разработчика Chrome. Для этого делаем следующее:

Кликаем правой кнопкой мыши на области, которую занимает интересующая таблица, выбираем в меню «Показать исходный код».
Откроется окно, где покажется дерево элементов исходной страницы. Скорее всего, автоматически выделится не сам элемент таблицы, а какой-то из дочерних элементов. Нас же интересует родительский элемент table. Идем вверх по дереву и находим его. Если таблица найдена правильно, при наведении мыши на веб-странице должна подсветиться область с нашей таблицей.

Когда таблица найдена, кликаем на ней правой кнопкой мыши и выбираем «Copy — Copy XPath». Затем в любом текстовом редакторе вставляем из буфера обмена текст, который будет выглядеть приблизительно так: //*[@id='mw-content-text']/table[3]. Число в квадратных скобках в конце — это порядковый номер нашей таблицы, начиная с нуля. Поэтому чтобы использовать его в таблицах Google, нужно добавить к нему единицу.

Функция REGEXEXTRACT

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

Синтаксис функции:

=REGEXEXTRACT(text, regular_expression)

В данном случае text — это будет название ячейки, а regular_expression (регулярное выражение) будет следующим:

^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)

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

Также можно воспользоваться функцией =ARRAYFORMULA, которая работает с массивом данных. Чтобы её использовать, оберните свою формулу в неё:

=ARRAYFORMULA(REGEXEXTRACT(A1:A,"^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)"))

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

=REGEXEXTRACT(A2,"блог[а-я]")

=REGEXEXTRACT(A2,"блог|сайт")

Вы можете применять и другие регулярные выражения.

Функция VLOOKUP

VLOOKUP (ВПР) для Google Таблиц — это одна из самых популярных функций среди SEO-специалистов. Она выполняет поиск в строках по вертикали (поэтому и ВПР — вертикальный просмотр) и возвращает значение из определённой ячейки в указанном диапазоне. С помощью VLOOKUP можно переносить и объединять данные из одной таблицы в другую, а также проверять наличие значения в других наборах данных.

Синтаксис:

=VLOOKUP(запрос; диапазон; индекс; [отсортировано])

где:

запрос — критерий, по которому производится поиск;
диапазон — диапазон данных;
индекс — номер столбца, из которого будет извлекаться нужное значение. Важно, чтобы номер столбца не выходил за рамки диапазона;
отсортировано — показывает, отсортированные ли значения в столбце, по которому выполняется поиск. Чаще всего указывается значение ЛОЖЬ — это означает точное совпадение, поэтому в формуле ставится «0».

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

Функция VLOOKUP (ВПР): пример применения

Чтобы перенести содержимое ячеек в столбце «Заголовок» я буду использовать формулу:

=ВПР(A2,Meta!A:B,2,0)

Для переноса данных из столбца «Описание» будет использоваться следующая формула:

=ВПР(A2,Meta!A:C,3,0)

Также есть аналогичная функция, только для горизонтального поиска (HLOOKUP), если вдруг у вас всё повернётся на 90 градусов.

3. IMPORTXML

IMPORTXML — это функция, которая позволяет импортировать данные формата XML, HTML, RSS, CSV и других. С её помощью вы можете парсить данные с сайтов не покидая Google Таблиц, например, извлекать метаданные или контактные данные со страниц.

Синтаксис:

IMPORTXML ("URL";"xpath_запрос")

На этой странице