Highlight occurrences 5 times and above
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.