Как отправить электронное письмо-подтверждение при ответе на определенный элемент формы Google?

Question

У меня проблемы с Google Forms/Sheets - и, чтобы вы знали с самого начала, я не большой программист, и я ОЧЕНЬ новичок в работе с Apps Script.

Я пытаюсь сделать следующее:

1) Попросите пользователя ответить на один вопрос в форме. 2) Если на этот вопрос дан ответ, и значение записано в столбец ячейки на соответствующем листе, автоматически отправьте электронное письмо на 2 заранее заданных адреса электронной почты утверждающего. 3) Если на этот вопрос нет ответа, и в столбец ячейки не записывается никакое значение, а затем электронное письмо не отправляется двум утверждающим

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

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

Но опять же, он не будет отправлять уведомления, если форма динамически обновляет столбец 5 или столбец 7.

Пожалуйста, помогите, я застрял на этом в течение нескольких дней - Спасибо!!!

Вот мой нынешний сценарий:

/* 
 * This function sends an email when a specific Google Sheets column is edited
 * The spreadsheets triggers must be set to onEdit for this function to work
 */

function sendNotification() {
    //var ss = SpreadsheetApp.openById('1N6dqSXs8hdhCi1vrOGT7I7tD2yDMtgK7BSddMPqmuo0');  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    //Get Active cell
    var mycell = ss.getActiveSelection();
    var cellcol = mycell.getColumn();
    var cellrow = mycell.getRow();
    //Define Notification Details for vars

    var recipients = "[e-mail],[e-mail]";
    var recipients1 = "[e-mail]"

    var subject = "NEW SENSITIVE DATA REQUEST REQUIRES APPROVAL";
    var subject1 = "ALERT: SENSITIVE DATA REQUEST";
    var body = ss.getName() + " has been updated.  Visit " + ss.getUrl() + " to view the changes.";

    //Check to see which column will trigger the email - this script sends an email to "var recipients" if 
    //column 5 is updated and sends and email to "var recipients1" when colum 7 is updated.

    if (cellcol == 5) {
        //Send the Email to recipients defined in "var recipients"
        MailApp.sendEmail(recipients, subject, body);
    }

    if (cellcol == 7) {
        //Send the Email to recipients defined in "var recipients1"
        MailApp.sendEmail(recipients1, subject1, body);
    }

    //End sendNotification
}

Вот как устанавливаются мои триггеры на листе: sendNotification из электронной таблицы 1) при изменении, 2) при редактировании и 3) при FormSubmit

И последнее: моя компания не допускает надстройки, поэтому я должен сделать это с помощью скрипта приложений...

Answer 1

Установить отдельные триггеры для отдельных событий;

для события редактирования листа:

function sendNotificationAtEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//get edited range
var range = event.range;
var cellcol = range.getColumn();
var cellrow = range.getRow();

//Define Notification Details for vars
var recipients = "[e-mail],[e-mail]";
var recipients1 = "[e-mail]"

var subject = "NEW SENSITIVE DATA REQUEST REQUIRES APPROVAL";
var subject1 = "ALERT: SENSITIVE DATA REQUEST";
var body = ss.getName() + " has been updated.  Visit " + ss.getUrl() + " to view the changes.";

    if (cellcol == 5) {
        //Send the Email to recipients defined in "var recipients"
        MailApp.sendEmail(recipients, subject, body);
    }

    if (cellcol == 7) {
        //Send the Email to recipients defined in "var recipients1"
        MailApp.sendEmail(recipients1, subject1, body);
    }

    //End sendNotification
}

для формы отправки события:

function sendNotificationAtFormSubmit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

//Define Notification Details for vars
var recipients = "[e-mail],[e-mail]";
var recipients1 = "[e-mail]"

var subject = "NEW SENSITIVE DATA REQUEST REQUIRES APPROVAL";
var subject1 = "ALERT: SENSITIVE DATA REQUEST";
var body = ss.getName() + " has been updated.  Visit " + ss.getUrl() + " to view the changes.";
//if form submitted 5th question's answer is not empty
    if (event.values[4] !== '') {
        //Send the Email to recipients defined in "var recipients"
        MailApp.sendEmail(recipients, subject, body);
    }

    if (event.values[6] !== '') {
        //Send the Email to recipients defined in "var recipients1"
        MailApp.sendEmail(recipients1, subject1, body);
    }

    //End sendNotification
}

Answer 2

Самый безопасный способ сделать то, что вы хотите, это проверить ответ формы, а не ячейку в электронной таблице. По крайней мере, не для триггера отправки формы. Вам может понадобиться ОБА, и onEdit() триггер, и триггер On формы отправки.

Таким образом, в триггере On Form Submit необходимо получить текущий ответ формы, получить необходимый элемент, а затем получить индивидуальный ответ от элемента, который соответствует столбцу 5 в электронной таблице.

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

Чтобы получить последний ответ с помощью триггера при отправке, вам нужно добавить объект "событие" в функцию.

function sendNotification(e) {

Тогда вам нужно получить текущий ответ:

function sendNotification(e) {
  /* Logger.log('e: ' + e);

  for (var key in e) {
    Logger.log('key: ' + key)  
    Logger.log('value: ' + e[key]);
  };  */

  var allAnswers = e.response.getItemResponses();
  var howManyQuestionsInForm = allAnswers.length;
  Logger.log('howManyQuestionsInForm: test again: ' + howManyQuestionsInForm);
  /* for (var key in allAnswers) {
    Logger.log('key: ' + key)  
    Logger.log('value: ' + allAnswers[key]);
  }; */

  var questionThree = allAnswers[2];

  var theAnswerIs = questionThree.getResponse();
  Logger.log('theAnswerIs: ' + theAnswerIs);

  if (theAnswerIs !== "") {
    //Send email
    Logger.log('it got to here')
  };

};

Google Documentation - Form Event