Google Script - Form to Email Part 1: Establishing 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 is setting the counselor's email, while the second is to create and send the email. 

Part 1. Establishing Email
One piece in creating this and putting it into place was having the counselors' names on the form, then using a script to add the counselor's email into another column.

Here is the script to put into the Script Editor -

function setEmail() {

  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();  //Number of columns
 
  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
 var data = dataRange.getValues();

  for (var i = 0; i < data.length; ++i) { //
    var row = data[i];
    var counselor = row[4];  //column with counselor selected
       
      if (counselor == "Mrs. Doe - Counselor") {    
           var cemail = sheet.getRange(startRow + i, 7);
      cemail.setValue("doej@domain.org");
    } else if (counselor == "Mrs. Smith - Counselor") {
            var cemail = sheet.getRange(startRow + i, 7);
      cemail.setValue("smitht@domain.org");
    } else if (counselor == "Mr. Green - Counsleor") {
            var cemail = sheet.getRange(startRow + i, 7);
      cemail.setValue("greenr@domain.org");
    }

  }
}  

Let's break it down -
The top part of the script is getting the active spreadsheet (the one with the form responses), establishing the first data row and the number of Rows and Columns with data (remember - these are all numerical calls).

The dataRange variable is establishing the range of data to pull - in this case our starting row to our last row, and the first column to the last column.  The last row is numRows-1 as we are starting at the second row; if we just used numRows we would be pulling an extra row of data.

The data variable is actually pulling the values in those cells in our range.  Note:  This call can actually be combined with the establishing the range; I have them as two lines as I prefer it for when I'm troubleshooting or making modifications.

Next we begin our loop in matching specific criteria and then creating a new variable - cemail - depending on what the criteria is.
First the   for (var i = 0; i < data.length; ++i)  - this is establishing a variable i which grows by increments (the ++i) from 0 to the data length.  Our for is saying we will do this for each i value that falls under this criteria.

var row = data[i];  pulls the data for that specific row

    var counselor = row[4];  this establishes which of the columns has the variable we will be checking a match against - in my form, it falls in the 5th column; since counting starts at 0 that makes it 4 in the code.

The if-else statement then establishes first what match to check for; the == is used as I want an exact value match.   It then creates and assigns a new variable cemail if the criteria is matched in the desired cell (this is a case where you use the actual column - so mine is going in the 7th column).   I use else if to look for the other matches.  Because these possible responses come from a multiple choice list, I have a finite number of possibilities so do not include a final else statement - everything should match prior to needing it.  

Don't forget to set triggers if you want this to happen automatically on a form submit!

That establishes the emails needed for part 2.