Google sheet script to send email

escalate_ticket.js

function escalate_ticket(event)
{ 
  sheet = SpreadsheetApp.getActiveSheet()
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  // 0 indexed. Col 6 contains email? Enter 5 here
  var email_col_num = 5
  var is_tat_breached_col_num = 3
  var comment_col_num = 4
  
  var emails_to_send = {}
  
  for(var i=0; i<values.length; i++)
  {
    email = null
    is_tat_breached = null
    comment = null

    for(var j=0; j<values[i].length; j++)
    {
      
      if(j == is_tat_breached_col_num)
      {
        is_tat_breached = values[i][j]
      }
      else if(j == email_col_num)
      {
        email = values[i][j]
      }
      else if(j == comment_col_num)
      {
        comment = values[i][j]
      }
        
    }
    if(email != null && is_tat_breached == 'yes' || is_tat_breached == 'Yes')
    {
      Logger.log("send email to : " + email + " with content: " + comment)
      if(emails_to_send[email] == undefined)
        emails_to_send[email] = [i]
      else
        emails_to_send[email].push(i)
    }
    
  }
  
  for(var email in emails_to_send)
  {
    timed_out_tickets = emails_to_send[email].join(", ")
    MailApp.sendEmail(email, "Support tickets TAT breached", "The following ticket's TAT has been breached: " + timed_out_tickets)
  }
}

Checks the value of a particular column in every row and sends an email based on the condition. Add this as a 'trigger' in your google sheet if you want to run this every hour or so