
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);
};

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.
No comments :
Post a Comment