Untitled
unknown
plain_text
22 days ago
13 kB
1
Indexable
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); } }
Editor is loading...
Leave a Comment