// this script will notify account recipients if there are 1) no rows have end dates > 24 hours away from the current date OR 2) the only rows with dates > 24 hours from current
// date are marked as TRUE in the Default column
// The script creates an Email Info sheet that is filled out by the PSC then will run at a set interval (1/day) to check the end dates of each row

// this script will notify account recipients if there are 1) no rows have end dates > 24 hours away from the current date OR 2) the only rows with dates > 24 hours from current
// date are marked as TRUE in the Default column
// The script creates an Email Info sheet that is filled out by the PSC then will run at a set interval (1/day) to check the end dates of each row

function alertFunction() {
  var sheetList =  SpreadsheetApp.getActiveSpreadsheet().getSheets();   // Gets the list of sheets in the spreadsheet, not names, will indicate how many there are
  var sheetNumber = sheetList.length
  var sheetNames = []
  
  // Loop will get all the sheet names in the spreadsheet and put those names in the array "sheetNames"
  
  for (var s = 0; s < sheetNumber; s++){
    var sheetList = SpreadsheetApp.getActiveSpreadsheet().getSheets()[s];
    sheetNames[s] = sheetList.getSheetName(); 
  }
  
  // looks to see if Email Info sheet has already been made, if it has not, i.e. first time running, it will insert a sheet with that name in the last index
  var indexSheetNames = sheetNames.indexOf("Email Info")
  if (indexSheetNames > 0 ){
    //SpreadsheetApp.getActiveSpreadsheet().insertSheet('Email Info',sheetNumber+1)
    
    var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Info");
   
    // grabs the user input values for sheet names, column names, and email list
    var CreativeSheetNames = emailSheet.getRange(1,2).getValue();
    var sheetString = CreativeSheetNames.toString().replace(", ",",")
    var defaultColumnInput = emailSheet.getRange(3,2).getValue();
    var defaultColumnInput = defaultColumnInput.replace(", ",",");
    var endDateColumnInput = emailSheet.getRange(5,2).getValue();
    var endDateColumnInput = endDateColumnInput.replace(", ",",");
    var StartDateColumnInput = emailSheet.getRange(7,2).getValue();
    var StartDateColumnInput = StartDateColumnInput.replace(", ",",");
    
    var sheetNameSplit = CreativeSheetNames.split(',')  // names of sheet separated by commas
    var NumCreativeSheets = sheetNameSplit.length
    var sheetNamesTemp =[]
    sheetNamesTemp[0] = sheetNameSplit[0]
    for (var x = 1; x < NumCreativeSheets;x++){
      var SheetNamesTemporary = sheetNameSplit[x].replace(" ","");
      sheetNamesTemp[x] = SheetNamesTemporary
    }
    var sheetNameSplit = sheetNamesTemp
    var defaultColumnNameOriginal = defaultColumnInput.split(',');  // names of default column names separated by commas
    var defaultColumnLength = defaultColumnNameOriginal.length
    var defaultColumnName = []
    var endDateNameOriginal = endDateColumnInput.split(',');  // names of end date column names separated by commas
    var endDateLength = endDateNameOriginal.length
    var endDateName = []
    var StartDateNameOriginal = StartDateColumnInput.split(',');  // names of end date column names separated by commas
    var StartDateLength = StartDateNameOriginal.length
    var StartDateName = []
    
    var errors = 0
    var activeRows = 0  // initializes number of active rows
    var index = 0
    var inactiveSheets = [] // initializes list of active sheets, indexed by above variable index
    
    //  This is an error check to see if the sheet names have been input properly, if they have not an error box is shown and script ends.
    for (var i=0;i<NumCreativeSheets;i++){
      var inputName = sheetNameSplit[i]
      var indexName = sheetNames.indexOf(inputName)
      if (indexName < 0){
        Browser.msgBox("Check your sheet name.  Copy and paste directly and remember these values are case sensitive, i.e. hello does not equal HELLO.")  
        errors = 1
      }
    }
    
    //  This is an error check to see if emails have been entered into, if they have not an error box is shown and script ends.
    var emailCheck = emailSheet.getRange(7,2).getValues();
    if (emailCheck == ""){
      Browser.msgBox("You have not entered any email addresses to notify, please fill in the appropriate contacts.")
      errors = 1
    }

    //  This is an error check to see if the default names have been input properly, if they have not an error box is shown and script ends.
    for (var d=0;d<NumCreativeSheets;d++){
      if (defaultColumnLength == 1){
        for (var q = 0; q<NumCreativeSheets;q++){
          defaultColumnName[q] = defaultColumnNameOriginal[0]
        }
      }
      else {
        for (var q = 0; q<NumCreativeSheets; q++){
          defaultColumnName[q] = defaultColumnNameOriginal[q]
        }
      }
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetNameSplit[d])
      var sheetInUse = sheetNameSplit[d]
      var defaultNameInUse = defaultColumnName[d]
      if (defaultNameInUse != "none"){
        var lastColumn = sheet.getLastColumn();
        var row1 = sheet.getRange(1,1,1,lastColumn).getValues();
        var headerValues = row1[0]
        var defaultCheckIndex = headerValues.indexOf(defaultColumnName[d])
        if (defaultCheckIndex < 0){
          Browser.msgBox("Check your default column name.  Copy and paste directly and remember these values are case sensitive, i.e. hello does not equal HELLO.")
          errors = 1
        }     
      }
    }
    
    //  This is an error check to see if the start date names have been input properly, if they have not an error box is shown and script ends.
    for (var e=0;e<NumCreativeSheets;e++){
      if (StartDateLength == 1){
        for (var s = 0; s<NumCreativeSheets;s++){
          StartDateName[s] = StartDateNameOriginal[0]
        }
      }
      else {
        for (var s = 0; s<NumCreativeSheets; s++){
          StartDateName[s] = StartDateNameOriginal[s]
        }
      }
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetNameSplit[e])
      var sheetInUse = sheetNameSplit[e]
      var StartDateNameInUse = StartDateName[e]
      var lastColumn = sheet.getLastColumn();
      var row1 = sheet.getRange(1,1,1,lastColumn).getValues();
      var headerValues = row1[0]
      var StartDateCheckIndex = headerValues.indexOf(StartDateName[e])
      if (StartDateCheckIndex < 0){
          Browser.msgBox("Check your start date column name.  Copy and paste directly and remember these values are case sensitive, i.e. hello does not equal HELLO.")
        errors = 1
      }     
    }
    
       //  This is an error check to see if the start date names have been input properly, if they have not an error box is shown and script ends.
    for (var e=0;e<NumCreativeSheets;e++){
      if (endDateLength == 1){
        for (var s = 0; s<NumCreativeSheets;s++){
          endDateName[s] = endDateNameOriginal[0]
        }
      }
      else {
        for (var s = 0; s<NumCreativeSheets; s++){
          endDateName[s] = endDateNameOriginal[s]
        }
      }
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetNameSplit[e])
      var sheetInUse = sheetNameSplit[e]
      var EndDateNameInUse = endDateName[e]
      var lastColumn = sheet.getLastColumn();
      var row1 = sheet.getRange(1,1,1,lastColumn).getValues();
      var headerValues = row1[0]
      var endDateCheckIndex = headerValues.indexOf(endDateName[e])
      if (endDateCheckIndex < 0){
          Browser.msgBox("Check your end date column name.  Copy and paste directly and remember these values are case sensitive, i.e. hello does not equal HELLO.")
        errors = 1
      }     
    }
    
    if (errors == 0){
      emailSheet.getRange(1,2).setBackground("#00FF00");
      emailSheet.getRange(3,2).setBackground("#00FF00");
      emailSheet.getRange(5,2).setBackground("#00FF00");
      emailSheet.getRange(7,2).setBackground("#00FF00");
      emailSheet.getRange(9,2).setBackground("#00FF00");
    }
    
    // if all checks pass, then the loop below grabs the end date for each row in the creative sheet(s), checks if it is > 24 hours apart, if it is adds 1 to active rows which 
    // indicates the sheet has an active row.  If no active rows, then the sheet name is added to the array inactivesheets
    // the loop runs through each sheet name the user input and will keep track of the sheets that do not have active rows
    if (errors == 0){
      for (var c = 0; c< NumCreativeSheets;c++){  
        var sheetInUse = sheetNameSplit[c]
        var defaultNameInUse = defaultColumnName[c]
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetNameSplit[c])
        var lastColumn = sheet.getLastColumn();
        var lastRow = sheet.getLastRow();
        var row1 = sheet.getRange(1,1,1,lastColumn).getValues();
        var headerValues = row1[0]
        var endDateIndex = headerValues.indexOf(endDateName[c]) + 1
        var startDateIndex = headerValues.indexOf(StartDateName[c]) + 1
        var defaultIndex = headerValues.indexOf(defaultNameInUse)
        if (defaultIndex > 0 ){
          var defaultIndex = defaultIndex + 1
          for (var v = 2; v < lastRow + 1; v ++){   
            var endDateValue = sheet.getRange(v,endDateIndex).getValue();
            var startDateValue = sheet.getRange(v,startDateIndex).getValue();
            var currentDate = new Date();
            if (endDateValue == ""){
              endDateValue = currentDate
            }
            if (startDateValue == ""){
              startDateValue = currentDate
            }
            var diffEndDate = endDateValue - currentDate
            var diffStartDate = startDateValue - currentDate
            var defaultValue = sheet.getRange(v,defaultIndex).getValue();
            if (diffEndDate >= 86400000 & defaultValue == false & diffStartDate <= 0){
              activeRows = 1
            }
          }
        }
        if (defaultIndex == -1){
          for (var v = 2; v < lastRow + 1; v ++){   
            var endDateValue = sheet.getRange(v,endDateIndex).getValue();
            var startDateValue = sheet.getRange(v,startDateIndex).getValue();
            var currentDate = new Date();
            if (endDateValue == ""){
              endDateValue = currentDate
            }
            if (startDateValue == ""){
              startDateValue = currentDate
            }
            var diffEndDate = endDateValue - currentDate
            var diffStartDate = startDateValue - currentDate
            if (diffEndDate >= 86400000 & diffStartDate <= 0){
              activeRows = 1
            }
          }
        }
        if (activeRows == 0){
          inactiveSheets[index] = sheetNameSplit[c]
          index = index + 1
        }
        activeRows = 0 
      }

      // this section sends the email if there are sheets with no active rows. it uses the inactivesheets array to determine if there are or are not active rows in the feed
      
      var emails = emailSheet.getRange(9,2).getValue();
      var notificationRequest = emailSheet.getRange(15,2).getValue().toLowerCase();
      if (notificationRequest == "yes"){
        emailSheet.getRange(15,2).setBackground("red")
      }
      else {
        emailSheet.getRange(15,2).setBackground("white")
      }
        
      var emailAddresses = emails.replace(", ",",");
      if (inactiveSheets.length > 0 & notificationRequest != "yes"){
        var spreadsheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
        var spreadsheetURL = SpreadsheetApp.getActiveSpreadsheet().getUrl();
        var subject = "Houston, we have a problem!"
        var inactiveSheetsFinal = ""
        for (var x = 0; x<inactiveSheets.length;x++){
          var num = x + 1
          inactiveSheetsFinal =  inactiveSheetsFinal + "" + num +")" + " " + inactiveSheets[x] + "<br></br>"
        }
        var inactiveSheetsFinal = inactiveSheetsFinal.toString().replace(",","");
        var message = "<html><BODY>"
        + "<p>You are receiving this notification because end dates in your dynamic creative feed, "
        + "<a href = \""+ spreadsheetURL + "\" target=\"_blank\">"
        + spreadsheetName
        + "</a>"
        + ", have already expired or will expire soon. This means that default content is or will be serving, instead of  dynamic content. </p>"
        + "<p> The following feeds contain content that is expiring: "
        + "<br></br>"
        + inactiveSheetsFinal
        + "<p><b> Action Needed</b></p>"
        + "<p>Follow these instructions to resolve this issue: <p>"
        + "<p>1) Click "
        + "<a href = \""+ spreadsheetURL + "\" target=\"_blank\">this link</a> to open your feed.</p>"
        + "<p>2) Update the end date for any non-default row that should not expire <p>"
        + "<p>3) Create a draft of the profile <p>"
        + "<p>4) Retransform the feed <p>"
        + "<p>5) Publish the profile <p>"
        + "<p> Visit the "
        + "<a href = \"https://support.google.com/richmedia/answer/2871724?hl=en&ref_topic=6010408\" target=\"_blank\">help center</a> for more information.</p>"
        + "<p> If this is the expected behavior, please ignore this alert. <p>"
        + "<br></br>"
        + "<p>Thanks!</p>";
        MailApp.sendEmail(emailAddresses, subject, "", {htmlBody:message});
        
        var metrics_sheet = SpreadsheetApp.openById("1t8G89qg5rTYQg4k9wiDqtOs4RyZFJXikrpb6hlOAo0Q").getSheetByName("Metrics");
        var metrics_last_row = metrics_sheet.getLastRow();
        var current_DATE = new Date();
        var spreadsheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
        for (var r = 1;r<metrics_last_row+1;r++){
          var feedName = metrics_sheet.getRange(r,3).getValue();
          if (feedName == spreadsheetName){
            var row = r
            }
        }
        var emailSent = metrics_sheet.getRange(row,4).getValue();
        metrics_sheet.getRange(row,4).setValue(emailSent + 1)
        metrics_sheet.getRange(row,5).setValue(current_DATE)
      }  
    }
  }
  else {
    SpreadsheetApp.getActiveSpreadsheet().insertSheet('Email Info',sheetNumber+1)
    var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email Info");
    var sheetNameQuestion = "Name of the sheet with Start and End dates: (copy and paste name directly, value is case sensitive)";
    var defaultColumnQuestion = "Name of the default column: (copy and paste directly, value is case sensitive).  If no default column type \"none\"";
    var endDateQuestion = "Name of the end date column: (copy and paste directly, value is case sensitive)";
    var startDateQuestion = "Name of the start date column: (copy and paste directly, value is case sensitive)";
    var emailList = "Email addresses for notifications: (separated by commas)"
    var notificationRequest = "Stop receiving email notifications (\“yes\“ to stop emails or leave blank to continue emails)"
    
    // formats the email info sheet, does not erase values if this is not the first time it has run
    emailSheet.getRange(1, 1).setWrap(true);
    emailSheet.getRange(3, 1).setWrap(true);
    emailSheet.getRange(5, 1).setWrap(true);
    emailSheet.getRange(7, 1).setWrap(true);
    emailSheet.getRange(9, 1).setWrap(true);
    emailSheet.getRange(15, 1).setWrap(true);
    emailSheet.setColumnWidth(1,400)
    emailSheet.setColumnWidth(2,600)
    emailSheet.getRange(1,1).setBackground("red")
    emailSheet.getRange(3,1).setBackground("red")
    emailSheet.getRange(5,1).setBackground("red")
    emailSheet.getRange(7,1).setBackground("red")
    emailSheet.getRange(9,1).setBackground("red")
    emailSheet.getRange(15,1).setBackground("red")
    emailSheet.getRange(1,2).setBackground("yellow")
    emailSheet.getRange(3,2).setBackground("yellow")
    emailSheet.getRange(5,2).setBackground("yellow")
    emailSheet.getRange(7,2).setBackground("yellow")
    emailSheet.getRange(9,2).setBackground("yellow")
    emailSheet.getRange(15,2).setBackground("yellow")
    emailSheet.getRange(1,1).setBorder(true,true,true,true,true,true)
    emailSheet.getRange(3,1).setBorder(true,true,true,true,true,true)
    emailSheet.getRange(5,1).setBorder(true,true,true,true,true,true)
    emailSheet.getRange(7,1).setBorder(true,true,true,true,true,true)
    emailSheet.getRange(9,1).setBorder(true,true,true,true,true,true)
    emailSheet.getRange(15,1).setBorder(true,true,true,true,true,true)
    
    emailSheet.getRange(1, 1).setValue(sheetNameQuestion);
    emailSheet.getRange(3, 1).setValue(defaultColumnQuestion);
    emailSheet.getRange(5,1).setValue(endDateQuestion)
    emailSheet.getRange(7,1).setValue(startDateQuestion)
    emailSheet.getRange(9,1).setValue(emailList)
    emailSheet.getRange(15,1).setValue(notificationRequest)

    var metrics_sheet = SpreadsheetApp.openById("1t8G89qg5rTYQg4k9wiDqtOs4RyZFJXikrpb6hlOAo0Q").getSheetByName("Metrics");
    var metrics_last_row = metrics_sheet.getLastRow();
    var installing_user = Session.getActiveUser().getEmail();
    var current_DATE = new Date();
    var spreadsheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
    
    metrics_sheet.getRange(metrics_last_row+1,1).setValue(installing_user);
    metrics_sheet.getRange(metrics_last_row+1,2).setValue(current_DATE);
    metrics_sheet.getRange(metrics_last_row+1,3).setValue(spreadsheetName);
    metrics_sheet.getRange(metrics_last_row+1,4).setValue(0);

  } 
}