Flow to auto transfer data on a Table from Excel > Excel?

[minniep]

Is it possible to have a flow: Get Data Twice Daily from an Excel report and transfer the data to another Excel automatically?

Hi there,

I tried to search for templates or similar flow and yet have no idea if and how it can be done.

Background info:

We have a report built on this Excel: A Report, and one of the sheets inside collect customer data for AM and PM.

The PM data will replace the AM, and the AM of the next day will replace the PM yesterday.

We want to keep the file simple as it has lots of formulas and running on SharePoint.

So we need to store those daily AM and PM customer data in another place (eg. another Excel on SharePoint).

I would appreciate any suggestions.

[Pstork1]

A couple questions

1) Does the source Excel file have the data you want to export in a table?  If not then the flow can't access the data

2) Does the source table contain any calculations?  Flow can't access a table with calculated columns or cells containing calculations

If the source data is in a table and that table has no calculations in it.  Then  you can create a recurring flow to copy the data you select to a different Excel file with an appropriate table.

[minniep]

Thank you @Pstork1 for replying to my inquiry.

Yes it is already a table and no formula, but certain cells are set as list using the data validation.

would that be a problem?

[Pstork1]

I'm not sure it will be a problem, but I suspect it will be.  Power Automate only works with simple tables that don't have any additional formulas or calculations associated with them.  You'll have to try it to see if you can see the table when you attach to the Spreadsheet.

To test, just create a flow with a manual trigger and then add a List Rows in Table action for ExcelOnline (For business).  In the settings point to your SharePoint site, the document library and the Excel file.  If you see the table in the list it should work.  if you don't then something about the table is preventing connection.

[minniep]

yes @Pstork1 , it does have the table

[Pstork1]

Then create a Flow with a Recurrence trigger and in the flow list the rows from the source Excel file and copy/update them in the destination Excel file. It should work. Just make sure to create a table in the destination file that you can update.

[minniep]

will the new data overlap? cover the data transferred earlier?

also is there a way I can add a date and time before pasting the data?

like I want the file storing the info looks like this:

Aug 29	AM
ID 1	Chat	Reason 1	Note
ID 2	Chat	Reason 2	Note
ID 3	Call	Reason 3	Note
Aug 29	PM
ID 10	Chat	Reason 1	Note
ID 15	Call	Reason 8	Note
ID 16	Chat	REason 2	Note

And the next day will just add on to new rows with new date and time with the data

[Pstork1]

If you put the new data in the Excel file using the following it will create new rows.

Patch(datasource, defaults(datasource), {record values you want to save})

------------------------------------------

Ignore this, its for Power Apps, not Power Automate.  For flows you will use the Update a row or Add a row actions.