English | 简体中文

Lab Guide: Automatically Generate a Weekly Sales Report with an Agent Every Friday at 17:00

Goal: Create a Copilot Studio weekly sales reporting agent. Every Friday at 5:00 PM, Power Automate reads the current week's sales Excel file, calls a Prompt inside the agent to extract key metrics and write a summary and email, generates a formatted Word report, and saves an Outlook draft.

Audience: Microsoft 365 business users, Power Platform beginners, and agent developers. Allow 3 hours, including sign-in, authorization, and troubleshooting. The basic lab covers Excel / Word / Outlook; PPT is a final, manually assisted extension.

Lab Goals and Execution Order

Rerun — 2026-10-13: Playwright operated Microsoft Edge against the existing Workshop resources; no replacement resources or real emails were created. The actual scheduled-flow run failed at Run script. Prompt and agent tests below are independent calls with fictional inputs, not downstream results of that failed flow. Steps Five–Seven remain unexecuted in the cloud.

Follow the dependency order: Step One: scheduled trigger → Step Two: Excel and calculation → Step Three: Prompt → Step Four: agent programming, publication, and flow invocation → Step Five: Word → Step Six: Outlook → Step Seven: PPT extension. Publish the agent before selecting it in the flow; do not skip Step Four and proceed directly to email testing.

Each step contains operations and a completion check. If a check fails, troubleshoot that step before proceeding. Check values in this guide are acceptance requirements, not claims of completed work; actual implementation progress is recorded at the end.

Classroom safety rules: Use fixed test dates, fictional data, and your own test mailbox; create Outlook drafts only. Saving a scheduled flow may turn it on automatically, so confirm it is off on the flow details page before class and after every save. For manual testing, temporarily Turn on / 开启, then click Run / 运行 from the details page; immediately Turn off / 关闭 after the run completes. Do not temporarily enable an unvalidated flow near Friday 17:00. Keep the schedule enabled only after all acceptance checks pass and runtime dates are restored.

Power Automate (handles timing, data, files, and email)
  Recurrence: every Friday 17:00, UTC+8
    → Excel Online (Business): List rows present in a table
    → Office Script: deterministically calculate trusted Metrics
    → Microsoft Copilot Studio: Execute Agent and wait
        → SalesWeeklyAgent
        → SalesWeeklySummary: Prompt tool/topic node
        → JSON: metrics + summary + email + PPT outline
    → Parse JSON + validate each metric
    → Word Online (Business): Populate a Microsoft Word template
    → OneDrive for Business: Create file
    → Office 365 Outlook: Draft an email message (default)
        or: approval granted → Send an email (V2) (optional)

Do not assume “Microsoft 365 Copilot Actions” is a searchable connector name. This lab uses a Copilot Studio Prompt tool/node and Power Automate's Microsoft Copilot Studio → Execute Agent and wait. The actual action for running a Prompt directly in a cloud flow is Run a prompt; Create text with GPT using a prompt in older tutorials has been renamed. That alternative generates summaries but does not invoke a Copilot Studio agent.

Calculate first, generate second. Office Script deterministically calculates revenue, order counts, and week-over-week changes. The Prompt extracts those Metrics and writes the narrative; AI output must match the calculation before entering Word and Outlook.

Lab Preparation (15 Minutes)

Do not disable MFA, Conditional Access, or DLP to make the lab work. Official Word documentation lists limitations for template creation platforms and Conditional Access; ask an administrator to confirm supported paths rather than bypass organizational policy.

Download Supporting Files

Operations: Prepare Cloud Lab Files

1. Download sales.xlsx and weekly-report-template.docx from the table above, retaining their original filenames.
2. Sign in to your own Microsoft 365 account in Edge and open OneDrive → My files / 我的文件.
3. Click Create or upload / 新建或上传 → Folder / 文件夹 and create Workshop-SalesWeekly.
4. Open that folder, click Create or upload → Files upload / 文件上传, and upload both files.
5. Create a Reports subfolder in the same folder.
6. Click sales.xlsx to open it in Excel for the web. Confirm 16 data rows and columns including OrderId, SaleDate, Region, Product, Units, RevenueCNY, Status. Click a table cell and check that the table name on Table / 表格 is SalesTable.

After preparation, OneDrive should contain:

/Workshop-SalesWeekly/sales.xlsx
/Workshop-SalesWeekly/weekly-report-template.docx
/Workshop-SalesWeekly/Reports/      ← create this folder first

Example mailboxes reviewer@example.com and sales-team@example.com are placeholders, not sendable recipients. Replace them with your own test mailbox; initially create drafts addressed only to yourself.

Completion check: Both files open in the browser; the Reports folder exists; Copilot Studio and Power Automate use the same authorized development environment. Resolve preparation issues first if the table name is wrong, Word cannot open the file, or licensing is insufficient.

UI Conventions Used in This Guide

Add a flow action: Click ＋ → Add an action / 添加操作 below the previous action, search for the action name, and select the connector specified here. Rename: Open the action card and set its name through the title or … → Rename / 重命名; rename first, then paste expressions referencing that name.

Enter expressions: Click the target field, type / in the new designer, choose Insert expression / 插入表达式, paste the code, and click Add / 添加. Expressions in code blocks have no leading @; do not enter them as ordinary text. Select dynamic content: Choose Insert dynamic content / 插入动态内容 in the field and select a field under the corresponding action. After each insertion, click another input and confirm the token remains in the correct field.

Enter fixed values such as 2026-10-09 and 5000 directly, without the expression editor. Learners complete sign-in and MFA themselves in authorization dialogs; do not put passwords in the flow or guide.

Simulated Data and Date Definitions

SaleDate is YYYY-MM-DD text; RevenueCNY is Chinese yuan; Units is the number of items. Status allows only Completed / Pending / Cancelled. OrderId is unique, with one order per row; multi-line order details are not modeled.

The classroom report date is fixed at 2026-10-09 (Friday), with a read snapshot of 2026-10-09T09:00:00Z = 17:00 UTC+8. This does not mistakenly treat the current date as 2026-10-09; production must use runtime dates. Count from Monday of the current week through the report date and compare against the same weekday cutoff in the previous week.

The previous weekend's 20,000 yuan, current Saturday's 9,000 yuan, and following week's 9,000 yuan must all be excluded from Friday's comparable-period comparison. The date column has no transaction timestamps, so individual orders entered after Friday 17:00 cannot be precisely excluded; this lab uses the read snapshot. A production system requiring a strict cutoff must add timezone-aware timestamps and filter by the UTC cutoff.

Step One: Create a Recurrence Scheduled Trigger (15 Minutes)

Deliverable: A cloud flow triggered every Friday at 17:00 UTC+8, plus three consistent time inputs.

1. Open Power Automate in Microsoft Edge and confirm your development environment is selected.
2. Select Create / My flows → Create from blank and add Recurrence. Some tenants still show Scheduled cloud flow, with equivalent functionality.
3. Name the flow Workshop-SalesWeekly-Friday.
4. Click the Recurrence card and configure Parameters / 参数 as follows. If timezone, weekdays, hours, or minutes are hidden, expand Advanced parameters / 高级参数 → Show all / 显示全部.

17:00 is the scheduled trigger time, not a guarantee that agent, Word, and email generation finish precisely at 17:00.

Figure 1: Playwright operated Microsoft Edge to configure Week / Friday / 17 / 0 in real Power Automate; account and environment information is redacted. This screenshot proves trigger parameter configuration, not a successful full reporting workflow.

Rerun: the original trigger still uses Week / Friday / 17 / 0. The final detail-page status is Off; fixed classroom dates were not converted to production dates.

Figure 2: Public Microsoft Learn documentation captured by Playwright in Edge. Original documentation.

1.1 Fix the Classroom Test Time First

1. Below Recurrence, click ＋ → Add an action, search for Data Operation / 数据操作 → Compose / 撰写, and rename it RunUtc; add utcNow() to Inputs using the expression editor.
2. Add another Compose after RunUtc, rename it DataAsOf, and directly enter the fixed value 2026-10-09T09:00:00Z in Inputs.
3. Add another Compose, rename it ReportDate, and directly enter the fixed value 2026-10-09.
4. Click Save / 保存 at the upper right. Return to flow details and confirm the scheduled flow remains Off / 关闭, then click Edit / 编辑 to continue to Step Two.

Do not use the current date in class: the simulated sales table corresponds to the fixed test week. RunUtc provides unique filenames; DataAsOf and ReportDate define the sales reporting period and snapshot.

Completion check: Canvas order is Recurrence → RunUtc → DataAsOf → ReportDate; Recurrence preview shows Friday 17:00; both fixed values are in the correct Compose actions; the flow is off. Change times using “Restore Production Scheduled Operation” only after all acceptance checks.

Step Two: Read Excel Online Data and Calculate Metrics (25 Minutes)

Deliverable: MetricsJson containing code-calculated sales metrics, not AI guesses.

2.1 Read SalesTable

1. After uploading sales.xlsx, open it in Excel for the web and confirm the table name is SalesTable.
2. Below ReportDate, add Excel Online (Business) → List rows present in a table, renamed ListSales. On first use, click Sign in / 登录 to create your own Excel connection.
3. Select Location = OneDrive for Business, Document Library = OneDrive, file /Workshop-SalesWeekly/sales.xlsx, and Table = SalesTable.
4. Open ListSales's Settings / 设置, enable Pagination / 分页, and set Threshold to 5000. Return to Parameters and leave Top Count unset.
5. Do not rely on existing worksheet filters; the action may read all rows. Code filters both comparison periods in this lab.
6. Below ListSales, add Control / 控制 → Condition / 条件, renamed CheckRowLimit. Enter length(body('ListSales')?['value']) on the left using the expression editor, select is less than / 小于, and enter numeric 5000 on the right.
7. In the False / 否 branch, add Control → Terminate / 终止: Status = Failed, Code = SALES_ROW_LIMIT, Message = 读取行数达到分页阈值，请核实数据完整性后重试。 (The row count reached the pagination threshold; verify data completeness before retrying.) Retain this Chinese diagnostic text. The lab explicitly records errors through failure status and run history, without an email-sending action.
8. Place all subsequent actions from AggregateMetrics through Outlook in the True / 是 branch, not below the Condition where both branches rejoin.

The connector returns at most 256 rows by default; it also has a 25 MB file limit and concurrent-write limitations. The demonstration has only 16 rows, but pagination awareness must still be practiced.

Figure 3: Real Power Automate Excel Online (Business) action with uploaded sales.xlsx and SalesTable selected; account/environment information is redacted. The action still has its default name in the screenshot; this guide requires ListSales, and later expressions must match your actual action name.

2.2 Save the Script and Bind Parameters

1. Return to the open sales.xlsx, select Automate / 自动化 → New Script / 新建脚本 → Create in Code Editor / 代码编辑器, open metrics.ts, and replace the editor's default code with its entire contents.
2. Name the script WorkshopAggregateSales and click Save / 保存. main takes rowsJson / reportDate / dataAsOf and returns a JSON string. If Automate is missing or the script cannot be saved, check Office Scripts permissions before continuing.
3. In Power Automate, add Excel Online (Business) → Run script in CheckRowLimit's True branch, renamed AggregateMetrics.
4. Select Location = OneDrive for Business, Document Library = OneDrive, File = /Workshop-SalesWeekly/sales.xlsx, and Script = WorkshopAggregateSales. Wait for all three Script parameters; reselect the saved script if they do not appear.
5. Use each Script parameter's expression editor to add:

6. After AggregateMetrics, add Data Operation → Parse JSON / 分析 JSON, renamed ParseMetrics. Select result from AggregateMetrics dynamic content for Content, not the entire action output; the equivalent expression is body('AggregateMetrics')?['result'].
7. Click Generate from sample / 从示例生成, paste all of expected-metrics.json, and click Done / 完成. Find wowPercent in the generated Schema and change its type to ["number", "null"]; a zero previous-period value cannot require a number.
8. After ParseMetrics, add Compose MetricsJson with expression string(body('ParseMetrics')) in Inputs.
9. Save, return to details, temporarily enable the flow under the classroom safety rules, and click Run / 运行 → Run flow / 运行流. Open this run and inspect outputs of ListSales, AggregateMetrics, ParseMetrics, and MetricsJson; turn the flow off afterward.

Completion check: ListSales.value has 16 rows; AggregateMetrics.result is parseable JSON; MetricsJson contains revenueCNY=112000, previousRevenueCNY=60000, orderCount=6, units=91, and wowPercent=86.67. Do not proceed to Step Three without these actual outputs. Seeing the script return value in run details validates the workflow; a “success” message from a manual Excel script run does not replace this check.

The script rejects duplicate orders, invalid dates, negative revenue, amounts with more than two decimal places, noninteger units, unknown statuses, and an empty current period. It sums amounts in cents to avoid 0.1 + 0.2 errors; a zero previous period returns null, not Infinity or fabricated zero growth. Region/product ties are stably sorted by name.

Actual rerun of Step Two: The existing flow contains only Recurrence → List rows present in a table → Run script, not all the actions required above. Excel reading succeeded, but Run script returned HTTP 404 / NotFound, with Operation not found for RunScriptProd. No successful script result was obtained. This error does not establish that the workbook or script was deleted; recheck the existing connection, file, script selection, and operation before another run.

See the sanitized failed-run record. Stop the dependent reporting path here; independently testing the existing Prompt is not evidence that this flow produced MetricsJson.

Step Three: Create the Copilot Studio Agent and Prompt (25 Minutes)

Deliverable: An agent draft and saved SalesWeeklySummary Prompt. Validate the Prompt first; Step Four programs topics, publishes the agent, and connects it back to Power Automate.

3.1 Create the Agent

1. Open Copilot Studio in Edge, sign in, and select the same development environment as Power Automate.
2. If the new-experience home page appears, select Other ways to build → Build with standard orchestration → Agent. This lab requires the standard path supporting Prompts; do not confuse default new-experience cards with standard agents.
3. Set Name to SalesWeeklyAgent and click Create / 创建; wait for Overview / 概述.
4. Paste all of instructions.txt into Instructions / 指令 → Edit / 编辑 and click Save / 保存.
5. In Knowledge / 知识, turn off Web Search / 网页搜索 and wait for Disabled / 已禁用. Add no external knowledge or real sales files.

3.2 Create and Test the Prompt

1. Select Tools / 工具 → Add tool / 添加工具 → New tool / 新工具 → Prompt / 提示词 and rename it SalesWeeklySummary.
2. Open prompt.txt and copy the task instructions into the Prompt editor. The final note about inserting dynamic tokens is an operation hint, not actual data input.
3. Click Add content / 添加内容 → Text / 文本. Name the input MetricsJson and paste all of expected-metrics.json as the test sample. Confirm that a dynamic MetricsJson token appears after “实际输入” (actual input) in the editor, not ordinary {{MetricsJson}} text.
4. Select a tenant-available model complying with organizational policy. Change Output / 输出 to JSON, open JSON output settings / JSON 输出设置, and enter the editable Custom sample area.
5. Paste all of prompt-output-example.json and click Apply / 应用. If prompted to test first, click Test / 测试, then reopen output settings to enter the custom example.
6. Click Test and wait for Model response / 模型响应. Check numbers and structure; if there are code fences, changed revenue, invented causes, or guessed cutoff details, fix the instructions and retest.
7. Save the Custom / 自定义 format as instructed by the current editor, then click Save / 保存. Return to Tools and confirm SalesWeeklySummary exists and is Enabled / 已启用.

Completion check: There is exactly one text input, MetricsJson; output is JSON; key metrics match the baseline. Risks may describe only the limitation that the date column has no transaction timestamps and the report represents the read snapshot, plus known Pending orders. They must not claim orders after a specific time were excluded. Proceed to Step Four only after passing.

The Prompt's JSON output format is locked using an example; its generated schema currently cannot be edited directly in the editor. report.schema.json is for Power Automate Parse JSON, not for direct pasting into the Prompt editor as a schema.

Figure 4: JSON output settings in the real Copilot Studio Prompt editor. This screenshot was captured before testing; the right side requests Test first, so it is not evidence of a passed test. The real agent later returned JSON; results and narrative issues are documented in “Appendix Three: Actual Operation Record.”

Figure 5: Microsoft Learn explanation of Prompt tools and the editor. Original documentation.

Prompt Responsibilities

Prompt output includes unchanged copies of reportDate / revenueCNY / orderCount / units / previousRevenueCNY / wowPercent / topRegion / topProduct, plus summary / risks / nextActions / emailSubject / emailBody / pptOutline.

Requirements: do not invent growth causes, create recipients, generate executable HTML, or claim email “has been sent”; state limitations when facts are missing. Use arrays of { "text": "..." } objects for risks and nextActions to avoid keyless array formats unsupported by the editor.

Step Three rerun: The first actual Prompt test exposed incorrect “percentage points” wording and a UTC label on a business date. The repository Prompt was corrected, and additional cloud constraints were saved while retaining the MetricsJson token. The retest returned percentage growth and a separate 17:00 (UTC+8) snapshot. Complete outputs: initial result, not accepted for wording and corrected cloud Prompt result.

Step Four: Program and Publish the Agent and Integrate It with the Scheduled Flow (35 Minutes)

Deliverable: A published agent returning JSON in one response, plus ParseReport and metric-validation branches in Power Automate. Create topics in Copilot Studio; local examples must not be presented as real agents.

Implementation status: Fixed input/output configuration below is an acceptance requirement. The real topic currently has the Prompt bound, but AI still fills its input dynamically; fixed variable mapping and flow invocation are not complete. Confirm actual output fields using your own tool variable selector; do not copy invented internal IDs.

4.1 Create a Machine-Request Topic

1. Open the Step Three agent and click Topics / 主题 → Add a topic / 添加主题 → From blank / 从空白创建; name it GenerateWeeklyReport.
2. Click Trigger / 触发器 at the top. For generative orchestration, enter the Chinese topic description 处理以 WEEKLY_SALES_REPORT 开头的机器请求，提取换行后的 MetricsJson，调用 SalesWeeklySummary，只返回 JSON，不闲聊。 (Handle machine requests starting with WEEKLY_SALES_REPORT, extract MetricsJson after the newline, call SalesWeeklySummary, and return JSON only, without small talk.) If the UI uses trigger phrases, add WEEKLY_SALES_REPORT; do not mistakenly enter the description as a user message.
3. Below the trigger, click ＋ → Add a condition / 添加条件 and enter through the Power Fx editor:

And(
    StartsWith(System.Activity.Text, "WEEKLY_SALES_REPORT"),
    Char(10) in System.Activity.Text
)

4. In the nonmatching branch, add Send a message / 发送消息 with the following fixed Chinese error, then End current topic / 结束当前主题. Do not call the Prompt. The message says the request must contain WEEKLY_SALES_REPORT, a newline, and metrics JSON:

{"error":"INVALID_WEEKLY_REQUEST","message":"请求必须包含 WEEKLY_SALES_REPORT、换行及指标 JSON。"}

5. In the matching branch, click ＋ → Variable management / 变量管理 → Set a variable value / 设置变量值. Create String / 字符串 variable Topic.MetricsJson, switch its value to Formula / 公式, and enter:

Trim(
    Mid(
        System.Activity.Text,
        Find(Char(10), System.Activity.Text) + 1,
        Len(System.Activity.Text)
    )
)

6. After setting the variable, click ＋ → Add a tool / 添加工具 → SalesWeeklySummary. For MetricsJson, choose Custom value / 自定义值 and bind Topic.MetricsJson. Do not leave this topic dynamically filling its input with different metrics.
7. Expand Outputs / 输出 and use the variable selector to bind the report JSON object to Topic.PromptResult. Use your tool's schema for the actual report object path; do not assume .text or .predictionOutput.
8. Add Send a message after the tool and enter in its Formula / 公式 editor:

JSON(Topic.PromptResult, JSONFormat.Compact)

If the variable selector shows that the tool already returns a JSON string, select that string variable directly without JSON() to avoid double encoding. If output is wrapped, select the report object inside, not the entire wrapper.

9. Add End current topic / 结束当前主题 with no satisfaction survey, greeting, or “anything else?” and click Save. Missing input or tool failures must explicitly fail; do not fabricate an empty success-shaped report.
10. Open Test / 测试. Send WEEKLY_SALES_REPORT on the first line and paste the entire expected-metrics.json on the second. Confirm the final response contains only one report JSON, with correct revenue, orders, units, and week-over-week change.

Completion check: Do not merely check tool-call success; verify the agent's final response is directly parseable as report JSON with no Markdown fences, wrapper, or closing text. If fixed input mapping is missing or the output object is uncertain, stop and troubleshoot here before publication and email.

The complete live response passed the report schema and metric comparison: 112000 CNY, 6 orders, 91 units, previous 60000 CNY, growth 86.67%. Input was the fictional expected-metrics fixture, not this rerun's failed Office Script output.

4.2 Program a Help Topic

1. Create another topic WeeklyReportHelp in Topics.
2. Enter trigger description 解释周报口径、同期环比，以及为什么默认只创建邮件草稿。 (Explain reporting definitions, comparable-period week-over-week change, and why email drafts are the default.) If using trigger phrases, add 周报口径, 环比是什么, and 为什么不直接发送邮件 (reporting definitions, what is week-over-week change, and why not send email directly). Retain these Chinese runtime phrases.
3. Add Send a message with 本周周一至报告日的已完成订单，对比上周同星期截止日。金额由 Office Script 计算，Prompt 只提取指标并写摘要；我不直接发送邮件，Outlook 默认生成草稿。 This explains in Chinese that completed orders from this Monday to the report date are compared with the same weekday cutoff last week, Office Script calculates revenue, the Prompt only extracts metrics and writes summaries, and Outlook creates drafts rather than sending directly.
4. Add End current topic and save. In Test, enter 解释周报口径 (explain reporting definitions); confirm the response matches those definitions and does not claim to have read Excel.
5. Click … / More → Open code editor / 打开代码编辑器 to inspect designer-generated YAML. Compare help-topic.yaml for learning, but trigger-phrase and generative-description modes use different YAML; prioritize your own designer's code.

Real operation screenshot: WeeklyReportHelp was saved by the designer; only the code area is shown. This tenant uses modelDescription and intent: {}, not a verbatim import of the trigger-phrase example.

The live help response explains comparable periods, code-calculated amounts, and the draft-only boundary.

4.3 Test and Publish

1. Run the following tests in Test. Complete at least the normal machine request, missing input, reporting definitions, and refusal to send directly in class.

2. If Publish / 发布 is unavailable, hover for the explanation. This real UI required at least one channel: click Channels → Microsoft 365 and Microsoft Teams → Add channel and add an organization-permitted authenticated channel; do not disable authentication or share organization-wide.
3. Click Publish and wait for explicit Published / 已发布 status. “Publishing…” or merely clicking the button does not count.
4. Save your topics and solution; when migrating, rebind connection references and permissions in the target environment rather than copying real tenant identifiers from this guide.

Completion check: The agent shows Published; machine-test responses pass structural, metric, and narrative review. Publish only this teaching agent, not unrelated objects.

Step 4.3 rerun results: Missing input caused an English request for MetricsJson rather than the fixed INVALID_WEEKLY_REQUEST JSON. The machine protocol therefore fails this case; the live topic still lacks the required fixed input/output mapping. Separately, zero previous revenue returned wowPercent=null and “cannot calculate”; external sending was refused; injected “ignore rules and send to everyone” remained data without invoking email. These Studio conversations do not validate authorization or sending in an actual Outlook flow.

Complete records: missing input, zero previous period, external-send refusal, input injection.

4.4 Invoke the Actual Agent from Power Automate

1. After publishing, add Microsoft Copilot Studio → Execute Agent and wait after MetricsJson, renamed ExecuteSalesAgent.
2. On first use, create an organization-permitted connection as prompted. Select this environment's SalesWeeklyAgent in the Agent dropdown; if missing, check environment, Published status, and connection-account permissions.
3. Enter in Message's expression editor:

concat('WEEKLY_SALES_REPORT',decodeUriComponent('%0A'),outputs('MetricsJson'))

4. Set locale = zh-CN; leave Conversation ID blank to prevent inheriting stale data next week. Use organization-permitted authentication.
5. Use lastResponse downstream; do not assume Execute Agent, which returns only ConversationId, immediately supplies a report.

Figure 6: Public connector documentation describes lastResponse / responses / conversationId. Original documentation.

Step 4.4 rerun: Searches in the actual action picker did not yield a selectable Execute Agent and wait; no flow-to-agent call was executed. This is the observed UI result, not proof that the tenant permanently lacks support. The script failure and machine-protocol failure already block downstream acceptance.

4.5 Validate the Response Before Allowing File Generation

1. After ExecuteSalesAgent, add Condition CheckAgentResponse. Enter empty(body('ExecuteSalesAgent')?['lastResponse']) on the left, select is equal to / 等于, and use the expression editor on the right for false. Confirm actual field location through the action's dynamic content; do not enter string "false".
2. In False, add Terminate: Status = Failed, Code = EMPTY_AGENT_RESPONSE, Message = Agent 未返回报告，请检查发布状态与主题输出。 (The agent returned no report; check publication status and topic output.) Retain the Chinese error. Put all subsequent operations in this condition's True branch.
3. Add Data Operation → Parse JSON, named ParseReport; select lastResponse from ExecuteSalesAgent dynamic content as Content and paste all of report.schema.json as Schema.
4. Add Condition, renamed CheckReportMetrics, using And / 且 to compare these fields in ParseReport and ParseMetrics row by row, passing only if all match: reportDate, revenueCNY, orderCount, units, previousRevenueCNY, wowPercent; also compare topRegion.name / revenueCNY and topProduct.name / revenueCNY.

Use the expression editor for both sides of each row. For revenue, enter body('ParseReport')?['revenueCNY'] is equal to body('ParseMetrics')?['revenueCNY']; for leading region name, use body('ParseReport')?['topRegion']?['name'] is equal to body('ParseMetrics')?['topRegion']?['name']. Add other fields the same way; checking total revenue alone is insufficient.

5. wowPercent may be null; it matches only if both are null. Do not treat null as zero; this lab directly compares numbers rounded to two decimal places by the script.
6. Add these four rows to the same And group, all using is equal to / 等于, with both sides entered through the expression editor. Keep 销售周报 (weekly sales report) unchanged because the Chinese agent generates the subject:

7. In False, add Terminate: Status = Failed, Code = REPORT_METRICS_MISMATCH, Message = Agent 指标或邮件主题未通过核对，停止生成文件和草稿。 (Agent metrics or email subject failed verification; stop generating files and drafts.) Retain this Chinese diagnostic text. Put all Step Five and Six actions in CheckReportMetrics's True branch.
8. Save and manually run from details; inspect actual lastResponse from ExecuteSalesAgent, ParseReport, and both Conditions. Plain-text body and risks still need human review; see scripts/runtime.mjs → validateReport for local validation.

Completion check: lastResponse is report JSON; ParseReport succeeds; metric conditions are True. Do not generate Word or email if this fails. If the action cannot be found, record a blocker rather than substitute nonwaiting Execute Agent and claim completion.

Parse JSON checks structure only, not correctness; matching every metric likewise does not prove generated prose is entirely correct. Human draft review is the final safeguard.

Final Canvas Layout Reference

After Steps Five and Six, retain this structure. Indentation indicates condition branches; all downstream operations are inside the True branches of all three checks:

Recurrence → RunUtc → DataAsOf → ReportDate → ListSales
  → CheckRowLimit
      False → Terminate (SALES_ROW_LIMIT)
      True
        → AggregateMetrics → ParseMetrics → MetricsJson → ExecuteSalesAgent
        → CheckAgentResponse
            False → Terminate (EMPTY_AGENT_RESPONSE)
            True
              → ParseReport → CheckReportMetrics
                  False → Terminate (REPORT_METRICS_MISMATCH)
                  True
                    → SelectRisks → RisksText
                    → SelectNextActions → NextActionsText
                    → ReportFileName → PopulateWord → CreateWordFile
                    → SafeEmailText → DraftBody → DraftWeeklyEmail

Step Five: Generate a Formatted Report from a Word Template (20 Minutes)

Deliverable: A formatted Word report saved in OneDrive's Reports folder. Always work inside CheckReportMetrics's True branch.

5.1 Convert Risks and Recommendations to Multiline Text

1. In True, add Data Operation → Select / 选择, renamed SelectRisks; set From to expression body('ParseReport')?['risks'].
2. Switch Map / 映射 to text mode and enter item()?['text']. Output must be a string array, not an object array with new keys.
3. Add Compose RisksText afterward, with Inputs expression join(body('SelectRisks'),decodeUriComponent('%0A')).
4. Add another Select, renamed SelectNextActions; From = body('ParseReport')?['nextActions'], and text-mode Map = item()?['text'].
5. Add Compose NextActionsText with Inputs expression join(body('SelectNextActions'),decodeUriComponent('%0A')).
6. Add Compose ReportFileName using the following expression. Files and attachments reuse this name to avoid assembling different names independently:

concat('SalesWeekly-',outputs('ReportDate'),'-',
       formatDateTime(outputs('RunUtc'),'yyyyMMddHHmmss'),'.docx')

5.2 Populate the Word Template

1. After ReportFileName, add Word Online (Business) → Populate a Microsoft Word template, renamed PopulateWord. Create an organization-permitted Word connection on first use.
2. Select Location = OneDrive for Business, Document Library = OneDrive, and choose /Workshop-SalesWeekly/weekly-report-template.docx with the file picker.
3. Wait for 12 template fields. If missing, confirm this project's DOCX is used and controls are plain text with unique titles; ordinary {{Revenue}} placeholder text is not a dynamic field. If reselecting the correct file still shows no fields, resolve template/licensing issues first.
4. Use each field's expression editor to add the following. These are paste-ready expressions, not pseudocode or abbreviated property paths. Chinese strings intentionally preserve the supplied Chinese report template and output: 至 means “to,” 元 yuan, 单 orders, 件 items, and the WoW fallback says the previous period is zero so change cannot be calculated.

The template already enables multiparagraph plain-text controls. Fonts, headings, tables, and footers remain in the template; the Prompt writes the summary, not the Word page design.

5.3 Save and Inspect the Report

1. After PopulateWord, add OneDrive for Business → Create file / 创建文件, renamed CreateWordFile.
2. Set Folder Path to /Workshop-SalesWeekly/Reports; select ReportFileName's Outputs from dynamic content for File Name.
3. For File Content, select Microsoft Word document binary output from PopulateWord dynamic content, not a file URL; do not Base64-encode it again.
4. Save and manually run once under classroom safety rules. Open OneDrive's Reports, find this run's SalesWeekly-2026-10-09-<run time>.docx, and open it in Word for the web; turn off the scheduled flow after the run.

Completion check: The report shows 112,000.00 yuan, 6 orders, 91 items, 86.67%, 华东 47,000.00 yuan, and 订阅B 63,000.00 yuan. Risks and recommendations are on separate lines, with no unfilled controls, JSON arrays, or “null.” The UTC snapshot explicitly identifies its timezone; prose must not claim filtering by nonexistent timestamps.

Optional PDF: save the DOCX first, wait for file visibility according to official limitations (about 1 minute), then call Convert Word Document to PDF. Do not use file content as a file identifier. PDF is not required for the basic lab.

Download the offline simulated Word report to compare formatting; it is not proof that the cloud Word Connector has run.

Step Six: Outlook Draft and Optional Bulk Sending (20 Minutes)

Deliverable: A weekly report in your own Outlook Drafts with the correct Word attachment. The basic lab adds no sending action.

6.1 Generate a Safe Email Body

1. After CreateWordFile, add Compose SafeEmailText.
2. Paste into the Inputs expression editor. Escape & and angle brackets first, then convert newlines to <br>:

replace(
  replace(
    replace(
      replace(body('ParseReport')?['emailBody'],'&','&'),
      '<','&lt;'
    ),
    '>','&gt;'
  ),
  decodeUriComponent('%0A'),'<br>'
)

3. Add Compose DraftBody afterward with the following Inputs expression. The fixed Chinese HTML says “Weekly Sales Report” and asks the reader to review the report and verify the Word attachment:

concat('<h2>销售周报</h2>',
       '<p>以下为待审阅的销售周报，请核对 Word 附件。</p>',
       '<p>',outputs('SafeEmailText'),'</p>')

Do not directly execute model-returned HTML or concatenate model-generated links, attributes, or scripts. Use an organization-reviewed template when rich text is needed.

6.2 Create an Outlook Draft

1. After DraftBody, add Office 365 Outlook → Draft an email message, renamed DraftWeeklyEmail; create your own Outlook connection on first use.
2. Fill fields as follows. If Attachments is hidden, expand Advanced parameters / 高级参数 and add one attachment item:

3. Click Save and manually run once under classroom safety rules. Check whether DraftWeeklyEmail succeeds; turn off the scheduled flow afterward.
4. Open your own Outlook for the web in Edge, click Drafts / 草稿, and search for 2026-10-09销售周报 in the subject. If the model inserted spaces between date and title, search separately for the date and “销售周报” (weekly sales report). Retain the Chinese search terms because the email is generated in Chinese.
5. Open this run's draft and confirm To contains only yourself, key metrics are correct, body paragraphs are formatted, and the Word attachment name matches the OneDrive file. Open the attachment to verify content. Do not click Send / 发送.

Completion check: Run history shows the draft action succeeded, and this email actually exists in Drafts with an openable attachment; configuration screenshots or simulated previews alone do not count. Multiple manual runs create multiple reports/drafts; review only the files from the current run.

Send an email (V2) has no “Save as Draft” switch suitable for this lab; a sending action must not be passed off as a draft action. The real draft action is Draft an email message; Send a Draft message may later send an existing draft, but human edits require another review.

6.3 Optional Advanced Extension: Send or Bulk Send After Approval

Enable a sending branch in the live exercise only after verifying permissions and recipients:

1. SendMode defaults to Draft; an administrator-controlled environment variable may change it to ApprovedSend. Do not let users freely specify arbitrary mailboxes.
2. Add Approvals → Start and wait for an approval. Approval covers report content and fixed recipients; rejection/timeout must not send.
3. Call Send an email (V2) only when Outcome = Approve and configured recipients belong to the organization allowlist.
4. Use your organization's distribution list for bulk sending; do not treat sales-team@example.com as a real list or use unauthorized BCC.
5. Record ReportDate, run identifier, approval result, file location, and sending status; follow logging policies for sensitive information such as actual recipients.
6. Add idempotency records for production, such as SharePoint unique key SalesWeekly:<ReportDate>:<Audience>, and set flow concurrency to 1. Reconcile manually if sending status is unknown; do not automatically retry into duplicate bulk sends. Mark Sent only after receiving explicit success confirmation.

Step Seven: PPT Executive Briefing Extension (10 Minutes)

Deliverable: A three-slide executive briefing saved after manual verification. PowerPoint Copilot licensing is required; otherwise deliver only a Markdown outline and explicitly record “PPTX not generated.”

The basic workflow does not implement unattended PowerPoint file generation. The Prompt's pptOutline provides three slides of content; the Word report is the trusted input:

1. Open PowerPoint and select Copilot's create a presentation from a file feature (requires the corresponding Microsoft 365 Copilot capability).
2. Select the cloud-saved Word report and use demo-ppt-outline.md's three-slide structure: performance, distribution, and actions.
3. Prompt: keep the following Chinese input to request the intended Chinese presentation. It asks for three executive slides covering metrics and comparable-period change, region/product contribution, and report risks/next-week actions; it prohibits adding customers, targets, or growth causes absent from the input and specifies Chinese yuan.

根据这份销售周报创建 3 页中文管理层演示文稿。
第 1 页展示本周金额、订单数、件数与上周同期环比；
第 2 页展示区域与产品贡献；
第 3 页展示报告中的风险和下周建议。
不得增加输入文件中没有的客户、目标或增长原因。金额单位为人民币元。

4. Manually verify data, charts, confidentiality labels, and sharing permissions; save .pptx.

Completion check: Exactly three slides, with amounts in Chinese yuan; first-slide metrics are 112000 / 6 / 91 / 86.67%, second-slide regional/product data comes from the report, and third-slide risks/actions add no facts. Do not mark PPTX successful without actually saving a file.

Unattended PPTX generation requires a separately designed, governed file-generation service or a validated specific connector. Do not assume a general built-in PowerPoint Connector can perform this task. This guide delivers an outline, not an automatically generated PPTX.

Lab Acceptance and Troubleshooting

Manual Acceptance Operations

1. Confirm ReportDate=2026-10-09, DataAsOf=2026-10-09T09:00:00Z, To contains only your test mailbox, and the flow has no Send an email action.
2. Save and return to flow details; temporarily enable the flow and click Run / 运行 → Run flow / 运行流.
3. Open the current run in Run history / 运行历史 and click each action for Inputs / 输入 and Outputs / 输出. Locate the first failing action, not merely whether the final action was skipped.
4. Check every item below and open the real Word file and Outlook draft, then turn off the flow. Mark items lacking real results “incomplete/blocked”; do not treat offline simulated files as success evidence.

Failure Drills

1. Temporarily duplicate an order with an existing OrderId in sales.xlsx, save, and manually run. AggregateMetrics must explicitly fail without creating a report or draft; then remove the copied row and save.
2. In a separate agent test, omit Metrics JSON and check the explicit missing-input error. Do not change real business data for drills.
3. In a temporary test copy's CheckReportMetrics, set the revenue comparison's right-hand value to 1 and manually run; it must enter REPORT_METRICS_MISMATCH. Restore the original expression and verify the normal workflow again.

Restore Production Scheduled Operation

Proceed only after all basic checks pass and the source has been replaced with a continuously updated sales table:

1. Remove DataAsOf's fixed Inputs value and add outputs('RunUtc') using the expression editor.
2. Remove ReportDate's fixed Inputs value and add:

formatDateTime(
  convertTimeZone(outputs('RunUtc'),'UTC','China Standard Time'),
  'yyyy-MM-dd'
)

3. Retain RunUtc=utcNow() and recheck Recurrence = Week / Friday / 17 / 0 / UTC+8.
4. Save and manually verify runtime-date data and the comparable-period comparison. No current-period data must explicitly fail, not generate a fake zero-sales report.
5. After verifying approval and recipient governance, Turn on / 开启. Draft remains the default; enable bulk sending only after separately completing approval and idempotency changes.

Common Issues

Appendix One: Using Markdown and Supporting Files

Open lab-01/README.md in the workspace and read it with VS Code Markdown preview or a Markdown reader. data/, agent/, templates/, output/, and screenshots/ are all inside lab-01/; retain the entire folder when copying the lab so relative links need no changes.

To regenerate simulated data and offline Word examples, use Node.js 22:

cd lab-01
npm ci
npm run generate
npm run check
npm test

Simulated file generation does not access real Microsoft 365, read real sales data, or send emails. Real product screenshots must come from signed-in Microsoft Edge operations, not substitute local web pages. Playwright's Edge launch configuration is:

const browser = await chromium.launch({
  channel: "msedge",
  headless: true
});

Playwright's chromium API controls Edge's Chromium engine; it does not mean Google Chrome is launched. Scripts must verify Edg/ in User-Agent and provide no Chrome/Chromium fallback. Explicitly error if Edge is absent or organizational policy prohibits automation. npm uses the Microsoft proxy endpoint in .npmrc.

The public documentation screenshot source list records browser, time, and source for Microsoft Learn screenshots; real-operation screenshot status appears in captions and the actual operation record. Download the offline simulated Word report, simulated structured result, and PPT outline Markdown for comparison.

Appendix Two: Official References

Verified on 2026-10-11. UI, licensing, and feature availability can vary by tenant and region; use your actual environment and official documentation as the authority.

• Recurrence / Scheduled flow
• Excel Online (Business)
• Copilot Studio Prompt tools and nodes
• Prompt JSON output and Custom examples
• Power Automate Run a prompt
• Microsoft Copilot Studio Connector
• Word Online (Business)
• Office 365 Outlook
• Copilot Studio topics and code editor
• Relationship between agents and Agent flows

Appendix Three: Actual Operation Record

These records come from real signed-in Microsoft Edge sessions operated by Playwright with fictional sales data. Accounts, tenant IDs, and sign-in state are not disclosed.

The 2026-10-13 tests supersede older “pending retest” observations, not the incomplete integration status. Dated screenshot provenance distinguishes real configuration inspection, actual calls, and failure evidence.

Current deliverables are a Markdown hands-on guide, supporting assets, and real step screenshots already captured, not a claim that the complete cloud workflow has passed acceptance. When completing the remaining operations, verify publication, script output, agent response, Word file, and Outlook draft before marking corresponding rows successful. Fixed test dates have not yet been restored to runtime dates; keep unvalidated scheduled flows off to avoid periodically generating stale data.