Trying To Make A Script That Scans A Google Sheet And Emails If Values Are Below 60%

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 inputt

Solution 1:

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) {
    varrange = e.range;

    if (range.getColumn() >= 10) { // Only check column I and upvar 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 = 'john.doe@example.com';
                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.

functionassessmentOnChange(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);
}