CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Cris Girardi
Cris Girardi

Reputation: 67

Script sendNotification doens't work correctly

I'm trying to make a script to send an email every day , after checking in a spreadsheet, two conditions. If the first condition for True , the data to e- mail will be " subject1 ", otherwise, will be " subject2 ".

The script works correctly when I run manually , however, through the trigger, the value I would like to send via email, simply does not appear in the body.

I can not understand what could be wrong if manually , works perfectly . The trigger is "time-driven/hour time/every 12 hours". Can anyone help me?

This is the code:

function sendNotificationD() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var cellvalue = ss.getActiveCell().getValue().toString();

  var column = ss.getRange('o13');
  var cell = sheet.getRange('o13');
  var value = column.getValue();
  var emailSheet = ss.getSheetByName("totais");
  var emails = emailSheet.getRange("q1:s1").getValues();

  var range_bill_com = sheet.getRange('o13');

  if (range_bill_com.getValue() !='CONGRATS'){
    var recipients = emails;
    var message = '';
    var subject = 'subject1 here';
    var body = 'IMPORTANT: ' + '\n\n' + 'your goal is' + ' ' + value;
    MailApp.sendEmail(recipients, subject, body);
    }

   if (range_bill_com.getValue() =='CONGRATS'){
    var recipients = emails;
    var message = '';
    var subject = 'subject2 here';
    var body = 'you have reached your goal';
    MailApp.sendEmail(recipients, subject, body);
  }
};

Upvotes: 1

Views: 72

Answers (1)

user3075569
user3075569

Reputation:

At this line:

var sheet = ss.getActiveSheet();

The method getActiveSheet() gets the the active sheet that is being displayed in the spreadsheet UI when a user opens it, but when it runs from a time-driven trigger there is no active sheet, so you need to indicate the sheet, you can use getSheetByName() or getSheets() the line should look like this:

var sheet = ss.getSheetByName("Sheet1");

or

var sheet = ss.getSheets()[0]; // Index of the tab, 0 will return the first one

The function will still work if you fire it manually or from a trigger.

Upvotes: 2

Related Questions