Как импортировать и обработать данные с помощью Google Spreadsheets
Содержание статьи
Google Spreadsheets многими позиционируется как «младший брат» Microsoft Excel. Формально оно, конечно, так и есть: набор функций и детальность инструментов Excel позволяет проводить операции с данными с хирургической точностью. В то же время Google Spreadsheet, как и любой другой продукт Google Docs, опережает Excel в плане удобства совместной работы.
Облачная основа сервиса от Google делает его еще и отличным инструментом для сбора и постоянного обновления данных из сети: будь то данные веб-страницы или ячейки из другой таблицы Google.
его в таблицах Google, нужно добавить к нему единицу.
🧾 Импорт данных с сайта в Google таблицы: | Описание функций парсинга Google Sheets: |
💻 Парсинг в Гугл Таблицы IMPORTXML: | Импорт данных из источников в формате XML, HTML, CSV, TSV, а также RSS и ATOM XML. |
⏳ Парсинг Гугл Таблицы IMPORTRANGE: | Импортирует диапазон ячеек из одной электронной таблицы в другую. |
💻 Парсинг Google Таблицы IMPORTFEED: | Импортирует фид RSS или Atom. |
⏳ Парсинг Гугл Таблицы IMPORTDATA: | Импортирует данные в формате CSV (значения, разделенные запятыми) или TSV (значения, разделенные табуляцией) |
Импорт и парсинг google sheets данных
Как парсить данные с сайтов в таблицу google? Она имеет набор гибких инструментов для сбора внешних данных. Функции импорта таковы:
ImportRange — импорт данных из другой таблицы Google
С помощью этой формулы можно избежать дублирования данных и импортировать их из существующей таблицы Google.
1 | 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]. Число в квадратных скобках в конце — это порядковый номер нашей таблицы, начиная с нуля. Поэтому чтобы использовать
ImportXML google sheets — гугл таблицы. Импорт данных с сайта
Используется, если вам нужно вытянуть из веб-страницы любую информацию, которая выходит за рамки таблицы или списка. Например, заголовки 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), ей мы и воспользовались.
Мы нашли скрипт, немного модифицировали его и выкладываем в Таблице с примером.
Работает он так:
- На листе «config» задаете название папки на вашем Google Диске с PNG / JPG (OCR работает с этими форматами файлов) и задаете лист, на который сохранится результат.
- Скрипт проходится по всем изображениям в заданной папке, сохраняя их копию в Doc. Внутри Doc будет распознанный текст.
- Дальше этот текст, ID и URL Документа скрипт вставит в Таблицу.
Чтобы скрипт заработал — активируйте Drive Api в редакторе скриптов (Ресурсы → Дополнительные функции Google)
Таблица со скриптом (файл → создать копию).
Папка со стикерами (сделайте копию, если захотите попробовать скрипт на нашей папке).Про OCR почитать можно еще тут и тут.
Заключение
Функция Importrange Google Spreadsheets — удобный инструмент для работы с данными из сети. Кроме того, он содержит достаточно возможностей по агрегированию и фильтрации данных, что помогает решать достаточно нетривиальные задачи по обработке данных.
Если у вас возникли вопросы относительно того, как решить ту или иную задачу в Google Spreadsheets — опишите ее в комментариях и мы поможем с ней справиться.
Часто задаваемые вопросы про импорт данных в гугл таблицы
1. Как импортировать данные из интернета в гугл таблицу?
- Откройте файл в Таблицах.
- В пустой ячейке введите =IMPORTRANGE.
- Укажите в скобках следующие параметры через запятую, заключив их в кавычки: URL таблицы в Таблицах.
- Нажмите Ввод.
- Нажмите Открыть доступ, чтобы связать таблицы.
2. Как импортировать данные из другой таблицы в таблицу Google?
Перенести данные из одной Google Таблицы в другую можно простой операцией «Ctrl+с — Ctrl+v».
3. Как перенести таблицу из Excel в гугл таблицы?
На Диске дважды нажмите на файл Excel. Он откроется в окне предварительного просмотра. Вверху страницы нажмите Открыть в Google Таблицах. Сохранить как таблицу Google.
4. Как скопировать всю таблицу в гугл таблицах?
- Откройте файл в приложении «Google Документы», «Google Таблицы» или «Google Презентации» на устройстве iOS.
- Только в Документах: нажмите на значок ручки.
- Выделите нужный фрагмент.
- Нажмите Копировать.