CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Tiziano TEI
Tiziano TEI

Reputation: 15

Trigger a google Sheet

Following this script Google Sheet - Send Email

I changed it a while and now it just loops every data in my sheet and then send the email.

The problem is that if I click the RUN button, it works, i receive the email.

But if I setup a trigger to that project, it doesn't work. The trigger looks executed without any error, but no emails are delivered.

Can someone explain why ?

this is the working code

 function SendEmailAlerts() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 20; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 30)
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var Ticker = row[1]; // Second column
    var currentPrice = row[2]; // 2 column
    var firstEntryLimit = row[9]; // 9 column
    var secondEntryLimit = row[8]; // 8 column
    var emailAddress = row[11]; // 11 column
    var ratioPick = row[13]; // 13 column
    var riskRatio = row[14]; // 14 column
    var rewardRatio = row[15]; // 15 column
    var FirstTarget = row[4]; // 4 column
    var rewardRatio2 = row[16]; // 16 column
    var FirstTarget2 = row[5]; // 5 column
    var FirstStop = row[6]; // 6 column
    var message = 'Ticker : #' + Ticker + '.\nEntry Price is any price around $' + firstEntryLimit + '.\nFirst Target : $'+FirstTarget+' (Reward '+rewardRatio+').\nSecond Target : $'+FirstTarget2+' (Reward '+rewardRatio2+').\nFirst Stop : $'+FirstStop+' (Risk '+riskRatio+').\nSignal ratio : '+ratioPick+'.';
    var subject = 'Alert on ' + Ticker + '';
    if (currentPrice < firstEntryLimit){
          if (currentPrice > secondEntryLimit){
            MailApp.sendEmail(emailAddress, subject, message);
          }
      }
  }
}

I already tried setting up the trigger in different hours this is the trigger

Upvotes: 1

Views: 109

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

The problem is using getActiveSheet() in conjunction with a time-driven trigger

  • Time-driven triggers are independent of the UI and thus of the currently open sheet
  • The will use a default sheet which might not be the one you have your data of interrest in

Solution

Replace

var sheet = SpreadsheetApp.getActiveSheet();

by

var sheet = SpreadsheetApp.openById(id).getSheetByName(name);

specifying the id and name of the spreadsheet and sheet you want the script to run on.

Upvotes: 2

Related Questions