使用 Power Automate 交叉引用 Excel 文件

项目
12/06/2023

此解决方案演示如何比较两个 Excel 文件之间的数据，以查找差异。它使用 Office 脚本来分析数据，并使用 Power Automate 在工作簿之间进行通信。

此示例使用 JSON 对象在工作簿之间传递数据。有关使用 JSON 的详细信息，请阅读使用 JSON 将数据传入 Office 脚本和从 Office 脚本传递数据。

示例方案

你是一名活动协调员，他正在为即将召开的会议安排演讲者。将事件数据保存在一个电子表格中，将演讲者注册保存在另一个电子表格中。若要确保两个工作簿保持同步，请结合 Office 脚本使用流来突出显示任何潜在问题。

示例 Excel 文件

下载以下文件，获取本示例的随时可用的工作簿。

添加以下脚本以亲自试用示例！在 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 流：检查工作簿中的不一致

此流从第一个工作簿中提取事件信息，并使用该数据来验证第二个工作簿。

登录到 Power Automate 并创建新的 即时云流。
选择 “手动触发流 ”，然后选择“ 创建”。
在流生成器中 + ，选择按钮和 “添加操作”。选择 Excel Online (Business) 连接器的 “运行脚本 ”操作。对操作使用以下值。
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件：使用文件选择器) 选择了 event-data.xlsx (
- 脚本：获取事件数据
重命名此步骤。在任务窗格中选择当前名称“运行脚本”，并将其更改为“获取事件数据”。
添加使用 Excel Online (Business) 连接器的 “运行脚本” 操作的第二个操作。此操作使用 Get 事件数据 脚本返回的值作为 验证事件数据 脚本的输入。对操作使用以下值。
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件：使用文件选择器) 选择了 speaker-registration.xlsx (
- 脚本：验证说话人注册
- keys：获取事件数据) 的结果 (动态内容
也重命名此步骤。在任务窗格中选择当前名称“运行脚本 1”，并将其更改为“验证说话人注册”。
此示例使用 Outlook 作为电子邮件客户端。对于此示例，请添加Office 365 Outlook 连接器的“发送”和“电子邮件 (V2) 操作。可以使用 Power Automate 支持的任何电子邮件连接器。此操作使用 验证说话人注册 脚本中返回的值作为电子邮件正文内容。对操作使用以下值。
- 目标：测试电子邮件帐户 (或个人电子邮件)
- 主题：事件验证结果
- 正文：验证说话人注册) (动态内容的结果
保存流。流设计器应如下图所示。
使用流编辑器页上的“ 测试 ”按钮，或通过“我的流”选项卡运行流。请务必在出现提示时允许访问。
应收到一封电子邮件，指出“找到不匹配。数据需要你的评审。”这表示speaker-registrations.xlsx中的行与 event-data.xlsx 中的行 之间存在差异。打开 speaker-registrations.xlsx ，查看演讲者注册列表存在潜在问题的几个突出显示单元格。

通过