Google Script - Form to Email Part 2: Creating the Email

I've been working with our counselors to create an online request form that replaces the "pink slips" currently in use by students.  We are hoping this solution increases efficiencies at several levels (i.e. students can put in the appointment request at any time including at home, pink slips are not lost, etc).

There were two pieces in this.  The first was setting the counselor's email, while the second is to create and send the email.  You may want to visit the first part if you have information to add to the spreadsheet prior to creating and sending the email. 

Part 2. Creating and Sending the Email
If you already have the email you will be sending to collected via the form, you will only need to use these directions. 

First, here is the sample script you can put into the Script Editor -

function sendEmail(){
 
   var sheet = SpreadsheetApp.getActiveSheet();
 
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  var numColumns = sheet.getLastColumn();
 
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
  var data = dataRange.getValues();
 
  var complete = "sent";
 
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var semail = row[1];  //student account/email
    var counselor = row[4];  //counselor selected
    var reason = row[5]; //reason for request
    var student = row[2]; //student name
    var cemail = row[6]; //counselor email
    var emailed = row[7];
   
    if (emailed != complete){
    var sent = sheet.getRange(startRow + i, numColumns);
    var email = cemail;
    var subject = student + " requests an appointment"
    var body = student + " from " + program + " would like to see you. \n\nThe reason for the request: " + reason + "\n\n" + "Student's email: " + semail;
   
    MailApp.sendEmail(email, subject, body);
     
      sent.setValue(complete);
    }

  }
}

The whole first section is connecting to the current spreadsheet (with the form responses), establishing the first row of data, the number of rows and columns with data within the spreadsheet, then grabbing the values within that range of data.

I've then established a new variable   var complete = "sent";  This variable will allow me to do a match so I am not sending the same emails over and over - they will only send if the appropriate cell is not marked as Sent. 

The next section is gathering the data in a particular row -
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var semail = row[1];  //student account/email
    var counselor = row[4];  //counselor selected
    var reason = row[5]; //reason for request
    var student = row[2]; //student name
    var cemail = row[6]; //counselor email
    var emailed = row[7];
I've established i, which will look at each row incrementally to the last row of data.  The new variables set up after pulling the data correspond to specific columns in the spreadsheet (this is a case where the numbering starts at 0 - so my semail is in the 2nd column, counselor is in the 5th column, etc)

The next section is first evaluating if the email has been sent - the != essentially says "is not equal to the value of the variable complete" (in my example, it does not have the word sent in that cell).
    if (emailed != complete){
It then is establishing that the variable sent is in a specific column - this is actually the same cell as emailed in my example, but I establish it here so that I can change the value later when the email has been sent.
    var sent = sheet.getRange(startRow + i, numColumns);

The next three lines are where I am establishing the email, subject and body of my email.  (I personally establish these as email, subject and body whenever doing an email for clarity when I am looking back at my scripts.)  You will notice that you can use your earlier variable in conjunction with fixed text - be sure to use a plus sign between joined pieces, and quotation marks around any fixed text.  Additionally, if you want to establish line breaks in the body of your email use the \n.
    var email = cemail;
    var subject = student + " requests an appointment"
    var body = student + " from " + program + " would like to see you. \n\nThe reason for the request: " + reason + "\n\n" + "Student's email: " + semail;
   
The next line is sending the email:
    MailApp.sendEmail(email, subject, body);

My last line is putting the word "sent" in the cell I established earlier so that when the script runs again it will disregard that line of data and not send the email again.
      sent.setValue(complete);

You will want to set up a trigger if you want this to run automatically.

I hope that if you are adding information to the spreadsheet - like I did with the couselor's email - you see how part 1 and part 2 interface very nicely together.   They can even be put together into one script file.