Hi everyone. During Work From Home condition, I tried to create time card application with Forms, Excel and Power Automate. Every tool is by Microsoft Office.
Today I would like to share how to create it.
What you can do this
When member fill in forms their working time, automatically it would be generated to excel.
If you set some formula to calculate, you can get data easily
This is overall of flow to create.
- Create Forms
- Create a table with excel
- Set business flow on power automate
Why we need Power Automate
If you have used Google forms, perhaps you wonder why I cannot complete those flow with only Google forms. At the first of first, I thought same things.
But I found a problem with Forms.
When you check the results, you can select a button [Open a Excel] to generate the results into excel.
But when you get new answer from user, that results would not be updated on the excel sheet. So you need to download new excel file again.
I think this is a big difference between G suite and office lens.
But this time I solved it with Power Automate.
How to create flow
I would like to show how I did it step by step.
1. Create Forms
At first, please create forms. This time, I put only 2 questions: Name and condition.
User doesn’t need to select time by themselves because automatically Forms record timestamp.
But if you need more question such as select department or comment, of course you can add as necessary.
2. Create a table with Excel
Now we need a table with excel to generate the results. I put it on sharepoint because my team member might check it.
The columns should be same as forms results.
In order to make a table easily, I generate the result into excel and upload it on sharepoint.
If you need to edit this table, you can create another sheet.
This time I need only Name, Condition, Date and Time so that I created another sheet as image.
3. Set business flow on power automate
So now I complete the part of Form and Excel. A thing what I need to do is to connect Form and Excel with Power Automate.
Power Automate has several templates. But this flow is really simple so I tried to create it from zero.
As trigger, I selected [When a new response is submitted]
And this is the flow I created.
Play Power Automate
After you saved this flow, please try to submit answer on form to check whether this flow works or not.
As next improvement, I would like to try to create time card system with Power Apps so that employee can submit it with their smartphone.
Thank you for reading.