Highlight occurrences 5 times and above

3xceln4ute 0 Reputation points
2024-02-14T14:06:02.1933333+00:00

Hello, I would like my script to color the columns from B to F of the rows that have the same code appearing in column E. I have a code that was generated but there is a persistent bug. The error message:

See line 13, column 22: Explicit Any is not allowed

See line 28, column 9: Office Scripts cannot infer the data type of this variable. Please declare a type for the variable.

Here is the script:
async function main(workbook: ExcelScript.Workbook) {   // Attempt to obtain the worksheet named "5_Retards"   let sheet = workbook.getWorksheet("5_Retards");   if (!sheet) {     console.error("Worksheet '5_Retards' not found.");     return;   }   // Attempt to obtain the column E range   let columnE = sheet.getRange("E:E");   // Explicitly declare columnEValues with a type   let columnEValues: any[][];   try {     // Attempt to obtain the values of column E     columnEValues = await columnE.getValues();   } catch (error) {     console.error("Failed to get values from column E:", error);     return;   }   // Initialize a dictionary to count occurrences of each code   let codeCount: { [key: string]: number } = {};   // Iterate over columnEValues to count each code's occurrences   columnEValues.forEach((row) => {     let code = row[0];     if (code) {       // Ensure code is treated as a string       code = code.toString();       // Increment the count for this code       codeCount[code] = (codeCount[code] || 0) + 1;     }   });   // Filter codes that appear 5 times or more   let filteredCodes = Object.keys(codeCount).filter(code => codeCount[code] >= 5);   // Apply conditional formatting for each filtered code   filteredCodes.forEach((code) => {     let rowsToFormat: number[] = [];     columnEValues.forEach((value, index) => {       if (value[0] === code) {         // Adjust for zero-based index         rowsToFormat.push(index + 1);       }     });     // Format the ranges of B to F for the found rows     rowsToFormat.forEach((rowIndex) => {       let rangeToColor = sheet.getRange(B${rowIndex}:F${rowIndex});       rangeToColor.getFormat().getFill().setColor("yellow");     });   }); } Thank you for your help.

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,324 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.