An answer in column B on Google Sheets generated from a Google form sends email to collaborators including all data in that row

Question

I'm a school teacher using Google Forms and would like to generate an email to myself, and if possible other collaborators, based on an text answer of "YES" in column B from the sheet generated by that form. I've been looking on here for a couple days and can't find exactly what I need. And I only know enough about this stuff to get myself in trouble. :)

There must be a simple script that basically sends me an alert email ONLY if someone types in an answer of "YES" for a particular text question (the answer shows up in column B on the form sheet responses, otherwise the response is left blank).

If it matters, I'm running a script for "formemailer" on the sheet as well to return copies of the responders answers back to them.

I do NOT want to have an email sent to me every time someone submits a response to the form - I already know how to set that up.

Answer 1

Here is how it could work. The script does the following:

1. Get the sheet with form responses
2. Get the last (nonempty) row there
3. Pick the entry from column B. To make matching more robust, it trims the entry (removing any whitespace left or right of text), and converts to lower case.
4. If the result is 'yes', an email is sent. The body is the content of the submitted form (I'm assuming here that it has at most 10 items; otherwise adjust the range).

function emailMe() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
  var height = sheet.getDataRange().getHeight();
  var lastEntry = sheet.getRange(height, 2).getValue().trim().toLowerCase();
  if (lastEntry == 'yes') {
    var subject = 'Someone said yes';
    var body = sheet.getRange(height, 1, 1, 10).getValues()[0].join('\n');
    GmailApp.sendEmail('[email protected]', subject, body);
  }
}

To cc: someone else, change the sending line to

GmailApp.sendEmail('[email protected]', subject, body, {cc: '[email protected],[email protected]'});