Импорт Google Docs таблиц в PowerBI
Google-таблицы — один из самых часто используемых инструментов для хранения данных в современных компаниях. Давайте разберемся, как импортировать данные из гугл-таблиц в PowerBI:
Создаем ссылку на документ
В таблице Google выбираем Файл — Опубликовать в интернете. Откроется окно публикации документа, в котором необходимо выбрать область экспорта и формат выгрузки:
Выбираем "Весь документ" (Лист мы выберем на этапе импорта данных в PowerBI) и "Таблица Microsoft Excel (XLSX)".
После нажатия на кнопку "Опубликовать" в окне появится ссылка на вашу таблицу вида https://docs.google.com/spreadsheets/***/pub?output=xlsx. Эту ссылку мы и будем использовать при импорте в PowerBI.
Импортируем данные в PowerBI
- Заходим в Power Query Editor (нажимаем Edit Queries)
- Нажимаем New Source
- Выбираем коннектор Web
- Вставляем ссылку на документ
- Выбираем лист, который необходимо импортировать
Данные после импорта будут автоматически распознаны правильным образом только в том случае, если исходная гугл-таблица имеет стандартную структуру: заголовки находятся в первой строке и указан формат данных для ячеек. В противном случае необходимо будет произвести некоторые манипуляции с данными: удаление лишних строк, определение типов данных в колонках и т.д.
Обновление данных
После внесения новых данных в исходную таблицу, данные в PowerBI будут подгружаться после обновления датасета (кнопка Refresh в PowerBI Desktop).
Обратите внимание на тип данных, которые определились в PowerBI. Внесенные после импорта данные в гугл-таблицу должны соответствовать типу данных для этой колонки в PowerBI. Например, если вы внесете текстовые данные в колонку, у которой в PowerBI указан числовой тип, то таблица при обновлении будет выдавать ошибку.
Как из Power Query или Power BI получить данные из Google Spreadsheets(Гугл таблицы)
Т.к. он-лайн редактирование Excel файлов через облачный сервис Microsoft был запущен позже, то исторически сложилось так, что довольно обширная часть пользователей Microsoft Office все равно для он-лайн доступа к книгам использует именно Google Spreadsheets (Google таблицы), а не решение от Microsoft(Excel Online).
Хоть сам я гугл-таблицы почти не использую, но понадобилось мне получить данные из этого замечательного сервиса при помощи надстройки Power Query. Однако шаблона для получения данных из Google-таблиц там нет(в Power BI есть только Google Analitics, что не совсем то. Точнее – совсем не то). Поэтому как и напрашивалось я использовал получение данных из Интернета. Но не все так просто – публичная ссылка, предоставляемая Google-ом для совместного просмотра при обработке запросами выдавала совсем не то, что хотелось бы. Вот что я получил после вставки ссылки на Google-таблицу: 
Т.е. мне предлагалось обработать структуру веб-страницы, а не структуру файла Excel. Можно было бы дальше танцевать с бубном именно с таким форматом и пытаться как-то из этого получить данные, но есть способы проще. Возможно для кого-то этот способ очевиден, но т.к. я обычно с Google-таблицами не работаю, то пришлось 20-30 минут поэкспериментировать, прежде чем дойти до простого решения.
А теперь по порядку о том, как правильно это сделать , притом с самого начала.
Есть у нас загруженный в Google файл Excel. Теперь необходимо предоставить ему доступ для других пользователей и получать актуальную информацию из этого файла через Power Query или Power BI при каждом обновлении запроса. Уточню – редактируется файл в службе Google, он никуда не скачивается, не пересылается – все пользователи вносят изменения он-лайн прямо в Google Spreadsheets.
далее в статье я буду писать Power Query, но для Power BI действия такие же
Первый способ получения корректной ссылки для обработки в Power Query заключается в том, что необходимо опубликовать файл через меню Google-а: Файл —Опубликовать в интернете 
Выбрать в появившемся окне Весь документ и Таблица Microsoft Excel(XLSX) 
Нажать Опубликовать. В появившемся окне скопировать ссылку. 
Именно эту ссылку используем далее: вкладка Данные —Создать запрос —Из других источников —Из интернета. В окно вставляем эту ссылку. Далее появляется окно предпросмотра, в котором выбираем нужный лист/листы и загружаем. Все как для обычных файлов Excel(пример обработки файла Excel можно посмотреть в этой статье: Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query).
Так же эту ссылку можно разослать всем, кто должен иметь доступ к просмотру файла. Ключевое слово здесь – просмотру. Т.е. таким методом мы можем без всяких дополнительных действий получить данные из файла через запрос, но изменять хоть что-то в файле никто кроме нас не сможет — только видеть наши изменения. Поэтому данный вариант подходит далеко не всегда.
Второй вариант –через Совместный доступ. Это более распространенный и наиболее популярный вариант, при котором все, кто перешел по ссылке могут вносить изменения в файл. На странице файла в Google жмем Файл —Совместный доступ 
Появится окно: 
В нем надо будет нажать на пункт » Включить доступ по ссылке «. В окне ниже появится ссылка, которую надо будет скопировать и нажать Готово. 
При необходимости перед тем как нажать на пункт Готово можно нажать на » Расширенные » и настроить доступ более гибко. Так же, если необходимо будет отключить совместный доступ по ссылке надо перейти в Файл -Совместный доступ, в окне выбрать Расширенные, далее в разделе «Уровни доступа» найти пункт Изменить, выбрать ВЫКЛ —Изменить —Готово
Созданная ссылка будет примерно такого вида.
https://docs.google.com/spreadsheets/d/1wUtUgK4ha6kYFM4ZpA0jMnM33rbvklZ7iqnxthp-t00/edit?usp=sharing
Если использовать напрямую именно такую строку, то нас ждет небольшое разочарование – на выходе получим структуру веб-страницы, а не структуру файла Excel(об этом я писал в самом начале статьи и там же можно посмотреть по скринам как это выглядит). Потому что такая форма ссылки генерирует именно веб-документ со своей разметкой, которая не распознается как файл Excel. Однако это очень легко исправить. Удаляем из ссылки последние аргументы: edit?usp=sharing . И вместо них ставим /export . Получиться должна ссылка примерно такого вида:
https://docs.google.com/spreadsheets/d/1wUtUgK4ha6kYFM4ZpA0jMnM33rbvklZ7iqnxthp-t00/export
И теперь идем на вкладку Данные —Создать запрос —Из других источников —Из интернета. В окно вставляем эту ссылку 
Появляется окно предпросмотра, в котором выбираем нужный лист/листы и загружаем 
Все, дальнейшие действия ничем не отличаются от действий с локально загруженным файлом Excel. Просто производите нужные манипуляции с данными для получения желаемого результата(анализ, сводные, графики и т.п.).
Подключаем Power BI к Google Sheets с помощью myBI Connect
Все началось с того, что осенью прошлого года я создал достаточно простой опрос о том, какие инструменты для анализа и отчетности используют причастные к контекстной рекламе. К нему сделал простую визуализацию в Power BI и опубликовал пост с результатами опроса. Сам опрос находится по ссылке и я бы очень хотел, чтобы вы прошли его, чтобы мы смогли построить еще одну визуализацию о том, как меняется ситуация со временем. Но речь в этом посте не об этом.
Отчет я построил на скорую руку и даже не настроил автоматическое обновление (каюсь;) На тот момент у нас еще не было коннектора к Google Sheets в нашем сервисе myBI Connect. Но сейчас сервис такую возможность позволяет — пользователи могут подключать гугл таблицы к хранилищу и сервис будет обновлять их по расписанию. Это достаточно удобно, если таблицей пользуется несколько человек и ее необходимо использовать в качестве справочника или таблицы фактов в основной отчетности. Я решил перевести свой старый отчет на новый источник данных и записал простое видео, в котором подробно разбираю процесс подключения к данным.
В завершении хотелось бы отметить, что выгрузка данных из Google Sheets, конечно, не является конечной целью пользователя, как правило. Но может быть удобным инструментом при использовании других источников в myBI Connect. К примеру, вы можете выгрузить данные из Директа (как это сделать мы рассказывали в прошлом посте), а после этого добавить к ним справочник расшифровки названий кампаний, который находится у вас в Google Sheets в общем пользовании маркетологов.
Кстати, если вы хотите показать себя в создании подобных простых визуализаций — напишите нам мы предоставим вам доступ к данным, а результаты разместим в блоге
Подключение к Google Sheets из Power BI
Открываем нужный нам документ Google Sheets. В разделе меню “Файл“ находим пункт “Опубликовать в Интернете”:
Откроется вот такое окошко:
Отмечаем ту вкладку (либо весь документ), которую хотим опубликовать. Отмечаем формат — Таблица Microsoft Excel (XLSX). Нажимаем “Опубликовать”:
Происходит публикация документа. И в следующем окне появится ссылка, по которой будут доступны опубликованные таблицы:
Копируем эту ссылку. И переходим ко второму шагу.
Шаг 2. Настраиваем подключение в Power BI
Подключаться к Google Sheets будем как к Интернет-ресурсу: ”Get data” → “Web”:
Получаем окошко для ввода информации, необходимой для подключения. Вводим в поле URL-адрес скопированную на первом шаге ссылку на опубликованные Google-таблички:
После нажатия “ОК” получаем окно Навигатора. В нём — список доступных по ссылке таблиц и предпросмотр выделенной таблицы:
После выбора таблицы кнопка “Преобразовать данные” (“Transform Data‘) отправит нас в Power Query, где мы сможем настроить дополнительную обработку данных перед загрузкой. Простая загрузка произойдёт после нажатия на кнопку “Загрузить” (“Load”):
Получаем табличку из Google Sheets в Power BI.