Querverweis auf Excel-Dateien mit Power Automate

Artikel
12/06/2023

Diese Lösung zeigt, wie Sie Daten über zwei Excel-Dateien hinweg vergleichen, um Abweichungen zu finden. Es verwendet Office-Skripts zum Analysieren von Daten und Power Automate für die Kommunikation zwischen den Arbeitsmappen.

In diesem Beispiel werden Daten mithilfe von JSON-Objekten zwischen Arbeitsmappen übergeben. Weitere Informationen zum Arbeiten mit JSON finden Sie unter Verwenden von JSON zum Übergeben von Daten an und aus Office-Skripts.

Beispielszenario

Sie sind ein Ereigniskoordinator, der Referenten für anstehende Konferenzen plant. Sie behalten die Ereignisdaten in einer Kalkulationstabelle und die Sprecherregistrierungen in einer anderen. Um sicherzustellen, dass die beiden Arbeitsmappen synchron bleiben, verwenden Sie einen Flow mit Office-Skripts, um potenzielle Probleme hervorzuheben.

Excel-Beispieldateien

Laden Sie die folgenden Dateien herunter, um einsatzbereite Arbeitsmappen für das Beispiel zu erhalten.

Fügen Sie die folgenden Skripts hinzu, um das Beispiel selbst auszuprobieren! Verwenden Sie in Excel Dasneue Skriptautomatisieren>, um den Code einzufügen und die Skripts mit den vorgeschlagenen Namen zu speichern.

Beispielcode: Abrufen von Ereignisdaten

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
}

Beispielcode: Überprüfen von Sprecherregistrierungen

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
}

Power Automate-Flow: Überprüfen auf Inkonsistenzen in den Arbeitsmappen

Dieser Flow extrahiert die Ereignisinformationen aus der ersten Arbeitsmappe und verwendet diese Daten, um die zweite Arbeitsmappe zu überprüfen.

Melden Sie sich bei Power Automate an, und erstellen Sie einen neuen Instant Cloud Flow.
Wählen Sie Manuell einen Flow auslösen und dann Erstellen aus.
Wählen Sie im Flow-Generator die + Schaltfläche und dann Aktion hinzufügen aus. Wählen Sie die Aktion Skript ausführen des Excel Online (Business)-Connectors aus. Verwenden Sie die folgenden Werte für die Aktion.
- Location: OneDrive for Business
- Document Library: OneDrive
- Datei: event-data.xlsx (mit der Dateiauswahl ausgewählt)
- Skript: Abrufen von Ereignisdaten
Benennen Sie diesen Schritt um. Wählen Sie im Aufgabenbereich den aktuellen Namen "Skript ausführen" aus, und ändern Sie ihn in "Ereignisdaten abrufen".
Fügen Sie eine zweite Aktion hinzu, die die Aktion Skript ausführen des Excel Online (Business)-Connectors verwendet. Diese Aktion verwendet die zurückgegebenen Werte aus dem Skript Ereignisdaten abrufen als Eingabe für das Skript Zum Überprüfen von Ereignisdaten . Verwenden Sie die folgenden Werte für die Aktion.
- Location: OneDrive for Business
- Document Library: OneDrive
- Datei: speaker-registration.xlsx (mit der Dateiauswahl ausgewählt)
- Skript: Überprüfen der Sprecherregistrierung
- keys: result (dynamischer Inhalt aus Ereignisdaten abrufen)
Benennen Sie auch diesen Schritt um. Wählen Sie im Aufgabenbereich den aktuellen Namen "Skript ausführen 1" aus, und ändern Sie ihn in "Sprecherregistrierung überprüfen".
In diesem Beispiel wird Outlook als E-Mail-Client verwendet. Fügen Sie für dieses Beispiel die Aktion Senden und E-Mail (V2)des Office 365 Outlook-Connectors hinzu. Sie können einen beliebigen E-Mail-Connector verwenden, der von Power Automate unterstützt wird. Diese Aktion verwendet die zurückgegebenen Werte aus dem Skript Sprecherregistrierung überprüfen als E-Mail-Textinhalt. Verwenden Sie die folgenden Werte für die Aktion.
- An: Ihr Test-E-Mail-Konto (oder persönliche E-Mail-Adresse)
- Betreff: Ergebnisse der Ereignisüberprüfung
- Text: Ergebnis (dynamischer Inhalt aus Sprecherregistrierung überprüfen)
Speichern Sie den Flow. Der Flow-Designer sollte wie in der folgenden Abbildung aussehen.
Verwenden Sie die Schaltfläche Test auf der Flow-Editor-Seite, oder führen Sie den Flow über die Registerkarte Meine Flows aus. Achten Sie darauf, den Zugriff zuzulassen, wenn Sie dazu aufgefordert werden.
Sie sollten eine E-Mail mit dem Hinweis "Konflikt gefunden. Daten erfordern Ihre Überprüfung." Dies weist darauf hin, dass es Unterschiede zwischen Zeilen in speaker-registrations.xlsx und Zeilen in event-data.xlsxgibt. Öffnen Sie speaker-registrations.xlsx , um mehrere hervorgehobene Zellen anzuzeigen, in denen potenzielle Probleme mit den Sprecherregistrierungslisten auftreten.

Freigeben über