HATO - statTrackDialog

function stattrackdialog2() {
  var html = HtmlService.createHtmlOutputFromFile('stattrackui'); // this will import the HTML file first
  SpreadsheetApp.getUi()
    .showModalDialog(html, 'Log in Activitiy Record');
}

function stattrackfillin2(form) {
  var app = SpreadsheetApp;
  var test_ss = app.openById("1U78414CxPZoRgqfh_DGVn6Gg0WxO9pw-cBezCb52o6s");
  var test_s = test_ss.getSheetByName("Test");
  var members_s = test_ss.getSheetByName("Members");
  var newhourlog_s = test_ss.getSheetByName("Hour Log");
  var stat_track_s = test_ss.getSheetByName("Stat Track");
  var newhourlog_array = newhourlog_s.getDataRange().getValues();
  var memory = stat_track_s.getRange('D3').getDisplayValue();
  stat_track_s.getRange('D3').setValue(form.hourlogdate).setNumberFormat('dd/mm/yyyy').setHorizontalAlignment("center");
  var date = stat_track_s.getRange('D3').getDisplayValue();
  for (i = 1; i < newhourlog_array[2].length + 1; i++) {
    if (newhourlog_s.getRange(3, i).getDisplayValue() == date) {
      var trigger = 1;
      var date_column = i;
      Logger.log(date_column);
      break
    } else {
      var trigger = 0;
    }
  }
  if (trigger == 1) {
    SpreadsheetApp.getUi().alert("Success", "Now logging data for the selected date ", SpreadsheetApp.getUi().ButtonSet.OK);
    stattrackFunction2(date_column);
  } else {
    stat_track_s.getRange('D3').setValue(memory);
    SpreadsheetApp.getUi().alert("Error", "There is no date match in Hour Log ", SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

function stattrackFunction2(date_column) {
  var app = SpreadsheetApp;
  var test_ss = app.openById("1U78414CxPZoRgqfh_DGVn6Gg0WxO9pw-cBezCb52o6s");
  var stat_track_s = test_ss.getSheetByName("Stat Track");
  var members_s = test_ss.getSheetByName("Members");
  var newhourlog_s = test_ss.getSheetByName("Hour Log");
  var date_array = date_column - 1;
  var date = newhourlog_s.getRange(3, date_column).getDisplayValue();
  var sooc_num = 0;
  var to_num = 0;
  var isu_num = 0;
  var sooc_sum_hour = 0;
  var to_sum_hour = 0;
  var isu_sum_hour = 0;
  for (i = 0; i < newhourlog_array.length; i++) {
    if (newhourlog_array[i][date_array] == "SOOC") {
      var sooc_num = sooc_num + 1;
      var sooc_sum_hour = Number(sooc_sum_hour) + newhourlog_array[i][date_array + 1];
    } else if (newhourlog_array[i][date_array] == "T/O" || newhourlog_array[i][date_array] == "ISU - T/O") {
      var to_num = to_num + 1;
      var to_sum_hour = Number(to_sum_hour) + newhourlog_array[i][date_array + 1];
    } else if (newhourlog_array[i][date_array] == "ISU") {
      var isu_num = isu_num + 1;
      var isu_sum_hour = Number(isu_sum_hour) + newhourlog_array[i][date_array + 1];
    }
  }
  var total_sum_hour = Number(sooc_sum_hour) + Number(to_sum_hour) + Number(isu_sum_hour);
  var new_row = stat_track_s.getLastRow();
  stat_track_s.insertRowAfter(new_row);
  var new_row = new_row + 1;
  stat_track_s.getRange("C" + new_row).setValue(date)
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("D" + new_row).setValue(Number(isu_num))
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("E" + new_row).setValue(Number(to_num))
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("F" + new_row).setValue(Number(sooc_num))
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("G" + new_row).setValue(Number(isu_num) + Number(to_num) + Number(sooc_num))
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("I" + new_row).setValue(isu_sum_hour)
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("J" + new_row).setValue(to_sum_hour)
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("K" + new_row).setValue(sooc_sum_hour)
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("L" + new_row).setValue(total_sum_hour)
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("N" + new_row).setValue(Math.round(Number(isu_sum_hour) / Number(isu_num)))
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("O" + new_row).setValue(Math.round(Number(to_sum_hour) / Number(to_num)))
    .setBorder(true, true, true, true, true, true);
  stat_track_s.getRange("P" + new_row).setValue(Math.round(Number(sooc_sum_hour) / Number(sooc_num)))
    .setBorder(true, true, true, true, true, true);