Як імпортувати і обробляти дані за допомогою Google Spreadsheets

Google Spreadsheets багатьма позиціонується як “молодший брат” Microsoft Excel. Формально воно, звичайно, так і є: набір функцій і детальність інструментів Excel дозволяє проводити операції з даними з хірургічною точністю. У той же час Google Spreadsheet, як і будь-який інший продукт Google Docs, випереджає Excel в плані зручності спільної роботи.

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

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

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

ImportRange — імпорт даних з іншої таблиці Google

За допомогою цієї формули можна уникнути дублювання даних та імпортувати їх з наявниої таблиці Google.

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

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 – опишіть її в коментарях і ми допоможемо з нею впоратися.

Часті питання про імпорт даних з сайту в Google таблиці