csSetFormulas.osts

{"version":"0.3.0","body":"function main(workbook: ExcelScript.Workbook) {\n    // Get the active worksheet\n    let ps = workbook.getWorksheet(\"PHONE 6.100 AC-PC\");\n    let cs = workbook.getWorksheet(\"CENSUS TRACKING\");\n    const protection = ps.getProtection();\n\n    // Pause protection\n    protection.pauseProtection(\"imsorrydave\");\n\n    // Set date formulas in specified cells\n    const dateCells = [\"B2\", \"B8\", \"B14\", \"B20\", \"B26\", \"B32\", \"B38\"];\n\n    const censusFormulas = {\n        \"Q3\": \"=S20\", \"Q4\": \"=S14 & ' /  ' & S15\", \"Q5\": \"=S26& ' /  ' & S23\", \"Q6\": \"=S19& ' /  ' & S18\", \"Q7\": \"=S29& ' / 0'\"\n    };\n\n    \n\n    for (const [cell, formula] of Object.entries(censusFormulas)) {\n        cs.getRange(cell).\n        setFormulaLocal(formula);\n    }\n\n    \n    // Set other formulas\n    const formulas = {\n        \"M14\": \"=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))\",\n        \"M15\": \"=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))\",\n        \"M18\": \"=COUNTIFS('PHONE 6.100 AC-PC'!D7:D38,\\\"AC\\\",'PHONE 6.100 AC-PC'!B7:B38,\\\"<>*(ADMIT)*\\\")\",\n        \"M19\": \"=COUNTIFS('PHONE 6.100 AC-PC'!D7:D38,\\\"PC\\\",'PHONE 6.100 AC-PC'!B7:B38,\\\"<>*(ADMIT)*\\\")\",\n        \"M20\": \"='PHONE 6.100 AC-PC'!O12\",\n        \"M23\": \"=COUNTIFS('PHONE 6.100 AC-PC'!B7:B38,\\\"*(+)*\\\",'PHONE 6.100 AC-PC'!B7:B38,\\\"<>*(ADMIT)*\\\")\",\n        \"M26\": \"=COUNTIFS('PHONE 6.100 AC-PC'!B7:B38,\\\"*(SIT)*\\\",'PHONE 6.100 AC-PC'!B7:B38,\\\"<>*(ADMIT)*\\\")\"\n    };\n\n\n\n    // Apply formulas to the specified ranges\n    for (const [cell, formula] of Object.entries(formulas)) {\n        cs.getRange(cell).setFormulaLocal(formula);\n        \n    }\n\n\n\n    // Resume protection\n    protection.resumeProtection();\n}\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}"}