Dynamically selecting a table in excel

(0) Share

Report

Posted on by HarZ

Hi Automaters,

I am trying to build a flow that can help track check in-outs from a Power App in an excel spread sheet. Every day the formatted table (CheckInOut) in this .xlsx is copied and renamed to the day's date.

In Power Apps once someone presses "check in" it should add to that day's sheet. Is there some way to dynamically assign the correct table in this spreadsheet? A new spreadsheet will be created at the start of every month too and I would like to dynamically track that if possible.

I assume this isn't as easy as formatting the date time as text and putting it in the Table section. Any advice?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Mira Ghaly 11,413 Moderator on at

Like (0)

Report

Hi,

I would say this would be a little bit complicated have you thought of building your check in - out on Microsoft Lists / Sharepoint instead of Excel?

Using Sharepoint would be much easier and you can then pull the data to excel from sharepoint if required but if excel is required , i can still say there is a way to pull tables dynamically if needed but needs some work , I can try on my end and let you know

Was this reply helpful? Yes No
HarZ 17 on at

Like (0)

Report

Hi Mira,

I haven't thought about Microsoft Lists or SharePoint due to unfamiliarity. However, if it greatly simplifies the current process I am all ears as I do not believe Excel is required in any capacity.

Was this reply helpful? Yes No
Suggested answer

Mira Ghaly 11,413 Moderator on at

Like (0)

Report

Hi,

So if you are happy with sharepoint lists then the process should be as below;

You can use the below link to see how you can create a Microsoft list https://support.microsoft.com/en-au/office/create-a-list-0d397414-d95f-41eb-addd-5e6eff41b083

For the Microsoft List your columns should like Below:

1. Column 1: Choose Type Person and this should be the person who is doing the check in - check out

2. Column 2 : Should be a Date(It should be Date Time but do not include the Time) and this is the date of the Record so both the Person and Date should be always entered once per day

3. Column 3: Check-In Time -> Date Time -> Include Time

4. Column 4: Check-Out Time -> Date Time -> Include Time

https://support.microsoft.com/en-au/office/add-edit-move-or-delete-columns-in-lists-2ad5098e-2d70-4aa7-a769-5e109546deb3

It should be something Similar to the below

Then you need your app to be connected to the Sharepoint list https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-sharepoint-online

If you started your app from this list it will look like the below but of course you will need a lot of logic to handle for the user to check- in and check out by themselves

Was this reply helpful? Yes No
HarZ 17 on at

Like (0)

Report

Hi Mira,

Thank you for the detailed instructions on setting up a SharePoint for check in/outs. I'm still unsure on how to dynamically select this SharePoint in Power Automate and Power Apps however.

Was this reply helpful? Yes No