CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
CristianCapsuna
CristianCapsuna

Reputation: 452

Function works as intended but when converted into an onEdit() function it doesn't run till the end

I have made this nice code which does the job if I fire it from the script editor, which is to check some dates and send out some e-mails if some dates are too close to each other, but if I convert it into an onEdit() function and do an edit it launched but doesn't finishes which is such a shame. The code I am using is below. Does anyone know why it does this?

function onEdit1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var readMeSheet = ss.getSheetByName("ReadMe");
  var today = new Date();
  readMeSheet.getRange(37,1).setValue(Utilities.formatDate(today,"GMT", "dd-MMM-yyyy"));
  var dateOfLastCheck = new Date(readMeSheet.getRange(37,2).getValue());
  var overdueCheck = Math.floor(today.getTime()-dateOfLastCheck.getTime());
  var days = (1000*60*60*24);
  var temp = overdueCheck/days;

  if ( overdueCheck/days > 2 ) {
  readMeSheet.getRange(37,2).setValue(Utilities.formatDate(today,"GMT", "dd-MMM-yyyy"));
  var lastActiveSheet = ss.getActiveSheet().getName();
  ss.setActiveSheet(ss.getSheetByName('Active Jobs'));
  var fEmptyRow = getFirstEmptyRow('Active Jobs');

  for ( i=2; i<fEmptyRow; i++ ){
  var jobDate = new Date(ss.getActiveSheet().getRange(i,15).getValue());
  var dif = Math.floor(jobDate.getTime()-today.getTime());
    if ( dif/days < 3) {
      switch(ss.getActiveSheet().getRange(i,8).getValue()){
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
          var emailExtension = "@*******.com";
          break
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
        case "*******":
          var emailExtension = "@*******.*******.com";
          break
      }

      switch(ss.getActiveSheet().getRange(i,8).getValue()){
          case "*******":
          case "*******":
          case "*******":
          case "*******":
          case "*******":
              var teamLeader = ", *******@*******.com";
              break
          case "*******":
          case "*******":
          case "*******":
          case "*******":
              var teamLeader = ", *******@*******.com";
              break
          case "*******":
          case "*******":
              var teamLeader = ", *******@*******.com";
              break
      }

                         if (MailApp.getRemainingDailyQuota() > 0) {
                            //GmailApp.sendEmail(ss.getActiveSheet().getRange(i,8).getValue() + emailExtension + teamLeader, 'Please be aware the job ID'+ss.getActiveSheet().getRange(i,1).getValue()+' less than 3 days away from the report out date',
                              GmailApp.sendEmail("*******@*******.com", 'Please be aware the job ID'+ss.getActiveSheet().getRange(i,1).getValue()+' less than 3 days away from the report out date',
                              'Hello, could you please amend the job if it is not on schedule to complete within the next 3 days and inform the PAT leader? If everything is on track please disregard this e-mail. :)')
                                                                   }  
                                                                 }
                                }
  ss.setActiveSheet(ss.getSheetByName(lastActiveSheet));
}
}

function getFirstEmptyRow(sheet) {
  var spr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);
  var column = spr.getRange(2,1,spr.getMaxRows()-1,1);
  var values = column.getValues(); // get all data in one call
  for(var ct = values.length-1; ct > -1; ct--){
    if(values[ct][0] != ""){
      var emptyRow = ct + 3;
      break;
    }
  }
  return emptyRow;
}

Upvotes: 1

Views: 82

Answers (2)

user6655984
user6655984

Reputation:

You should know the difference between simple triggers, which are functions named in a specific way like onEdit, and installable triggers that you create. As Documentation says,

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions: [...] They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization

If this appears strange to you, imagine if there was no such restriction; then when someone created a spreadsheet with onEdit trigger and shared it with you, the trigger would start doing things like sending/reading email from your account, without your knowledge.

An installable trigger has the authorization of the user who creates the trigger, who can authorize it to send email, etc, from their account.

Upvotes: 1

iJay
iJay

Reputation: 4273

1st thing: Change your method name to onEdit() [Not onEdit1] and try

Or you can configure a trigger manually.

Edit > Current project's triggers

Read further: https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually

Upvotes: 1

Related Questions