Теперь AdWords – это Google Реклама. Новое название отражает, что сервис включает в себя различные рекламные инструменты в поисковой и контекстно-медийной сетях, на YouTube и не только. Подробнее

Сейчас в сети: 284
Сейчас в сети: 284
Учитесь работе в Google Реклама вместе с нами!
star_border

Сделай Сам: Импорт таблиц Google в Excel с помощью PowerQuery

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

Несмотря на эти достоинства, таблицы имеют 2 существенных недостатка.

1. Таблицы работают в браузере. С ростом объемов данных сильно падает производительность (иными словами, все начинает висеть).

2. Какими бы отличными ни были таблицы Google, по сравнению с Excel их функционал, мягко говоря, ограничен, что мешает раскрыть потенциал вашего аналитического гения.

3. Если используется несколько таблиц, то навигация между таблицами будет затруднена.

 

Рад вас обрадовать - есть способ, который позволит одновременно сохранить все достоинства и избавиться от недостатков таблиц - настроить импорт данных из Таблиц Google в Excel.

 

Warning: описываемый способ импорта данных в Excel использует надстройку под названием Power Query. Она требует Excel версии Professional Plus. Читатели без Power Query или вообще без Excel могут продолжать знакомиться со статьей - для вас тоже найдутся полезные штуки Веселый

 

1. Устанавливаем Power Query

Первым делом необходимо скачать нашу надстройку. Скачать её можно с этой страницы:

http://goo.gl/9FUZ5s

 

Не забываем выбрать подходящий язык. Рекомендую английскую версию - будет проще ориентироваться в этой статье. 

 

Далее устанавливаем скаченный пакет. После успешной установки запускаем Excel. Должна появиться вкладка с названием Power Query

power.png

 

Получилось? Двигаемся дальше! Веселый

 

2. Подключаем таблицы к Excel через Power Query

 

Приступаем к непосредственной настройке. Мы будем импортировать данные из таблиц Google в формате .xlsx, поэтому во вкладке Power Query выбираем импорт из файла Excel (Power Query > From File > From Excel)

 powerquery1.png

 

Нам откроется окно, в котором мы должны указать адрес нашего xlsx файла. Здесь начинается самое интересное.

Копируем ссылку на таблицы. Она будет похожа на эту:

https://docs.google.com/spreadsheets/d/многоБукв/edit

 

Важно: таблицы Google должны быть доступны для просмотра по ссылке.

 

В ссылке заменяем /edit на /export 

https://docs.google.com/spreadsheets/d/многоБукв/export

 

Переход по ссылке с /export в конце запускает скачивание таблицы Google в формате xlsx. Думаю, теперь вы догадались, что нужно сделать Веселый

Вставляем эту ссылку в поле для адреса файла. Жмем "открыть"

 

powerquery2.png

 

На заметку: Таблицы Google можно выгружать и в других форматах. Например, ссылка

https://docs.google.com/spreadsheets/d/многоБукв/export?format=csv запустит скачивание таблицы в формате csv, который тоже можно импортировать с помощью Power Query (Power Query > From File > From Csv)

 

Ура! Полдела сделано! 

 

После обработки таблицы должно появиться боковое меню, которое будет выглядеть примерно так:

 

powerquery3.png

 

В принципе, уже на этом этапе можно все выгрузить в Excel, выбрав необходимые листы или весь файл и нажав Load, но мы с вами немного "причешем" наши данные, чтобы потом было комфортно с ними работать.

Поэтому жмем Edit 

 

3. Меняем настройки импорта

 

Нажатие на Edit перевед нас в интерфейс настройки нашего запроса. Здесь можем выбрать, что именно в полученной выгрузке следует импортировать (например, импортировать только определенные столбцы), каким образом следует обрабатывать данные перед импортом (например, можно убрать дубликаты), а также задать нужный формат данных для определенных столбцов (например для столбца с датой указываем формат Дата).

 

В моем примере, я выбрал весь файл целиком, поэтому мне по умолчанию отображается таблица с листами. Уже сейчас я могу удалить лишние строки (Home > Remove Rows) или столбцы (Home > Remove Columns), чтобы они мне не мешали в дальнейшем. 

 

Я также могу выбрать, что именно следует экспортировать из листов, кликнув, на значок возле столбца Datapowerquery4.png

 

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

 

Для этого я выделяю колонки (кликая по заголовкам таблицы с зажатым Ctrl), в которых содержатся заголовки (в моем примере это Keyword, AdGroup,...,Month, Year - у вас они могут иметь другие названия), и запускаю функцию Remove Dublitcates (Home > Remove Dublicates)

 

powerquery5.png

 

Также я хочу, чтобы заголовки таблицы имели нормальные названия. По умолчанию они могут быть названы по типу Data.Column1 или Column1. В Power Query есть функция Use First Row As Headers (Transform > Use First Row As Headers), которая заменяет заголовки на значения первой строки (при этом первая строка удаляется). Нам эта функция подходит идеально.

 

Также мне необходимо поменять тип данных для некоторых столбцов, чтобы они корректно отображались в Excel. Например, столбцам Date, Week, Month я хочу назначить тип данных Date (Transform > Data type: Date) 

 

powerquery6.png

Как правило, Power Query без особых проблем определяет тип данных, но иногда лучше перестраховаться Веселый

 

На этом моя подготовка данных заканчивается и я сохраняю настройки:

 

powerquery61.png

На заметку: Возможности Power Query на этом не заканчиваются. Например, вы можете подключить сразу несколько таблиц Google к тому одному Excel файлу. Ссылка на документацию к Power Query http://goo.gl/mX5L3b

 

4. Пользуемся!

 

После сохранения настроек Power Query начнет импорт данных в Excel (должна сгенерироваться таблица), и в боковом меню появится наш обработанный запрос (см. Workbook Queries).

 

Импортированный набор данных можно актуализировать, обновив запрос (Правая кнопка по запросу > Refresh). Эта функция крайне полезна, если данные в таблицах Google обновляются часто. 

powerquery7.png

 

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

 powerquery8.png

 

***

Итак, данный мануал научил вас одной нехитрой, но крайне полезной для работы специалиста по контексту (и не только) возможности Power Query. Также мы разобрали некоторые особенности Power Query, а также успели поработать с API таблиц Google (в процессе экспорта данных).

 

Всем спасибо, все молодцы! Веселый

 

P.S. Пишите в комментарии свои отзывы. Если тема вам интересна, можем продолжить её изучение.

Владимир Ковтун

Привет! Меня зовут Владимир Ковтун. Я сертифицированный специалист Google AdWords и не только :-) Фанат своего ремесла. Всегда рад помочь!

Комментарии
Petr Abroskin Выпускник Сообщества
марта 2015

Скоро стыдно будет статью на простую тему написать. Пацаны не поймут... :-)

Владимир Ковтун Ведущий участник
марта 2015 - последнее изменение марта 2015

Петя, дело не в сложности темы, а в её пользе для читателей. Мне, например, удобнее в excel работать, и этот метод импорта мне сэкономил кучу времени. Вот я и подумал, что кто-то также мучается, как и я в свое время Веселый

Petr Abroskin Выпускник Сообщества
марта 2015

Вова, да я понимаю, что ты о пользе. :-) Реально круто, что делишься такими хаками. Этого контента не хватает в сообществе.

Dmitry_Mukhach
марта 2015

Добрый день!

 

А лайфхака по конвертации CSV с одним набором столбцов на CSV с другим набором и другими наименованиями столбцов - нет? Веселый

 

--
"Sales Arriving Daily"

Дмитрий, добрый день!

 

Для каких целей необходима подобная задача? PowerQuery для таких задач использовать не следует - лучше написать скрипт, обрабатывающий ваши csv.

Николай Г
июля 2015

Здравствуйте. Помогите решить вопрос. Необходима функция в ексель аналогична функции Qery в гугл таблицах. Будет использована для выборки данных из списка. Например в одном столбце много раз повторяется одна и та же номенклатура а в столбце рядом, суммы данных номенклатур. Необходимо вывести в итог номенклатуру, а все суммы что бы были сбиты в одну.

Сможете что то подсказать ???

Спасибо.

Валентин М
мая 2017

по какой причине могут не грузиться данные? Все сделал по мануалу..

arcsocpar
Март
а если доступ только по приглашению, возможно подключиться?
M Z
‎18-07-2018 09:07 PM

Огромное спасибо!!!! СУПЕР!!! ПОБОЛЬШЕ ТАКИХ ЛАЙФХАКОВ И ИНТЕРЕСНЫХ СТАТЕЙ!!!!