Script 7.osts

{"version":"0.3.0","body":"function main(workbook: ExcelScript.Workbook) {\n    let cs = workbook.getWorksheet(\"CENSUS TRACKING\");\n    const protection = cs.getProtection();\n\n    // Pause protection if active\n    if (protection.getProtected()) {\n        protection.pauseProtection(\"imsorrydave\");\n    }\n\n    // Apply date formulas for B2, B8, B14, ..., B38\n    for (let i = 0; i < 7; i++) {\n        let cell = cs.getRange(`B${2 + i * 6}`);\n        cell.setFormula(`=TEXT(L6+${i}, \"mm/dd/yy\")`);\n    }\n\n    // Other formulas and their target ranges\n    const formulas = [\n        { range: 'M14', formula: `=SUM(IF(FREQUENCY(IF('PHONE 6.100 AC-PC'!E7:E38<>\"\", MATCH('PHONE 6.100 AC-PC'!E7:E38, 'PHONE 6.100 AC-PC'!E7:E38, 0)), IF('PHONE 6.100 AC-PC'!E7:E38<>\"\", MATCH('PHONE 6.100 AC-PC'!E7:E38, 'PHONE 6.100 AC-PC'!E7:E38, 0))) > 0, 1)) + IF(COUNTIF('PHONE 6.100 AC-PC'!E7:E38, 'PHONE 6.100 AC-PC'!C4)=0, 1, 0) + SUM(IF('PHONE 6.100 AC-PC'!G7:G38 = \"RESOURCE\",1,0))` },\n        { range: 'M15', formula: `=SUM(IF(FREQUENCY(IF('PHONE 6.100 AC-PC'!G7:G38<>\"\", MATCH('PHONE 6.100 AC-PC'!G7:G38, 'PHONE 6.100 AC-PC'!G7:G38, 0)), IF('PHONE 6.100 AC-PC'!G7:G38<>\"\", MATCH('PHONE 6.100 AC-PC'!G7:G38, 'PHONE 6.100 AC-PC'!G7:G38, 0))) > 0, 1)) + SUM(IF('PHONE 6.100 AC-PC'!G7:G38 = \"RESOURCE\",-1,0))` },\n        { range: 'M18', formula: `=COUNTIFS('PHONE 6.100 AC-PC'!D7:D38,\"AC\",'PHONE 6.100 AC-PC'!B7:B38,\"<>*(ADMIT)*\")` },\n        { range: 'M19', formula: `=COUNTIFS('PHONE 6.100 AC-PC'!D7:D38,\"PC\",'PHONE 6.100 AC-PC'!B7:B38,\"<>*(ADMIT)*\")` },\n        { range: 'M20', formula: `='PHONE 6.100 AC-PC'!O13` },\n        { range: 'M23', formula: `=COUNTIFS('PHONE 6.100 AC-PC'!B7:B38,\"*(+)*\",'PHONE 6.100 AC-PC'!B7:B38,\"<>*(ADMIT)*\")` },\n        { range: 'M26', formula: `=SUM(COUNTIFS('PHONE 6.100 AC-PC'!B7:B38,{\"*(SIT)*\",\"*(TS)*\",\"*(72)*\"},'PHONE 6.100 AC-PC'!B7:B38,\"<>*(ADMIT)*\"))` },\n        { range: 'M29', formula: `=COUNTIF('PHONE 6.100 AC-PC'!B7:B38,\"*(ADMIT)*\")` },\n        { range: 'A1', formula: `=\"Census Tracking Week : \" & TEXT(L6,\"mm/dd\") & \" - \" & TEXT(L6+6,\"mm/dd\")` }\n    ];\n\n    // Apply all formulas\n    formulas.forEach(({ range, formula }) => {\n        cs.getRange(range).setFormula(formula);\n    });\n\n    billThing(workbook);\n\n    clearAndSetAllowEdit(workbook, [\"C3:H7\", \"C9:H13\", \"C15:H19\", \"C21:H25\", \"C27:H31\", \"C33:H37\", \"C39:H43\", \"L6\"]);\n\n    // Resume protection\n    protection.resumeProtection();\n\n\n}\n\n\n\nfunction billThing(workbook: ExcelScript.Workbook) {\n    let cs = workbook.getWorksheet(\"CENSUS TRACKING\");\n\n    const formulas = [{ range: 'Q19', formula: '=S20' }, { range: 'Q20', formula: '=M14& \" / \" &M15' }, { range: 'Q21', formula: '=S26& \" / \" & S23' }, { range: 'Q22', formula: '=S19& \" / \" & S18' }, { range: 'Q23', formula: '=S29& \" / 0\"' }];\n\n    const formulas2 = [{ range: 'Q3', formula: '=S20' }, { range: 'Q4', formula: '=S14& \" / \" &S15' }, { range: 'Q5', formula: '=S26& \" / \" & S23' }, { range: 'Q6', formula: '=S19& \" / \" & S18' }, { range: 'Q7', formula: '=S29& \" / 0\"' }];\n\n    const formulas3 = [{ range: 'S20', formula: '=SUM(M20,P20)' }, { range: 'S14', formula: '=SUM(M14,P14)' }, { range: 'S15', formula: '=SUM(M15,P15)' }, { range: 'S18', formula: '=SUM(M18,P18)' }, { range: 'S19', formula: '=SUM(M19,P19)' }, { range: 'S23', formula: '=SUM(M23,P23)' }, { range: 'S26', formula: '=SUM(M26,P26)' }, { range: 'S26', formula: '=SUM(M26,P26)' }, { range: 'S29', formula: '=SUM(M29,P19)' }];\n\n    // Apply all formulas\n    formulas.forEach(({ range, formula }) => {\n        cs.getRange(range).setFormula(formula);\n    });\n\n    //formulas2.forEach(({ range, formula }) => {\n    //  cs.getRange(range).setFormula//(formula);\n    //});\n\n    formulas3.forEach(({ range, formula }) => {\n        cs.getRange(range).setFormula(formula);\n    });\n\n    let values = cs.getRange(\"Q19:Q23\");\n\n    cs.getRange(\"Q3:Q7\").copyFrom(values, ExcelScript.RangeCopyType.values, false, false);\n}\n\nfunction clearAndSetAllowEdit(workbook: ExcelScript.Workbook, editableRanges: string[]) {\n    // Select the active sheet (you can change this to a specific sheet name if needed)\n    let sheet = workbook.getActiveWorksheet();\n\n    // Clear all existing allowEdit permissions by unprotecting the sheet\n    let protection = sheet.getProtection();\n    if (protection.getProtected()) {\n        protection.pauseProtection();\n    }\n\n    // Protect the sheet to apply new settings\n    protection.protect();\n\n    protection.setPassword(\"imsorrydave\");\n\n    // Loop through the specified ranges and set them as editable\n    editableRanges.forEach(rangeAddress => {\n        let range = sheet.getRange(rangeAddress);\n        range.getFormat().getProtection().setLocked(false);\n    });\n\n    // Log completion\n    console.log(\"Protection settings updated. Specified ranges are now editable.\");\n}","description":"","noCodeMetadata":"","parameterInfo":"{\"version\":1,\"originalParameterOrder\":[],\"parameterSchema\":{\"type\":\"object\",\"default\":{},\"x-ms-visibility\":\"internal\"},\"returnSchema\":{\"type\":\"object\",\"properties\":{}},\"signature\":{\"comment\":\"\",\"parameters\":[{\"name\":\"workbook\",\"comment\":\"\"}]}}","apiInfo":"{\"variant\":\"synchronous\",\"variantVersion\":2}"}