Answered

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

(0) Share

Report

Posted on by Tbruns

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!

Categories:

Building flows

I have the same question (0)

All responses (16)

Answers (1)

Sort by

efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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

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

Was this reply helpful? Yes No
Tbruns on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Tbruns on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

@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-6c94334e492c) 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/copy-sharepoint-list-items-into-a-csv-each-week/

Hope this makes sense

Was this reply helpful? Yes No
Tbruns on at

Like (0)

Report
Copy link

Link copied!

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 you

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Tbruns on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No