Хранение данных в Google Sheets очень распространено, т.к. это удобный онлайн сервис работы с табличными данными.
С его помощью можно обеспечить работу со структурированными данными, выполнять необходимые вычисления. Многие проекты при разработке отчетности с использованием Power BI сталкиваются с необходимостью получения данных из Google Sheets, поскольку в таблицах хранят различные показатели по эффективности, справочники и др. информацию.
Проблема подключения к Google Sheets
Основной вопрос при получении данных из Google Sheets в Power BI — это безопасность. Можно опубликовать таблицу и сделать ее доступной как CSV документ (или иной вариант), но в этом случае данные могут стать общедоступными, что зачастую недопустимо. С ноября 2021 года в Power BI можно использовать встроенный коннектор для получения данных из Google Sheets, при этом для соединения потребуется пройти авторизацию, что защищает исходные данные.
Еще один вариант — это предварительный перенос данных в Big Query и получение их из хранилища. Давайте ниже разберем два варианта подключения к Google Sheets без использования Big Query.
Подключение к Google Sheets из Power BI по ссылке
Перейдите к настройкам вашей таблицы и выполните ее публикацию Файл -> Открыть доступ -> Опубликовать в интернете:
После этого укажите лист и формат, в котором необходимо получить данные:
Нажмите Опубликовать, подтвердите действие, и после этого появится ссылка, которая потребуется для подключения из Power BI. Скопируйте ее.
Имея ссылку, можно подключиться по ней к источнику данных из Power BI, как Web:
Вставляем в открывшемся окне ссылку и нажимаем Ок:
Если все сделано верно, данные из Google Sheets будут загружены, и теперь можно с ними работать в Power BI сразу или выполнить предобработку в Power Query:
Минус этого варианта в том, что это небезопасный способ работы, т.к. требуется предварительная публикация данных как CSV документа (или иного доступного). Если это не критично для вас, то его можно использовать.
Подключение к Google Sheets из Power BI через коннектор
В Power Bi перейдите к доступным источникам, в строке поиска введите sheets и выберите Google Sheets:
После этого нажмите Connect. Power BI попросит указать URL того документа в Google Sheets, к которому вы хотите подключиться, скопируйте его из адресной строки браузера и вставьте в соответствующее поле:
После этого нажмите Ок. В следующем окне Power BI попросит вас выполнить авторизацию для доступа к документу. Нажмите Sign in. Откроется вкладка в браузере, выберите аккаунт, который имеет доступ к документу и разрешите приложению подключиться к нему:
Если все прошло успешно, то в Power BI вы получите сообщение о том, что авторизация пройдена:
Подключиться к документу теперь можно, кликнув по Connect, после этого будет загружен документ и отображены доступные для работы листы:
Выбирайте необходимый, производите манипуляции с данными и переходите к визуализации данных. На этом подключение и извлечение данных завершено:
Обновление данных из Google Sheets в Power BI Service
Скорее всего ваш отчет будет опубликован в службе Power BI и появится необходимость его регулярного обновления. Для того, чтобы все прошло корректно, нужно проверить наличие необходимых подключений в облаке.
Перейдите в Power BI Service, выберите рабочую область, в которой опубликован отчет и перейдите к настройкам набора данных:
В выпадающем списке перейдите по Settings. В блоке Data source credentials (при условии, что ранее не была выполнена настройка подключений) отобразится информация, что необходимо обновить учетные данные для источников. Другими словами, необходимо для них пройти авторизацию. Разберем два варианта:
Подключение по ссылке — слева от источника надпись Web:
Нажмите Edit credentials, а затем Sign in в появившемся окне:
Подключение к сервису — слева от источника надпись Google Sheets:
Нажмите Edit credentials, укажите уровень Private, а затем Sign in в появившемся окне:
В браузере откроется окно, и вас попросят подтвердить то, что вы разрешаете доступ к Google Sheets. Если все прошло успешно, информационные сообщения пропадут, и вы сможете настроить автоматическое обновление отчета в Power BI Service:
Заключение
Мы разобрали несколько вариантов подключения к Google Sheets из Power BI. Какой из них использовать — принимайте решение, исходя из задачи.
Не безопасный: опубликовать по ссылке и подключиться к ней. Можно использовать для получения справочников или иной информации, которая не представляет ценности посторонним.
Безопасный: использовать встроенный коннектор и пройти авторизацию. Этот вариант подойдет для получения информации с ограниченным кругом пользователей.
Наиболее сложный: перенести данные из Google Sheets в Big Query. Можно использовать, когда Big Query используется как хранилище данных при разработке отчета.