Share via

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.

Microsoft 365 and Office | Development | Other
0 comments No comments

Your answer

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