Untitled

function main(workbook: ExcelScript.Workbook) {
  let liveSheet = workbook.getWorksheet("Live Data");
  let localSheet = workbook.getWorksheet("Local Copy");
  let backupSheet = workbook.getWorksheet("Local Copy Backup");

  //unprotect local data
  localSheet.getProtection().unprotect("AHC");

  //Copy all data

  let lastRow = liveSheet.getUsedRange().getLastCell().getRowIndex();
  let lastColumn = liveSheet.getUsedRange().getLastColumn().getColumnIndex();
  let lastColumnChar = ""

  if (lastColumn >= 26) {
    lastColumnChar = "A" + String.fromCharCode(65 + (lastColumn - 26));
  } else {
    lastColumnChar = String.fromCharCode(65 + lastColumn);
  }

  //filter values
  let filterValue = localSheet.getRange("B1").getValue();
  let filterColumnNumber = localSheet.getRange("D1").getValue();

  lastRow++;

  //Table handling to ensure no table deletion

  let localTable = localSheet.getTable("Local_Data");
  if (localTable == null) {
    //create fresh table
    //copy data over
    localSheet.getRange("A2:" + lastColumnChar + lastRow).copyFrom(liveSheet.getUsedRange(), ExcelScript.RangeCopyType.values);
    //localSheet.getRange("A2:" + lastColumnChar + lastRow).copyFrom(liveSheet.getUsedRange(), ExcelScript.RangeCopyType.formats);
    localTable = localSheet.addTable("A2:" + lastColumnChar + lastRow, true);
    localTable.setName("Local_Data");
  } else {

    //make backup
    let backupTable = backupSheet.getTable("Backup");
    if (backupTable != undefined) {
      backupTable.convertToRange();
      backupSheet.getUsedRange().clear(ExcelScript.ClearApplyTo.all);
    }

    backupSheet.getRange("A1:" + lastColumnChar + (lastRow + 1)).copyFrom(localTable.getRange());
    backupSheet.getTables()[0].setName("Backup");
    let date = new Date
    localSheet.getRange("H1").setValue(date.toDateString() + " at " + date.toLocaleTimeString());

    //delete table contents and copy
    localTable.deleteRowsAt(0, localTable.getRowCount());
    localSheet.getRange("A3:" + lastColumnChar + lastRow).copyFrom(liveSheet.getRange("A2:" + lastColumnChar + lastRow), ExcelScript.RangeCopyType.values);
    //localSheet.getRange("A3:" + lastColumnChar + lastRow).copyFrom(liveSheet.getUsedRange(), ExcelScript.RangeCopyType.formats);
  }

  //filtering
  let tableRange = localSheet.getTable("Local_Data").getRangeBetweenHeaderAndTotal();

  if(filterColumnNumber != -1){
    for(let i = 0; i < tableRange.getRowCount(); i++){
      if(tableRange.getCell(i, filterColumnNumber).getValue() != filterValue){
        tableRange.getRow(i).delete(ExcelScript.DeleteShiftDirection.up);
        i--;
      }
    }
  }

  lastRow = localSheet.getUsedRange().getLastCell().getRowIndex();
  lastColumn = localSheet.getUsedRange().getLastColumn().getColumnIndex();
  lastColumnChar = ""

  if (lastColumn >= 26) {
    lastColumnChar = "A" + String.fromCharCode(65 + (lastColumn - 26));
  } else {
    lastColumnChar = String.fromCharCode(65 + lastColumn);
  }

  //Small Formatting for QOL
  let format: ExcelScript.RangeFormat = localSheet.getUsedRange().getRow(0).getFormat();
  format.autofitColumns();

  //remove all conditional formats
  let cFormat = localSheet.getUsedRange().getConditionalFormat("0")
  while(cFormat != undefined){
    cFormat.delete()
    cFormat = localSheet.getUsedRange().getConditionalFormat("0")
  }

  //add conditional format for individual cells

  //add per column
  for(let i = 1; i < lastColumn + 1; i++){
    let columnChar = ""
    if (i >= 26) {
      columnChar = "A" + String.fromCharCode(65 + (i - 26));
    } else {
      columnChar = String.fromCharCode(65 + i);
    }

    let conditionalFormat: ExcelScript.ConditionalFormat = localSheet.getRange(columnChar + "3:" + columnChar + (lastRow + 1)).addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    conditionalFormat.getCustom().getFormat().getFill().setColor("#E23F44");
    conditionalFormat.getCustom().getRule().setFormula("= " + columnChar + "3 <> VLOOKUP($A3, INDIRECT(\"Programs_Group_HR_Workbook\"), " + (i + 1) + ")");
  }

  //set green by default
  localSheet.getRange("A3:A" + (lastRow + 1)).getFormat().getFill().setColor("#90EE90");

  //format for NEW ID
  let newEntryConditionalFormat: ExcelScript.ConditionalFormat = localSheet.getRange("A3:A" + (lastRow + 1)).addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
  newEntryConditionalFormat.getCustom().getFormat().getFill().setColor("A020F0");
  newEntryConditionalFormat.getCustom().getRule().setFormula("= $A3 = \"NEW\"");
  
  //ID column 'if something is differnet' conditional format logic
  let firstColConditionalFormat: ExcelScript.ConditionalFormat = localSheet.getRange("A3:A" + (lastRow + 1)).addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
  firstColConditionalFormat.getCustom().getFormat().getFill().setColor("#FDC959");
  let formulaString = "= OR("
  for (let i = 1; i < lastColumn; i++) {
    let columnChar = ""
    if (i >= 26) {
      columnChar = "A" + String.fromCharCode(65 + (i - 26));
    } else {
      columnChar = String.fromCharCode(65 + i);
    }
    
    formulaString += "$" + columnChar + "3 <> VLOOKUP($A3, INDIRECT(\"Programs_Group_HR_Workbook\"), " + (i + 1) + "), ";
  }
  formulaString = formulaString.substr(0, formulaString.length - 2);
  formulaString += ")";
  firstColConditionalFormat.getCustom().getRule().setFormula(formulaString);

  if(localTable.getColumn("Diff") == undefined){
    localTable.addColumn().setName("Diff");
  }

  localTable.getColumn("Diff").getRangeBetweenHeaderAndTotal().setFormula(formulaString);


  /**
   * ["NEW", //ID 1
    employeeCell.getValue(), 2
    directorateCell.getValue(), LIST 3
    teamCell.getValue(), LIST 4
    substTitleCell.getValue(), LIST 5
    substGroup, LIST ?? 6
    substSubGroup, LIST ?? 7
    substLevel, LIST ?? 8
    substPositionNumberCell.getValue(), 9
    substPositionLanguageProfile, 10
    current Directorate 11
    current Team 12
    currentTitleCell.getValue(), LIST 13
    currentGroup, LIST ?? 14
    currentSubGroup, LIST ?? 15
    currentLevel, LIST ?? 16
    currentPositionNumberCell.getValue(), 17
    currentPositionLanguageProfile, 18
    employmentTypeCell.getValue(), LIST 19
    statusCell.getValue(), LIST 20
    statusStartDateCell.getValue(), DATE 21
    statusEndDateCell.getValue(), DATE 22
    substManagerCell.getValue(), LIST 23
    currentManagerCell.getValue(), LIST 24
    employeeSLE, 25
    floorCell.getValue(), LIST 26
    closedDoorOfficeCell.getValue(), LIST 27
    assignedWorkStationCell.getValue(), 28
    assignedLockerCell.getValue(), 29
    typeOfWAACell.getValue(), LIST 30
    waaStatusCell.getValue(), LIST 31
    reasonForWAATypeCell.getValue(), 32
    positionGeographicLocationCell.getValue(), LIST 33
    teleworkLocationCell.getValue(), 34
    regionalWorkSiteCell.getValue(), LIST 35
    additionalCommentsCell.getValue(), 36
    "", //modified 37
    "", //created 38
    "", //authour 39
    "", //editor 40
    "NEW"]) //diff 41
   */
  
  //unprotect workbook
  workbook.getProtection().unprotect("AHC");

  //BEGIN DATA VALIDATION

  let listSheet = workbook.getWorksheet("Lists");
  let classificationsSheet = workbook.getWorksheet("Classifications List");
  let typeOfWAASheet = workbook.getWorksheet("Type of WAA");
  let waaStatusSheet = workbook.getWorksheet("WAASTATUS");
  let employmentTypeSheet = workbook.getWorksheet("EmploymentType");
  let positionGeographicLocationSheet = workbook.getWorksheet("PositionGeographicLocation");
  let closedDoorOfficeSheet = workbook.getWorksheet("ClosedDoorOffice");
  let floorSheet = workbook.getWorksheet("Floor");
  let titleSheet = workbook.getWorksheet("TitleOfPosition");
  let regionalWorkSiteSheet = workbook.getWorksheet("RegionalWorkSite");
  let statusSheet = workbook.getWorksheet("Status");

  let directorateListRange = listSheet.getRange("C3:C50");
  let teamListRange = listSheet.getRange("D3:D100");
  let titleListRange = titleSheet.getRange("A2:A300");
  let groupListRange = classificationsSheet.getRange("G2:G200");
  let subGroupListRange = classificationsSheet.getRange("H2:H200");
  let levelListRange = classificationsSheet.getRange("I2:I200");
  let employmentTypeListRange = employmentTypeSheet.getRange("A2:A300");
  let statusListRange = statusSheet.getRange("A2:A300");
  let managerListRange = listSheet.getRange("E3:E3000");
  let floorListRange = floorSheet.getRange("A2:A300");
  let closedDoorOfficeListRange = closedDoorOfficeSheet.getRange("A2:A300");
  let typeOfWAAListRange = typeOfWAASheet.getRange("A2:A300");
  let waaStatusListRange = waaStatusSheet.getRange("A2:A300");
  let positionGeographicLocationListRange = positionGeographicLocationSheet.getRange("A2:A300");
  let regionalWorkSiteListRange = regionalWorkSiteSheet.getRange("A2:A300");
  let languageProfilesRange = listSheet.getRange("F3:F300");

  makeValidationRule(localTable, 3, false, directorateListRange); //subst. directorate
  makeValidationRule(localTable, 4, false, teamListRange); //subst. team
  makeValidationRule(localTable, 5, false, titleListRange); //subst title
  makeValidationRule(localTable, 6, false, groupListRange); //subst group
  makeValidationRule(localTable, 7, false, subGroupListRange); //subst sub-group
  makeValidationRule(localTable, 8, false, levelListRange); //subst level
  makeValidationRule(localTable, 10, false, languageProfilesRange); //subst language profile
  makeValidationRule(localTable, 11, false, directorateListRange); //current directorate
  makeValidationRule(localTable, 12, false, teamListRange); //current team
  makeValidationRule(localTable, 13, false, titleListRange); //current title
  makeValidationRule(localTable, 14, false, groupListRange); //current group
  makeValidationRule(localTable, 15, false, subGroupListRange); //current sub-group
  makeValidationRule(localTable, 16, false, levelListRange); //current level
  makeValidationRule(localTable, 18, false, languageProfilesRange); //current language profile
  makeValidationRule(localTable, 19, false, employmentTypeListRange);
  makeValidationRule(localTable, 20, false, statusListRange);
  makeValidationRule(localTable, 21, true); //status start
  makeValidationRule(localTable, 22, true); //status end
  makeValidationRule(localTable, 23, false, managerListRange); //subst manager
  makeValidationRule(localTable, 24, false, managerListRange); //current manager
  makeValidationRule(localTable, 26, false, floorListRange);
  makeValidationRule(localTable, 27, false, closedDoorOfficeListRange);
  makeValidationRule(localTable, 30, false, typeOfWAAListRange);
  makeValidationRule(localTable, 31, false, waaStatusListRange);
  makeValidationRule(localTable, 33, false, positionGeographicLocationListRange);
  makeValidationRule(localTable, 35, false, regionalWorkSiteListRange);
  
  let customRuleString = "=AND(LEN(Y3) = 3, OR(EXACT(MID(Y3, 1, 1), \"A\"), EXACT(MID(Y3, 1, 1), \"B\"), EXACT(MID(Y3, 1, 1), \"C\"), EXACT(MID(Y3, 1, 1), \"E\"), EXACT(MID(Y3, 1, 1), \"-\")), OR(EXACT(MID(Y3, 2, 1), \"A\"), EXACT(MID(Y3, 2, 1), \"B\"), EXACT(MID(Y3, 2, 1), \"C\"), EXACT(MID(Y3, 2, 1), \"E\"), EXACT(MID(Y3, 2, 1), \"-\")), OR(EXACT(MID(Y3, 3, 1), \"A\"), EXACT(MID(Y3, 3, 1), \"B\"), EXACT(MID(Y3, 3, 1), \"C\"), EXACT(MID(Y3, 3, 1), \"E\"), EXACT(MID(Y3, 3, 1), \"-\")))"

  let validation = localTable.getColumn(25).getRangeBetweenHeaderAndTotal().getDataValidation();

  let rule: ExcelScript.DataValidationRule = {
    custom: {
      formula: customRuleString
    }
  };

  validation.setRule(rule);

  validation.setErrorAlert({
    message: "Enter a valid Second Language Evaluation string. For example: CBC, AAA, ECE, etc. This cell is case-sensitive.",
    showAlert: true,
    style: ExcelScript.DataValidationAlertStyle.stop,
    title: "Invalid Entry"
  });

  //END DATA VALIDATION

  workbook.getWorksheet("Admin").setVisibility(ExcelScript.SheetVisibility.hidden);

  localSheet.getProtection().protect({selectionMode: ExcelScript.ProtectionSelectionMode.unlocked}, "AHC");
  workbook.getProtection().protect("AHC");
}

function makeValidationRule(table : ExcelScript.Table, columnNumber: number, isDate: boolean, listRange?: ExcelScript.Range){
    let validation = table.getColumn(columnNumber).getRangeBetweenHeaderAndTotal().getDataValidation();
    if(isDate){
      let rule: ExcelScript.DataValidationRule = {
        date: {
          formula1: "=1900/01/01",
          operator: ExcelScript.DataValidationOperator.greaterThan
        }
      }
      validation.setRule(rule);
      
      validation.setErrorAlert({
        message: "Enter a date value with the following format: yyyy-mm-dd.",
        showAlert: true,
        style: ExcelScript.DataValidationAlertStyle.stop,
        title: "Invalid Entry"
      });
    }else{
      let rule: ExcelScript.DataValidationRule = {
        list: {
          inCellDropDown: true,
          source: listRange
        }
      }
      //console.log(columnNumber);
      validation.setRule(rule);
    }
}