Google Apps Script:
дрессированные коды

Коллекция скриптов для Google Apps Script с подробными пояснениями:
готовые кейсы - бери и пользуйся!
12 мая 2020, автор: Елена Позднякова
Эта статья периодически пополняется новыми кейсами и скриптами.
Задать вопрос или оставить комментарий можно здесь:
Оглавление
для Гугл Таблицы, Гугл Документа, Гугл Слайдов, Гугл Формы
Скрипт для Гугл Таблицы. Функция отправляет письмо клиенту, заполнившему форму, на указанный в форме email
Продолжение предыдущего кейса
Пользовательское меню
для Гугл Таблицы, Гугл Документа, Гугл Формы
Как это работает:
С помощью Google Apps Script можно создать пользовательское меню для:
  • Гугл Таблицы,
  • Гугл Документа,
  • Гугл Формы,
  • Гугл Слайдов.
Доступно только из Контейнерного скрипта, который встроен в документ.
Меню Гугл Таблицы будет видеть только тот, кто имеет право редактирования (пользователи с доступом на просмотр меню не видят).
Меню Гугл Формы будет видеть тот, кто редактирует форму, а не заполняет её
.
1
Вот так выглядит простейшее пользовательское меню для Гугл Таблицы (на две функции):
Скрипт:
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('МОЁ МЕНЮ')
    .addItem('Запустить функцию 1', 'myFunction1')
    .addSeparator()
    .addItem( 'Запустить функцию 2', 'myFunction2')
    .addToUi();
}
2
А здесь пользовательское меню для Гугл Документа, которое включает подменю:
Скрипт:
function onOpen() {
  var ui = DocumentApp.getUi();
  // или SpreadsheetApp или FormApp.
  ui.createMenu('ПОЛЬЗОВАТЕЛЬСКОЕ МЕНЮ')
  .addItem('Пункт меню1', 'function1')
  .addItem('Пункт меню2', 'function2')
  .addSeparator()
  .addSubMenu(ui.createMenu('Подменю')
              .addItem('Пункт подменю 1', 'function3')
              .addItem('Пункт подменю 2', 'function4'))
  .addToUi();
}
Некоторые использованные функции и триггеры:
1
Функция onOpen является простым триггером. Выполняется при открытии документа.
Авторизация пользователя для запуска этой функции не требуется, поэтому возможности её ограничены лишь несколькими действиями.
В частности, для onOpen доступно создание пользовательского меню документа, таблицы, слайдов и форм в контейнерном скрипте.

Описание простых триггеров на сайте разработчика.
Описание пользовательских меню на сайте разработчика.
2
Функция addItem добавляет элемент меню.

addItem(caption, functionName)
caption - название элемента меню
finctionName - функция, которую запускает элемент меню


Описание на сайте разработчика.
~
Автоматическое письмо после заполнения формы
Как это работает:
1
Клиент заполняет Гугл Форму:
2
Ответы автоматически загружаются в Гугл Таблицу:
3
Клиенту автоматически отправляется письмо:
Скрипт:
Функция sendEmailToClient отправляет письмо клиенту, заполнившему форму, на указанный в форме email.
Контейнерный скрипт для Гугл Таблицы.
function sendEmailToClient() {
  
  //Текущая Гугл Таблица записана в переменную:
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //Лист текущей таблицы с именем "Ответы на форму (1)", в который загружаются ответы из формы, записан в переменную:
  var listAnswers = ss.getSheetByName("Ответы на форму (1)"); 
  
  //Получен email клиента из ячейки с адресом: последняя строка, 4 ряд, - и записан в переменную:
  var clientEmail = listAnswers.getRange(listAnswers.getLastRow(),4).getValue();
  
  //Тело письма, отформатированное в html-разметке, записано в переменную:
  var body = 
      'Здравствуйте! <br/> Благодарим за заявку на онлайн-курс <strong>Power BI!</strong><br/> ' +
        'В ближайшее время наш менеджер свяжется с вами по указанному телефону.<br/>' +
          'Подробности о системе бизнес-аналитики Power BI по ' +
            '<a href = "https://finver.ru/blog/epoha-power-bi">ссылке.</a><br/>' +
              '<br/><br/>' + 
                'С уважением, <br/> онлайн-школа Codelab';
  //В описании кода приведены дополнительные пояснения относительно html-разметки
  
  //Отправлено письмо клиенту (отправителем будет тот, кто запускает скрипт):
  //Для отправки используется функция sendEmail (по-правильному: метод класса MailApp)
  //Ссылка на подробную информацию о методе в описании к коду
  
MailApp.sendEmail(clientEmail, "Ваша заявка получена",'',{htmlBody: body});
  
}
Некоторые использованные функции и триггеры:
1
Триггер, который срабатывает при отправке формы, настраивается в разделе "Триггеры текущего проекта":
Как альтернативный вариант, можно прописать триггер с помощью кода:
var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
ScriptApp.newTrigger('myFunction')
    .forForm(form)
    .onFormSubmit()
    .create();
2
Функция getLastRow (получить порядковый номер последнего ряда в диапазоне), метод класса Range (range = диапазон).
Описание на сайте разработчика.
3
Функция sendEmail (отправить письмо), метод класса MailApp.
Описание на сайте разработчика.
4
Пояснения относительно html-разметки в письме:
Фрагмент кода:
//Тело письма, отформатированное в html-разметке, записано в переменную:​

var body = 

      'Здравствуйте! <br/> Благодарим за заявку на онлайн-курс <strong>Power BI!</strong><br/> ' +
        'В ближайшее время наш менеджер свяжется с вами по указанному телефону.<br/>' +
          'Подробности о системе бизнес-аналитики Power BI по ' +
            '<a href = "https://finver.ru/blog/epoha-power-bi">ссылке.</a><br/>' +
              '<br/><br/>' + 
                'С уважением, <br/> онлайн-школа Codelab';
Использование html-разметки в письме не обязательно, это всего лишь дополнительный атрибут.

Если в письме нет html разметки, то будет отправлен слепой текст:
  • без отступов,
  • без выделения жирным шрифтом,
  • без кликабельных ссылок.

Текст письма относится к типу данных текст, поэтому он должен быть заключен в кавычки: "двойные" или 'одинарные'.
Кавычки обязательны независимо от того, используется html или нет.

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

Поэтому перенос текстовых строк в коде выглядит так:
"текст текст текст " +
"текст текст"


Альтернативный равнозначный вариант кода выглядит так:
"текст текст текст текст текст"

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

Для того, чтобы добавить в письмо перенос строки, нужно в конце строки добавить тег <br/>. Это мы уже плавно перешли к тегам html-разметки.

Два бр подряд: <br/><br/> - это 2 отступа.

Чтобы текст получился жирным, его нужно заключить в парные теги <strong></strong>.
Вот так:
<strong>жирный текст</strong>

Чтобы добавить ссылку, её нужно поместить в парный тег <a></a>, и добавить к первому тегу <a> атрибут со ссылкой: href="здесь ссылка".

Вот так это выглядит:
<a href="https://megabyte.ga/"> текст ссылки </a>
Так выглядит письмо, созданное с помощью приведенного выше кода:
~
Три варианта письма
продолжение предыдущего кейса
Как это работает:
В зависимости от того, какой курс клиент выбирает в заявке:

  • Power BI
  • Google Apps Script
  • Javascript

отправлять ему три разных варианта письма.

Есть еще и четвертый вариант, если клиент выбрал больше одного курса или не выбрал ни один.
Скрипт:
/*
Контейнерный скрипт. При заполнении Гугл Формы ответы автоматически загружаются в Гугл Таблицу и запускается триггер.
Триггеры установлены в разделе "Триггеры текущего проекта".
*/



//Функция отправляет  письмо клиенту, заполнившему форму, на указанный в форме Email. В зависимости от результатов формы есть 4 варианта письма
function sendEmailToClient() {
  
  //Текущая Гугл Таблица записана в переменную:
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //Лист текущей таблицы с именем "Ответы на форму (1)", в который загружаются ответы из формы, записан в переменную:
  var listAnswers = ss.getSheetByName("Ответы на форму (1)"); 
  
  //Получен email клиента из ячейки с адресом: последняя строка, 4 ряд, - и записан в переменную:
  var clientEmail = listAnswers.getRange(listAnswers.getLastRow(),4).getValue();
  
  //Выбранный курс из таблицы с ответами записан в переменную
  var kurs = listAnswers.getRange(listAnswers.getLastRow(),2).getValue();
  
  //Созданы 4 разных тела письма:
  var bodyForPower = 
      'Здравствуйте! <br/> Благодарим за заявку на онлайн-курс <strong>Power BI!</strong><br/> ' +
        'В ближайшее время наш менеджер свяжется с вами по указанному телефону.<br/>' +
          'Подробности о системе бизнес-аналитики Power BI по ' +
            '<a href = "https://finver.ru/blog/epoha-power-bi">ссылке.</a><br/>' +
              '<br/><br/>' + 
                'С уважением, <br/> онлайн-школа Codelab';
  
  var bodyForJava = 
      'Здравствуйте! <br/> Благодарим за заявку на онлайн-курс <strong>Javascript!</strong><br/> ' +
        'В ближайшее время наш менеджер свяжется с вами по указанному телефону.<br/>' +
          '<br/><br/>' + 
            'С уважением, <br/> онлайн-школа Codelab';
  
  
  var bodyForGas = 
      'Здравствуйте! <br/> Благодарим за заявку на онлайн-курс <strong>Google Apps Script!</strong><br/> ' +
        'В ближайшее время наш менеджер свяжется с вами по указанному телефону.<br/>' +
          '<br/><br/>' + 
            'С уважением, <br/> онлайн-школа Codelab';
  
  var bodyForOther = 
      'Здравствуйте! <br/> Благодарим за заявку! <br/> ' +
        'В ближайшее время наш менеджер свяжется с вами по указанному телефону.<br/>' +
          '<br/><br/>' + 
            'С уважением, <br/> онлайн-школа Codelab';
  
  var body;
  
  if (kurs == 'Power BI')   {body = bodyForPower}
  
  else if (kurs == 'Javascript')   {body = bodyForJava}
  
  else if (kurs == 'Google Apps Script')   {body = bodyForGas} 
  
  else {body = bodyForOther};
  
  
  //Отправлено письмо клиенту (отправителем будет тот, кто запускает скрипт):
  //Для отправки используется функция sendEmail, метод класса MailApp

  MailApp.sendEmail(clientEmail, "Ваша заявка получена",'',{htmlBody: body});
  
}
~
Установить дату и время изменения
для Гугл Таблицы
Как это работает:
1
Комментарий к ячейке с датой и временем последнего изменения, установленный с помощью Google Apps Script:
Скрипт:
function onEdit(e) {
  
  var range = e.range;
  
   range.setNote('Последнее изменение: ' + new Date().toLocaleString('ru'))
  }
2
Такой же комментарий, но с условием: только для колонки №2
Скрипт:
function onEdit(e) {
  
  var range = e.range;
  
  if (range.getColumn() == '2')
    
  {range.setNote('Последнее изменение: ' + new Date().toLocaleString('ru'))
  }
}
3
Установить дату и время изменения в ячейку справа
Скрипт:
function onEdit(e) {
  
// Это контейнерный скрипт:
// e = SpreadsheetApp.getActiveRange()
  
  var range = e.range,
      row =   range.getRow(),
      column =   range.getColumn();
  
  
  range.offset(0, 1).setValue(new Date().toLocaleString('ru'))
  
}
Некоторые использованные функции и триггеры:
1
Функция onEdit является простым триггером. Выполняется при редактировании ячейки.

Описание триггера на сайте разработчика.
~
Вспомогательные функции
Проверить значение и тип переменной х
Посмотреть результат можно в редакторе скриптов: Вид - Журналы
Logger.log ('переменная: '+ х + ', тип: '+ typeof х)

// [20-05-17 09:19:35:584 MSK] переменная: Маша, тип: string

~