CODEWITHSUNDEEP
google-apps-script
Taryn Grant
Taryn Grant

Reputation: 25

How do I loop through rows to check a value in Google Apps Script

My script checks the value of a cell, and depending on the value, it triggers an email. I need it to loop through the rows instead of apply to a specific cell. Code works perfectly, but it must check all the values in that column.

function CheckCalStatus() {
var CalStatusRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("Q2"); 
var CalStatus = CalStatusRange.getValue();
if (CalStatus == "INVITED"){
var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("P2");
var emailAddress = emailRange.getValues();

// Send Alert Email.
var message = 'Respond to your invite'; 
var subject = 'A Calendar Invite is waiting for your response';
MailApp.sendEmail(emailAddress, subject, message);
}

}

Upvotes: 1

Views: 4955

Answers (1)

shabnam bharmal
shabnam bharmal

Reputation: 584

function CheckCalStatus() {

  var CalStatusData=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getValues();

  //Loops through each row of the sheet
  for(var i=0;i<CalStatusData.length;i++)
  {
    //Takes data of Q Column
    var CalStatus=CalStatusData[i][16];
    if (CalStatus == "INVITED"){

      //Takes data of P Column
      var emailAddress =CalStatusData[i][15];
      // Send Alert Email.
      var message = 'Respond to your invite'; 
      var subject = 'A Calendar Invite is waiting for your response';
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Upvotes: 2

Related Questions