CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Richard Selwyn
Richard Selwyn

Reputation: 1

How to trigger google sheets function on specific cell change (or triggered by time)

I'm trying to trigger a function (send email) when a cell changes on sheets.

At present, my code looks like this.

  function RealtimeFeedbackdue() {

  var remainingHoursRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("F5"); 
var remainingHours = remainingHoursRange.getValue();
var studentNameRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("B1"); 
var studentName = studentNameRange.getValue();
// Check total hours
if (remainingHours < 6){

// Fetch the email address

var emailAddressRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("B5"); 
var emailAddress = emailAddressRange.getValue();

var emailAddressRange2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("B6"); 
var emailAddress2 = emailAddressRange2.getValue();

// Send Alert Email for teacher.
var message =  studentName + ' has ' + remainingHours + ' hours left of their course. This means their realtime feedback report is due. ' +
'This is an automatically generated email.' ;
var subject = 'Realtime feedback report due for ' + studentName;
MailApp.sendEmail(emailAddress, subject, message);

// Send Alert Email for course consultant.
var message2 =  studentName + ' has ' + remainingHours + ' hours left of their course. Their teacher will be completing the realtime feedback report this week. Please speak to them regarding course renewal etc. This is an automatically generated email.' ;
var subject2 = 'Private student ' + studentName + ' has nearly completed their hours';
MailApp.sendEmail(emailAddress2, subject2, message2);

}
}

I would settle for having a weekly trigger but I can't work out how to program that either. I've tried this.

function RealtimeFeedbackdue() {

  ScriptApp.newTrigger('RealtimeFeedbackdue')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();

  var remainingHoursRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("F5");


  var remainingHours = remainingHoursRange.getValue();

  var studentNameRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("B1");


  var studentName = studentNameRange.getValue();

  // Check total hours
  if (remainingHours < 6) {

    // Fetch the email address

    var emailAddressRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("B5");
    var emailAddress = emailAddressRange.getValue();

    var emailAddressRange2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Attendance").getRange("B6");
    var emailAddress2 = emailAddressRange2.getValue();

    // Send Alert Email for teacher.
    var message = studentName + ' has ' + remainingHours + ' hours left of their course. This means their realtime feedback report is due. ' +
      'This is an automatically generated email.';
    var subject = 'Realtime feedback report due for ' + studentName;
    MailApp.sendEmail(emailAddress, subject, message);

    // Send Alert Email for course consultant.
    var message2 = studentName + ' has ' + remainingHours + ' hours left of their course. Their teacher will be completing the realtime feedback report this week. Please speak to them regarding course renewal etc. This is an automatically generated email.';
    var subject2 = 'Private student ' + studentName + ' has nearly completed their hours';
    MailApp.sendEmail(emailAddress2, subject2, message2);

  }
}
}

Upvotes: 0

Views: 1708

Answers (1)

ale13
ale13

Reputation: 6072

Since you want to trigger the execution of the function based on an edit of the cell, the most appropriate trigger in this situation is the onEdit one.

Therefore, you can use something similar to this:

Snippet

function onEdit(e){
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var sheet = e.range.getSheet();
  
  if (sheet.getName() == 'NAME_OF_THE_SHEET' && row == NUMBER_OF_ROW && col == NUMBER_OF_COL)
    // if the edit was made in the wanted cell
    // add the instructions here
}

Explanation

The above function makes use of the e event in object in order to get the data needed about the edited cell.

Snippet 2

However, you can also use a time-based trigger, but this won't have anything to do with the edited cell - it will just trigger every Monday at the time you chose.

function theTimeTrigger() {
   ScriptApp.newTrigger('RealtimeFeedbackdue')
     .timeBased()
     .onWeekDay(ScriptApp.WeekDay.MONDAY)
     .atHour(9)
     .create();
}

Explanation

For this, a new function was needed in order to create the time-based trigger and associate it to the function you already have.

Create triggers from the UI

You can also create triggers using the Apps Script's UI. This can be done by clicking this icon which will redirect you to the current project's triggers.

current project triggers

Afterwards, you can click this button in order to create a new trigger and you customize it in the way you want.

add trigger

Reference

I also suggest you to take a look at these links below since they might be of help to you in your future development.

Upvotes: 2

Related Questions