Как импортировать и обработать данные с помощью 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.

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

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

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

    Чтобы охватить все возможности этого расширения, необходима отдельная статья. Наиболее полное руководство руководство по 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 почитать можно еще тут и тут.

     Заключение

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

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

    Часто задаваемые вопросы про импорт данных в гугл таблицы

    1. Как импортировать данные из интернета в гугл таблицу?

    1. Откройте файл в Таблицах.
    2. В пустой ячейке введите =IMPORTRANGE.
    3. Укажите в скобках следующие параметры через запятую, заключив их в кавычки: URL таблицы в Таблицах.
    4. Нажмите Ввод.
    5. Нажмите Открыть доступ, чтобы связать таблицы.

    2. Как импортировать данные из другой таблицы в таблицу Google?

    Перенести данные из одной Google Таблицы в другую можно простой операцией «Ctrl+с — Ctrl+v».

    3. Как перенести таблицу из Excel в гугл таблицы?

    На Диске дважды нажмите на файл Excel. Он откроется в окне предварительного просмотра. Вверху страницы нажмите Открыть в Google Таблицах. Сохранить как таблицу Google.

    4. Как скопировать всю таблицу в гугл таблицах?

    1. Откройте файл в приложении «Google Документы», «Google Таблицы» или «Google Презентации» на устройстве iOS.
    2. Только в Документах: нажмите на значок ручки.
    3. Выделите нужный фрагмент.
    4. Нажмите Копировать.