This is a preliminary post just to tell you how I accomplished this task. I’ll follow up with screenshots at a later date.
I thought this task would be fairly straight forward using flow, but it turned out to be a little more challenging than expected.
I had the simple requirement to backup what is currently in a SharePoint list and output to a spreadsheet file in excel format. Basically taking a snapshot in time.
Building the template file
First I went to the SharePoint list that I want to export and made sure all the columns I want to display are visible in the view. Then I exported that list structure to excel using the export to excel button above the list…
… Done, no automation needed… I’m joking… Read on
I exported the file to create a template for my backup. I opened the file in excel and removed the last columns that are just meta data and only kept the information columns I need.
I also converted the data to a table and called it Table1. This table will be used in the flow later so this step is important.
I then deleted all the content so the table is blank and only contains the header row and insert row. Save it to OneDrive.
Build the workflow
I created a blank flow and inserted a schedule trigger to run at 7 am.
Insert the get items action and choose your SharePoint list
Insert action to add row to a table and then choose your template file and the table that you created earlier.
All the columns in the table should then appear as fields in the form.
Àdd a dynamic value that matches your column from the original get items action. When you do this, the flow will insert the apply to each action for you because it will detect that you have multiple values.
Continue to map the rest of your dynamic values from the get items action to put your SharePoint list values into the insert row action fields .
After the apply to each action, insert a get file content action on the template file to obtain the inserted rows.
Insert a create file action and put the output of the previous action as the file content. Create a dynamic value for the filename using the it now expression or similar as part of your filename.
At this stage the flow will have created your backup. Now we just need to reset our template file, ready for the next time this flow runs.
Insert a get items in a table action and choose the template file.
Insert a delete row in a table action and use a unique column as a key. I used the ID column of the list from the previous get rows in a table action. Flow should automatically insert the apply to each action for you when it detects the multiple values.
Insert an email notification action to let you know that your backup is ready.
That’s it in a nutshell. If you have multiple lists to backup to the excel file, just add more tables to your template file. Then repeat the insert row actions.
Hope this helps somebody