Form Notification for Teachers when Students Fill out Google Form

Question

I'm making a website for school to connect students and teachers. Teachers fill out a Google Form asking for service (grading papers...) and it is connected to Google Spreadsheets. Then a student can see that Spreadsheet and sign up on a different Google Form to help the teacher, and that response is recorded in the same Google Spreadsheet. When the student submits his/her Google Form, I want the teacher to be notified by email that a student signed up to help them. How can I send that notification? And I don't want the notification to come from my own school address. Is that possible? Here is the Google website: https://sites.google.com/fcpsschools.net/jmhsservicesignup/subjects/math

This is the code I have so far:

function sendEmails() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tutors 
Signed Up");

  var startRow = sheet.getLastRow();  // First row of data to process

  var dataRange = sheet.getRange(startRow, 1, 1, 5)
  // Fetch values for each row in the Range.

  var data = dataRange.getValues();

  for (i in data) {

    var row = data[i];

    var emailAddress = row[3];       // Second column

    var tutor = row[0];

    MailApp.sendEmail(emailAddress,"Tutor Request Filled",""+tutor+" has accepted your request to be tutored! Please notify"+tutor+" if anything changes.");

  }
}

The getLastRow doesn't seem to work, and the email sends from my own address, not a Google forms notification if that is even possible.

If this is impossible I might just make the students send the email to the teacher, but I really want to automate the process. If you can think of a workaround, that would also be awesome.

In order to have the email come from a different email address, the account must allow alias email addresses, and you must use the advanced parameters and the from setting. There is a process to go through to set up and verify that the alias email address is owned by the account. Otherwise, anyone could send emails as coming from anyone else. You will probably also need to speak with the account domain administrator to see if there are any restrictions set up. So you need to find out who the domain administrator is. — Alan Wells
In addition to Sandy Good comment, Notification Add-on is open source, you should take a look at the code. — user8214858

e__n e__n · Accepted Answer · 2018-06-13T04:01:52

Adding to what Sandy Good and Guilherme mentioned. Here are a few more tips:

Not clear what data you're trying to get in your function. Are you trying to grab the data from the form submission? In that case you need to set up a trigger from the scripts menu, and connect it to the function (function needs to have an 'event' argument, usually called e).

function processFromSubmission(e) {//get data from e}

Apologies if you're not trying to do that. What happens when you Log sheet.getLastRow()?

Ask the school if they can get you another email address to use for this form, something like [email protected]. Follow the steps here to set up your alias. Do it for the gmail account that "owns" the spreadsheet these forms are sending data to.

Make sure your alias is set up properly. You should be able to see it in the "from" dropdown menu when you compose an email. Also try running the code Logger.log(GmailApp.getAliases()) and check the log to see that it's available.

Use GmailApp.sendEmail to send email, not MailApp.sendEmail. They're almost the same, but it looks like the MailApp version doesn't let you send from an alias. You can specify the 'replyTo' email as well if you want to set it to something else, like the student filling out the form or whatever (still not clear exactly what you're trying to set up!).

Send email like:

GmailApp.sendEmail(emailAddress, subject, body,
   {replyTo: replyToEmailAddress_can_be_any_email,
    from: '[email protected]'
 });

Note: your link didn't open.

Form Notification for Teachers when Students Fill out Google Form

1 Answers