Référence croisée des fichiers Excel avec Power Automate

Article
12/06/2023

Cette solution montre comment comparer des données entre deux fichiers Excel pour trouver des différences. Il utilise des scripts Office pour analyser les données et Power Automate pour communiquer entre les classeurs.

Cet exemple transmet des données entre des classeurs à l’aide d’objets JSON . Pour plus d’informations sur l’utilisation de JSON, consultez Utiliser JSON pour passer des données vers et à partir de scripts Office.

Exemple de scénario

Vous êtes un coordinateur d’événements qui planifie des conférenciers pour les conférences à venir. Vous conservez les données d’événement dans une feuille de calcul et les inscriptions de l’orateur dans une autre. Pour vous assurer que les deux classeurs sont synchronisés, vous utilisez un flux avec des scripts Office pour mettre en évidence les problèmes potentiels.

Exemples de fichiers Excel

Téléchargez les fichiers suivants pour obtenir des classeurs prêts à l’emploi pour l’exemple.

Ajoutez les scripts suivants pour essayer l’exemple vous-même ! Dans Excel, utilisez Automatiser>un nouveau script pour coller le code et enregistrer les scripts avec les noms suggérés.

Exemple de code : Obtenir des données d’événement

function main(workbook: ExcelScript.Workbook): string {
  // Get the first table in the "Keys" worksheet.
  let table = workbook.getWorksheet('Keys').getTables()[0];

  // Get the rows in the event table.
  let range = table.getRangeBetweenHeaderAndTotal();
  let rows = range.getValues();

  // Save each row as an EventData object. This lets them be passed through Power Automate.
  let records: EventData[] = [];
  for (let row of rows) {
    let [eventId, date, location, capacity] = row;
    records.push({
      eventId: eventId as string,
      date: date as number,
      location: location as string,
      capacity: capacity as number
    })
  }

  // Log the event data to the console and return it for a flow.
  let stringResult = JSON.stringify(records);
  console.log(stringResult);
  return stringResult;
}

// An interface representing a row of event data.
interface EventData {
  eventId: string
  date: number
  location: string
  capacity: number
}

Exemple de code : Valider les inscriptions de l’orateur

function main(workbook: ExcelScript.Workbook, keys: string): string {
  // Get the first table in the "Transactions" worksheet.
  let table = workbook.getWorksheet('Transactions').getTables()[0];

  // Clear the existing formatting in the table.
  let range = table.getRangeBetweenHeaderAndTotal();
  range.clear(ExcelScript.ClearApplyTo.formats);

  // Compare the data in the table to the keys passed into the script.
  let keysObject = JSON.parse(keys) as EventData[];
  let speakerSlotsRemaining = keysObject.map(value => value.capacity);
  let overallMatch = true;

  // Iterate over every row looking for differences from the other worksheet.
  let rows = range.getValues();
  for (let i = 0; i < rows.length; i++) {
    let row = rows[i];
    let [eventId, date, location, capacity] = row;
    let match = false;

    // Look at each key provided for a matching Event ID.
    for (let keyIndex = 0; keyIndex < keysObject.length; keyIndex++) {
      let event = keysObject[keyIndex];
      if (event.eventId === eventId) {
        match = true;
        speakerSlotsRemaining[keyIndex]--;
        // If there's a match on the event ID, look for things that don't match and highlight them.
        if (event.date !== date) {
          overallMatch = false;
          range.getCell(i, 1).getFormat()
            .getFill()
            .setColor("FFFF00");
        }
        if (event.location !== location) {
          overallMatch = false;
          range.getCell(i, 2).getFormat()
            .getFill()
            .setColor("FFFF00");
        }

        break;
      }
    }

    // If no matching Event ID is found, highlight the Event ID's cell.
    if (!match) {
      overallMatch = false;
      range.getCell(i, 0).getFormat()
        .getFill()
        .setColor("FFFF00");
    }
  }

  

  // Choose a message to send to the user.
  let returnString = "All the data is in the right order.";
  if (overallMatch === false) {
    returnString = "Mismatch found. Data requires your review.";
  } else if (speakerSlotsRemaining.find(remaining => remaining < 0)){
    returnString = "Event potentially overbooked. Please review."
  }

  console.log("Returning: " + returnString);
  return returnString;
}

// An interface representing a row of event data.
interface EventData {
  eventId: string
  date: number
  location: string
  capacity: number
}

Flux Power Automate : rechercher les incohérences dans les classeurs

Ce flux extrait les informations d’événement du premier classeur et utilise ces données pour valider le deuxième classeur.

Connectez-vous à Power Automate et créez un flux cloud instantané.
Choisissez Déclencher manuellement un flux , puis sélectionnez Créer.
Dans le générateur de flux, sélectionnez le + bouton et Ajouter une action. Sélectionnez l’action Exécuter le script du connecteur Excel Online (Business). Utilisez les valeurs suivantes pour l’action.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : event-data.xlsx (sélectionné avec le sélecteur de fichiers)
- Script : Obtenir des données d’événement
Renommez cette étape. Sélectionnez le nom actuel « Exécuter le script » dans le volet Office et remplacez-le par « Obtenir les données d’événement ».
Ajoutez une deuxième action qui utilise l’action Exécuter le script du connecteur Excel Online (Business). Cette action utilise les valeurs retournées à partir du script Obtenir des données d’événement comme entrée pour le script valider les données d’événement . Utilisez les valeurs suivantes pour l’action.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : speaker-registration.xlsx (sélectionné avec le sélecteur de fichiers)
- Script : Valider l’inscription de l’orateur
- clés : résultat (contenu dynamique à partir de l’obtention de données d’événement)
Renommez également cette étape. Sélectionnez le nom actuel « Exécuter le script 1 » dans le volet Office et remplacez-le par « Valider l’inscription de l’orateur ».
Cet exemple utilise Outlook comme client de messagerie. Pour cet exemple, ajoutez l’action Envoyeret e-mail (V2) du connecteur Outlook Office 365. Vous pouvez utiliser n’importe quel connecteur de messagerie pris en charge par Power Automate. Cette action utilise les valeurs retournées par le script Valider l’inscription de l’orateur comme contenu du corps de l’e-mail. Utilisez les valeurs suivantes pour l’action.
- À : votre compte de messagerie de test (ou e-mail personnel)
- Objet : Résultats de la validation d’événement
- Corps : résultat (contenu dynamique de Valider l’inscription de l’orateur)
Enregistrez le flux. Le concepteur de flux doit ressembler à l’image suivante.
Utilisez le bouton Test de la page de l’éditeur de flux ou exécutez le flux via votre onglet Mes flux . Veillez à autoriser l’accès lorsque vous y êtes invité.
Vous devriez recevoir un e-mail indiquant « Incompatibilité trouvée. Les données nécessitent votre révision. » Cela indique qu’il existe des différences entre les lignes dans speaker-registrations.xlsx et les lignes dans event-data.xlsx. Ouvrez speaker-registrations.xlsx pour voir plusieurs cellules mises en surbrillance en cas de problèmes potentiels avec les listes d’inscription de l’orateur.

Partager via