Как импортировать и обработать данные с помощью Google Spreadsheets

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

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

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

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

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

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

IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcdefghijklmn123", "Лист1!A1:C10")

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

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

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

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

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


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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• В меню "Строки" выбираем "ga:SourceMedium", а в меню "Столбцы" — "ga:day".


• В меню "Значения" выбираем интересующий показатель — пользователи, сессии либо количество выполненных целей.

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

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

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

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

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

Скрипт. Прочитаем, что написано на стикерах. OCR в Google Docs

Недавно нам потребовалось распознать текст, который был написан на нескольких сотнях стикеров. В Google Документах доступна функция OCR (optical character recognition), ей мы и воспользовались.

Мы нашли скрипт, немного модифицировали его и выкладываем в Таблице с примером.

Работает он так:

1. На листе «config» задаете название папки на вашем Google Диске с PNG / JPG (OCR работает с этими форматами файлов) и задаете лист, на который сохранится результат.




2. Скрипт проходится по всем изображениям в заданной папке, сохраняя их копию в Doc. Внутри Doc будет распознанный текст.




3. Дальше этот текст, ID и URL Документа скрипт вставит в Таблицу.

Чтобы скрипт заработал — активируйте Drive Api в редакторе скриптов (Ресурсы → Дополнительные функции Google)

Таблица со скриптом (файл → создать копию).
Папка со стикерами (сделайте копию, если захотите попробовать скрипт на нашей папке).

Про OCR почитать можно еще тут и тут.

 Заключение

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

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