'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use Power Automate and a Teams Chatbot to Search an Excel File
Question details
The user wants to set up a Microsoft Teams chatbot that collects input, like an order number, and passes it to a Power Automate flow to search an Excel file and return a specific status.
- Product
- Microsoft Excel, Power Automate, Microsoft Teams
- Device & OS
- not provided
- Scenario
- Automating data retrieval from a cloud-stored Excel spreadsheet using a Teams chatbot and Power Automate flow.
- Observed behavior
- The user needs to successfully query an Excel table and return a matching status to the chatbot seamlessly and securely without making business data public.
Ensure your Excel data is formatted as a formal Table with clear, unique column headers (e.g., 'Order Number'), and that the file is saved in a secure SharePoint or OneDrive for Business folder.
Build a Power Automate Flow with Power Virtual Agents and Excel Online
Configure a flow that is triggered by a chatbot, searches your Excel table for the requested variable, and returns the result back to the user.
To allow your Teams chatbot to read Excel data, the file must be stored in a supported cloud location like SharePoint or OneDrive for Business. You should never place sensitive business data on a publicly accessible, unsecured website just to bypass authentication.
Open your spreadsheet, format your data range as a Table (Insert > Table), ensure the order-number column has a unique header, and save the file to your organization's OneDrive for Business or SharePoint.
Open Power Automate, create a new flow, and select Power Virtual Agents (or Copilot Studio) as the trigger. Add an input variable to capture the 'Order Number' from the user's chat message.
Click 'New Step', search for 'Excel Online (Business)', and select the 'List rows present in a table' action.
Select the Location, Document Library, File, and Table from the dropdowns. In the 'Filter Query' field, enter an OData filter (e.g., OrderNumber eq 'Variable') using the dynamic content from your chatbot trigger.
Add a final step 'Return value(s) to Power Virtual Agents'. Map the matching status retrieved from the Excel row to an output variable so the chatbot can display it to the user in Teams.
Prepare Your Automation Datasets Easily with WPS Office
While integrating Power Automate and Teams relies on the Microsoft ecosystem, you can effortlessly manage, format, and prepare your Excel datasets for automation using WPS Office. It provides a lightweight, highly compatible, and free alternative to Microsoft Office for everyday spreadsheet tasks.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx order tracking file.
- 3. Format as Table: Select your data range, navigate to the Insert tab, and click 'Table' to structure your data for Power Automate compatibility, then save.
Frequently Asked Questions
Why isn't Power Automate finding my Excel data?
Power Automate requires the data in your Excel file to be formally formatted as a Table. Simply having data in rows and columns is not enough. Select your data range in Excel, press Ctrl+T (or Insert > Table), and assign a name to the table before connecting it to your flow.
Can I use a personal OneDrive account for the Excel flow?
For Teams chatbot and business-level Power Automate integrations, it is highly recommended to use OneDrive for Business or SharePoint. Personal OneDrive accounts may cause authentication issues or lack the required organizational sharing permissions.
How do I filter for the exact order number in Power Automate?
In the 'List rows present in a table' action, utilize the 'Filter Query' field under advanced options. You must use OData syntax, such as `OrderNumberColumn eq 'YourChatbotVariable'`, to return only the specific row matching the user's input.
