Scripting

Okay, we're going to talk a little bit about scripting. Don't worry if you've never written any code - you can just copy/paste the code into your script, and change any sheet names that need to be changed.

Open the spreadsheet that collects responses, and click on Tools - Script Editor...

This will open a script editor window. Highlight everything in the window, and delete it. Now copy and paste the following code into the window, then click File - Save.



/**
 * Retrieves all the rows in the active spreadsheet that contain data and logs the
 * values for each row.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};

/**
 * Retrieve the name, email address, and grade for the last student who completed
 * the quiz, and send the result to the email address listed.
 */
function sendEmailToLast() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("entry");  // look for the sheet titled entry
  sheet.activate();
  var lastRow = sheet.getLastRow();   //find out the number of the row for the latest form submission
  sheet = ss.getSheetByName("Grades");   // go to the Grades sheet
  sheet.activate();
  var entries = sheet.getSheetValues(lastRow, 1, 1, 4)  //find the entries in results that correspond to the last submission
  var firstName = entries[0][1];  // collect data from results
  var lastName = entries[0][0];
  var emailAddress = entries[0][2]; 
  var result = entries[0][3];
  var subject = ss.getSheetName();
  var message = "Thank you for completing the " + subject + " review activity, " + firstName + " " +lastName +  ". You got " + result + " %.";
  MailApp.sendEmail(emailAddress, subject, message); // send the result by email
  
}  

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Send Latest Result by Email",
    functionName : "sendEmailToLast"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

Depending on what you've named your sheets, you may need to change the sheet names listed in the code example (the text is written in red in the above code). Change the sheet names to match what you've chosen, and be sure to match case (CaPiTaLs Or LoWeRcAsE).

The final step is to set up the trigger. This determines when the script is run. Click on Resources - Current project's triggers... This will open a dialog box asking you to define what to run, and when to run it. Click Add a Trigger, and choose the following options.

This will ensure that every time a form submission is received, the script to email the results will be run.