CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
dhjeffery89
dhjeffery89

Reputation: 25

Sending Email Notification Google Sheets

My company has created a Google Form set up to make one of our processes a lot easier. The Google Form is based off of the Master Spreadsheet that contains all of the data inputted from the Form. This spreadsheet then filters out the the form submission and sends the data to each department’s spreadsheet, which as previously stated before, gets all of the information from the "Master Spreadsheet."

We previously had it set up so when employees would go in and approve or deny these requests in their spreadsheet, we would receive an email notification if someone entered "Approved" or "Denied." Recently we changed it so if a certain person submitted a request for a customer, it would be automatically approved, but when we did this the email notification stopped working because no one is manually entering in "Approved" or "Denied" for these requests. It still works when it's manually typed in, but when the cell is automatically filled in, the sendNotification does not work.

Since no actual data is being input into the individual department sheets, we wanted to put the notification trigger on the "Master Sheet," but we are having a heck of a time getting the email notification to send. Basically we want it so if any cell in "Column F" contains a certain list of email addresses it will send an email to a third party notifying them to actually go ahead and make the changes.

Here is what we have so far. Keep in mind this is the code that worked originally. I've tried many different variations of things and have had no luck whatsoever, but I'm not the most educated coder:

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 3");
  //Get Active cell
  var mycell = ss.getActiveSelection();
  var cellcol = mycell.getColumn();
  var cellrow = mycell.getRow();
  var cellValue = mycell.getValue();

  var activeUser = Session.getActiveUser();
  var recipients = "[email protected]";
  var subject = "Update to "+ss.getName();
  var body = activeUser + " has marked row " + cellrow + " \"" + cellValue + "\" in \"" + ss.getName() + "\".  Visit " + ss.getUrl() + " to view the changes."; 

  if (cellcol == 2) {
    if (cellValue.indexOf('[email protected]') >= 0) {
      var subject = "Lunch Is Served";
      Logger.log('Sending approval notification of row ' + cellrow + ' to ' + recipients);
      MailApp.sendEmail(recipients, subject, body);
    }  
  }
}

Please keep in mind that we can't use lastRowNumber (at least I didn't think we could) because we already have over one thousand rows listed so the information will fill in to the array automatically.Lastly, our current trigger is set to "On Form Submission" because we want these emails to come in as the forms are submitted.

I have included a sample spreadsheet for you guys to look at. Please use [email protected] as your email address when completing the form.

The Google Sheet can be found at the following site: Test Sheet!

Thank you so much and I look forward to reading your responses!

Upvotes: 0

Views: 1536

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

You can't use the line:

var mycell = ss.getActiveSelection();

If that function is running from an "On Form Submit" trigger, there is no active selection. Although, there is a property available to the "On Form Submit" event object that gives the currently edited range. You must get the event object from the form submission. Then you have 3 options. 1) Just get the values 2) Get an object of questions and their values 3) Get the range of the range edited. First, you get the event object that is passed into the function. When the form is submitted, data is automatically made available to the function associated with the On Form Submit trigger. The letter e is typically used as the variable name to get the event object:

function sendNotification(e) {

But you can use any variable name:

function sendNotification(objOfData) {

Apps Script Documentation - Spreadsheet - On Form Submit

function sendNotification(e) {
  var cellValue = e.values[4];//Get the value in column 5

Upvotes: 0

Related Questions