'Send different email reminders based on dates on multiple columns

I have a list of properties in Col A and different certificates expiry dates in Col G (Gas certificate),H ( Electric certificate),I (EPC),K (Fire alarm),L (Emergency lighting). I have found a script that works perfectly the only problem is that this code only reads one column of dates. If I do a separate code for another Column and so a different certificate, it disable the other one. I suppose it's not possible to have so many different script for the same sheet referring to different columns so it must be within same script but with different conditions. I need it to read all dates and send me an email 30 days before expiry and 2 weeks before expiry and when is expired but with the name of the certificate situated in the header row 2 of the same column.

Below is the script that I have so far but which only reads one column

function emailAlert() {
    // today's date information
    var today = new Date();
    var todayMonth = today.getMonth() + 1;
    var todayDay = today.getDate();
    var todayYear = today.getFullYear();
  
    // 27 days from now
    var twoWeeksFromToday = new Date();
    twoWeeksFromToday.setDate(twoWeeksFromToday.getDate() + 14);
    var twoWeeksMonth = twoWeeksFromToday.getMonth() + 1;
    var twoWeeksDay = twoWeeksFromToday.getDate();
    var twoWeeksYear = twoWeeksFromToday.getFullYear();
  
    // 1 month from now 
    var newToday = new Date()
    var oneMonthFromToday = new Date(newToday.setMonth(newToday.getMonth()+1));
    var oneMonthMonth = oneMonthFromToday.getMonth() + 1;
    var oneMonthDay = oneMonthFromToday.getDate();
    var oneMonthYear = oneMonthFromToday.getFullYear();

    // getting data from spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Compliance");
    var startRow = 3; // First row of data to process
    var numRows = sheet.getLastRow() - 2; // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, 14);
    var data = dataRange.getValues();

    //looping through all of the rows
    for (var i = 0; i < data.length; ++i) {
      var row = data[i];

    var expireDateFormat = Utilities.formatDate(
      new Date(row[6]),
      'ET',
      'dd/MM/yyyy'
    );

    //expiration date information
    var expireDateMonth = new Date(row[6]).getMonth() + 1;
    var expireDateDay = new Date(row[6]).getDate();
    var expireDateYear = new Date(row[6]).getFullYear();

    //checking for today
       if (
       expireDateMonth === todayMonth &&
       expireDateDay === todayDay &&
       expireDateYear === todayYear
      ) {
      var subject =
        row[0] +
        '\n' +
        '-' +
        '\n' +
        ' REMINDER Gas Cert expired: ' +
      expireDateFormat;
      var message =
      ' Arrange with engineer or Landlord ASAP ' ;
      MailApp.sendEmail('[email protected]', subject, message);
    }

     //checking for 2 weeks from now

    Logger.log('2 weeks month, expire month' + twoWeeksMonth + expireDateMonth);
    if (
      expireDateMonth === twoWeeksMonth &&
      expireDateDay === twoWeeksDay &&
      expireDateYear === twoWeeksYear
    ) {
      var subject =
        row[0] +
        '\n' +
        '-' +
        '\n' +
        ' REMINDER Gas Cert ending: ' +
      expireDateFormat;
      var message =
      ' Has it been arranged ? ' ;

      MailApp.sendEmail('[email protected]', subject, message);
      Logger.log('2 weeks from now');
    }

    //checking for 1 month from now
    if (
      expireDateMonth === oneMonthMonth &&
      expireDateDay === oneMonthDay &&
      expireDateYear === oneMonthYear
    )  {
      var subject =
        row[0] +
        '\n' +
        '-' +
        '\n' +
        ' GAS Cert ending: ' +
      expireDateFormat;
      var message =
      ' Email Landlord or organise with gas engineer ' ;

      MailApp.sendEmail('[email protected]', subject, message);
      Logger.log('1 month from now');
    }
  }
}


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source