Сегодняшний пост — своеобразное продолжение поста о том, как добавить триггер в гугл таблицу.

Иногда при написании скрипта возникает задача, когда необходимо добавить и настроить триггер с помощью скрипта. Например, в платной версии CRM Клиент для реализации напоминаний о определенных событиях (важная встреча или звонок клиенту, напоминание о дне рождения и пр.) создается специальный триггер, который в определенный день и время (данные брались из таблицы) при определенных условиях оправляет на почту напоминание. Конечно же, триггер создается и при необходимости отключается (удаляется) програмно.

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

Итак, приступим.

Создаем триггер, который будет срабатывать на событие «Открытие таблицы» — при открытии нашей таблицы запускать заданную функцию:

function createTrigger() {
//создание триггера - сработка на открытие таблицы
//ссылаться на таблицу можно либо по ID, либо используя иные способы
let funcName = 'afterOpenSheet'; //название функции, которую будет запускать наш триггер
//let id = SpreadsheetApp.getActiveSpreadsheet().getId(); //получаем идентификатор нашей таблицы
let spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); //в переменной ссылка на нашу таблицу (активную в данный момент)

ScriptApp.newTrigger(funcName)
.forSpreadsheet(spreadSheet) //здесь вместо переменной spreadSheet можно использовать идентификатор (id)
.onOpen() //событие открытие нашей таблицы
.create(); //команда создать триггер

По сути, после строчки «newTrigger» указывается источник срабатывания триггера (в примере выше — для таблицы), а в следующей строчке — когда (после чего) должен сработать наш триггер (в нашем примере — при открытии).
Это основной принцип программного создания триггеров.

Исходя из описанного выше, создадим триггер, срабатывающий на событие редактирование (если в вашей таблице что-то изменяется в одной из ячеек).

function createTrigger() {
//создаем триггер на изменение в таблице
  let funcName = 'onEditCheckBox'; //имя функции, которая запускается при срабатывании триггера
  let spreadSheet  = SpreadsheetApp.getActive();
  
  ScriptApp.newTrigger(funcName)
  .forSpreadsheet(spreadSheet)
  .onEdit()
  .create();
}

Как видно из кода, отличие от предыдущего — только событие «onEdit».

Также может возникнуть вопрос, зачем «городить огород», если можно воспользоваться событием «onEdit» прямо из кода (если создать функцию с именем «onEdit», то она будет запускаться каждый раз, когда будет возникать событие «редактирование»).

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

Теперь объяснение. Например, есть владелец таблицы (администратор), который дает своим коллегам доступ к таблице (редактирование), однако некоторые листы защищены (вносить правки может только владелец (администратор).

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

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

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

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

Идем дальше. Создадим триггер, срабатывающий в определенное время.

function createTriggerReminder() {
//создаем триггер-напоминалку
 let functionName = "ИмяФункции";
 let periodTime = 9;
  ScriptApp.newTrigger(functionName)
  .timeBased()
  .atHour(periodTime) //период срабатывания (время)
  .everyDays(1) //период срабатывания (каждый день)
  .create();
}

В данном примере мы создаем триггер, который будет запускать нашу функцию каждый день в промежуток с 9 до 10 утра. Исходя из моей практики, скрипт запускается где-то с 9:30 до 9:40.

Конечно-же, вы имя функции и время срабатывания можете передать в функцию, как параметры.

Если немного видоизменить данную функцию, можно создать триггер для перезапуска определенного скрипта.

function createTimerTrigger(functionName, timeWait) {
//создаем триггер, срабатывающий в определенное время (через timeWait после создания)
  let currTime = (new Date()).getTime(); //получаем текущее время (в миллисекундах)
  ScriptApp.newTrigger(functionName)
  .timeBased()
  .at(new Date(currTime+timeWait)) //через сколько должен сработать триггер
  .create();
}

Расшифруем данный код.

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

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

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

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

К примеру, у нас есть много данных, которые нам необходимо обработать. Мы можем определить опытным путем порцию данных, которые скрипт будет обрабатывать за один раз.

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

Скрипт, в свою очередь, за один раз обрабатывает заданную порцию данных.

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

Функция отрабатывает очередную порцию данных и создает новый триггер. И так до тех пор, пока не будут обработаны все данные.

Как удалять триггер, я напишу в конце статьи.

Еще один из триггеров, которые я использовал в своих проектах — триггер, срабатывающий при отправке формы. К примеру, у вас есть таблица и связанная форма. После того, как кто-либо, у кого есть ссылка на форму, заполняет поля формы и кликает на кнопку «Отправить», данные из формы попадают в Google таблицы и срабатывает наш триггер.

Соответственно, вам не нужно контролировать сам момент заполнения и отправки формы, т.к. теперь за вас это сделает ваш скрипт. Дальше уже все зависит от ваших задач. Вы можете обработать полученные из формы данные, отправить сообщение в телеграм или на почту и т.д. и т.п.

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

function createTrigger() {
//создаем триггер-отправка формы
  let spreadSheet = SpreadsheetApp.getActive(); //наша активная таблица
  let funcName = 'afterSendForm'; //имя функции, запускаемой при срабатывании триггера
  
  ScriptApp.newTrigger(funcName)
  .forSpreadsheet(spreadSheet)
  .onFormSubmit()
  .create();
}

Думаю, расшифровка кода излишняя)

Ну и напоследок приведу скрипт, который удалит ранее созданный триггер.

function deleteTrigger(functionName) {
//удаление триггера по имени функции, которую он запускает
  let triggers = ScriptApp.getProjectTriggers();
  for (let i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == functionName) ScriptApp.deleteTrigger(triggers[i]);
  }
}

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

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

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

Как вы знаете, реагировать на событие «редактирование таблицы» можно либо из кода (с помощью функции с зарезервированным название «onEdit»), либо с помощью триггера, срабатывающего на событие редактирования.

В первом случае на событие редактирования (например по клику на чекбоксе) запускается специальная функция («onEdit») и что-то делает.

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

Так вот, из «нововведений» раньше функция «onEdit» могла создавать триггер в вашем проекте, теперь — нет.

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

Специальная функция «onEdit» отработает, но триггер не создаст. Более того, функция запускается, что-то делает (что вы там прописали), доходит до строчки, где вы пытаетесь что-то сделать с триггером (создать или удалить) и ОСТАНАВЛИВАЕТСЯ!. Ошибки не ВОЗНИКАЕТ!

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

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

Долго искал причину, почему раньше работало, а теперь — нет, пока случайно не обнаружил.

Возможно, данная информация сэкономит вам и вашим клиентам время и нервы 🙂

На сегодня все.

Если есть вопросы, задавайте их в комментария.

 
Рекомендую почитать:

• «10 бирж фриланса и удаленной работы для новичков и профи: список и рекомендации по заработку «
• «Скачать футажи, видео, видеошаблоны, презентации, графику и т.д.: цифровые ресурсы без ограничений»
• «Бесплатная мультиссылка за 5 минут для Инстаграм, Тик Ток, Фейсбук и др.»