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 "Feuil1"
let sheet = workbook.getWorksheet("Feuil1");
if (!sheet) {
console.error("Worksheet 'Feuil1' 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");
});
});
}
What can you suggest to debug it ?
Thank you for your help.
Option 1:
getUsedRange()
returns the used range. It saves processing time of the code.let columnEValues: any[][];
doesn't work. Explicit Any
is not allowed.Microsoft documentation:
async function main(workbook: ExcelScript.Workbook) {
// Attempt to obtain the worksheet named "Feuil1"
let sheet = workbook.getWorksheet("Feuil1");
if (!sheet) {
console.log("Worksheet 'Feuil1' not found.");
return;
}
sheet.getRange("B:F").getFormat().getFill().clear();
// Attempt to obtain the column E range
let columnE = sheet.getRange("E:E").getUsedRange();
// Explicitly declare columnEValues with a type
let columnEValues: (String | Number | Boolean)[][];
try {
// Attempt to obtain the values of column E
columnEValues = await columnE.getValues();
} catch (log) {
console.log("Failed to get values from column E:", log);
return;
}
console.log(columnEValues);
// 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) => {
const code: string = row[0]?.toString() || '';
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
columnEValues.forEach((value, index) => {
filteredCodes.forEach((code) => {
if (value[0] === code) {
// Adjust for zero-based index
let rangeToColor = sheet.getRange(`B${index + 1}:F${index + 1}`);
rangeToColor.getFormat().getFill().setColor("yellow");
return;
}
});
});
}
Option 2: conditional formatting
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
let usedRange = sheet.getUsedRange();
let selectedRange = sheet.getRange("B:F").getIntersection(usedRange);
if(selectedRange){
let condFormat = selectedRange.addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
condFormat.getCustom().getFormat().getFill().setColor("yellow");
const cfFormual = `=COUNTIF($E:$E,$E1)>=5`;
condFormat.getCustom().getRule().setFormula(cfFormual);
}
}