Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function openDialog() {
- var html = HtmlService.createHtmlOutputFromFile('Index');
- SpreadsheetApp.getUi()
- .showModalDialog(html, 'Activity Check');
- }
- function fillin(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");
- creatlog();
- var set_executor = newhourlog_s.getRange("F2").setValue(form.Executor).setHorizontalAlignment("center")
- .setBorder(true, true, true, true, true, true); //put in executor, fix the alignment within the cell; need updates subjected to sheet changes
- var set_syncdate = newhourlog_s.getRange("F3").setValue(form.syncdate).setNumberFormat('dd/mm/yyyy').setHorizontalAlignment("center")
- .setBorder(true, true, true, true, true, true);
- }
- function creatlog() {
- 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 newhourlog_array = newhourlog_s.getDataRange().getValues();
- newhourlog_s.insertColumnsAfter(5, 3);
- newhourlog_s.setColumnWidth(8, 10);
- newhourlog_s.setColumnWidth(6, 110);
- newhourlog_s.setColumnWidth(7, 70);
- for (i = 0; i < newhourlog_array.length; i++) {
- if (newhourlog_array[i][1] == 'Senior Officer on Call') {
- var sooc_row1 = i + 3;
- var sooc_sec = i + 2;
- for (o = sooc_row1 - 1; o < newhourlog_array.length; o++) {
- if (newhourlog_array[o][2] < 1) {
- var sooc_rowl = o;
- break
- }
- }
- break
- }
- }
- for (i = 0; i < newhourlog_array.length; i++) {
- if (newhourlog_array[i][1] == 'Traffic Officers') {
- var to_row1 = i + 3;
- var to_sec = i + 2;
- for (o = to_row1 - 1; o < newhourlog_array.length; o++) {
- if (newhourlog_array[o][2] < 1) {
- var to_rowl = o;
- break
- }
- }
- break
- }
- }
- for (i = 0; i < newhourlog_array.length; i++) {
- if (newhourlog_array[i][1] == 'Incident Support Units') {
- var isu_row1 = i + 3;
- var isu_sec = i + 2;
- var isu_rowl = newhourlog_s.getLastRow();
- break
- }
- }
- newhourlog_s.getRange("F" + sooc_sec + ":G" + sooc_sec).setBackground("#b7b7b7").setFontWeight("bold").setValues([
- ["Rank ATM", "Hours"]
- ])
- .setBorder(true, true, true, true, true, true);
- newhourlog_s.getRange("F" + to_sec + ":G" + to_sec).setBackground("#b7b7b7").setFontWeight("bold").setValues([
- ["Rank ATM", "Hours"]
- ])
- .setBorder(true, true, true, true, true, true);
- newhourlog_s.getRange("F" + isu_sec + ":G" + isu_sec).setBackground("#b7b7b7").setFontWeight("bold").setValues([
- ["Rank ATM", "Hours"]
- ])
- .setBorder(true, true, true, true, true, true);
- newhourlog_s.getRange("F" + sooc_row1 + ":G" + sooc_rowl).setBackground("#efefef").setBorder(true, true, true, true, true, true);
- newhourlog_s.getRange("F" + to_row1 + ":G" + to_rowl).setBackground("#efefef").setBorder(true, true, true, true, true, true);
- newhourlog_s.getRange("F" + isu_row1 + ":G" + isu_rowl).setBackground("#efefef").setBorder(true, true, true, true, true, true);
- var sooc_rank = newhourlog_s.getRange("D" + sooc_row1 + ":D" + sooc_rowl).getValues();
- var to_rank = newhourlog_s.getRange("D" + to_row1 + ":D" + to_rowl).getValues();
- var isu_rank = newhourlog_s.getRange("D" + isu_row1 + ":D" + isu_rowl).getValues();
- var sooc_colour = newhourlog_s.getRange("D" + sooc_row1 + ":D" + sooc_rowl).getBackgrounds();
- var to_colour = newhourlog_s.getRange("D" + to_row1 + ":D" + to_rowl).getBackgrounds();
- var isu_colour = newhourlog_s.getRange("D" + isu_row1 + ":D" + isu_rowl).getBackgrounds();
- newhourlog_s.getRange("F" + sooc_row1 + ":F" + sooc_rowl).setValues(sooc_rank).setBackgrounds(sooc_colour);
- newhourlog_s.getRange("F" + to_row1 + ":F" + to_rowl).setValues(to_rank).setBackgrounds(to_colour);
- newhourlog_s.getRange("F" + isu_row1 + ":F" + isu_rowl).setValues(isu_rank).setBackgrounds(isu_colour);
- var merge1 = newhourlog_s.getRange('F2:G2').merge().setBackgroundRGB(90, 105, 122).setFontColor('white'); //executor and date, need updates subjected to sheet changes
- var merge2 = newhourlog_s.getRange('F3:G3').merge().setBackgroundRGB(90, 105, 122).setFontColor('white');
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement