如果值低于60％，则尝试制作扫描Google表格和电子邮件的脚本 [英] Trying to make a script that scans a Google Sheet and emails if values are below 60%

问题描述

我搜索并试图使用我能找到的，但我被卡住了。我正在尝试创建一个脚本，用于扫描Google表单（大约30张），其中新输入的值低于60％。然后，我想让脚本通过电子邮件向我发送第二列（学生姓名）中的数据，第四列（教师姓名）以及当前数据行的百分比（测试分数） 60％（例如克拉克，约翰夫人布朗56.64％）。我是Java新手，但一直在努力学习。

这是我迄今为止的脚本。它发现没有问题的正确表单。它也会给我发电子邮件（带有错误的信息，但它可以起作用）。但是有些东西我被困住了。就像我知道的，我必须设置某种onEdit触发器，以便它只发送新输入的乐谱，但我不知道该把它放在哪里或怎么做。我也知道dataRange代码，我似乎对我和if语句错误也似乎是错误的。我不知道如何指定低于60％。
我是一名技术老师，使用Google Drive为我们学校建立了一个海量数据系统。当学生需要帮助进行特定评估时，我希望主数据表能够自动发送给我的校长。任何帮助你可以给我非常感谢！
非常感谢你们所有的时间我喜欢把我的时间花在这里学习每个人。

Brandon

以下是我们使用的数据电子表格示例的链接

下面的简化脚本

function sendEmail（）{var spreadsheet = SpreadsheetApp .openById（'spreadsheet ID'）; ///数据的ID spreadshfeet var sheet = spreadsheet.getSheets（）; //获取所有表单var startRow = 3; //处理第三行数据var numRows = 40; //要处理的行数var dataRange = sheet.getRange（startRow，10，numRows，50）; //开始第3行第10列，并停止第40行，第50列//获取Range中每一行的值。 var data = dataRange.getValues（）; //Browser.msgBox（data）for（i in data）{var row = data [i]; if（dataRange.getValues（）<= .60）; {var emailAddress =我的电子邮件; var message = row [2]; //第二栏var subject =ALERT - 评估分数低于60％输入。; MailApp.sendEmail（emailAddress，主题，消息）; // Browser.msgBox（emailAddress）}}}

$ b

 函数emailAlert（e）{
 var range = e.range; 
 if（range.getColumn（）> = 10）{//只检查第一列和最多
 var editedSheet = e.source.getActiveSheet（）; 
 var editedRow = range.getRow（）; 
 var value = range.getValue（）; 
 if（value！=='undefined'）{
 if（value <0.6）{
 var studentData = editedSheet.getRange（editedRow，1，9）.getValues（） ; 
 Logger.log（
'StudentId：'+ studentData [0] [0] + 
'\\\
 Name：'+ studentData [0] [1] + 
' \\\
 HR：'+ studentData [0] [2] + 
'\\\
 Teacher：'+ studentData [0] [3] + 
'\\\
等级：'+ studentData [0 ] + 
'\\\
 Race：'+ studentData [0] [5] + 
'\\\
 G：'+ studentData [0] [6] + 
' \\\
 Ed：'+ studentData [0] [7] + 
'\\\
 AVG：'+ studentData [0] [8]）; 
 var emailAddress =[e-mail]; 
 var message =测试
 var subject =ALERT  - 评估分数低于60％输入。; 
 MailApp.sendEmail（emailAddress，subject，message）; 
 //发送电子邮件.. 
} 
} 
} 
  

}

我添加了上面的新代码，其中安装的触发器处于活动状态。我仍然无法使电子邮件功能正常工作。有任何想法吗？
Thanks ...

解决方案

听起来像你的问题可以通过使用 onEdit来解决触发器。但是，由于您想发送电子邮件，因此您需要手动添加触发器，因为该功能需要授权发送电子邮件。

试试这个：

  function assessmentOnEdit（e）{
 var range = e.range; 
 
 if（range.getColumn（）> = 10）{//只检查第一列和最多
 var editedSheet = e.source.getActiveSheet（）; 
 var editedRow = range.getRow（）; 
 var value = range.getValue（）; 
 if（typeof value ==='number'）{
 if（value <0.6）{
 var studentData = editedSheet.getRange（editedRow，1，1，9）.getValues（ ）; 
 var message ='Assessment score：'+ Math.round（（value * 100）* 10）/ 10 +'％'+ 
'\\\
StudentId：'+ studentData [0] [0] + 
'\ nName：'+ studentData [0] [1] + 
'\\\
HR：'+ studentData [0] [2] + 
'\\\
Teacher：'+ studentData [0] [3] + 
'\\\
Grade：'+ studentData [0] [4] + 
'\\\
Race：'+ studentData [0] [5] + 
 '\\\
G：'+ studentData [0] [6] + 
'\ nEd：'+ studentData [0] [7] + 
'\\\
AVG：'+ Math.round（（ studentData [0] [8] * 100）* 10）/ 10 +'％'; 
 
 var emailAddress ='[e-mail]'; 
 var subject ='ALERT  - 评估分数低于60％输入。'; 
 MailApp.sendEmail（emailAddress，subject，message）; 
} 
} 
} 
} 
  

检查有关触发器的更多信息，请参阅Google参考页面： https://developers.google.com / apps-script / guides / triggers / events

编辑

<因为你想使用 IMPORTRANGE ，所以 onEdit 触发器不会触发。尝试使用下面的函数，使用 onChange 而不是 onEdit 。

  function assessmentOnChange（e）{
 
 var editedSheet = e.source.getActiveSheet（）; 
 var scriptProperties = PropertiesService.getScriptProperties（）; 
 var propertyKey ='currentColumn'; 
 var currentColumn = scriptProperties.getProperty（propertyKey）; 
 
 if（currentColumn === null）{
 currentColumn = 14; // Fisrt空列为N 
} 
 
 var table = editedSheet.getRange（3，Number（currentColumn），editedSheet.getLastRow（），editedSheet.getLastColumn（））。getValues（） ，
 rowNumber = 3，
 valuesToProcess = []; 
 
 table.forEach（function（row）{
 var column = 0; 
 row.forEach（function（cell）{
 if（typeof cell === （
 value：cell，
 row：rowNumber，
 column：Number（currentColumn）+ column 
}）
} 
列++; 
}）; 
 rowNumber ++; 
}）; 
 
 var maxColumn = Number（currentColumn）; 
 for（var i in valuesToProcess）{
 assessmentOnEdit（{
 source：e.source，
 range：{
 getRow：function（）{
返回valuesToProcess [i] .row 
}，
 getValue：function（）{
返回valuesToProcess [i] .value 
}，
 getColumn：function（）{ 
返回valuesToProcess [i] .column 
} 
} 
}）; 
 
 if（valuesToProcess [i] .column> maxColumn）{
 maxColumn = valuesToProcess [i] .column; 
} 
} 
 
 scriptProperties.setProperty（propertyKey，maxColumn + 1）; 
} 
  

I searched and tried to use what I could find, but I'm stuck. I am attempting to create a script that scans a Google sheet (with about 30 sheets) for values that are newly inputted and are below 60%. I am then looking to have the script e-mail me the data that is in the 2nd column (Student's name), 4th column (teacher's name) of the current row of the data as well as the percentage (test score)that was below 60% (ex. Clarke,John Mrs. Brown 56.64%) . I am new to Java script but have been trying to learn.

Here is the script that I have so far. It finds the correct sheet without a problem. It will also email me as well (with the wrong message, but it works none the less). But there are certain things that I am stuck on. Like I know that I have to set up some sort of onEdit trigger so that it only sends the newly inputted score, but I don't know where to put it or how to do that. I also know that the dataRange code that I have seems wrong to me and the if statement also seems wrong. I am not sure how to specify "below 60%". I am a technology teacher and set up a massive data system for our school using Google Drive. I want to have the master data sheet automatically email my principal whenever a student needs help on a specific assessment. Any help you could give me would be much appreciated! Thank you guys so much for all of your time I love spending my time on here learning from everyone.
Brandon

Here is a link to an example of the data spreadsheet we use

Choppy Script Below

function sendEmail() {

  var spreadsheet = SpreadsheetApp.openById('spreadsheet ID');       
  /// The ID of the data spreadshfeet   

  var sheet = spreadsheet.getSheets(); // gets all sheets
  var startRow = 3;  // Third row of data to process
  var numRows = 40;   // Number of rows to process

  var dataRange = sheet.getRange(startRow, 10, numRows, 50); // Start row 3 column 10, and stop row 40, column 50

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  //Browser.msgBox(data)

  for (i in data) {
    var row = data[i];

    if (dataRange.getValues() <= .60); {
      var emailAddress = "my email";  
      var message = row[2];       // Second column
      var subject = "ALERT - Assessment score below 60% inputted.";
      MailApp.sendEmail(emailAddress, subject, message);
      // Browser.msgBox(emailAddress)
    }
  }
}

function emailAlert(e) {
var range = e.range;
if (range.getColumn() >= 10) { // Only check column I and up
    var editedSheet = e.source.getActiveSheet();
    var editedRow = range.getRow();
    var value = range.getValue();
    if (value !== 'undefined') {
        if (value < 0.6) {
            var studentData = editedSheet.getRange(editedRow, 1, 1, 9).getValues();
            Logger.log(
                'StudentId: ' + studentData[0][0] +
                '\n Name: ' + studentData[0][1] +
                '\n HR: ' + studentData[0][2] +
                '\n Teacher: ' + studentData[0][3] +
                '\n Grade: ' + studentData[0][4] +
                '\n Race: ' + studentData[0][5] +
                '\n G: ' + studentData[0][6] +
                '\n Ed: ' + studentData[0][7] +
                '\n AVG: ' + studentData[0][8]);
            var emailAddress = "[e-mail]";  
            var message = Test    
            var subject = "ALERT - Assessment score below 60% inputted.";
            MailApp.sendEmail(emailAddress, subject, message);
            // Send email..          
        }
    }
}

}

I added the new code above with the installable trigger active. I still can't get the email function to work properly. Any Ideas? Thanks...

解决方案

It sounds like your problem can be solved using the onEdit trigger. However, since you want to send an email you'll need to add the trigger manually because the function requires authorization to send emails.

Try this:

  function assessmentOnEdit(e) {
    var range = e.range;

    if (range.getColumn() >= 10) { // Only check column I and up
        var editedSheet = e.source.getActiveSheet();
        var editedRow = range.getRow();
        var value = range.getValue();
        if (typeof value === 'number') {
            if (value < 0.6) {
                var studentData = editedSheet.getRange(editedRow, 1, 1, 9).getValues();
                var message = 'Assessment score: ' + Math.round((value * 100) * 10) / 10 + ' %' +
                    '\nStudentId: ' + studentData[0][0] +
                    '\nName: ' + studentData[0][1] +
                    '\nHR: ' + studentData[0][2] +
                    '\nTeacher: ' + studentData[0][3] +
                    '\nGrade: ' + studentData[0][4] +
                    '\nRace: ' + studentData[0][5] +
                    '\nG: ' + studentData[0][6] +
                    '\nEd: ' + studentData[0][7] +
                    '\nAVG: ' + Math.round((studentData[0][8] * 100) * 10) / 10 + ' %';

                var emailAddress = '[e-mail]';
                var subject = 'ALERT - Assessment score below 60% inputted.';
                MailApp.sendEmail(emailAddress, subject, message);
            }
        }
    }
}

Check the google referance pages for more info on triggers: https://developers.google.com/apps-script/guides/triggers/events

EDIT

Since you want to use IMPORTRANGE the onEdit trigger will not fire. Try using the function below and use this function with onChange instead of onEdit.

function assessmentOnChange(e) {

  var editedSheet = e.source.getActiveSheet();
  var scriptProperties = PropertiesService.getScriptProperties();
  var propertyKey = 'currentColumn';
  var currentColumn = scriptProperties.getProperty(propertyKey);

  if (currentColumn === null) {
    currentColumn = 14; //Fisrt Empty column is N
  }

  var table = editedSheet.getRange(3, Number(currentColumn), editedSheet.getLastRow(), editedSheet.getLastColumn()).getValues(),
      rowNumber = 3,
      valuesToProcess = [];

  table.forEach(function(row) {
    var column = 0;
    row.forEach(function(cell) {
      if (typeof cell === 'number') {
        valuesToProcess.push({
          value: cell,
          row: rowNumber,
          column: Number(currentColumn) + column
        })
      }
      column++;
    });
    rowNumber++;
  });

  var maxColumn = Number(currentColumn);
  for (var i in valuesToProcess) {
    assessmentOnEdit({
      source: e.source,
      range: {
        getRow: function() {
          return valuesToProcess[i].row
        },
        getValue: function() {
          return valuesToProcess[i].value
        },
        getColumn: function() {
          return valuesToProcess[i].column
        }
      }
    });

    if (valuesToProcess[i].column > maxColumn) {
      maxColumn = valuesToProcess[i].column;
    }
  }

  scriptProperties.setProperty(propertyKey, maxColumn + 1);
}

这篇关于如果值低于60％，则尝试制作扫描Google表格和电子邮件的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！