Google script project trigger not running?

问题:

I'm pretty new to Javascript and have been working on this script to take the most recent entry in a spreadsheet (created from a Google form), match the users email address that is collected to a roster in a second sheet, and send an email to parents. I'm a teacher and the idea is to be able to create a google form that compiles the info students enter and email it to their parents once they submit the form/update the sheet.

I know it's pretty messy...there are some extra variables and things, but the script works perfectly/as expected when you "Run" the script. The only thing is, I have tried to have the script run on a trigger when the form is submitted, but it doesn't. Am I missing something with using triggers?

Code is 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: "[e-mail]" + ", " + 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>"

  });

}

回答1:

You are aware that there are 2 types of trigger 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 documentation here.

A simple trigger is used by simply naming the function with the trigger name. For example, with Sheets / Forms, the trigger onFormSubmit(e) is fired when the user submits a form. The parameter e contains all the information relating to the submission, you should look into this as it's much more reliable than your current method of getting the submitted information. See here: 'e' parameter

Simple triggers are limited in their functionality since the script doesn't have to be authorised for the trigger to fire. A simple trigger cannot access other files, send emails or perform any action that requires authorisation. See here

An installed trigger is one that is either manually set up by the user or a script. Installed triggers have a lot more functionality but they still have some restrictions. See here

An installed trigger can call any named function and e parameter works in the same way as it does with simple triggers.

From your code above your installed trigger should look like this.

When you click save you should be asked for authorisation, if you are not asked, click the debug/ run button to authorise the script.

If it still doesn't work check the execution transcript in view -> execution transcript, the last line will indicate the error.

回答2:

Ok, James helped out a lot, but I was seeming to have a lot of problems authenticating the permissions to send emails. I decided in the end to re-write everything more clearly, add some functions so others I work with could use the same script, and keep a record of whether or not emails were actually sent to parents. This final version uses a if statement to look in a column at whether or not 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 set up that "confirmation" column and create a roster sheet as a single function as well as seperately. Oh, and it also looks for an html template to format the email. I found that info on a Google developers live 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: "[e-mail]" + ", " + 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