Answered

Assistance Updating Excel file in Sharepoint from existing Sharepoint list

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

General topics

All responses (4)

Answers (1)

Sort by

SCTdan 604 on at

Like
a
(0)

Report
Copy link

Link copied!

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.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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-Excel-and-email/td-p/295478 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 604 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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"