2

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.

1 Answer 1

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]'});
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.