Notify me when specific column data is entered in Google Sheets form submissions

Other Questions Google Sheets
1

Hey folks! I need some help with a Google Sheets script. I want to get an email when someone submits a form and fills out a particular question.

Here’s what I’m trying to do:

  1. Watch for new form submissions
  2. Check if a specific column (let’s say column E) has data
  3. If it does, send me an email with a link to the sheet

I’ve got a script that works when I edit the sheet directly, but it’s not catching form submissions. Here’s what I’ve got so far:

function alertMe() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Responses');
  var activeCell = sheet.getActiveCell();
  var column = activeCell.getColumn();
  var value = activeCell.getValue();
  var emailTo = '[email protected]';

  if (column === 5 && value) {
    var message = 'New response in column E!';
    var url = SpreadsheetApp.getActive().getUrl();
    MailApp.sendEmail(emailTo, 'Form Alert', message + '\n' + url);
  }
}

Any ideas on how to make this work with form submissions instead of edits? Thanks a bunch!

2

yo, try the onFormSubmit trigger. it fires on new form submissions. if (e.values[4]) is truthy, your script sends an email. be sure to set the trigger in the menu. hope it sorts ur issue!

3

I’ve dealt with a similar issue before, and I can offer some insights. The script you’re using is triggered by edits to the sheet, which doesn’t catch form submissions automatically. To make this work, you’ll need to use a form submit trigger instead.

Consider using the onFormSubmit trigger instead of relying on edit events. In the script you’ll receive an event object (e) containing an array of values from the form submission. You then check the appropriate value corresponding to column E (remembering that arrays are zero-indexed, so column E is at index 4). If that value is present, send the email with the link to your sheet.

For example:

function onFormSubmit(e) {
var response = e.values;
var columnEValue = response[4];

if (columnEValue) {
var emailTo = ‘[email protected]’;
var message = 'New response in column E: ’ + columnEValue;
var url = SpreadsheetApp.getActive().getUrl();
MailApp.sendEmail(emailTo, ‘Form Alert’, message + ‘\n’ + url);
}
}

After setting up a form submit trigger for the function, any new submission that includes data in column E will prompt an email notification. Hope this helps!

4

The key to solving your issue lies in using the onFormSubmit trigger instead of an edit-based function. This trigger activates whenever a form submission occurs, allowing you to process the data immediately.

To implement this, you’ll need to modify your script and set up the trigger in the Apps Script editor. Here’s a basic implementation:

function onFormSubmit(e) {
var values = e.values;
var columnEValue = values[4];

if (columnEValue) {
var emailTo = ‘[email protected]’;
var message = ‘New response received with data in column E’;
var url = SpreadsheetApp.getActive().getUrl();
MailApp.sendEmail(emailTo, ‘Form Submission Alert’, message + ‘\n’ + url);
}
}

Remember to set up the trigger in the Apps Script editor under ‘Triggers’ to run this function on form submit. This should effectively notify you when column E contains data in new submissions.