Script 1.osts

{"version":"0.3.0","body":"function main(workbook: ExcelScript.Workbook) {\n    let sheet: ExcelScript.Worksheet = workbook.getWorksheet(\"PHONE 6.100 AC-PC\");\n    const protection: ExcelScript.WorksheetProtection = sheet.getProtection();\n    const password: string = \"imsorrydave\";\n    protection.pauseProtection(password);\n\n    let range: ExcelScript.Range = sheet.getRange(\"B7:B38\");\n    let data: (string | number | boolean)[][] = range.getValues();\n    let numRows: number = data.length;\n\n    // Array to store values for column M (index 12)\n    let columnMValues: (number | string)[][] = [];\n\n    // Object to store word occurrences\n    let wordCounts: { [key: string]: number } = {};\n\n    // Define the list of texts to exclude\n    const excludedTexts: string[] = [\"(S)\", \"(P)\", \"(SIT)\", \"(ADMIT)\"];\n\n    // Define the texts to check for red or blue fill\n    const redFillTexts: string[] = [\"(S)\", \"(P)\"];\n    const blueFillTexts: string[] = [\"(SIT)\", \"(ADMIT)\"];\n\n    // Red and blue RGB codes\n    const redRGB: string = \"#FF0000\";\n    const blueRGB: string = \"#0000FF\";  // Blue RGB code\n\n    // Helper function to remove excluded texts from a name\n    function cleanName(name: string): string {\n        excludedTexts.forEach((text: string) => {\n            name = name.replace(text, \"\"); // Remove each excluded text\n        });\n        return name.trim();\n    }\n\n    // First pass: Count occurrences of each word in the full names (after cleaning)\n    for (let i: number = 0; i < numRows; i++) {\n        let fullName: string = data[i][0] as string;\n        if (fullName) {\n            let cleanedFullName: string = cleanName(fullName); // Clean the name by removing excluded texts\n            let nameWords: string[] = cleanedFullName.split(\" \"); // Split into individual words\n\n            // Count each word in the cleaned full name\n            for (let word of nameWords) {\n                let lowerWord: string = word.toLowerCase();\n                wordCounts[lowerWord] = (wordCounts[lowerWord] || 0) + 1;\n            }\n        }\n    }\n\n    // Second pass: Set values in column M based on the word count and check red/blue background\n    for (let i: number = 0; i < numRows; i++) {\n        let fullName: string = data[i][0] as string;\n        let cell: ExcelScript.Range = sheet.getRange(`B${i + 7}`); // Get the cell in column B\n        let fillColor: string = cell.getFormat().getFill().getColor(); // Get cell background color\n\n        if (!fullName) {\n            // If the cell is empty, set M to 0\n            columnMValues.push([0]);\n        } else {\n            let cleanedFullName: string = cleanName(fullName); // Clean the name by removing excluded texts\n            let nameWords: string[] = cleanedFullName.split(\" \"); // Split into individual words\n\n            let hasMultipleOccurrences: boolean = nameWords.some((word: string) => wordCounts[word.toLowerCase()] > 1);\n\n            // If any word appears more than once in any other row, mark 2; otherwise, mark 1\n            columnMValues.push([hasMultipleOccurrences ? 2 : 1]);\n\n            // Check if the background fill is red and the name doesn't contain \"(S)\" or \"(P)\"\n            if (fillColor === redRGB && !redFillTexts.some(text => fullName.includes(text))) {\n                // If it's red but doesn't contain \"(S)\" or \"(P)\", change the background fill to white\n                cell.getFormat().getFill().setColor(\"#FFFFFF\");\n            }\n\n            // Check if the background fill is blue and the name doesn't contain \"(SIT)\" or \"(ADMIT)\"\n            if (fillColor === blueRGB && !blueFillTexts.some(text => fullName.includes(text))) {\n                // If it's blue but doesn't contain \"(SIT)\" or \"(ADMIT)\", change the background fill to white\n                cell.getFormat().getFill().setColor(\"#FFFFFF\");\n            }\n        }\n    }\n\n    // Write all values to column M (index 12) at once\n    sheet.getRangeByIndexes(6, 12, numRows, 1).setValues(columnMValues);  // Use getRangeByIndexes\n\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}"}