BYU Student Author: @Kaden_Sandberg
Reviewers: @Kiya_Smith, @James_Behling
Estimated Time to Solve: 30 Minutes
We provide the solution to this challenge using:
- Power Automate
Need a program? Click here.
Overview
You’ve just started working at Big Four Beans, a factory specializing in high-quality bean production. Part of your job involves handling daily emails from Greg, who manually counts beans and sends a message formatted like this:
“Greg counted: 150 beans.”
The number of beans changes each day based on his count. Your task is to extract these numbers and manually enter them into the database.
In the past, this process has led to errors from mistyping numbers—an embarrassing mistake!
Recently, you attended a work training on Power Automate and learned how it can streamline repetitive tasks. This seems like the perfect opportunity to put your new skills to the test and eliminate errors from this tedious process!
Instructions
Setting Up Your Automation
Before you begin, ensure your Microsoft license supports OneDrive, Excel Online, and Power Automate. For ease of use, all three should be linked to the same account email.
Steps to Automate Bean Counting
- Set Up Your Excel Table
- In OneDrive, create a new Excel file in an easily accessible folder.
- Add a table with a single column—this is where the bean count numbers will be stored.
- Log into Power Automate Online
- Visit (Power Automate Online)[Power Automate: Intelligent Robotic Process Automation | Microsoft Power Platform].
- If you’re a student with a Microsoft 365 license, sign in with your student email; if you’re a professional, use another appropriate email.
- Connect Your Email and OneDrive to Power Automate
- Open the Connections page (found on the left-hand side near the bottom).
- Add new connections using the same Microsoft account to keep everything within the Microsoft ecosystem:
a. Office 365 Outlook
b. OneDrive for Business
c. Excel Online (Business)
- Create a New Flow
- Start a new Power Automate Flow and select Automated Cloud Flow.
- For training purposes, set it to activate when you send an email to yourself.
- Add a trigger for Microsoft Outlook and configure it to activate when the sender is your email address.
- Define the Trigger
- Your flow should trigger whenever you email yourself a message. Remember to send all test messages in the following format: “Greg counted 123 beans.”
- Extract the Number
- Configure the flow to extract only the number from the standard email and store it in the first column of your Excel table.
- There are a couple of tools that can do this. If you get stuck ask Copilot or see the Hints Section.
- Ensure the flow captures numbers of any length accurately.
- Test Your Automation
- Save your flow
- Place the flow in test mode by clicking test in the top right corner and selecting “Manual” at the top and “test” at the bottom.
- Send yourself an email using the format above.
- The blank testing screen should show you how each test runs as it is triggered and moves through each step.
- If nothing happens within a minute after you receive your email, check your email trigger again to make sure it’s triggers with the exact same email address that you received in your inbox.
- Check if the extracted number appears in your spreadsheet automatically.
Suggestions and Hints
Power Automate offers many useful tools to assist with this task. If you’re feeling a bit lost, start by exploring the “HTML to Text” tool and the “Add a Row into a Table” tool.
Don’t hesitate to use Power Automate’s built-in AI Copilot, but keep in mind that it can still make mistakes and misinterpret your flow.
Solution
