Office 脚本示例方案：自动任务提醒

项目
02/21/2024

在此方案中，你将管理项目。每月使用 Excel 工作表跟踪员工的状态。你通常需要提醒用户填写其状态，因此你决定自动执行该提醒过程。

你将创建一个 Power Automate 流，以向缺少状态字段的人员发送消息，并将他们的响应应用于电子表格。为此，你将开发一对脚本来处理工作簿的处理。第一个脚本获取具有空白状态的人员列表，第二个脚本将状态字符串添加到右侧行。你还将利用 Teams 自适应卡片让员工直接从通知中输入其状态。

涵盖的脚本技能

在 Power Automate 中创建流
将数据传递给脚本
从脚本返回数据
Teams 自适应卡片
表格

先决条件

此方案使用 Power Automate 和 Microsoft Teams。

设置说明

下载工作簿

将示例工作簿下载到 OneDrive。

下载示例工作簿
在 Excel 中打开工作簿。

创建脚本

首先，我们需要一个脚本来获取电子表格中缺少的状态报告的所有员工。在“ 自动 ”选项卡下，选择“ 新建脚本 ”，并将以下脚本粘贴到编辑器中。

/**
 * This script looks for missing status reports in a project management table.
 *
 * @returns An array of Employee objects (containing their names and emails).
 */
function main(workbook: ExcelScript.Workbook): Employee[] {
  // Get the first worksheet and the first table on that worksheet.
  let sheet = workbook.getFirstWorksheet()
  let table = sheet.getTables()[0];

  // Give the column indices names matching their expected content.
  const NAME_INDEX = 0;
  const EMAIL_INDEX = 1;
  const STATUS_REPORT_INDEX = 2;

  // Get the data for the whole table.
  let bodyRangeValues = table.getRangeBetweenHeaderAndTotal().getValues();

  // Create the array of Employee objects to return.
  let people: Employee[] = [];

  // Loop through the table and check each row for completion.
  for (let i = 0; i < bodyRangeValues.length; i++) {
    let row = bodyRangeValues[i];
    if (row[STATUS_REPORT_INDEX] === "") {
      // Save the email to return.
      people.push({ name: row[NAME_INDEX].toString(), email: row[EMAIL_INDEX].toString() });
    }
  }

  // Log the array to verify we're getting the right rows.
  console.log(people);

  // Return the array of Employees.
  return people;
}

/**
 * An interface representing an employee.
 * An array of Employees will be returned from the script
 * for the Power Automate flow.
 */
interface Employee {
  name: string;
  email: string;
}

使用名称 Get 人员 保存脚本。

接下来，我们需要另一个脚本来处理状态报表卡，并将新信息放入电子表格中。在“代码编辑器”任务窗格中，选择“ 新建脚本 ”，并将以下脚本粘贴到编辑器中。

/**
 * This script applies the results of a Teams Adaptive Card about
 * a status update to a project management table.
 *
 * @param senderEmail - The email address of the employee updating their status.
 * @param statusReportResponse - The employee's status report.
 */
function main(workbook: ExcelScript.Workbook,
  senderEmail: string,
  statusReportResponse: string) {

  // Get the first worksheet and the first table in that worksheet.
  let sheet = workbook.getFirstWorksheet();
  let table = sheet.getTables()[0];

  // Give the column indices names matching their expected content.
  const NAME_INDEX = 0;
  const EMAIL_INDEX = 1;
  const STATUS_REPORT_INDEX = 2;

  // Get the range and data for the whole table.
  let bodyRange = table.getRangeBetweenHeaderAndTotal();
  let tableRowCount = bodyRange.getRowCount();
  let bodyRangeValues = bodyRange.getValues();

  // Create a flag to denote success.
  let statusAdded = false;

  // Loop through the table and check each row for a matching email address.
  for (let i = 0; i < tableRowCount && !statusAdded; i++) {
    let row = bodyRangeValues[i];

    // Check if the row's email address matches.
    if (row[EMAIL_INDEX] === senderEmail) {
      // Add the Teams Adaptive Card response to the table.
      bodyRange.getCell(i, STATUS_REPORT_INDEX).setValues([
        [statusReportResponse]
      ]);
      statusAdded = true;
    }
  }

  // If successful, log the status update.
  if (statusAdded) {
    console.log(
      `Successfully added status report for ${senderEmail} containing: ${statusReportResponse}`
    );
  }
}

使用名称“ 保存状态”保存脚本。

创建 Power Automate 流

打开 “Power Automate 创建 ”选项卡。

提示

如果之前尚未创建流，请检查我们的教程开始在 Power Automate 中使用脚本来了解基础知识。
创建新的 即时云流。
从选项中选择 “手动触发流 ”，然后选择“ 创建”。
流需要调用 Get 人员 脚本来获取状态字段为空的所有员工。在流生成器中 + ，选择按钮和 “添加操作”。选择 Excel Online (Business) 连接器的 “运行脚本 ”操作。为流步骤提供以下条目：
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件：task-reminders.xlsx (通过文件浏览器) 选择
- 脚本：获取人员
接下来，流需要处理脚本返回的数组中的每个 Employee。添加 Microsoft Teams 连接器的 Post 自适应卡并等待响应操作。

发送自适应卡需要将卡的 JSON 作为消息提供。可以使用自适应卡片Designer创建自定义卡片。对于此示例，请使用以下 JSON。

{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "type": "AdaptiveCard",
  "version": "1.0",
  "body": [
    {
      "type": "TextBlock",
      "size": "Medium",
      "weight": "Bolder",
      "text": "Update your Status Report"
    },
    {
      "type": "Image",
      "altText": "",
      "url": "https://i.imgur.com/f5RcuF3.png"
    },
    {
      "type": "TextBlock",
      "text": "This is a reminder to update your status report for this month's review. You can do so right here in this card, or by adding it directly to the spreadsheet.",
      "wrap": true
    },
    {
      "type": "Input.Text",
      "placeholder": "My status report for this month is...",
      "id": "response",
      "isMultiline": true
    }
  ],
  "actions": [
    {
      "type": "Action.Submit",
      "title": "Submit",
      "id": "submit"
    }
  ]
}

对于“ 收件人” 字段，添加来自动态内容 的电子邮件 ， (所选内容将) 具有 Excel 徽标。添加 电子邮件 会导致流步骤被 For each 块括起来。这意味着该数组将由 Power Automate 循环访问。
按如下所示填写剩余字段：
- 发布为：流机器人
- 发布内容：与 Flow 机器人聊天
- 更新消息：感谢你提交状态报告。你的响应已成功添加到电子表格。
在“针对每个”块中，遵循“发布自适应卡并等待响应操作，添加新操作。选择 Excel Online (Business) 连接器的 “运行脚本 ”操作。为流步骤提供以下条目：
- 位置：OneDrive for Business
- 文档库：OneDrive
- 文件：task-reminders.xlsx (通过文件浏览器) 选择
- 脚本：保存状态
- senderEmail： 电子邮件 (Excel) 的动态内容
- statusReportResponse： 响应 (来自 Teams) 的动态内容
保存流。流设计器应如下图所示。