Not running script google project trigger?
I am new to Javascript and am working on this script to take the most recent entry in a spreadsheet (generated from a google form), map the email address of the users that is going to the registry in a second to the sheet, and send an email to the parents. I am a teacher and the idea is to be able to create a google form that will compile information contributors and send them to their parents after the form is submitted / sheet refreshed.
I know this is pretty messy ... there are additional variables and that's it, but the script works fine / as expected when you run the script. The only thing I tried to run the script when the form was launched, but it doesn't. Am I missing something with triggers?
Code below:
function createEmail() {
// Sets variables for both sheets
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheets()[0];
var sheet2 = ss.getSheets()[1];
// This gathers information from the most recent entry and write it to an array called newReflectionValues
var reflectionLastRow = sheet1.getLastRow();
var reflectionLastColumn = sheet1.getLastColumn();
var reflectionLastCell = sheet1.getRange(reflectionLastRow, reflectionLastColumn).getValue();
var reflectionRange = sheet1.getRange(reflectionLastRow, 1, 1, reflectionLastColumn);
var newReflectionValues = reflectionRange.getValues();
var studentEmail = newReflectionValues[0][3];
Logger.log("NEW REFLECTION VALUES")
Logger.log(newReflectionValues);
Logger.log("Email will send to student email:")
Logger.log(studentEmail)
// Makes an array of the parent email addresses
var rosterLastRow = sheet2.getLastRow();
var rosterLastColumn = sheet2.getLastColumn();
var rosterEmails = sheet2.getSheetValues(2, 1, rosterLastRow, rosterLastColumn);
Logger.log("PARENT EMAILS")
Logger.log(rosterEmails);
// Cross check emails - if a match, write emails to variable
var parentEntriesLength = rosterLastRow;
for (i = 0; i < parentEntriesLength; i++) {
var currentRange = rosterEmails[i];
if (currentRange[2] == studentEmail) {
var toParents = String(currentRange[3]) + ", " + String(currentRange[4]);
var studentName = String(currentRange[0]);
var countOfReflections = currentRange[6];
break;
} else {
var toParents = "NO PARENT EMAILS FOUND";
}
}
// FINISH EMAIL BELOW
MailApp.sendEmail({
to: toParents,
bcc: "rdoyle@rafos.org" + ", " + String(studentEmail),
subject: "Behavior Reflection Notification",
htmlBody: "<p>Hello,</p>" +
"<p>Today studentName received a behavior reflection for the following action:</p>" +
"<p>newReflectionValues</p>" +
"<p>They took a short break in class and completed the following reflection:</p>" +
"<p>reflectionInformation</p>" +
"<p>" + String(studentName) + " has recieved " + countOfReflections + " reflections this year." + "</p>" +
"<p>This email has been sent with information that the student completed directly on the reflection form and has been bcc'd to them as well as myself. If you have any questions regarding this behavior or incident, please feel free to ask.</p>"
});
}
source to share
You know there are two types of triggers, simple and installable , but I think you are a little confused as to what they actually mean / do. I'll try to explain the key points from the documentation here.
A simple trigger is used by a simple function name with the trigger name. For example, with sheets / forms, a trigger is onFormSubmit(e)
fired when the user submits the form. The parameter e
contains all information related to the view, you should consider this as more reliable than your current method of obtaining the presented information. See here: parameter 'e'
Simple triggers are limited in their functionality, as a script does not have to be allowed in order to fire a trigger. A simple trigger cannot access other files, send emails, or perform any actions that require authorization. See here
An installed trigger is one that is either manually configured by the user or a script. Installed triggers have a lot more functionality, but they still have some limitations. See here
The set trigger can call any named function, and the parameter e
works the same as with simple triggers.
From your code above the set trigger should look like this.
When you click Save, you are asked for authorization, if you are not, click the Debug / Run button to allow the script.
If it still doesn't work, check the execution transcript in view -> execution protocol, the last line will indicate an error.
source to share
Ok, James helped a lot, but I had a lot of problems authenticating permissions to send emails. I decided to eventually rewrite everything more clearly, add some functionality so that others I worked with could use the same script, and keep a record of whether the emails were actually sent to the parents. This final version uses an if statement to look in a column if an email was sent for each response, then sends an email if needed and records when it was sent. I also added a function to customize this "confirm" column and create a list sheet as a standalone function or separately. Oh, and it also looks for a html template to format the email. I found this information on the google devs stream:https://www.youtube.com/watch?v=U9Ej6PCeO6s
Thanks everyone!
function createConfirmationColumn() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheets()[0];
var lastColumn = sheet1.getLastColumn();
var lastColumnValue = sheet1.getRange(1,lastColumn).getValue();
// Creates the final column to log the time that emails are sent
if (lastColumnValue != "Email Sent On:") {
sheet1.insertColumnsAfter(lastColumn, 1);
var emailSentOnColumn = sheet1.getRange(1,lastColumn+1).setValue("Email Sent On:");
}
}
function createRosterSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.insertSheet("Roster & Parent Emails", 1);
var rosterSheet = ss.getSheets()[1];
rosterSheet.getRange(1,1).setValue("First Name")
rosterSheet.getRange(1,2).setValue("Last Name")
rosterSheet.getRange(1,3).setValue("Student Email")
rosterSheet.getRange(1,4).setValue("Parent Email 1")
rosterSheet.getRange(1,5).setValue("Parent Email 2")
rosterSheet.getRange(1,6).setValue("Notes")
rosterSheet.getRange(1,7).setValue("Total Reflections")
}
function formSetup() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheets()[0];
var lastColumn = sheet1.getLastColumn();
var lastColumnValue = sheet1.getRange(1,lastColumn).getValue();
// Creates the final column to log the time that emails are sent
if (lastColumnValue != "Email Sent On:") {
sheet1.insertColumnsAfter(lastColumn, 1);
var emailSentOnColumn = sheet1.getRange(1,lastColumn+1).setValue("Email Sent On:");
}
ss.insertSheet("Roster & Parent Emails", 1);
var rosterSheet = ss.getSheets()[1];
rosterSheet.getRange(1,1).setValue("First Name")
rosterSheet.getRange(1,2).setValue("Last Name")
rosterSheet.getRange(1,3).setValue("Student Email")
rosterSheet.getRange(1,4).setValue("Parent Email 1")
rosterSheet.getRange(1,5).setValue("Parent Email 2")
rosterSheet.getRange(1,6).setValue("Notes")
rosterSheet.getRange(1,7).setValue("Total Reflections")
}
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheets()[0];
var sheet2 = ss.getSheets()[1];
var lastColumn = sheet1.getLastColumn();
var lastColumnValue = sheet1.getRange(1,lastColumn).getValue();
var allFormEntries = sheet1.getDataRange().getValues();
var allRosterValues = sheet2.getDataRange().getValues()
for (e = 1; e < sheet1.getLastRow(); e++) {
var formRange = allFormEntries[e];
var studentEmailInForm = formRange[1];
var emailSentOn = formRange[4];
if (emailSentOn == "") {
for (i = 1; i < sheet2.getLastRow(); i++) {
var individualRosterEntry = allRosterValues[i];
if (studentEmailInForm == individualRosterEntry[2]) {
var parentEmails = String(individualRosterEntry[3]) + ", " + String(individualRosterEntry[4]);
var emailTemplate = HtmlService.createTemplateFromFile("emailTemplate");
emailTemplate.studentName = individualRosterEntry[0];
emailTemplate.reflectionCount = individualRosterEntry[6];
emailTemplate.reason = formRange[2];
MailApp.sendEmail({
to: parentEmails,
bcc: "rdoyle@rafos.org" + ", " + String(studentEmailInForm),
subject: "Behavior Reflection Notification",
htmlBody: emailTemplate.evaluate().getContent(),
})
sheet1.getRange((e+1), lastColumn).setValue(new Date());
break;
} else {
sheet1.getRange((e+1), lastColumn).setValue("No valid email found") ;
}
} // for i loop
} //if email sent == ""
} //for e loop
} //function
source to share