cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tbruns
Post Patron
Post Patron

Create Excel spreadsheet from a Sharepoint List and save to Sharepoint Library

Hello! Needing some help!

I have a very large Sharepoint List. I was wanting to be able export to Excel but only for a specific date. Since Sharepoint does not allow me to do that I am wondering if I can use PA to create a Excel file and then add it to a Sharepoint Library?

I need it to be a manual trigger to start the flow. 

Would there be any way for PA to prompt me to select the date that I am wanting to export from the SP List?

 

Any help or suggestions would be greatly appreciated! Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi again!

 


So hopefully last question...When I open the Excel document there are several columns that show the below - How do I get it to show just the value instead of the below?

 

Two questions:

 

1.-The example you are sharing belongs to a Choice type column in your Sharepoint list, right?

2.-I believe you are finally using action block 'Create CSV table' right?

If so, you can switch 'Create CSV' 'Column' input from its default value 'Automatic' into 'Custom'. THis way you can select which columns you wanna add, and also perform manipulations if needed

Good example here

https://powerusers.microsoft.com/t5/Building-Flows/Create-CSV-Table-Multiple-Rows-and-attaching-CSV-...

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

16 REPLIES 16
efialttes
Super User III
Super User III

Yes you can!

So if you select 'MAnually trigger a flow' trigger, you can add as many inputs as you need, so everytime you execute the flow, you will be prompted to fill the inputs first

FLow_manualInput.png

THe same philosophy applies if you use 'For a selected item' trigger. Good stuff here:

https://michalguzowski.pl/how-to-trigger-microsoft-flow-in-sharepoint-list/

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Wonderful! Thank you for that. 

 

Now can you help me build the Flow to create the excel worksheet?

So far I have Manually trigger a flow, Get Items and Create Worksheet. I am having an issue on the Create Worksheet as I do not know what I need to enter.

Hi again!

if you use 'Create worksheet' you need to reference an already existing Excel file, and depending on the connector you use -'Excel Online (Business)' vs Excel Online (OneDrive)'- the Excel file shall be stored in Sharepoint/OneDrive4Business or in OneDrive. So, you need to decide where to store the Excel file.

BTW, can you share a screenshot from your current flow design?

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@efialttes 

I have just started to build the flow but stuck on File. I added it to the Sharepoint Library but when I click Browse in Flow it gives me a "general" folder but nothing is in the folder.06-04-2020 12-27-13 PM.png

@Tbruns 

I would not recomment to use 'Create worksheet' As I mentioned if you use 'Create worksheet' you need to reference an already existing Excel file (you can click on the 'File' icon on its right to explore your cloud directories, and depending on the connector you use -'Excel Online (Business)' vs Excel Online (OneDrive)'- the Excel file shall be stored in Sharepoint/OneDrive4Business or in OneDrive).

My suggestion is to create manually an Excel file, store it wherever you need better (Sharepoint/OneDrive4Business or OneDrive), then create manually a table inside it (https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e49...) and add all the columns you need to backup your SP list.

Once this steps are completed, I would add an 'Apply to each' action block to your flow, just after 'Get items', assigning as its input 'Get items' output from the Dynamic content menu; and finally add inside your 'Apply to each' an Excel 'Add a row into a table' action block.

Plase also note you need to configure an ODATA filter in 'Get items' in order the items related to your selected date.

 

BTW, did you consider to make the backup as .csv intead of .xlsx? It would be much much faster, and you can also open .CSVfiles with Excel. In case you are interested in this alternate approach, you will find good stuff here, also a template

http://www.sharepointsamples.com/export-sharepoint-list-items-as-csv-using-microsoft-flow/

https://emea.flow.microsoft.com/en-us/galleries/public/templates/79095f001ee911e79dc3835964ea3218/co...

 

Hope this makes sense



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



I would like to do whatever is easiest. I tried the CSV but I am receiving an error on the "Create file". Do you know what I am doing wrong? It would be very helpful to me if you provided screen shots as I am fairly new to this. Thank you06-04-2020 1-39-24 PM.png

Hi again!

Not with my laptop, unable to share screenshots

 

Two comments:

1.Please remove 'outputs' dynamic content from 'Create File' 'File Name' input

2.Once done, please reexecute your flow. If it still fails, share a screenshot from your new flow design, and please expand 'Create CSV table' by clicking twice on it, so we can see its inputs

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



That was the issue! Thank you!

 

Can you advise how I configure an ODATA filter in 'Get Items', as the spreadsheet pulled all dates instead of the specific date I selected.

 

Hi!

What's the name of the Sharepoint column you want to compare the date with? Is it of type 'Date and Time', 'Date only', 'Single line of text'?

In the meanwhile, good stuff on SP ODATA filters here

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



v-alzhan-msft
Community Support
Community Support

Hi @Tbruns ,

 

It seems that your problem is solved with @efialttes 's help, if yes, please go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

 

Best Regards,

Alice

 

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

@efialttes 

Yes it is Date and Time (Date Only)

Hi!

So, let's assume your SP column internal name is called 'MyDate' . As explained in the link I already shared, if you want to get all items whose MyDate is equal to 218-11-25 you just need to assign the following text to 'Filter query' input

MyDate eq '2018-11-25'

 

Now if the date comes from trigger outputs dynamic content, just replace 2018-11-25 and add the trigger output from the dynamic content menu instead, but please remember to keep the single quotes

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Great that worked!

So hopefully last question...When I open the Excel document there are several columns that show the below - How do I get it to show just the value instead of the below?

 

Hi again!

 


So hopefully last question...When I open the Excel document there are several columns that show the below - How do I get it to show just the value instead of the below?

 

Two questions:

 

1.-The example you are sharing belongs to a Choice type column in your Sharepoint list, right?

2.-I believe you are finally using action block 'Create CSV table' right?

If so, you can switch 'Create CSV' 'Column' input from its default value 'Automatic' into 'Custom'. THis way you can select which columns you wanna add, and also perform manipulations if needed

Good example here

https://powerusers.microsoft.com/t5/Building-Flows/Create-CSV-Table-Multiple-Rows-and-attaching-CSV-...

Hope this helps

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Thank you so much for all your help! It works great!

d365_katiepage
Regular Visitor

Hi @efialttes ,

 

You seemed to be very knowledgeable in MS Flow.  Can I please ask for your help. I am new to Flows.

I am trying to retrieve a record from SharePoint list then create/update excel file to different Sharepoint folder.
So I used Get Items and was able to retrieve columns.  The issue was, I don't know how to do the second step which is create/update excel file in different SP folder.  The columns from the source must be assigned to the correct column in the target.

Any idea on how I can do it the creation of excel with correct columns from source?

Thank you


Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Kudoed Authors
Users online (2,433)