Получение данных из Google Sheets с помощью Excel Power Query

Вопрос или проблема

let
    Source = Excel.Workbook(Web.Contents("http://spreadsheets.google.com/pub?key=0AkBd6lyS3EmpdFhPbDdCTTYxM1dGc21UdE9sSkp1WEE&output=xls"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", type number}, {"Column14", type number}, {"Column15", type number}, {"Column16", type number}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", type number}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column25", type number}, {"Column26", type number}, {"Column27", type number}, {"Column28", type number}, {"Column29", type number}, {"Column30", type number}, {"Column31", type number}, {"Column32", type number}, {"Column33", type number}, {"Column34", type number}, {"Column35", type number}, {"Column36", type number}, {"Column37", type number}, {"Column38", type number}, {"Column39", type number}, {"Column40", type number}, {"Column41", type number}, {"Column42", type number}, {"Column43", type number}, {"Column44", type number}, {"Column45", type number}, {"Column46", type number}, {"Column47", type number}, {"Column48", type number}, {"Column49", type number}, {"Column50", type number}, {"Column51", type number}, {"Column52", type number}})
in
    #"Changed Type"

Ответ или решение

Для интеграции данных из Google Sheets в Excel Power Query, даже при использовании версий Excel начиная с 2013, требуется специфический подход из-за особенностей взаимодействия с веб-данными.

Шаги получения данных из Google Sheets в Excel через Power Query:

1. Подготовка Google Sheets для импорта:

   • Убедитесь, что таблица Google Sheets общедоступна. Для этого откройте документ в Google Sheets, перейдите в «Файл» -> «Опубликовать в интернете» и скопируйте URL. Вам потребуется ссылка вида: https://docs.google.com/spreadsheets/d/e/2PACX-<код_документа>/pub?output=xlsx или output=csv.

2. Импорт через Power Query:

   • Откройте Excel и перейдите на вкладку «Данные».
   • Выберите «Получить данные» -> «Из других источников» -> «Из веба».
   • В диалоговом окне вставьте скопированную ранее ссылку.
   • Если вы используете ссылку с output=xlsx, Power Query автоматически интерпретирует это как Excel-совместимый файл и импортирует данные.

3. Настройка в Power Query Editor:

   • При появлении Navivator выберите нужный лист данных.
   • Щёлкните «Загрузить» или «Трансформировать данные» для их последующей обработки.
   • В Power Query Editor вы увидите сгенерированный код M языком, который можно редактировать для изменения типов данных, фильтрации и других преобразований.

4. Пример кода M для использования в Power Query:

let
    Source = Excel.Workbook(Web.Contents("https://список_документов_вашего_гугл_шита?output=xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data_Sheet,{{"Column1", type text}, {"Column2", type number}, ...}})
in
    #"Changed Type"

Рекомендации:

• Внимательность к деталям: Убедитесь, что ссылка открывается в отдельном браузере (это подтвердит, что используемая вами ссылка действительно работает).
• Своевременное обновление данных: Если данные в Google Sheets меняются, в Excel выберите «Обновить» для актуализации данных.
• Обработка данных: Используйте функции Power Query для преобразования, сортировки и фильтрации данных, чтобы наилучшим образом удовлетворить ваши бизнес-потребности.

Заключение

Следуя данным рекомендациям, вы сможете без проблем интегрировать данные из Google Sheets в Excel 2013 и выше с помощью Power Query, что значительно расширит ваши аналитические возможности и упростит работу с актуальными данными.