A A A

Google Spreadsheets многими позиционируется как «младший брат» Microsoft Excel. Формально оно, конечно, так и есть: набор функций и детальность инструментов Excel позволяет проводить операции с данными с хирургической точностью. В то же время Google Spreadsheet, как и любой другой продукт Google Docs, опережает Excel в плане удобства совместной работы.

Облачная основа сервиса от Google делает его еще и отличным инструментом для сбора и постоянного обновления данных из сети: будь то данные веб-страницы или ячейки из другой таблицы Google.

Импорт и парсинг данных

Таблицы Google имеют набор гибких инструментов для сбора внешних данных. Функции импорта таковы:

ImportRange — импорт данных из другой таблицы Google

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

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

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

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

importhtml

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

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

ImportXML — импорт других данных с веб-страницы

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

importxml

Второй параметр формулы ImportXML — адрес XPath — добывается точно таким же способом, что описан выше. Впрочем, если вас смущает нагромождение лишних символов в автоматически сгенерированном адресе, всегда можно ознакомиться с документацией XPath и попробовать составить свой адрес XPath. Например, в нашем случае с равным успехом сработал бы простой адрес «//h1».

GOOGLETRANSLATE — Автоматический перевод текста внутри листа

Радикально сокращает объем работы при сборе семантического ядра для мультиязычной аудитории.

googletranslate

Будьте осторожны: как и любая система автоматического перевода, Google Translate не может угадать ваши мысли. Особенно это касается ключевых слов, содержащих имя бренда: оно почти без исключений не подлежит переводу. Поэтому ручной контроль при использовании этой формулы обязателен.

IMAGE — изображение внутри ячейки

Эта функция будет полезной для оформления и брендирования отчетов внутри Google Spreadsheets. Синтаксис незамысловатый, при желании можно добавить параметры для трансформации изображения.

function-image

Как импортировать данные из Google Analytics?

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

Куда более элегантный способ — воспользоваться официальным расширением для таблиц Google с говорящим названием Google Analytics Spreadsheets Add-On. Установить его можно, перейдя по ссылке, либо же перейдя по меню «Дополнения — Установить дополнения»:

gs-addon

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

Чтобы охватить все возможности этого расширения, необходима отдельная статья. Наиболее полное руководство руководство по Google Analytics Spreadsheets Add-on на русском языке написал Алексей Селезнев на блоге Нетпика. Рекомендуем начать знакомство с нее, а в случае необходимости — ознакомиться с официальной документацией Google.

Агрегирование данных

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

Сводные таблицы

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

В качестве примера возьмем данные по источникам трафика, сессиям и конверсиям для интернет-магазина за последние семь дней.

pivot

Сводные таблицы — то, что поможет нам вытащить любую комбинацию данных по столбцам и строкам из этой таблицы. Выделив нужный диапазон, жмем меню «Данные — Сводная таблица». Откроется пустой новый лист с меню настроек справа. Чтобы удобно просмотреть информацию по каждому дню, выполняем  в боковом меню следующие пункты:

  • В меню «Строки» выбираем «ga:SourceMedium», а в меню «Столбцы» — «ga:day».
  • В меню «Значения» выбираем интересующий показатель — пользователи, сессии либо количество выполненных целей.

В результате получаем таблицу с удобно сгруппироваными по источнику и разбитым по дням конверсиями:

pivot_tables

Фильтрация таблиц

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

filter

Пусть перед нами стоит задача просмотреть только страницы, посвященные поисковым запросам. Для движка WordPress, например, это будут все страницы с параметром ?s=поисковой запрос. Выделяем интересующий нас диапазон, при желании даем ему название, и выбираем в меню «Данные — Фильтры — создать новый фильтр». Внешний вид таблицы слегка изменится. Кликнув на иконке в правой части ячейки-заголовка, мы теперь можем указать необходимый нам параметр фильтра: «Фильтровать по условию — текст содержит s=«.

filter-2

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

 Заключение

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

Если у вас возникли вопросы относительно того, как решить ту или иную задачу в Google Spreadsheets — опишите ее в комментариях и мы поможем с ней справиться.

Если вы нашли ошибку, выделите участок текста и нажмите Ctrl + Enter или , чтобы сообщить нам.

  • Вадим

    Здається, формула ImportRange має обмеження при імпорті діапазону.
    Наприклад, формула ImportRange(«url»;’лист'(T7:T)) імпортує не весь стовбець, а частину. Причому максимальна кількість строк не зрозуміла, приблизно 100.
    Чи можна це якось обійти?

    • Олег Омельченко

      Здравствуйте!
      Протестировал работу функции на таблице с несколькими тысячами строк — не заметил никаких ограничений при импорте столбца (в формате A1:A). Если есть возможность, отправьте доступы к таблицам, где воспроизводится проблема, на адрес o.omelchenko@inweb.ua — интересно разобраться, в чем дело.