cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minniep
Advocate III
Advocate III

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

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.

7 REPLIES 7
Pstork1
Dual Super User
Dual Super User

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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?

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

minniep_0-1598736056062.png

 

yes @Pstork1 , it does have the table

 

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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 29AM  
ID 1ChatReason 1Note
ID 2ChatReason 2Note
ID 3CallReason 3Note
Aug 29PM  
ID 10ChatReason 1Note
ID 15CallReason 8Note
ID 16ChatREason 2Note

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

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.

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (6,214)