Как вставить календарь в гугл таблицу
Функции Google Таблиц для работы с датой и временем
В Google Таблицах есть несколько функций для работы с датой и временем – есть очень полезные, есть менее очевидные. Рассмотрим их.
На скриншоте во втором столбце результат действия формулы, а в третьем – текст самой формулы.
Далее я расскажу про каждую функцию отдельно. Отмечу, что в качестве даты можно использовать ссылку на ячейку с таковой или указывать дату напрямую в формуле в формате «01.02.2015».
Функция TODAY (СЕГОДНЯ)
возвращает сегодняшнюю дату. Аргументов у нее нет – сегодняшний день и есть сегодняшний. Результат вычисления функции обновляется вместе с остальными ячейками при любом изменении в документе.
Функция NOW (ТДАТА)
возвращает текущие время и дату. Аргументов тоже нет. Если ее не отформатировать – в ячейке будет отображаться и то, и другое:
А если в качестве формата использовать временной, в ячейке будет только текущее время:
Сверху неотформатированная ячейка, снизу – ячейка с временным форматом.
Функция NETWORKDAYS (ЧИСТРАБДНИ)
возвращает количество рабочих дней между двумя датами. Даты могут храниться в ячейках, как в этом примере:
Между первым октября 2015 года и 18 февраля 2016 года – 101 рабочий день.
Кроме того, даты можно задать и в самой формуле, хотя это и менее гибкий способ – ведь в ячейках их менять удобнее:
Можно задать в качестве второго аргумента функцию TODAY – и каждый день у вас будет отображаться актуальное количество рабочих дней, прошедших с определенной даты (аналогично можно задать TODAY и как первый аргумент – и отслеживать количество рабочих дней ДО определенной даты):
Последний аргумент этой функции – праздники, то есть даты, которые рабочими не считаются.
Удобнее всего их задать в диапазоне, а в функции сослаться на этот диапазон. Диапазон может выглядеть так:
Рабочих дней стало 95.
Функция NETWORKDAYS.INTL (ЧИСТРАБДНИ.INTL)
предыдущей возможностью задать нестандартную рабочую неделю. Это третий аргумент, и задается он в виде «0000011», где нули – это рабочие дни, а единицы – выходные. Допустим, для шведской четырехдневной недели формула будет выглядеть так:
И рабочих дней стало 77.
Функция WORKDAY (РАБДЕНЬ)
возвращает дату, которая наступит по прошествии определенного количества дней (второй аргумент) с определенной даты (первый аргумент):
В данном примере мы ссылаемся на ячейку B7, в которой стоит дата 01.10.2015. По прошествии 155 рабочих дней с этой даты наступит пятое мая.
Функции DAY (ДЕНЬ), MONTH (МЕСЯЦ), YEAR (ГОД)
возвращают соответствующий параметр из даты, которая является единственным аргументом этой функции:
В примере аргумент – сегодняшняя дата, заданная функцией TODAY.
Функция WEEKNUM (НОМНЕДЕЛИ)
возвращает номер недели. Первый аргумент – дата, а второй – необязательный – тип. По умолчанию тип равен 1, и это означает, что первый день недели – воскресенье, если задать аргумент тип равным 2, то первым днем недели будет считаться понедельник.
Функция DATEDIF (РАЗНДАТ)
вычисляет количество дней, месяцев и лет между двумя датами.
Первые два аргумента – стартовая и конечная даты. А третий – параметр, у него есть следующие варианты:
Y – полные годы. В примере находим разницу между 1 октября 2015 и 18 февраля 2016:
Видимо, что полного года еще не прошло – но можно вычислить десятичную долю года, воспользовавшись функцией YEARFRAC (ДОЛЯГОДА)
У DATEDIF есть еще три варианта последнего аргумента:
MD – количество дней после вычитания полных месяцев ( в примере 17 дней между 01.01.2013 и 18.02.2016);
YM – количество месяцев после вычитания полных лет ( в примере 1 месяц между 01.01.2013 и 18.02.2016);
YD – количество дней
после вычитания полных лет ( в примере 48 дней между 01.01.2013 и 18.02.2016).
Функция EOMONTH (КОНМЕСЯЦА)
возвращает последнюю дату месяца, следующего от заданной даты через определенное количество месяцев. Например:
Вернет 30.04.2016 в феврале (апрель = два месяца от февраля):
Если прибавить ко всей формуле единицу – то получим первый день следующего месяца, что тоже может пригодиться:
Функция WEEKDAY (НОМНЕДЕЛИ)
возвращает порядковый номер дня недели указанной даты (первый аргумент). Второй аргумент – тип, как у функции WEEKNUM. Для недели, начинающейся с понедельника, тип = 2:
Если же аргумент тип = 1, то четверг становится пятым днем недели, как видно на скриншоте:
Наконец, несколько функций для работы со временем. TIME (ВРЕМЯ)
преобразует заданные часы, минуты и секунды (это и есть ее аргументы) во время. Аргументы могут быть заданы, конечно, и внутри функции, и в виде ссылок на ячейки.
Ну а SECOND (СЕКУНДЫ), MINUTE (МИНУТЫ) и HOUR (ЧАС)
возвращают одно соответствующее значение из даты. На скриншоте выше видно, что заснял я его в 22 часа, 22 минуты и 27 секунд – ведь во всех трех функциях в качестве аргумента задано текущее время – NOW().
Как сделать календарь в Google Sheets
Если вы хотите использовать альтернативу платным календарным приложениям и службам, вы можете бесплатно создать их в Google Sheets. Вот как.
Случайный просмотр любого магазина мобильных приложений, и вы увидите огромное количество доступных приложений календаря. Многие из которых взимают плату просто за их использование или за доступ к довольно основным функциям. Оплата такой базовой услуги может показаться проблематичной, тем более что бесплатные календари легко доступны в Интернете.
Если вы хотите создать свой собственный календарь, вы можете сделать это с помощью Google Sheets. Создание собственного календаря дает вам гораздо больший контроль над тем, как ваш календарь выглядит и функционирует. Вы можете использовать его в Интернете или распечатать, чтобы использовать в качестве настольного календаря с ручкой и бумагой.
Если вы хотите создать календарь в Google Sheets, вот что вам нужно сделать.
Использование шаблона календаря Google Sheets
Самый простой способ создать быстрый и простой календарь в Google Sheets — это использовать один из готовых шаблонов, доступных пользователям.
Для этого отправляйтесь в Веб-сайт Google Drive— вам нужно войти, если вы еще этого не сделали. После того, как вы вошли в систему, нажмите новый Кнопка слева, чтобы создать новый документ.
Выбрать Google Sheets и парить над > символ.
Во всплывающем меню выберите Из шаблона.
Выберите один из шаблонов календаря — будет создан новый лист, содержащий выбранный вами дизайн календаря.
Создание календаря с нуля
Любой документ, который вы создаете из шаблона, можно редактировать в соответствии с вашими потребностями, но вам может потребоваться создать календарь с дизайном, который отличается от доступных шаблонов. Если это так, вам нужно создать свой календарь с нуля.
Добавление дней в пользовательский календарь Google Sheets
Для начала откройте пустой документ Google Sheets. Для начала вам нужно будет указать дни недели — выберите начальную ячейку для своего календаря и введите день, с которого вы хотите начать.
Выберите ячейку, которую вы только что набрали, затем нажмите и удерживайте небольшой квадрат в нижнем правом углу ячейки.
Перетащите курсор по горизонтали или вертикали, чтобы заполнить дополнительные ячейки днями недели. В этом примере мы будем располагать наши дни недели вертикально.
Вы можете продолжить перетаскивание, сколько хотите, если хотите создать целый месяц или даже год дат.
Добавление дат в пользовательский календарь Google Sheets
Далее нам нужно добавить дату. Убедитесь, что у вас есть ячейка для правильного дня недели, введите дату в формате [month]/[date],
Если вы используете другой формат даты, перейдите к Файл> Настройки электронной таблицы и измените локаль на свою. Это изменит различные параметры форматирования по умолчанию в соответствии с вашей локалью, включая форматы даты и местную валюту.
Затем нажмите на начальную ячейку даты, чтобы выбрать ее, затем нажмите Формат> Число> Дополнительные форматы> Дополнительные форматы даты и времени изменить способ отображения даты.
Выберите ячейку, которую вы только что отредактировали, нажмите и удерживайте небольшой квадрат в правом нижнем углу ячейки, затем перетащите вниз, чтобы заполнить календарь датами.
Теперь у вас есть базовая структура вашего календаря с указанием названных дней и дат. Вы можете расширить его, добавив столбцы, содержащие другие важные данные.
Если вы хотите, чтобы ваш календарь выглядел немного чище, вы также можете скрыть линии сетки в Google Sheets.
Создание календаря Google Sheets
Как мы показали выше, создание календаря Google Sheets является простым процессом. Он предлагает хорошую альтернативу использованию платных приложений или онлайн-сервисов, поскольку вы можете использовать Google Sheets в автономном режиме.
Создание календаря в Google Sheets также позволяет вам использовать другие функции Google Sheets. Например, вы можете настроить электронную таблицу для отправки электронного письма на основе значения ячейки. Если ваш календарь открыт для общего доступа, вы можете настроить оповещения, чтобы сообщать вам о завершении задачи, обновляя ячейки с ожидающих до завершения.
Вы также можете точно контролировать, какие разделы вашего календаря могут редактировать другие пользователи, узнав, как заблокировать ячейки в Google Sheets, чтобы вы могли поделиться им с другими пользователями.
Google Apps Script: переносим расписание из таблицы в календарь
Для кого предназначена данная статья
Что понадобится
1. Создаем таблицу в Google Sheets
Это можно сделать, перейдя на страницу своего Google Drive.
Рис. 1. Правый клик мышью по рабочему пространству открывает контекстное меню, где нужно выбрать «Google Таблицы» — «Создать пустую таблицу»
В созданную таблицу нужно скопировать имеющееся у Вас расписание, в моем случае это расписание занятий в университете. Важно, чтобы в таблицы были следующие поля:
Вся остальная информация, в моем случае — преподаватель, тип занятия (лекция, семинар и т.д.), может быть отправлена в описание мероприятия.
Рис. 2. Таблица с расписанием
2. Создаем скрипт
2.1. Переходим в редактор скриптов
Нужно создать скрипт, который будет связан с только что созданной таблицей, будет иметь доступ к данным в ней и переносить их в календарь.
Рис. 3. Скрипт создается, через меню «Инструменты», в котором нужно выбрать пункт «Редактор скриптов»
Скрипты разрабатываются на языке JavaScript. Только что созданный скрипт содержит одну пустую функцию myFunction. Писать код можно в ней, но я предпочту дать ей более осмысленное имя SetCalendar.
Рис. 4. Созданный скрипт, дадим функции осмысленное имя — SetCalendar
Над редактором кода имеется выпадающий список, который позволяет выбрать основную функцию, которая будет являться точкой входа в запущенный скрипт. В нашем случае следуем выбрать функцию SetCalendar.
2.2. Разбираемся с отладкой
Часто для отладки скриптов на JavaScript мы выводим различные значения в консоль:
В Google Apps Script логировать значения следует немного иначе. Вместо объекта console следует использовать глобальный объект Logger:
После запуска скрипта, все выведенные в лог значения можно посмотреть из меню «Вид», выбрав пункт «Журналы» или же по сочетанию клавиш Ctrl+Enter:
Рис. 5. Модальное окно с логами последнего запуска скрипта
2.3. Извлекаем информацию о мероприятиях из таблицы
Сразу небольшой кусок кода
Рассмотрим строку 11.
Глобальный объект SpreadsheetApp — глобальный объект, содержащий методы, представляющие собой интерфейс для взаимодействия скрипта с приложением Google Sheets.
Первое, что нужно сделать — получить объект, представляющий активную (открытую в данный момент) страницу документа. Для этого необходимо вызвать метод getActiveSheet.
Метод возвращает объект sheet.
Далее, нужно получить объект range — он представляет собой диапазон ячеек таблицы, из которых мы будем извлекать данные. Это делается путем вызова метода getRange. В качестве параметров передаются:
Обратите внимание! Индексация строк и столбцов начинается с 1.
Строка 15: мы получаем из выбранного выше диапазона ячеек непосредственно данные путем вызова метода getDisplayValues.
Обратите внимание! Метод getDisplayValues() отдает данные в том виде, в каком он отображается в ячейках таблицы. Например, если ячейка содержит дату, то указанный метод вернет не какое-то внутреннее представление записанной в ячейку даты, а ее представление, которое видит пользователь в ячейке.
2.4. Извлекаем данные из ячеек
Прежде, чем работать с ячейками, удобно было бы дать номерам столбцов имена, дабы избежать ошибок при указании номеров столбцов с интересующими нас данными. Создадим для этого несколько констант:
Вы можете посмотреть какие столбцы стоят в таблице под данными номерами на рисунке 2.
Объект data представляет набор ячеек с данными, которые были получены. Теперь нужно получить данные из отдельных ячеек.
При использовании цикла for с переменной i по объекту data, переменная i на каждой итерации получает значение — целое число, которое является индексом очередной извлеченной из таблицы строки.
Строка 3: обращение к объекту data по индексу i возвращает объект row — текущую строку таблицы.
Строки 5-9: мы обращаемся к строке по индексу — номеру столбца с интересующими нас данными. Например, row[dateCol] — мы из текущей строки получаем дату мероприятия из столбца с индексом dateCol.
В строках 5-9 мы получили из текущей строки данные о дате занятия, времени начала и окончания, типе занятия, названии дисциплины и имени преподавателя.
В конце мы выводим полученные данные в лог, чтобы убедиться, что извлекли данные из таблицы правильно.
2.5. Работа с объединенными ячейками
В результате выполнения предыдущих шагов, мы получаем следующий скрипт
Выполним скрипт и посмотрим, что было выведено в лог:
Рис. 6. Не все даты были правильно извлечены из таблицы
Из лога видно, что из первой строки данные были извлечены, а в следующих строках, которые описывают мероприятия того же дня, поле с датой оказалось пустым. Почему так происходит?
Еще раз взглянем на таблицу:
Рис. 7. Даты вписаны в объединенные ячейки
При создании таблицы, дата вписывалась только в первую строчку расписания на конкретную дату. После чего, все ячейки из столбца, относящиеся к одной дате были объединены.
Когда мы двигаемся по строкам таблицы, мы получаем дату не из всех ячеек, которые были объединены в одну, а только из первой.
Немного изменим алгоритм, чтобы корректно извлекать дату из объединенных ячеек:
Мы вводим дополнительную переменную savedDate — в ней храним последнюю прочитанную дату.
При чтении даты из текущей строки. Если прочитанная дата — пустая строка, то берем дату из переменной savedDate, иначе обновляем значение переменной savedDate.
2.6. Парсим дату и время из строк
Как было отмечено выше — если мы используем метод getDisplayValues(), то значения из таблицы мы получаем в виде строк и ровно так, как они отображаются в ячейках, никаких внутренних представлений данных мы не получаем.
Как будет показано далее, чтобы создать событие в Google Calendar, нужно предварительно создать два объекта класса Date: дату-время начала события, дату-время окончания события.
В рассматриваемой таблице (см. рисунок 2), мы получаем дату занятия в формате dd.mm.yyyy, и временной промежуток, когда занятие длится, в формате hh:mm-hh.mm.
Из этих двух строк, нам нужно получить два объекта Date. Такая задача не касается Google Apps Script, с этим по идее должен справиться хоть сколько-нибудь опытный программист. Поэтому я не буду подробно останавливаться на алгоритме, а лишь приведу код двух JS-функций, которые парсят дату время и возвращают дату-время начала и дату-время окончания занятия.
Чтобы получить дату-время начала и окончания занятия, нужно вызывать функцию extractPeriod и передать две строки — дата и временной период, в который длится занятие.
2.7. Создание события в Google Calendar
Сразу небольшой листинг, пояснения будут дальше
Строки 1-6: получаем дату-время начала и окончания занятия, а также склеиваем в одну строку всю дополнительную информацию, которая будет записана в описание мероприятия в календаре.
Далее, к интерфейсу сервиса Google Calendar мы обращаемся через методы глобального объекта CalendarApp.
Посредством метода getCalendarsByName мы получаем массив календарей с указанным именем.
Получить объект-календарь можно также посредством метода getDefaultCalendar, тогда событие будет создано в основном календаре. Однако, я советую зайти в веб-версию Google Calendar и создать новый календарь, в который вы будете экспортировать мероприятия. Во-первых, вы получаете возможность скрывать события этого календаря, выделять события отдельным цветом.
Но, что более важно, в случае, если вы при написании скрипта где-то ошиблись, и экспортировали события как-то неправильно, то вместо того, чтобы удалять все многочисленные неправильные события вручную, вы можете просто удалить календарь, после чего создать новый, пустой, и попробовать запустить новый, исправленный скрипт.
В данном случае я создал календарь «Учеба», и собираюсь создавать события в нем. Поэтому в коде вызывается метод getCalendarsByName и в качестве параметра передается срока «Учеба».
Метод возвращает массив календарей с указанным названием, поэтому нужно взять из этого массива один элемент, в нашем случае нулевой:
У объекта-календаря необходимо вызывать метод createEvent. В качестве параметров нужно передать следующее:
Последняя строка в скрипте — задержка на 50 миллисекунд. Во время разработки скрипта и отладки я обнаружил, что иногда при выполнении кода выбрасывается исключение, суть которого в том, что Google Calendar не нравятся частые обращения к сервису, и в описании исключения рекомендуется вставить задержку между вызовами методов API календаря.
3. Итоговый скрипт
Ниже приведен полный скрипт, который получился
Обратите внимание! Данный скрипт заточен под структуру таблицы, которая приведена на рисунке 2. Если вы хотите использовать этот скрипт для себя как есть, убедитесь, что ваша таблица с расписанием имеет такую же структуру, или же измените скрипт под свою таблицу.
4. Немножко скриншотов
Что можно улучшить
Открыт к конструктивной критике, буду рад, если укажете на недостатки, посоветуете что можно улучшить, а что изменить в статье.
Синхронизация Гугл Таблицы с Гугл Календарем
Анатолий Опарин / март, 2019
