Reputation: 21
I am looking for a way to get a cell to mark if the email gets sent from a google sheet. For an example if the email sent it would say in column P "email sent" on that row or something like that. I'm pretty new to this I found some helpful similar questions but I just don't know how to set it up. I think I need to use an IF command but don't know how or where to set it
function SendStudentEmails() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Homework Hour").activate();//use name of active sheet
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DO NOT DELETE! Parent Email Template").getRange(1, 1).getValue();
var quotaLeft = MailApp.getRemainingDailyQuota();
if((lr-1) > quotaLeft) {
Browser.msgBox("You have " + quotaLeft + " left and you're trying to send" (lr-1) + " emails. Emails were not sent")
} else {
for (var i = 2;i<=lr;i++){
var currentEmail = ss.getRange(i,14).getValue(); // 15 is the email column
var currentStudent = ss.getRange(i, 4).getValue(); // 4 is student name column
var currentMessage = ss.getRange(i, 2).getValue(); // 2 is the message column
var currentMissingAssingment = ss.getRange(i, 9).getValue(); // 9 is the missing assingment column
var massageBody = templateText.replace("<<Student Name>>",currentStudent).replace("<<Message>>",currentMessage).replace("<<Missing Assingment>>",currentMissingAssingment);
var subjectLine = currentStudent +" Has Homework Hour";
if (currentEmail.trim() !== '')// checks if email is not blank
MailApp.sendEmail(currentEmail, currentStudent, massageBody);
}
}
}
Upvotes: 2
Views: 90
Reputation: 912
You could use setBackground("color")
too if you want to change background color of your cell.
Here is a small example:
ss.getRange(5, 15).setBackground("green")
- it will change background color to green in cell N5
This is how it looks like in your snippet, (below MailApp, trigger setBackground after MailApp does the work):
function SendStudentEmails() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Homework Hour").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastRow = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DO NOT DELETE! Parent Email Template").getRange(1, 1).getValue();
var quotaLeft = MailApp.getRemainingDailyQuota();
if ((lastRow - 1) > quotaLeft) {
Browser.msgBox("You have " + quotaLeft + " left and you're trying to send " + (lastRow - 1) + " emails. Emails were not sent")
} else {
for (var i = 2; i <= lastRow; i++) {
var currentEmail = ss.getRange(i, 15).getValue(); // 15 is the email column
var currentStudent = ss.getRange(i, 4).getValue(); // 4 is student name column
var currentMessage = ss.getRange(i, 2).getValue(); // 2 is the message column
var currentMissingAssingment = ss.getRange(i, 9).getValue(); // 9 is the missing assingment column
var massageBody = templateText.replace("<<Student Name>>", currentStudent).replace("<<Message>>", currentMessage).replace("<<Missing Assingment>>", currentMissingAssingment);
var subjectLine = currentStudent + " Has Homework Hour";
if (currentEmail.trim() !== '' && ss.getRange(i,15).getBackground !== 'green') { // checks if email is not blank
MailApp.sendEmail(currentEmail, currentStudent, massageBody);
ss.getRange(i, 15).setBackground("green") // change cell background to green
}
}
}
}
Upvotes: 1
Reputation: 505
I'm new to answering, but I hope this helps you out! It sounds like you want to mark next to each row of a sent email, in which case, you already have your if/for cases setup to do this (except that you appear to be missing one set of braces for the last if test, as below). You can just add whatever text you want to column P in the row after the email gets sent. Here is that section of the code with the added part to add text to column P:
if (currentEmail.trim() !== '')// checks if email is not blank {
MailApp.sendEmail(currentEmail, currentStudent, massageBody);
ss.getRange(i,16).setValue("email sent") // writes text (email sent) into column P (col 16), row i (the current row in the for loop)
}
}
Upvotes: 2