Advertisement
RUZBE

HATO - hourLog

Feb 17th, 2019
119
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function openDialog() {
  2.   var html = HtmlService.createHtmlOutputFromFile('Index');
  3.   SpreadsheetApp.getUi()
  4.     .showModalDialog(html, 'Activity Check');
  5. }
  6.  
  7. function fillin(form) {
  8.   var app = SpreadsheetApp;
  9.   var test_ss = app.openById("1U78414CxPZoRgqfh_DGVn6Gg0WxO9pw-cBezCb52o6s");
  10.   var test_s = test_ss.getSheetByName("Test");
  11.   var members_s = test_ss.getSheetByName("Members");
  12.   var newhourlog_s = test_ss.getSheetByName("Hour Log");
  13.   creatlog();
  14.   var set_executor = newhourlog_s.getRange("F2").setValue(form.Executor).setHorizontalAlignment("center")
  15.     .setBorder(true, true, true, true, true, true); //put in executor, fix the alignment within the cell; need updates subjected to sheet changes
  16.   var set_syncdate = newhourlog_s.getRange("F3").setValue(form.syncdate).setNumberFormat('dd/mm/yyyy').setHorizontalAlignment("center")
  17.     .setBorder(true, true, true, true, true, true);
  18. }
  19.  
  20. function creatlog() {
  21.   var app = SpreadsheetApp;
  22.   var test_ss = app.openById("1U78414CxPZoRgqfh_DGVn6Gg0WxO9pw-cBezCb52o6s");
  23.   var test_s = test_ss.getSheetByName("Test");
  24.   var members_s = test_ss.getSheetByName("Members");
  25.   var newhourlog_s = test_ss.getSheetByName("Hour Log");
  26.   var newhourlog_array = newhourlog_s.getDataRange().getValues();
  27.   newhourlog_s.insertColumnsAfter(5, 3);
  28.   newhourlog_s.setColumnWidth(8, 10);
  29.   newhourlog_s.setColumnWidth(6, 110);
  30.   newhourlog_s.setColumnWidth(7, 70);
  31.   for (i = 0; i < newhourlog_array.length; i++) {
  32.     if (newhourlog_array[i][1] == 'Senior Officer on Call') {
  33.       var sooc_row1 = i + 3;
  34.       var sooc_sec = i + 2;
  35.       for (o = sooc_row1 - 1; o < newhourlog_array.length; o++) {
  36.         if (newhourlog_array[o][2] < 1) {
  37.           var sooc_rowl = o;
  38.           break
  39.         }
  40.       }
  41.       break
  42.     }
  43.   }
  44.   for (i = 0; i < newhourlog_array.length; i++) {
  45.     if (newhourlog_array[i][1] == 'Traffic Officers') {
  46.       var to_row1 = i + 3;
  47.       var to_sec = i + 2;
  48.       for (o = to_row1 - 1; o < newhourlog_array.length; o++) {
  49.         if (newhourlog_array[o][2] < 1) {
  50.           var to_rowl = o;
  51.           break
  52.         }
  53.       }
  54.       break
  55.     }
  56.   }
  57.   for (i = 0; i < newhourlog_array.length; i++) {
  58.     if (newhourlog_array[i][1] == 'Incident Support Units') {
  59.       var isu_row1 = i + 3;
  60.       var isu_sec = i + 2;
  61.       var isu_rowl = newhourlog_s.getLastRow();
  62.       break
  63.     }
  64.   }
  65.   newhourlog_s.getRange("F" + sooc_sec + ":G" + sooc_sec).setBackground("#b7b7b7").setFontWeight("bold").setValues([
  66.       ["Rank ATM", "Hours"]
  67.     ])
  68.     .setBorder(true, true, true, true, true, true);
  69.   newhourlog_s.getRange("F" + to_sec + ":G" + to_sec).setBackground("#b7b7b7").setFontWeight("bold").setValues([
  70.       ["Rank ATM", "Hours"]
  71.     ])
  72.     .setBorder(true, true, true, true, true, true);
  73.   newhourlog_s.getRange("F" + isu_sec + ":G" + isu_sec).setBackground("#b7b7b7").setFontWeight("bold").setValues([
  74.       ["Rank ATM", "Hours"]
  75.     ])
  76.     .setBorder(true, true, true, true, true, true);
  77.   newhourlog_s.getRange("F" + sooc_row1 + ":G" + sooc_rowl).setBackground("#efefef").setBorder(true, true, true, true, true, true);
  78.   newhourlog_s.getRange("F" + to_row1 + ":G" + to_rowl).setBackground("#efefef").setBorder(true, true, true, true, true, true);
  79.   newhourlog_s.getRange("F" + isu_row1 + ":G" + isu_rowl).setBackground("#efefef").setBorder(true, true, true, true, true, true);
  80.   var sooc_rank = newhourlog_s.getRange("D" + sooc_row1 + ":D" + sooc_rowl).getValues();
  81.   var to_rank = newhourlog_s.getRange("D" + to_row1 + ":D" + to_rowl).getValues();
  82.   var isu_rank = newhourlog_s.getRange("D" + isu_row1 + ":D" + isu_rowl).getValues();
  83.   var sooc_colour = newhourlog_s.getRange("D" + sooc_row1 + ":D" + sooc_rowl).getBackgrounds();
  84.   var to_colour = newhourlog_s.getRange("D" + to_row1 + ":D" + to_rowl).getBackgrounds();
  85.   var isu_colour = newhourlog_s.getRange("D" + isu_row1 + ":D" + isu_rowl).getBackgrounds();
  86.   newhourlog_s.getRange("F" + sooc_row1 + ":F" + sooc_rowl).setValues(sooc_rank).setBackgrounds(sooc_colour);
  87.   newhourlog_s.getRange("F" + to_row1 + ":F" + to_rowl).setValues(to_rank).setBackgrounds(to_colour);
  88.   newhourlog_s.getRange("F" + isu_row1 + ":F" + isu_rowl).setValues(isu_rank).setBackgrounds(isu_colour);
  89.   var merge1 = newhourlog_s.getRange('F2:G2').merge().setBackgroundRGB(90, 105, 122).setFontColor('white'); //executor and date, need updates subjected to sheet changes
  90.   var merge2 = newhourlog_s.getRange('F3:G3').merge().setBackgroundRGB(90, 105, 122).setFontColor('white');
  91. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement