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

Assistance Updating Excel file in Sharepoint from existing Sharepoint list

I've built a powerapp that grabs data and updates a sharepoint list then these items get marked approved and done.

 

After thats done I'd like to be able to get a list of approved items in the SP list (either once a week, or on trigger) and send them over to an Excel file residing in a document library.

 

I'm currently struggling to do this, have tried a variety of ways but always hit a brick when updating the table or columns. 

 

Any step by step guides or screenshots of similar exisiting setups appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
MD7648
Level: Powered On

Re: Assistance Updating Excel file in Sharepoint from existing Sharepoint list

Templates don't really work terribly well. Its just frustrating because this should be a fairly straightfoward bit of functionality but currently makes relying on the Excel connector, especially the Create Worksheet connector, almost useless compared to what it could do (dynamic reports, dynamic extracts of lists)

 

Combine that with the seeming inability to trigger a flow against multiple list items manually for Excel and your missing a lot of scope for automatic or quick data use. 

 

Anyway, onto the form. What I've done is setup a scheduled flow that searches the data on the list for key words in a column (in this case I've added a column and copied over Approval Status) and then takes that data and updates it into a file in the document library.

 

The flow then triggers a sharepoint update item and sets the column to "Exported to Excel" 

View solution in original post

4 REPLIES 4
SCTdan
Level 8

Re: Assistance Updating Excel file in Sharepoint from existing Sharepoint list

Can you post a screenshot of what you've got now, and where it's failing?  Are you just constantly appending to one table, or are you trying to make a new excel doc each time?

 

It should be relatively straight-forward.  Get Items from SharePoint -> Apply to each / Add Row to Table.  

MD7648
Level: Powered On

Re: Assistance Updating Excel file in Sharepoint from existing Sharepoint list

Its the adding a row to table that fails, I've had a bit of a deep dive into this this afternoon.

 

Based on articles such as this - https://powerusers.microsoft.com/t5/General-Flow-Discussion/MS-Flow-to-SharePoint-List-Export-into-E... it appears there is no easy way to create a file or update a new worksheet as currently MS flow does not support adding dynamic tables, key values or files to the Excel connector so I can't do what I need to do which is generate a new worksheet, with a new table then populate with the data needed. The excel file has to exist first and have a table in it before Flow will talk to it. 

 

So what I've done is create a flow to just update a single files table with the values I need, that works fine. 

 

Instead of having to mess around with editing the backend in future I'll proably just tell users to open up Edge and export to Excel (because thats not an option in Chrome) and save as whatever value they need. 

SCTdan
Level 8

Re: Assistance Updating Excel file in Sharepoint from existing Sharepoint list

You could just have a excel template with your blank table, then on each run, create a copy (via sharepoint action) and rename appropriately. 

MD7648
Level: Powered On

Re: Assistance Updating Excel file in Sharepoint from existing Sharepoint list

Templates don't really work terribly well. Its just frustrating because this should be a fairly straightfoward bit of functionality but currently makes relying on the Excel connector, especially the Create Worksheet connector, almost useless compared to what it could do (dynamic reports, dynamic extracts of lists)

 

Combine that with the seeming inability to trigger a flow against multiple list items manually for Excel and your missing a lot of scope for automatic or quick data use. 

 

Anyway, onto the form. What I've done is setup a scheduled flow that searches the data on the list for key words in a column (in this case I've added a column and copied over Approval Status) and then takes that data and updates it into a file in the document library.

 

The flow then triggers a sharepoint update item and sets the column to "Exported to Excel" 

View solution in original post

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

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 (4,142)