Building an Autonomous Agent for Excel Data Enrichment

Agent-driven automation in Copilot Studio allows you to build AI workers that handle tasks like data entry, classification, and report generation. By combining generative orchestration with code interpreter and Power Automate flows, an agent can read an Excel file, apply business rules from a knowledge document, and then write results back to the same file. In this article, you'll create an agent that enriches a project expense report with cost center codes and category names derived from a company policy guide.

How the Agent Works

The lifecycle of the agent has three main stages:

1. Read – When a new Excel file is uploaded to a SharePoint document library, the agent triggers and runs a topic that uses Python (via code interpreter) to extract all cell values into a JSON structure.
2. Analyze – The agent consults a PDF knowledge file listing valid cost center codes, their categories, and descriptions. Using its instructions, it determines the correct code and category for each expense line.
3. Write – The agent calls a custom tool (a Power Automate flow) that updates specific cells in the original Excel file with the determined values.

The agent uses generative orchestration mode, meaning Copilot Studio automatically decides the sequence of actions based on the topic descriptions and instructions you provide.

Our Scenario – Monthly Project Expense Classification

In this example, project managers submit a standard Excel workbook each month. The workbook contains columns for Date, Vendor, Amount, Cost Center Code, and Category Name. The first two columns are filled; the last two are blank and must be determined from an official "Cost Center Policy.pdf". The agent reads the file, looks up each expense in the policy, assigns the correct code and category, and writes them back to the workbook.

We'll build this agent step by step.

Prerequisites

• Copilot Studio license with generative orchestration enabled
• A SharePoint Online site where you can create a document library
• Power Automate (included with many Microsoft 365 plans)
• A sample Excel file and a PDF policy document (instructions to create them below)
• Basic familiarity with Copilot Studio topics and flows

Step 1 – Create the SharePoint Library and Sample Files

Create a new document library named "Project Expense Reports". Add an Excel file called "MarchExpenses.xlsx" with a worksheet named "Expenses". Include these columns:

• A: Date
• B: Vendor
• C: Amount
• D: Cost Center Code (empty)
• E: Category Name (empty)

Enter a few records (e.g., three or four expenses) and leave columns D and E blank.

Next, create a PDF policy document. For testing, you can write a simple table like the following:

CC-101, Travel, "Airfare, hotel, meals"
CC-102, Office Supplies, "Paper, pens, toner"
CC-103, Software Licenses, "SaaS subscriptions, yearly fees"

Save it as "CostCenterPolicy.pdf". You'll upload it as agent knowledge later.

Step 2 – Create the Autonomous Agent

In Copilot Studio, choose Create and select Agent. Name your agent something descriptive, for example Budget Classifier.

• Turn Generative orchestration On.
• Select a model that supports code interpreter (e.g., GPT-4o or later).
• In the Instructions field, provide clear guidance:

Goal: Classify each expense line in the Excel file by filling in the correct Cost Center Code and Category Name using the policy knowledge.

Instructions:
1. Execute the topic "ParseExcelWorkbook" to read the Excel content.
2. Consult the knowledge document "CostCenterPolicy.pdf" to find matching cost center codes.
3. For each row, determine the appropriate code and category.
4. Call the tool "UpdateExcelCells" to write the values back to column D and E.
5. Do not assign a code unless it appears in the policy PDF.

Step 3 – Add a SharePoint File Trigger

On the agent’s overview page, click Add trigger and select When a file is created (properties only). Choose your SharePoint site and the "Project Expense Reports" library. The trigger captures the file path when a new file is uploaded.

Rename the automatically created input variable to something clear, like filePath. This variable will be passed to the next topic.

Step 4 – Build the ParseExcelWorkbook Topic

Create a new topic with these settings:

• Trigger type: The agent chooses
• Description: This topic reads an Excel file and outputs all cell values as a JSON structure.

Add an input variable:

• Name: FileFullPath (type String)
• Description: Full path of the Excel file to parse

Add an output variable:

• Name: ExcelContent
• Type: Record (schema shown below)
• Description: Contains all cell values from the workbook

Read the File Content

Inside the topic, add a SharePoint – Get file content using path action. Provide the site address and map the path to FileFullPath. This action outputs the file's binary content.

Use Code Interpreter Prompt

Add a Prompt node named "Extract cells via code interpreter". Use the following configuration:

• Inputs: [Excel File] of type Document (map it from the output of the Get file content action)
• Enable code interpreter: ✅
• Output type: JSON

Prompt instructions:

Goal: Read the Excel workbook and return all cell values in a structured JSON format.

Instructions:
1. Open the workbook using openpyxl.
2. List all worksheet names.
3. For each worksheet, get the used range.
4. Iterate over rows, capturing cell address and its string value.
5. Build and return a JSON object with the following structure:
   {
     "sheets": [
       {
         "name": "Sheet1",
         "cells": [
           {"ref": "A1", "value": "header1"},
           {"ref": "B1", "value": "header2"}
         ]
       }
     ]
   }

After the prompt, set the topic's output variable ExcelContent to the result of the prompt.

Schema for ExcelContent:

kind: Record
properties:
sheets:
  type:
    kind: Table
    properties:
      name: String
      cells:
        type:
          kind: Table
          properties:
            ref: String
            value: String

Step 5 – Upload the Policy Knowledge

Go to the agent’s Knowledge tab, click Add knowledge, and upload "CostCenterPolicy.pdf". Add a description like:

This document lists all valid cost center codes, their category names, and descriptions. The agent must use it to determine the correct code and category for each expense line.

The agent will automatically index the PDF and use it during orchestration when the instructions refer to it.

Step 6 – Create the Update Excel Cells Tool

We need a way to write values back to the Excel file. This will be a Power Automate flow registered as a tool.

1. In Copilot Studio, go to Tools → Add tool → Agent flow.

2. In the Power Automate designer, set the trigger to When an agent calls the flow.

3. Add four inputs of type string:

   • DriveID
   • DriveItemID
   • WorksheetName
   • CellUpdates – a JSON array string, e.g., [{"cell":"D2","value":"CC-101"},{"cell":"E2","value":"Travel"}]

4. Add an action Parse JSON using the CellUpdates input (provide a schema that matches the array).

5. Use Apply to each to iterate over the parsed JSON.

6. Inside the loop, add Excel Online (Business) – Update a cell. For each iteration:

   • File: Use the DriveID and DriveItemID to locate the file (you may need to first use a Get item action from SharePoint to retrieve drive details).
   • Worksheet: WorksheetName
   • Cell: The cell field from the current item.
   • Value: The value field.

7. Save and test the flow manually.

Once the flow is saved, return to Copilot Studio. The tool will appear automatically. Rename it to something like "UpdateExcelCells".

Step 7 – Wire Everything Together

Because generative orchestration is enabled, the agent can decide the order of topics and tools based on your instructions. However, you can enforce a sequence:

• Ensure the trigger’s filePath is mapped to the FileFullPath input of the ParseExcelWorkbook topic.
• The instructions explicitly call out the topic and tool, so the agent will automatically run them in order.

To test, upload "MarchExpenses.xlsx" to the SharePoint library. If everything is configured correctly, the agent will trigger, read the file, consult the policy PDF, compute the correct codes and categories, and write them back to the workbook.

Security and Performance Considerations

• Connections: The agent uses the identity that set up the SharePoint trigger. That identity must have write permissions to the library.
• Code interpreter sandbox: All file processing is done in Microsoft’s managed environment; your data stays within the tenant boundary.
• Data loss prevention (DLP): Ensure the Excel Online connector is allowed in your Power Platform DLP policies.
• File size: Large Excel files may slow down code interpreter or cause timeouts. For production, keep files under a few MB or process them in batches.
• Concurrent updates: The flow runs cell updates sequentially; no special locking is needed for small files.

Common Mistakes and Troubleshooting

Recommendations and Next Steps

This pattern can be applied to many data enrichment and classification tasks: invoice coding, customer onboarding, survey processing, and more. Consider extending the agent with:

• Error handling – Log failed records to a separate file.
• Notifications – Send an email to the submitter when processing completes.
• Multiple file formats – The agent could handle .csv or .xls files with minor prompt adjustments.
• Human review – For low‑confidence matches, the agent can flag rows for manual approval.

Start with a small sample and iterate on the instructions and knowledge base quality to achieve high accuracy.

References

• Original article: "Copilot Studio Autonomous Agent To Read & Write Excel Files" by Matthew Devaney – https://www.matthewdevaney.com/copilot-studio-autonomous-agent-to-read-write-excel-files/
• Microsoft Learn: Build agents with Copilot Studio – https://learn.microsoft.com/en-us/microsoft-copilot-studio/
• Microsoft Learn: Use code interpreter in prompts – https://learn.microsoft.com/en-us/microsoft-copilot-studio/advanced-ai-code-interpreter (placeholder)
• Microsoft Learn: Create custom flows for agent tools – https://learn.microsoft.com/en-us/microsoft-copilot-studio/custom-flow (placeholder)