Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как из 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
И теперь идем на вкладку Данные -Создать запрос -Из других источников -Из интернета. В окно вставляем эту ссылку
Из интернета
Появляется окно предпросмотра, в котором выбираем нужный лист/листы и загружаем
Запрос к файлу на Google
Все, дальнейшие действия ничем не отличаются от действий с локально загруженным файлом Excel. Просто производите нужные манипуляции с данными для получения желаемого результата(анализ, сводные, графики и т.п.).


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 6 комментариев
  1. Василь:

    Вылезает ошибка. Подробности: "Тег Relationship содержит неправильный атрибут., строка 2, позиция 86." Кто-то знает как ее исправить?

    • Василь, к сожалению, эта ошибка указывает на неверно сформированный файл. Бывает так, что сторонние разработчики и программы(в том числе Google) допускают огрехи в схеме XML при формировании файлов xlsx, что и вызывает подобные ошибки. По сути проверить в это просто: сохранить файл на диск -открыть при помощи Excel(возможно, появится сообщение о попытке восстановить ошибки) -сохранить -закрыть. После этого файл будет загружаться корректно. Знаю случаи, когда такая ошибка возникала при наличии сводной таблицы в Google Sheet. Это тоже можно проверить: удалить сводную таблицу и повторить попытку загрузки или переместить сводную таблицу в другую книгу, а данные из этой сводной вытянуть в нужный файл по ссылке на неё путем применения IMPORTRANGE.

  2. OlegV:

    Здравствуйте Дмитрий !
    Благодарю вас за такое простое решение для получения данных из gs.
    Успешно пользовался им все время, но вчера при обновлении запроса выскочила ошибка. Как с ней справиться ?DataSource.Error:

    Web.Contents не удалось получить содержимое из "https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://doc-14-1k-sheets.googleusercontent.com/export/70cmver1f290kjsnpar5ku2h9g/47k1biko72afv9nbdk3e1v6028/1650950700000/114708278579398321193/*/1g8CxKxHlazJ44NY_8mpcvNEBE_l2ZREptLVPAeUVCWU&followup=https://doc-14-1k-sheets.googleusercontent.com/export/70cmver1f290kjsnpar5ku2h9g/47k1biko72afv9nbdk3e1v6028/1650950700000/114708278579398321193/*/1g8CxKxHlazJ44NY_8mpcvNEBE_l2ZREptLVPAeUVCWU&ltmpl=sheets" (400): Bad Request
    Сведения:
    DataSourceKind=Web
    DataSourcePath=https://docs.google.com/spreadsheets/d/1g8CxKxHlazJ44NY_8mpcvNEBE_l2ZREptLVPAeUVCWU/export
    Url=https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://doc-14-1k-sheets.googleusercontent.com/export/70cmver1f290kjsnpar5ku2h9g/47k1biko72afv9nbdk3e1v6028/1650950700000/114708278579398321193/*/1g8CxKxHlazJ44NY_8mpcvNEBE_l2ZREptLVPAeUVCWU&followup=https://doc-14-1k-sheets.googleusercontent.com/export/70cmver1f290kjsnpar5ku2h9g/47k1biko72afv9nbdk3e1v6028/1650950700000/114708278579398321193/*/1g8CxKxHlazJ44NY_8mpcvNEBE_l2ZREptLVPAeUVCWU&ltmpl=sheets

    • OlegV, проблема либо на стороне Гугла, либо к файлу не предоставлен доступ(файл не расшарен для всех, у кого есть ссылка). Попробуйте переназначить ссылку на файл в Гугле и подключиться заново.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти