Power Automate を使用した Excel ファイルの相互参照

このソリューションでは、2 つの Excel ファイル間でデータを比較して不一致を見つける方法を示します。 Office スクリプトを使用してデータを分析し、Power Automate を使用してブック間で通信します。

このサンプルでは、 JSON オブジェクトを使用してブック間でデータを渡します。 JSON の操作の詳細については、「JSON を 使用して Office スクリプトとの間でデータを渡す」を参照してください。

シナリオ例

あなたは、今後の会議の講演者をスケジュールしているイベント コーディネーターです。 イベント データは 1 つのスプレッドシートに保持し、話者の登録は別のスプレッドシートに保持します。 2 つのブックが確実に同期されるようにするには、Office スクリプトを含むフローを使用して、潜在的な問題を強調表示します。

Excel ファイルのサンプル

次のファイルをダウンロードして、サンプルのすぐに使用できるブックを取得します。

1. event-data.xlsx
2. speaker-registrations.xlsx

サンプルを自分で試すには、次のスクリプトを追加します。 Excel では、[新しいスクリプトの自動化] を>使用してコードを貼り付け、推奨される名前でスクリプトを保存します。

サンプル コード: イベント データを取得する

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
}

サンプル コード: 話者の登録を検証する

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 フロー: ブック全体の不整合を確認する

このフローは、最初のブックからイベント情報を抽出し、そのデータを使用して 2 番目のブックを検証します。

1. Power Automate にサインインし、新しいインスタント クラウド フローを作成します。

2. [ フローを手動でトリガーする ] を選択し、[ 作成] を選択します。

3. フロー ビルダーで、[アクションの追加] + ボタンを選択 します。 Excel Online (Business) コネクタの [スクリプトの実行] アクションを選択します。 アクションには次の値を使用します。

   • 場所: OneDrive for Business
   • ドキュメント ライブラリ: OneDrive
   • ファイル: event-data.xlsx (ファイル選択で選択)
   • スクリプト: イベント データを取得する

4. この手順の名前を変更します。 作業ウィンドウで現在の名前 "スクリプトの実行" を選択し、"イベント データの取得" に変更します。

5. Excel Online (Business) コネクタの [スクリプトの実行] アクションを使用する 2 つ目のアクションを追加します。 このアクションでは、イベント データの検証スクリプトの入力として、Get イベント データ スクリプトから返された値が使用されます。 アクションには次の値を使用します。

   • 場所: OneDrive for Business
   • ドキュメント ライブラリ: OneDrive
   • ファイル: speaker-registration.xlsx (ファイル選択で選択)
   • スクリプト: 話者の登録を検証する
   • keys: result (イベント データの取得から動的コンテンツ)

6. この手順の名前も変更します。 作業ウィンドウで現在の名前 "Run script 1" を選択し、"話者登録の検証" に変更します。

7. このサンプルでは、メール クライアントとして Outlook を使用します。 このサンプルでは、Office 365 Outlook コネクタの [送信と電子メール (V2)] アクションを追加します。 Power Automate でサポートされている任意の電子メール コネクタを使用できます。 このアクションでは、 スピーカー登録 スクリプトの検証から返された値を電子メール本文のコンテンツとして使用します。 アクションには次の値を使用します。

   • 対象: テスト用メール アカウント (または個人用メール)
   • 件名: イベント検証の結果
   • 本文: 結果 (話者登録の検証からの動的コンテンツ)

   

8. フローを保存します。 フロー デザイナーは次の図のようになります。

   

9. フロー エディター ページの [テスト ] ボタンを使用するか、[ マイ フロー ] タブでフローを実行します。プロンプトが表示されたら、必ずアクセスを許可してください。

10. "不一致が見つかりました" というメールが届きます。 データにはレビューが必要です。これは、speaker-registrations.xlsxの行と event-data.xlsx の行 の間に違いがあることを示します。 speaker-registrations.xlsx を開いて、スピーカー登録リストに問題が発生する可能性がある強調表示された複数のセルを表示します。