Google 脚本项目触发器未运行? [英] Google script project trigger not running?

问题描述

我对 Javascript 还很陌生，并且一直在研究这个脚本，以获取电子表格(从 Google 表单创建)中的最新条目，将收集到的用户电子邮件地址与第二个工作表中的名册相匹配，并发送电子邮件给父母.我是一名老师，我的想法是能够创建一个谷歌表单，编译学生输入的信息，并在他们提交表单/更新表格后通过电子邮件将其发送给他们的父母.

我知道这很混乱……有一些额外的变量和东西，但是当您运行"脚本时，脚本可以完美/按预期工作.唯一的问题是，我试图在提交表单时让脚本在触发器上运行，但它没有.我在使用触发器时遗漏了什么吗?

代码如下:

function createEmail() {//为两个工作表设置变量var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet1 = ss.getSheets()[0];var sheet2 = ss.getSheets()[1];//这会从最近的条目中收集信息并将其写入一个名为 newReflectionValues 的数组中varreflectionLastRow = sheet1.getLastRow();varreflectionLastColumn = sheet1.getLastColumn();varreflectionLastCell = sheet1.getRange(reflectionLastRow,reflectionLastColumn).getValue();varreflectionRange = sheet1.getRange(reflectionLastRow, 1, 1,reflectionLastColumn);var newReflectionValues =reflectionRange.getValues();var studentEmail = newReflectionValues[0][3];Logger.log("新的反射值")Logger.log(newReflectionValues);Logger.log("邮件将发送到学生邮箱:")Logger.log(学生邮箱)//创建一个父电子邮件地址数组var rosterLastRow = sheet2.getLastRow();var rosterLastColumn = sheet2.getLastColumn();var rosterEmails = sheet2.getSheetValues(2, 1, rosterLastRow, rosterLastColumn);Logger.log("父电子邮件")Logger.log(rosterEmails);//交叉检查电子邮件 - 如果匹配，则将电子邮件写入变量var parentEntriesLength = rosterLastRow;for (i = 0; i < parentEntriesLength; i++) {var currentRange = rosterEmails[i];如果(currentRange[2] == studentEmail){var toParents = String(currentRange[3]) + ", " + String(currentRange[4]);var studentName = String(currentRange[0]);var countOfReflections = currentRange[6];休息;} 别的 {var toParents = "没有找到家长电子邮件";}}//完成下面的电子邮件MailApp.sendEmail({给:给父母，bcc: "[e-mail]" + ", " + String(studentEmail),主题:行为反映通知"，htmlBody: "<p>你好，</p>"+<p>今天，studentName 收到了以下操作的行为反映:</p>"+"<p>newReflectionValues</p>"+<p>他们在课堂上短暂休息并完成了以下反思:</p>"+<p>反射信息</p>"+<p>"+ String(studentName) + " 已收到 " + countOfReflections + " 今年的反思."+ "</p>"+<p>这封电子邮件已发送学生直接在反思表上填写的信息，并已密送给他们和我自己.如果您对此行为或事件有任何疑问，请随时提问.</p>"});}

解决方案

您知道触发器有 2 种类型

当你点击保存你应该被要求授权，如果你没有被要求，点击调试/运行按钮来授权脚本.

如果还是不行，查看view-> execution script中的执行脚本，最后一行会提示错误.

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>"

  });

}

解决方案

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.

这篇关于Google 脚本项目触发器未运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！