Google Apps Script: переносим расписание из таблицы в календарь

Для кого предназначена данная статья

• Для людей, кто активно пользуется сервисом Google Calendar
• Для людей, у которых есть файлы таблиц с расписаниями, и которые хотели бы работать с ними более продуктивно — всегда иметь ближайшие мероприятия перед глазами, получать уведомления с напоминаниями на почту или push-уведомления
• Для людей, которым хочется быстро познакомиться с Google Apps Script, понять, что это такое, и где его можно применить

Что понадобится

• Google-аккаунт
• Базовые знания JavaScript

1. Создаем таблицу в Google Sheets

Это можно сделать, перейдя на страницу своего Google Drive.

Рис. 1. Правый клик мышью по рабочему пространству открывает контекстное меню, где нужно выбрать "Google Таблицы" — "Создать пустую таблицу"

В созданную таблицу нужно скопировать имеющееся у Вас расписание, в моем случае это расписание занятий в университете. Важно, чтобы в таблицы были следующие поля:

• Название мероприятия
• Дата мероприятия
• Время начала
• Время конца или длительность (впрочем, это необязательно — в итоге вы сможете установить какое-нибудь общее дефолтное значение длительности мероприятия, например 1 час, или вовсе сделать мероприятие, длящимся целый день)

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

Рис. 2. Таблица с расписанием

2. Создаем скрипт

2.1. Переходим в редактор скриптов

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

Рис. 3. Скрипт создается, через меню "Инструменты", в котором нужно выбрать пункт "Редактор скриптов"

Скрипты разрабатываются на языке JavaScript. Только что созданный скрипт содержит одну пустую функцию myFunction. Писать код можно в ней, но я предпочту дать ей более осмысленное имя SetCalendar.

Рис. 4. Созданный скрипт, дадим функции осмысленное имя — SetCalendar

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

2.2. Разбираемся с отладкой

Часто для отладки скриптов на JavaScript мы выводим различные значения в консоль:

console.log("Hello, world!")

В Google Apps Script логировать значения следует немного иначе. Вместо объекта console следует использовать глобальный объект Logger:

function SetCalendar() {
  Logger.log("Hello, world!");
}

После запуска скрипта, все выведенные в лог значения можно посмотреть из меню "Вид", выбрав пункт "Журналы" или же по сочетанию клавиш Ctrl+Enter:

Рис. 5. Модальное окно с логами последнего запуска скрипта

2.3. Извлекаем информацию о мероприятиях из таблицы

Сразу небольшой кусок кода

function SetCalendar() {
  //Индексы первой строки и первого столбца в таблице с данными
  const rowStart = 1;
  const colStart = 1;

  //Количество строк и столбцов в расписании
  const colsCount = 5;
  const rowsCount = 67;  

  //Получаем объект страницы
  var sheet = SpreadsheetApp.getActiveSheet();

  //Извлекаем данные таблицы в указанных диапазонах
  var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount)
  var data = range.getDisplayValues();
}

Рассмотрим строку 11.

Глобальный объект SpreadsheetApp — глобальный объект, содержащий методы, представляющие собой интерфейс для взаимодействия скрипта с приложением Google Sheets.

Первое, что нужно сделать — получить объект, представляющий активную (открытую в данный момент) страницу документа. Для этого необходимо вызвать метод getActiveSheet.

Метод возвращает объект sheet.

Далее, нужно получить объект range — он представляет собой диапазон ячеек таблицы, из которых мы будем извлекать данные. Это делается путем вызова метода getRange. В качестве параметров передаются:

• Индекс первой строки диапазона
• Индекс первого столбца диапазона
• Количество извлекаемых строк
• Количество извлекаемых столбцов

Обратите внимание! Индексация строк и столбцов начинается с 1.

Строка 15: мы получаем из выбранного выше диапазона ячеек непосредственно данные путем вызова метода getDisplayValues.

Обратите внимание! Метод getDisplayValues() отдает данные в том виде, в каком он отображается в ячейках таблицы. Например, если ячейка содержит дату, то указанный метод вернет не какое-то внутреннее представление записанной в ячейку даты, а ее представление, которое видит пользователь в ячейке.

2.4. Извлекаем данные из ячеек

Прежде, чем работать с ячейками, удобно было бы дать номерам столбцов имена, дабы избежать ошибок при указании номеров столбцов с интересующими нас данными. Создадим для этого несколько констант:

//Константы-имена для индексов столбцов
  const dateCol = 0;
  const timeCol = 1;
  const typeCol = 2;
  const nameCol = 3;
  const teacherCol = 4;

Вы можете посмотреть какие столбцы стоят в таблице под данными номерами на рисунке 2.

Объект data представляет набор ячеек с данными, которые были получены. Теперь нужно получить данные из отдельных ячеек.

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];
    let classPeriod = row[timeCol];
    let classType = row[typeCol];
    let className = row[nameCol];
    let classTeacher = row[teacherCol];

    Logger.log("[DATE] " + classDate);
    Logger.log("[PERIOD] " + classPeriod);
    Logger.log("[TYPE] " + classType);
    Logger.log("[NAME] " + className);
    Logger.log("[TEACHER] " + classTeacher);
    Logger.log("============================================");
  }

При использовании цикла for с переменной i по объекту data, переменная i на каждой итерации получает значение — целое число, которое является индексом очередной извлеченной из таблицы строки.

Строка 3: обращение к объекту data по индексу i возвращает объект row — текущую строку таблицы.

Строки 5-9: мы обращаемся к строке по индексу — номеру столбца с интересующими нас данными. Например, row[dateCol] — мы из текущей строки получаем дату мероприятия из столбца с индексом dateCol.

В строках 5-9 мы получили из текущей строки данные о дате занятия, времени начала и окончания, типе занятия, названии дисциплины и имени преподавателя.

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

2.5. Работа с объединенными ячейками

В результате выполнения предыдущих шагов, мы получаем следующий скрипт

function SetCalendar() {
  //Индексы первой строки и первого столбца в таблице с данными
  const rowStart = 1;
  const colStart = 1;

  //Количество строк и столбцов в расписании
  const colsCount = 5;
  const rowsCount = 8;  

  //Получаем объект страницы
  var sheet = SpreadsheetApp.getActiveSheet();

  //Извлекаем данные таблицы в указанных диапазонах
  var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount)
  var data = range.getDisplayValues();

  //Константы-имена для индексов столбцов
  const dateCol = 0;
  const timeCol = 1;
  const typeCol = 2;
  const nameCol = 3;
  const teacherCol = 4;

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];
    let classPeriod = row[timeCol];
    let classType = row[typeCol];
    let className = row[nameCol];
    let classTeacher = row[teacherCol];

    Logger.log("[DATE] " + classDate);
    Logger.log("[PERIOD] " + classPeriod);
    Logger.log("[TYPE] " + classType);
    Logger.log("[NAME] " + className);
    Logger.log("[TEACHER] " + classTeacher);
    Logger.log("============================================");
  }
}

Выполним скрипт и посмотрим, что было выведено в лог:

Рис. 6. Не все даты были правильно извлечены из таблицы

Из лога видно, что из первой строки данные были извлечены, а в следующих строках, которые описывают мероприятия того же дня, поле с датой оказалось пустым. Почему так происходит?

Еще раз взглянем на таблицу:

Рис. 7. Даты вписаны в объединенные ячейки

При создании таблицы, дата вписывалась только в первую строчку расписания на конкретную дату. После чего, все ячейки из столбца, относящиеся к одной дате были объединены.

Когда мы двигаемся по строкам таблицы, мы получаем дату не из всех ячеек, которые были объединены в одну, а только из первой.

Немного изменим алгоритм, чтобы корректно извлекать дату из объединенных ячеек:

  let savedDate = "";

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];

        //...

    if (classDate.trim() == "")
    {
      classDate = savedDate;
    }
    else
    {
      savedDate = classDate;
    }

    Logger.log("[DATE] " + classDate);
    //...
    Logger.log("============================================");
  }

Мы вводим дополнительную переменную savedDate — в ней храним последнюю прочитанную дату.

При чтении даты из текущей строки. Если прочитанная дата — пустая строка, то берем дату из переменной savedDate, иначе обновляем значение переменной savedDate.

2.6. Парсим дату и время из строк

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

Как будет показано далее, чтобы создать событие в Google Calendar, нужно предварительно создать два объекта класса Date: дату-время начала события, дату-время окончания события.

В рассматриваемой таблице (см. рисунок 2), мы получаем дату занятия в формате dd.mm.yyyy, и временной промежуток, когда занятие длится, в формате hh:mm-hh.mm.

Из этих двух строк, нам нужно получить два объекта Date. Такая задача не касается Google Apps Script, с этим по идее должен справиться хоть сколько-нибудь опытный программист. Поэтому я не буду подробно останавливаться на алгоритме, а лишь приведу код двух JS-функций, которые парсят дату время и возвращают дату-время начала и дату-время окончания занятия.

Поехали:

function extractTime(timeStr, dateStr)
{
  let sepIdx = timeStr.indexOf(":");

  let hoursStr = timeStr.substring(0, sepIdx);
  let minsStr = timeStr.substring(sepIdx + 1);

  sepIdx = dateStr.indexOf(".");

  let dayStr = dateStr.substring(0, sepIdx);
  let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3);

  sepIdx = dateStr.indexOf(".", sepIdx + 1);

  let yearStr = dateStr.substring(sepIdx + 1);

  let t = new Date();
  t.setHours(parseInt(hoursStr), parseInt(minsStr));
  t.setYear(parseInt(yearStr));
  t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr));

  return t;
}

function extractPeriod(periodStr, dateStr)
{
  let sepIdx = periodStr.indexOf("-");

  let fromStr = periodStr.substring(0, sepIdx);
  let toStr = periodStr.substring(sepIdx + 1);

  fromStr = fromStr.trim();
  toStr = toStr.trim();

  return {
    from: extractTime(fromStr, dateStr),
    to: extractTime(toStr, dateStr)
  }
}

Чтобы получить дату-время начала и окончания занятия, нужно вызывать функцию extractPeriod и передать две строки — дата и временной период, в который длится занятие.

2.7. Создание события в Google Calendar

Сразу небольшой листинг, пояснения будут дальше

let classTimeInfo = extractPeriod(classPeriod, classDate);

let classStartTime = classTimeInfo.from;
let classEndTime = classTimeInfo.to;

let info = "Преподаватель: " + classTeacher + "\nТип занятия: " + classType;

var event = (CalendarApp.getCalendarsByName("Учеба"))[0].createEvent
(
  className,
  classStartTime,
  classEndTime,
  {
    description: info
  }
);

Utilities.sleep(50);

Строки 1-6: получаем дату-время начала и окончания занятия, а также склеиваем в одну строку всю дополнительную информацию, которая будет записана в описание мероприятия в календаре.

Далее, к интерфейсу сервиса Google Calendar мы обращаемся через методы глобального объекта CalendarApp.

Посредством метода getCalendarsByName мы получаем массив календарей с указанным именем.

Получить объект-календарь можно также посредством метода getDefaultCalendar, тогда событие будет создано в основном календаре. Однако, я советую зайти в веб-версию Google Calendar и создать новый календарь, в который вы будете экспортировать мероприятия. Во-первых, вы получаете возможность скрывать события этого календаря, выделять события отдельным цветом.

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

В данном случае я создал календарь "Учеба", и собираюсь создавать события в нем. Поэтому в коде вызывается метод getCalendarsByName и в качестве параметра передается срока "Учеба".

Метод возвращает массив календарей с указанным названием, поэтому нужно взять из этого массива один элемент, в нашем случае нулевой:

(CalendarApp.getCalendarsByName("Учеба"))[0]

У объекта-календаря необходимо вызывать метод createEvent. В качестве параметров нужно передать следующее:

• Название мероприятия (в данном случае название дисциплины)
• Объект Data — дата-время начала мероприятия
• Объект Data — дата-время окончания мероприятия
• Необязательный параметр — объект с дополнительными опциями (в данном случае я заполняю одно поле — description — это описание мероприятия)

Последняя строка в скрипте — задержка на 50 миллисекунд. Во время разработки скрипта и отладки я обнаружил, что иногда при выполнении кода выбрасывается исключение, суть которого в том, что Google Calendar не нравятся частые обращения к сервису, и в описании исключения рекомендуется вставить задержку между вызовами методов API календаря.

3. Итоговый скрипт

Ниже приведен полный скрипт, который получился

Обратите внимание! Данный скрипт заточен под структуру таблицы, которая приведена на рисунке 2. Если вы хотите использовать этот скрипт для себя как есть, убедитесь, что ваша таблица с расписанием имеет такую же структуру, или же измените скрипт под свою таблицу.

function extractTime(timeStr, dateStr)
{
  let sepIdx = timeStr.indexOf(":");

  let hoursStr = timeStr.substring(0, sepIdx);
  let minsStr = timeStr.substring(sepIdx + 1);

  sepIdx = dateStr.indexOf(".");

  let dayStr = dateStr.substring(0, sepIdx);
  let monthStr = dateStr.substring(sepIdx + 1, sepIdx + 3);

  sepIdx = dateStr.indexOf(".", sepIdx + 1);

  let yearStr = dateStr.substring(sepIdx + 1);

  let t = new Date();
  t.setHours(parseInt(hoursStr), parseInt(minsStr));
  t.setYear(parseInt(yearStr));
  t.setMonth(parseInt(monthStr) - 1, parseInt(dayStr));

  return t;
}

function extractPeriod(periodStr, dateStr)
{
  let sepIdx = periodStr.indexOf("-");

  let fromStr = periodStr.substring(0, sepIdx);
  let toStr = periodStr.substring(sepIdx + 1);

  fromStr = fromStr.trim();
  toStr = toStr.trim();

  return {
    from: extractTime(fromStr, dateStr),
    to: extractTime(toStr, dateStr)
  }
}

function SetCalendar() {
  //Индексы первой строки и первого столбца в таблице с данными
  const rowStart = 1;
  const colStart = 1;

  //Количество строк и столбцов в расписании
  const colsCount = 5;
  const rowsCount = 8;  

  //Получаем объект страницы
  var sheet = SpreadsheetApp.getActiveSheet();

  //Извлекаем данные таблицы в указанных диапазонах
  var range = sheet.getRange(rowStart, colStart, rowsCount, colsCount)
  var data = range.getDisplayValues();

  //Константы-имена для индексов столбцов
  const dateCol = 0;
  const timeCol = 1;
  const typeCol = 2;
  const nameCol = 3;
  const teacherCol = 4;

  let savedDate = "";

  for (var i in data)
  {
    let row = data[i];

    let classDate = row[dateCol];
    let classPeriod = row[timeCol];
    let classType = row[typeCol];
    let className = row[nameCol];
    let classTeacher = row[teacherCol];

    if (classDate.trim() == "")
    {
      classDate = savedDate;
    }
    else
    {
      savedDate = classDate;
    }

    let classTimeInfo = extractPeriod(classPeriod, classDate);

    let classStartTime = classTimeInfo.from;
    let classEndTime = classTimeInfo.to;

    let info = "Преподаватель: " + classTeacher + "\nТип занятия: " + classType;

    var event = (CalendarApp.getCalendarsByName("Учеба"))[0].createEvent
    (
      className,
      classStartTime,
      classEndTime,
      {
        description: info
      }
    );

    Utilities.sleep(50);
  }
}

4. Немножко скриншотов

Что можно улучшить

• Некоторые значения в коде я просто захардкодил — например, индексы начала таблицы, размеры таблицы, количество строк (количество мероприятий). Теоретически, можно покопаться в API Google Sheets для Google Apps Script и придумать, как выцеплять нужные координаты ячеек автоматически;
• Я никогда плотно не изучал JavaScript, могут быть различные стилистические и прочие недостатки в коде, возможно тот же парсинг даты-времени можно сделать красивее и элегантнее.

Открыт к конструктивной критике, буду рад, если укажете на недостатки, посоветуете что можно улучшить, а что изменить в статье.

Что читать дальше

• Apps Script Overview
• Apps Script: Calendar
• Apps Script: Spreadsheet