cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KeithCa
Level: Powered On

Flow to update spreadsheet in sharepoint from excel on onedrive

So I'm pretty new to flows and sharepoint in general and was asked to scope something out to see if its plausible, but been playing around to no avail.

 

We have a excel spreadsheet that is updated every week on a computer and needs to go onto sharepoint list for viewing.

 

We were wondering if it was possible to create a flow(or similar) so that everytime that sheet is updated the sheet on sharepoint would also be updated. Probably not possible from the system but maybe if we got it to uploaded to onedrive and from there?

 

And also asked to scope out if it was possible for the list to be updated in the sharepoint and auto exported to excel to somewhere. This one seems more pausible and I can handle.

 

 

 
 
 
 
 
4 REPLIES 4
Super User
Super User

Re: Flow to update spreadsheet in sharepoint from excel on onedrive

Why aren't you just directly updating the spreadsheet  in SharePoint? Why have two copies?

 

Have you considered using OneDrive to sync the two excel files?

KeithCa
Level: Powered On

Re: Flow to update spreadsheet in sharepoint from excel on onedrive

Probably worded it a bit wrongly but its a list in sharepoint that needs to be updated. I'm not too sure why but this is what I was asked haha. I just know we have a excel file and want to have that excel file to be a list in sharepoint and be bidirectional updateable.

 
Super User
Super User

Re: Flow to update spreadsheet in sharepoint from excel on onedrive

Ok. To read an excel file i SharePoint you can use the Excel(Business) connector. You will however need to create the data as a table.

 

First create a flow that includes an action "create table" to create the rigth format fro the Excel file. This is where you specify all the columns in the table. Make sure that you create an empty excel file first.

 

Then you can update the excel and create SharePoint items every time a row is added.

 

Community Support Team
Community Support Team

Re: Flow to update spreadsheet in sharepoint from excel on onedrive

HI @KeithCa,

 

Does the Excel file stored in your OneDrive has same data structure with the Excel file stored in your SharePoint list?

Would you delete some records within the Excel file stored in OneDrive?

 

I suppose that the Excel file stored in your OneDrive has same data structure with the Excel file stored in your SharePoint list, is it true? In addition, I assume that the data of your Excel file has been formated as a Table.

 

If you delete or update some old records when you update the Excel file stored in your OneDrive folder, I afraid that there is no way to sync the updates to the Excel file stored in your SharePoint library currenly.

 

If you just add new records within the Excel file stored in your OneDrive folder, I have made a test on my side and please take a try with the following workaround:

  • Add a "When a file is modified" trigger, specify Folder.
  • Add a "Get rows" action, File name set to File identifier dynamic content of the trigger and Table name set to Table1 as a custom value.
  • Add a "Apply to each" action, input parameter sset to output of "Get rows" action.
  • Within "Apply to each" action, add a "Add a row into a table" action of Excel Online (Business) connector. specify Excel file within your SharePoint library. TaskName field set to following formula:
item()?['TaskName']

Executor field set to following formula:

item()?['Executor']

Due Date field set to following formula:

item()?['Due_x0020_Date']

Note: The TaskName, Executor and Due Date are columns in my Excel table. On your side, you should type the following formula:

item()?['ColumnNameOfExcelTable']

If there is a space within the column name, please replace the space with '_x0020_'.

 

Image reference:21.JPG

Note: I assume that the data of the Excel stored in your SharePoint library has been formatted as a table.

 

The flow works successfully as below:22.JPG

 

 

More details about using expression in flow actions, please check the following article:

Using Expression in Flow actions

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (6,416)