function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // Row to start gathering data. Usually 2 because of header. You shouldn't need to change this.
var numRows = 3; // Number of rows to process. This is typically number of students.
var numCol = 7; // Number of columns in the spreadsheet
var emailSent = 8; // In this column, script will write "E-mail Sent." Future e-mail will not be sent if "E-mail sent" exists
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, numCol)
var dataRange2 = sheet.getRange(1,1, numRows, numCol)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
var data1 = dataRange2.getValues();
var word;
for (var i = 0; i < data.length; ++i) {
var row = dataundefined;
var emailAddress = row[3]; // Column e-mail address appears in. Don’t count first Column.
var message = ""; //put a message between the quotation marks, if you'd like to add anything to the body of the email
for(var x = 0; x < data[0].length; ++x)
{
message += data1[0][x] + ":\t" + dataundefined[x] + "\n\n";
}
During presentations and essays, I like to have students use Google Forms to provide peer feedback. While Google Forms is awesome, there are two big problems when using it for peer feedback: 1) It isn’t great for collecting wordy feedback. As you know, when you collect big pieces of text via Google Forms it can be hard to read. 2) Once I have all that peer feedback collected, it is difficult to share it back to that student so they can reflect.
For a while. I was printing out the feedback, cutting it, and handing it back to each student. This was annoying and time consuming. I said to myself, “Self, there has to be a better way!” Thus, my first foray into scripting came about like most inventions: Necessity.
I scoured the internet to see if any code that would solve my problem already existed and I came across this. From there, I did a bit more research, read a few articles, and played with Google Apps Scripts until I got it to do what I wanted.
This script will take a Google Form’s assigned Sheet, make an easy to read e-mail out of it, and send it to a person. For me, it means a student is e-mailed peer feedback in a way that they can easily digest. It is far from perfect, but it gets the job done.
I intended to keep this script locked away forever, since I’m not confident in my abilities yet, but in a recent conversation a teacher was lamenting that she was having the same problem I was: getting Google Form feedback back to the students in a way that was easy to read. I told this friend, the great Kate Baker, about my script and she asked me to share it with her.
I figured I would do one better. Below, you will find the script and below that a tutorial to “install” it. Again, I’m still learning, but if this script can be useful I feel it is my duty to share it; we’re all in this together. Feel free to use it as you see fit, and if you happen to know anything about code, I’d love to hear some feedback. Also, if there is an add-on or script out there that does this job already, let me know!
Here is a link to the Google Apps Script if you’d like to copy it from there. Otherwise, here’s the code:
function myFunction() {
}
var EMAIL_SENT = "EMAIL_SENT";
var emailSent2 = row[emailSent];
if (emailSent2 != EMAIL_SENT) { // Prevents sending duplicates
var subject = "Your Feedback"; //change this to change the subject of the email
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, emailSent).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
And the tutorial*:
Until Next Time,
GLHF!
*Note: I mess up column and row every time I say it in the screencast. I really do know which is which… I promise.*
cross-posted at Teched Up Teacher
Chris Aviles teaches English at Barnegat High School in New Jersey. He presents on education topics including gamification, technology integration, BYOD, blended learning, and the flipped classroom. Read more at Teched Up Teacher.
