Microsoft 365 [Office script] Identifier les occurrences // 5 et +

[3xceln4ute]

Bonjour,

J'aimerais mettre en place un script qui permet d'identifier les occurrences qui apparaissent 5 fois et plus, en coloriant les lignes concernées.

À cet effet, j'ai ce script qui tend à répondre au besoin, mais il y a une erreur qui l'empêche d'aller jusqu'au bout.

Code de l'erreur:

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

Voici le 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");
    });
  });
}

Ce serait grandement apprécié qu'un des membres puisse m'éclairer.

Cordialement.