Highlight occurrences 5 times and above

Question

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.

Answer 1

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.
• Add a code line to clear the filling color on Col B:F

Microsoft documentation:

ExcelScript.Range interface getUsedRange(valuesOnly)

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

• Less code than option 1.

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);
    }
}