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

[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!

[efialttes]

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

[Tbruns]

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.

[efialttes]

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!

[Tbruns]

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

[efialttes]

@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

[Tbruns]

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

[efialttes]

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!

[Tbruns]

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.

[efialttes]

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

[v-alzhan-msft]

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.

[Tbruns]

@efialttes 

Yes it is Date and Time (Date Only)

[efialttes]

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

[Tbruns]

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?

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Provider Services"}

[efialttes · Accepted solution]

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?

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"Provider Services"}

---

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

[Tbruns]

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

[d365_katiepage]

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