Система учета заказов на Google Calendar и Excel


Многие бизнес-процессы (и даже целые бизнесы) в этой жизни предполагают выполнение заказов ограниченным количеством исполнителей к заданным срокам. Планирование в таких случаях происходит, что называется, "от календаря" и часто возникает потребность переноса запланированных в нём событий (заказов, встреч, поставок) в Microsoft Excel - для дальнейшего анализа формулами, сводными таблицами, построения диаграмм и т.п.

Само-собой, хотелось бы реализовать такой перенос не тупым копированием (что как раз несложно), а с автоматическим обновлением данных, чтобы в будущем всё внесенные в календарь изменения и новые заказы "на лету" отображались бы в Excel. Реализовать такой импорт можно за считанные минуты с помощью надстройки Power Query, встроенной в Microsoft Excel, начиная с 2016-й версии (для Excel 2010-2013 её можно скачать с сайта Microsoft и установить отдельно по ссылке).

Предположим, что мы используем для планирования бесплатный Google Calendar, в котором я, для удобства, создал отдельный календарь (кнопка со знаком "плюс" в правом нижнем углу рядом с Другие календари) с названием Работа. Сюда мы заносим все заказы, которые нужно выполнить и доставить клиентам по их адресам:

Календарь заказов

Открыв двойным щелчком любой заказ можно просмотреть или отредактировать его детали:

Подробности заказа в календаре

Обратите внимание, что:

  • В названии мероприятия указано имя менеджера, который выполняет этот заказ (Елена) и номер заказа (45).
  • Указан адрес доставки
  • В примечании вписаны (отдельными строками, но в любом порядке) параметры заказа: тип оплаты, сумма, имя клиента и т.д. в формате Параметр=Значение.
Для наглядности, заказы каждого менеджера выделяются своим цветом, хотя это и не обязательно.

Шаг 1. Получаем ссылку на Google Calendar

Сначала нам нужно получить веб-ссылку на наш календарь с заказами. Для этого щёлкаем по кнопке с тремя точками Параметры календаря Работа рядом с названием календаря и выбираем команду Настройки и общий доступ:

Параметры календаря

В открывшемся окне можно, при желании, сделать календарь общедоступным или открыть к нему доступ для отдельных пользователей. Нам же нужна ссылка для закрытого доступа к календарю в формате iCal:

Ссылка на доступ к календарю

Шаг 2. Загружаем данные из календаря в Power Query

Теперь открываем Excel и на вкладке Данные (если у вас Excel 2010-2013, то на вкладке Power Query) выбираем команду Из интернета (Data - From Internet). Затем вставляем скопированный путь к календарю и жмём ОК.

Распознавать формат iCal Power Query не умеет, но ему легко помочь. По сути, iCal - это обычный текстовый файл c двоеточием в качестве разделителя и внутри он выглядит примерно так:

Исходник файла iCal

Так что можно просто щелкнуть правой кнопкой мыши по значку загруженного файла и выбрать максимально близкий по смыслу формат CSV - и наши данные о всех заказах будут загружены в редактор запросов Power Query и разделены на два столбца по двоеточию:

Загруженные данные iCal

Если присмотреться, то хорошо видно, что:

  • Информация о каждом мероприятии (заказе) группируется в блок, начинающийся со слова BEGIN и заканчивающийся END.
  • Дата-время начала и окончания хранится в строках с метками DTSTART и DTEND.
  • Адрес доставки - это LOCATION.
  • Примечание к заказу - поле DESCRIPTION.
  • Название мероприятия (имя менеджера и номер заказа) - поле SUMMARY.
Осталось извлечь эту полезную информацию и преобразовать её в удобную таблицу. 

Шаг 3. Преобразуем в нормальный вид

Для этого выполним следующую цепочку действий:

  1. Удалим ненужные нам верхние 7 строк до первого BEGIN командой Главная - Удалить строки - Удаление верхних строк (Home - Remove rows - Remove top rows).
  2. Отфильтруем по столбцу Column1 строки, содержащие нужные нам поля: DTSTART, DTEND, DESCRIPTION, LOCATION и SUMMARY.
  3. На вкладке Добавление столбца выберем Столбец индекса (Add column - Index column), чтобы добавить к нашим данным колонку с порядковым номером строки.
  4. Там же на вкладке Добавление столбца выберем команду Условный столбец (Add column - Conditional column) и в начале каждого блока (заказа) выведем значение индекса:

    Добавляем условный столбец

  5. Заполним пустые ячейки в получившемся столбце Блок, щёлкнув по его заголовку правой кнопкой мыши и выбрав команду Заполнить - Вниз (Fill - Down).
  6. Удалим ненужный больше столбец Индекс.
  7. Выделим столбец Column1 и выполним по нему свёртку данных из столбца Column2 с помощью команды Преобразование - Столбец сведения (Transform - Pivot column). В параметрах обязательно выбрать Не агрегировать (Don't aggregate), чтобы к данным не применялась никакая математическая функция:

    Выполняем свертку

  8. В получившейся двумерной (кросс) таблице зачистить обратные слэши в столбце адреса (правой кнопкой по заголовку столбца - Замена значений) и удалить ненужный более столбец Блок.
  9. Чтобы превратить содержимое столбцов DTSTART и DTEND в полноценную дату-время, выделив их, выберем на вкладке Преобразование - Дата - Выполнить анализ (Transform - Date - Parse). Затем подправим код в строке формул, заменив функцию Date.From на DateTime.From, чтобы не терять значения времени:

    Исправляем дату и время

  10. Затем, щёлкнув правой кнопкой мыши по заголовку, разделим столбец DESCRIPTION с параметрами заказов по разделителю - символу \n, но при этом в параметрах выберем деление на строки, а не на столбцы:

    Делим на строки

  11. Еще раз разделим получившийся столбец на два отдельных - параметр и значение, но уже по знаку "равно".
  12. Выделив колонку DESCRIPTION.1 выполним свёртку, как уже делали ранее, командой Преобразовать - Столбец сведения (Transform - Pivot column). Столбцом значений в этом случае будет колонка со значениями параметров - DESCRIPTION.2  В параметрах обязательно выбрать функцию Не агрегировать (Don't aggregate):

    Еще раз сворачиваем

  13. Останется задать форматы для всех столбцов и переименовать их желаемым образом. И можно выгружать результаты обратно в Excel командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...)

И вот наш список заказов, загруженный в Excel из Google-календаря:

Список сделок, загруженный в Excel
В будущем, при изменении-добавлении новых заказов в календарь, достаточно будет лишь обновить наш запрос командой Данные - Обновить всё (Data - Refresh All).

Ссылки по теме



17.07.2021 22:07:50
Николай, если у вас будет возможность, разработайте и опубликуйте пожалуйста приём, в котором описана обратная операция: перенос данных из таблицы Excel в Google Calender.
23.07.2021 10:33:36
Изучайте Google Calendar API и отправку POST запросов при помощи Power Query или VBA.
23.07.2021 17:00:09
Напишите пожалуйста, если у вас есть информация, реализован ли уже где-то перенос данных из таблицы Excel в Google Calender?
28.07.2021 12:20:52
Я же вам написал, да реализован. При помощи Google Calendar API можно формировать post-запросы которые будут заводить новые записи в календаре. Как это делать изучайте документацию по их API
28.07.2021 20:40:54
Если у вас есть такая информация, дайте пожалуйста ссылку на готовое решение.
18.07.2021 17:04:04
никак пример не скачать, открывается новая вкладка и всё, файлы из других примеров скачиваются
04.08.2021 10:29:33
Подскажите пожалуйста.

Преобразование фала календаря ICS в CSV происходит не корректно, т.к. длина строки в файле CSV ограничена 75 символами, далее происходит перенос на новую строку.

Скрин: https://ibb.co/jD5VBfd

Таким образом в Excel часть данных обрезается и происходит перенос на новую строку не возможно сделать корректный фильтр по Column1.

Скрин: https://ibb.co/hf4QM8S

Скрин: https://ibb.co/9c7JR0b

Как возможно решить данную проблему?

Файл календаря ICS прилагаю: https://yadi.sk/d/zoXmavsdPgBZhA
К
06.08.2021 08:49:08
Присоединяюсь к комментарию Анатолия, файл с примером недоступен.
24.09.2021 23:39:22
Мне бы тоже было бы интересно обратный прием с Эксель таблицей и почтой аутлук, с автоматической рассылкой. Например: есть количество N продукции, с ограниченным сроком годности, и за 5 дней до его истечения происходит рассылка через аутлук всем ответственным за реализацию продукта.... Но это видимо больше макрос для почтового сервиса(
Наверх