I Let GPT-4.1 Take Over Our Office Lunch Orders. Here is How I Built It in Copilot Studio
The ultimate architectural breakdown of “LunchBot” a fully automated, native Microsoft AI agent that completely eliminates the monthly lunch coordination chore
Every workplace has its rituals. At ours it is the monthly Consultant lunch a recurring Outlook calendar event where the team gathers at a local restaurant to eat, catch up, and occasionally debate which burger is the superior choice. The food is great. The logistics, however, are anything but casual. Someone has to check who accepted the calendar invite, message each attendee to ask what they want to order, keep track of all the responses, reconcile them against the current menu, and then compile a summary for the restaurant. Every month, our office coordinator spends roughly 45 minutes chasing 15 people through this exact loop. It is the kind of repetitive, multi-step coordination task that should not require human effort in 2026 so I automated it.
This article walks through LunchBot: a Copilot Studio agent deployed in Microsoft Teams that automates the entire lunch-ordering lifecycle end-to-end. It reads attendance from Outlook, proactively chats with each attendee, takes their orders conversationally (including personalised recommendations based on order history), writes everything to Dataverse, and sends a consolidated summary email to the organiser the following day.
The stack is intentionally pure Microsoft: Copilot Studio (GPT-4.1), Power Automate cloud flows, Dataverse, Microsoft Graph API, and Teams. No external APIs, no Azure Functions, no code deployments.
Architecture Overview
LunchBot is a purely native Microsoft, zero-code solution built around a generative Copilot Studio agent deployed within Microsoft Teams. The architecture is driven by four Power Automate cloud flows that handle the backend logistics: refreshing the current menu, using the Microsoft Graph API to extract accepted attendees from an Outlook calendar, proactively initiating Teams chats with those attendees, and sending a final HTML order summary email to the organizer. Dataverse acts as the central data backbone, using custom tables to store menus, user profiles, and order history, which allows the AI agent to use structured semantic search and generative actions to process natural-language orders and offer personalized dish recommendations.
The Lifecycle in Six Steps
1. Menu Refresh : On the 1st of each month, Flow 1 loads the current restaurant’s menu items into the AIMenu Dataverse table, replacing stale data.
2. Attendance Check: A few days before the lunch date, Flow 2 authenticates against Microsoft Graph using client credentials (secret stored in Azure Key Vault), queries the organiser’s calendar for the “Konsulent lunch” event, and extracts the accepted attendees.
3. Proactive Outreach: Flow 3 invokes the Copilot bot to generate a formatted menu message, lists all registered People from Dataverse, and uses the Teams connector to open a 1:1 chat with each person sending them the menu directly from the bot.
4. Conversational Ordering: Each attendee replies in the Teams chat. The bot understands natural language (“I’ll have the Jack Burger, no tomato”), validates against the AIMenu table, and writes the order to the MealAttendance table using a generative action.
5. Personalised Recommendations: If a user asks for a recommendation, the bot queries MealAttendance history and suggests dishes they have not tried yet or have ordered frequently.
6. Order Summary Email: The day after the meeting, Flow 4 queries all MealAttendance records for the current month, builds an HTML summary table, and sends it to the organiser and optionally the restaurant.
Under the Hood: Technical Implementation
Before You Build: The Setup Checklist
LunchBot is a premium Power Platform build the HTTP and Key Vault connectors demand it. Before touching Dataverse or Power Automate, make sure you have these five pieces in place:
1. A Power Platform Environment with Dataverse You need a dedicated environment with a Dataverse database enabled. Your account needs the System Administrator or System Customizer security role without it, you won’t be able to create the custom tables or build the Copilot.
2. The Right Licenses Two licenses beyond your standard Microsoft 365 seat. First, a Microsoft Copilot Studio license to build and run the generative AI agent a tenant trial works perfectly if you just want to test this out. Second, a Power Automate Premium license for the HTTP and Azure Key Vault connectors, which the standard seeded license does not cover.
3. A Microsoft Entra ID App Registration Your flows need to check the lunch calendar entirely in the background, with no user logged in. Set up an App Registration and assign the Calendars.Read permission make sure you select Application, not Delegated. Crucial step: you will need a Tenant Admin to grant admin consent for this permission to take effect.
4. An Azure Key Vault Secret Create a Key Vault to securely store the client secret from your App Registration. I recommend naming the secret PowerAutomateGraph it aligns with the flow logic we will build later and keeps things clean when you have multiple secrets down the line.
5. Teams Admin Policies for Custom Apps The bot needs to proactively drop the menu into your coworkers’ chats, which means publishing it as an app in Teams. Make sure your organization’s Teams Admin Center policies allow custom apps or sideloading so you can test your creation before a wider rollout.
Dataverse Data Model
The solution defines four custom Dataverse tables, all using the alx_ publisher prefix (Alex). Here is the schema for each table and how they interrelate.
AIMenu (alx_aimenus)
Stores the current month’s menu items. Refreshed monthly by Flow 1. This is the bot’s primary knowledge source for menu queries.
MealAttendance (alx_mealattendances)
The orders table. Each row represents one person’s order for one lunch event. Written by the Copilot bot’s generative action and read by the summary email flow.
People (alx_peoples)
Registry of team members eligible for the lunch event. The bot auto-registers new users via a secondary action if they are not already present.
Restaurant (alx_restaurants)
Holds restaurant metadata. The alx_current flag indicates which restaurant is active for the current month, allowing menu rotation without reconfiguring flows.
The Copilot Studio Agent
LunchBot is built as a generative AI agent in Microsoft Copilot Studio, powered by GPT-4.1 (PreviewModels). It uses three Dataverse structured-search knowledge sources and two generative actions all orchestrated by a carefully crafted system prompt.
Agent Configuration
Key Settings
- Model: GPT-4.1 (PreviewModels)
- Channel: Microsoft Teams
- Recogniser: GenerativeAIRecognizer
- Content Moderation: High
- Semantic Search: Enabled
- Generative Actions: Enabled
System Prompt (Instructions)
The agent’s personality and guardrails are defined in the instructions block. Here is the complete prompt configured in the solution:
Instructions
Role & Persona You are Lunchbot, a casual, friendly, and humorous AI assistant designed to help consultants seamlessly order their meals for the monthly lunch event. Keep the tone lighthearted, use emojis frequently, and occasionally include clean, food-related jokes to make the experience fun.
Primary Directive: Always Show the Full Menu Whenever a user initiates an order, asks for the menu, or starts a meal selection process, your FIRST action must be to query the AIMenu Dataverse table and present the ENTIRE menu. You must never truncate, summarize, or hide available options.
Data & Operational Instructions
Retrieve Menu: Use the Dataverse table to get the current list of available dishes, descriptions, and allergens.
Save Orders: Once a user confirms their choice, save their meal selection accurately to the Meal Attendance Dataverse table.
Recommend: If asked, analyze historical data from the Meal Attendance table to recommend items based on the user's past orders.
Strict Constraints
NEVER mention, suggest, or hallucinate dishes that are not currently listed in the AIMenu table.
ONLY accept orders for exact items that exist in the AIMenu table.
Do not process, request, or store any sensitive personal information (PII) beyond what is strictly required for the meal tracking.Several design decisions stand out here. The prompt enforces menu-only ordering, meaning the bot will refuse to take an order for a dish not in the AIMenu table. It also mandates full menu display with allergen information every time, which is important for food safety. The casual tone with emoji keeps the interaction lightweight, consistent with a team lunch context rather than a formal procurement workflow.
Knowledge Sources (RAG)
The agent connects to three Dataverse structured-search knowledge sources. Unlike document-based knowledge (SharePoint, websites), structured search lets the bot query Dataverse tables directly and reason over the results using GPT-4.1.
- AIMenu the active menu. The bot reads this to display dishes, validate orders, and check allergens.
- MealAttendance order history. Used for recommendations (“you ordered The Jack Burger three times, want to try something new?”) and to prevent duplicate orders.
- People registered team members. Used to link orders to user profiles and auto-register new users.
Generative Actions
Copilot Studio’s generative actions let the GPT model decide when and how to call a Dataverse connector based on the conversation context. LunchBot has three:
Action 1: Add Meal Attendance
Triggered when a user confirms their order. The action writes a row to alx_mealattendances with the following field mapping:
Mix of manual (auto-filled) and automatic (GPT-extracted) inputs is key. The user’s identity fields are populated deterministically from the Teams session context, while the menu item and notes are extracted by GPT from the conversational turn. This ensures data accuracy for identity while preserving natural-language flexibility for ordering.
Action 2: Add User to People Table
A self-registration mechanism. If the bot detects that the current user is not present in the People table, it creates a new record with their name and email. This eliminates the need for manual user onboarding anyone who interacts with the bot is automatically registered.
Action 3: List Menu items from Table
A mechanism that retrieve the full list of items of the current menu
Topics and Conversation Design
The bot uses a mix of manually authored topics and the generative AI recogniser. Key topics include:
• ConversationStart / Greeting : Displays a rich welcome message with emoji listing all capabilities. Triggered on conversation open and standard greetings (Hi, Hello, Good morning).
• Fallback : Three-strike pattern: the first two unrecognised inputs get a friendly retry prompt; the third escalates.
• EndofConversation: CSAT survey with yes/no follow-up. Asks if the user wants to continue or end the session.
Get Alejandro Balburgos Bugeda’s stories in your inbox
Join Medium for free to get updates from this writer.
All menu queries, order placement, recommendations, and history lookups are handled by the generative orchestrator no manual topic authoring required. This is a significant advantage of Copilot Studio’s generative mode: the agent reasons over its knowledge sources and actions to handle arbitrary user requests without pre-built dialog trees.
Conversation Starters
Six pre-configured conversation starters appear as quick-reply buttons in the Teams chat:
• “I want to order lunch for the monthly consultant lunch.”
• “Show me the full menu, don’t forget any item.”
• “Can you recommend a menu item based on my past orders?”
• “Save my meal choice for this month.”
• “Show me my previous lunch orders.”
• “I want to change my meal choice for this month.”
Power Automate Flows
Four cloud flows orchestrate the end-to-end lifecycle. Each runs on a monthly recurrence and is fully automated no manual triggers required.
Flow 1: RefreshMenu
Trigger: Monthly Recurrence (1st of each month, 08:00 UTC)
Connectors: Dataverse | Purpose: Load the current restaurant’s menu into AIMenu
This flow ensures the AIMenu table always reflects the active restaurant’s offerings. The process works as follows:
- Query the Restaurant table: with filter alx_current eq ‘1’ and take the top 1 result. This returns the restaurant flagged as active for the current month.
Note: Since the Restaurant table already stores the menu URL (
tb_menuwebsite), Flow 1 could scrape the current menu dynamically on each run ensuring the AIMenu table always reflects an up-to-date list of dishes without manual maintenance.
2. Compose the menu array: a JSON array of objects, each with name, description, and allergens.
3. Apply to each menu item and create a new row in alx_aimenus with the dish name, description, and allergens.
A practical enhancement would be to first delete existing AIMenu rows before inserting new ones, preventing duplicates across months. Another option is to add a month/year column to AIMenu and filter by it in the bot’s knowledge source.
Sample Menu Items (Café Tullin)
Flow 2: TriggerMonthlyCheckAttendance
Trigger: Monthly Recurrence (6th of each month, 08:00 UTC)
Connectors: Azure Key Vault, HTTP (Graph API), Office 365 Outlook
Purpose: Extract accepted attendees from the Consultant lunch calendar event
This is the most technically interesting flow because it uses the Microsoft Graph API with client-credentials authentication a pattern required when reading another user’s calendar in a fully automated (no-user-context) scenario.
1. Authentication Pattern Hardcoding client secrets in Power Automate flows is a major security risk. Instead, this flow dynamically fetches credentials:
- Retrieve the client secret from Azure Key Vault (secret name:
PowerAutomateGraph). - POST to the Entra ID OAuth 2.0 token endpoint using
client_id,client_secret,scope=https://graph.microsoft.com/.default, andgrant_type=client_credentials. - Extract the
access_tokenfrom the JSON response to use as a Bearer token in subsequent calls.
2. Calendar Query & Event ID Extraction The flow queries the organizer’s calendar with a filter that matches events by subject name and the current month’s date range:
GET /users/{organiser}/calendar/events
?$filter=contains(subject,'Consultant lunch')
and start/dateTime ge '{first day of month}'
and start/dateTime le '{last day of month}'3. Filtering for “Accepted” Attendees
Once the specific event is identified, the flow fetches the detailed attendee list:
GET /users/{organizer_email}/events/{eventId}?$select=subject,attendees,organizer,start,endThe Graph API returns an attendees array. Because we only want to buy lunch for people who are actually coming, we cannot just message everyone on the invite. The flow uses a Power Automate Filter Array action to evaluate the status object for each person.
It specifically filters for attendees where the status.response is equal to accepted. Anyone who is marked as declined, tentative, or none (not responded) is dropped from the array.
4. Dataverse Cross-Reference Finally, this filtered list of accepted email addresses is cross-referenced with the People (alx_peoples) Dataverse table. This finalized list is passed to Flow 3 to trigger the proactive Teams messages.
Flow 3: TriggerMonthlyMenu
Trigger: Monthly Recurrence (1st of each month, 09:00 UTC)
Connectors: Copilot Studio, Microsoft Teams, Dataverse | Purpose: Proactively send the menu to each team member via the bot in Teams
This flow is the bridge between the system’s scheduled infrastructure and the user-facing conversational experience. It works in three stages:
Stage 1: Get the Menu from the Bot
The flow uses the “Execute Copilot and wait” action to invoke the LunchBot agent with a specific prompt:
“Can you share with me the full menu?
“avoid the use of references or links to tables in dataverse”
The trailing instruction is a prompt-engineering trick: it prevents the bot from including raw Dataverse references in its response, producing clean, user-ready output instead. The bot returns a fully formatted menu with emoji, bold dish names, descriptions, and allergens.
Stage 2: List Recipients
The flow queries the People Dataverse table (alx_peoples) with no filter, retrieving all registered team members and their email addresses.
Stage 3: Proactive Teams Chat
For each Copilot response and each person, the flow uses the Teams connector’s “Post message in a chat or channel” action configured as follows:
• Poster: Power Virtual Agents (the bot’s identity)
• Location: powerva (Copilot Studio channel)
• Bot: alx_lunchBot__bxFv1 (the LunchBot agent)
• Recipient: the person’s email from the People table
• Message Body: the Copilot’s formatted menu response
• Active Chat: Send (creates a new 1:1 chat if one doesn’t exist)
The result: every team member receives a direct Teams message from LunchBot containing the full menu, and can immediately start conversing with the bot to place their order. No group chats, no email threads, no manual coordination.
Flow 4: SendOrderSummary
Trigger: Monthly Recurrence (day after the lunch event)
Connectors: Dataverse, Office 365 Outlook | Purpose: Compile and email a consolidated order summary
This flow closes the loop. The day after the Consultant lunch event, it queries all orders for the current month and sends a professionally formatted HTML email to the organiser.
Flow Logic
1. Initialize variables for the HTML table body (htmlRows) and an order counter.
2. List MealAttendance rows from alx_mealattendances, filtered by alx_date ge first-of-month and alx_date le last-of-month.
3. Apply to each order record: append an HTML table row with the person’s name, email, dish ordered, special notes, and order timestamp.
4. Compose the full HTML email wrapping the accumulated rows in a styled <table> with headers.
5. Send the email via the Office 365 Outlook connector to the organiser’s address with the subject line “Consultant lunch Order Summary [Month Year]”.
Email Template (HTML)
The composed email uses inline CSS for maximum compatibility across email clients:
<h2 style="color:#1B4F72;">Consultant lunch Order Summary</h2>
<p>Here are the orders collected for this month's lunch:</p>
<table style="border-collapse:collapse; width:100%;">
<tr style="background:#2E86C1; color:white;">
<th style="padding:8px;">Name</th>
<th style="padding:8px;">Dish</th>
<th style="padding:8px;">Notes</th>
</tr>
<!-- Dynamic rows from Apply to each -->
<tr style="background:#EBF5FB;">
<td style="padding:8px;">@{items('Apply_to_each')?['alx_name']}</td>
<td style="padding:8px;">@{items('Apply_to_each')?['alx_ai_menu_iten_name']}</td>
<td style="padding:8px;">@{items('Apply_to_each')?['alx_notes']}</td>
</tr>
</table>
<p style="color:#7F8C8D;">Total orders: @{variables('orderCount')}</p>Lessons Learned
1. Generative Actions are Powerful but Need Guardrails
Letting GPT decide when to call Dataverse is incredibly flexible it can handle “I’ll take the burger, no tomato please” in a single turn. But without the system prompt instruction “Only accept orders of items in AIMenu,” the bot would happily try to order a pizza that does not exist in the table. The guardrails in the system prompt are as important as the actions themselves.
2. Proactive Teams Messages Need Careful Design
The “Post message in a chat or channel” action with poster set to “Power Virtual Agents” and location set to “powerva” is the magic combination that makes the message appear as coming from the bot rather than a flow. This is critical for user experience the recipient can immediately reply to the bot and start ordering, rather than receiving a static notification.
3. Client Credentials for Graph API Require Key Vault
Hardcoding client secrets in Power Automate flows is a common anti-pattern. Using the Key Vault connector to retrieve the secret at runtime is the production-grade approach. It enables secret rotation without flow modification and centralises credential management.
4. The “Execute Copilot and Wait” Pattern
Using a Power Automate flow to invoke the Copilot bot with a specific prompt (“show me the menu”) and then forwarding the response to users is an underappreciated pattern. It essentially turns the bot into a content-generation service that can be called from any automation context. The anti-referencing instruction appended to the prompt (“avoid the use of references or links to tables in dataverse”) shows how prompt engineering matters even in system-to-system calls.
Conclusion
LunchBot demonstrates that meaningful workplace automation does not require complex code deployments or external services. With Copilot Studio’s generative AI capabilities, Power Automate’s scheduling and integration connectors, and Dataverse’s structured data storage, a complete conversational ordering system can be built, deployed, and maintained entirely within the Microsoft ecosystem.
The pattern extends well beyond lunch orders. Any recurring event that requires attendance tracking, preference collection, and summary reporting team offsites, training registrations, equipment requests, office supply orders can be automated with the same architecture: a scheduled flow that triggers proactive bot conversations, a generative agent that handles the interaction, and a follow-up flow that compiles and distributes the results.
