How We Connected a Website Form to an Existing Excel Workflow
A lot of companies already have their workflows in place. We like to get in and help them optimize as much as we can. It is very common today to connect different software together. We keep an eye out for creative and cost-effective ways to do this with existing technologies.
The Task
The client has a proprietary Excel spreadsheet that it uses throughout the entire company — from sales to the physical delivery/pickup personal. The client wanted a way to collect orders on the website without adding a more clerical work for someone to manually update and create spreadsheets.
We wanted to automate this process to make it easy to scale for a large number of orders.
The solution we developed uses low-code technologies. That means we can develop something that is more cost effective than an entire custom program. It also lets us stay within the client’s familiar ecosystem instead of forcing them to use proprietary software or learning a new external workflow.
The Discovery
Our M3 Method starts with our Magnify step. Within this step we perform research and discovery, which saved the day on this project. With all web projects it is very important to outline exactly what the client expects and exactly what the production staff (developers, designers, etc.) are creating.
We began this project with a clear interactive prototype that showed the client exactly how the client and its customers will interact with the website. This prototype also acted as a springboard for production staff to ask questions about how data flows from one place or another.
A successful discovery concludes with both the client and the development team satisfied they know exactly what will be built.
The Tech
Zapier was the first technology we knew we would use. Zapier allows us to connect data from the website and send it to the client’s OneDrive.
Zapier is a low-code solution to a lot of problems with connections and integrations. There are a lot of “Zaps” that they provide that are easy and sometimes totally free to use. Those “Zaps” leverage the APIs for the technologies they support.
Power Automate was the second piece of the equation. Once the data from the form lands in OneDrive, a Power Automate script fires to collect, parse and send the data as necessary. That is where the data from the form gets inserted into the correct cells.
Previously named Flow, Power Automate is a way to connect Microsoft Office software to other Microsoft 365 software or some other supported third party. You can see a full list at us.flow.microsoft.com/en-us/connectors/.
You can think of Power Automate like inbox rules in Outlook — but on steroids. Commonly in Outlook, you can setup rules to send specific messages to specific folders. Another common rule is for out-of-office messages.
In this situation we setup Power Automate to watch a specific folder in OneDrive. When a file appears in that folder, it triggers an automatic process to pull that data and insert it into the Excel sheet.
Existing Technologies vs. Custom Code
One major point that we promote at M3 Group is using existing and well-supported software — and try to avoid custom code when necessary. Custom code is great to get exactly what a client would need. However, the upkeep required may not be worth the initial investment.
We take Zapier and Microsoft for granted: They are doing the heavy work of keeping the platform secure, updating and fixing bugs, which saves our client (and us) money and time. In today’s world, updates and security are pivotal to keep things working over time.
Considerations for Business Ecosystems
For a similar project, Google Sheets would be more effective instead of Excel and Excel Online.
Google Sheets is a much newer program — and because of that, it has a more robust external API or data connections. That means that Zapier has a much easier time integrating and utilizing everything Google Sheets has.
Using Excel Online forced us to use Power Automate with Zapier instead of possibly only Zapier. It would be easier and more cost efficient for us to use Google Sheets for that specific project. However, since the client already lives within Microsoft 365/Windows, uprooting them to save a couple bucks in the short term was not worth it.
These are just some of the considerations we look at when a client approaches us with complex web-based tasks. Discovery is the key to a successful integration project like this.
What We Can Do for You
This is one example of a complex workflow that is prime for automation. Why? Because data is consistent from order to order and processing that data is consistent from order to order.
There are many workflows in your day-to-day world that meet those two points. Almost any mindless or menial task may be automated in some way or another — things like moving files or storing data. Low-code solutions are easy and fast to spin up and can save a lot of labor over long periods of time.
Do you have a complex workflow that you need improved or automated? If you have an upcoming project, reach out to us and we can show you exactly how our M3 Method can be applied to your situation. We put a huge emphasis on Magnify in all of our services, including our web development.
Reach out today to info@m3group.biz.
