How to create a Google Sheets script that sends notifications when a form entry matches a certain column?

Other Questions Google Sheets
1

I’m working on a script for Google Sheets that should send out notifications whenever a form is submitted, specifically if there’s data in a certain column. For instance, if the form includes a response for column E, I want to receive an email with a link directing me to the spreadsheet. I noticed that when I set the trigger to ‘on edit’, the script operates correctly when column E is modified. However, I’m struggling to get it functioning with ‘on form submit’. Can someone assist me in modifying this script for proper functionality with form submissions? Thank you!

function notifyOnFormSubmission() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var responseSheet = spreadsheet.getSheetByName('Responses');
  var activeCell = spreadsheet.getActiveCell().getA1Notation();
  var activeRow = responseSheet.getActiveRange().getRow();
  var valueInCell = spreadsheet.getActiveCell().getValue().toString();
  var emailRecipient = '[email protected]';
  var emailSubject = 'New Form Submission Alert';
  var emailContent = 'A new form has been submitted.';

  if (activeCell.indexOf('E') !== -1) {
    MailApp.sendEmail(emailRecipient, emailSubject, emailContent);
  }
}
2

You gotta use the onFormSubmit trigger directly. In notifyOnFormSubmission, try changing the logic to directly check column E of the last submitted row instead of relying on activeCell. Use e.values to get the most recent row data and check if column E has a value. Hope this helps!