Импорт данных из Google Таблицы в Excel с обновлением через Power Query

Дано: хотим, чтобы данные с листа Google Таблицы попадали в Excel почти что "в режиме реального времени" — чтобы была возможность обновлять данные в Excel, ничего не скачивая и не вставляя руками.

У Таблицы должен быть открыт доступ по ссылке. К этой самой ссылке добавляем справа /export:

https://docs.google.com/spreadsheets/d/1S_d6CxMX8dLkh9TkN4v65kgXdkw4CVwNocWJI51J8kI/export

Далее устремляемся в Excel в Power Query.

Эта надстройка может быть установлена бесплатно в Excel 2010-2013 и является частью Excel 2016 и 2019. В последних версиях ее можно найти на ленте инструментов (Данные → Получить и преобразовать). Увы, в версиях для Mac эта надстройка отсутствует.

Нам нужен источник "Из Интернета"

После чего вводим ту самую ссылку с /export:

Далее выбираем нужный лист, смотрим, что данные отображаются и вообще все это похоже на правду и на то, что нам нужно:

Далее у нас есть два пути: Загрузить сразу и Преобразовать (а потом все равно загрузить).

Загрузить

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

Для примера — загрузим в виде "Таблицы":

Добавим в Google новых строк.

После этого достаточно кликнуть правой кнопкой мыши по Таблице в Excel --> "Обновить".

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

Преобразовать

Если на этапе импорта выбрать сначала "Преобразовать", то попадем в редактор Power Query.

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

Например, удалим столбец:

Отфильтруем по другому столбцу:

После того как все преобразования выполнены, идем в Файл —> "Закрыть и загрузить" —> "Закрыть и загрузить в..."

И далее по той же схеме - выбираем, в каком виде выгружать:

Можно выгрузить в сводную (при ее обновлении тоже будет обновляться подключение, то есть новые данные из Google Таблицы в нее попадут):

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