Aug 31 2022 04:26 AM
Aug 31 2022 04:26 AM
I have a SharePoint site with 6000 items in. I have analysed the list of files and identified 4000 that I want to delete. I have a list of the exact filenames to be deleted in Excel. Does anyone know how we could create an automatic way to do this?
(Note - I am a SharePoint novice)
Sep 01 2022 02:42 AM
@JP123JP to automate this you will need to build a flow in Power Automate. In this example we have the Excel spreadsheet with the exact name of the file including the extension. You must format it as a table.
This is the SharePoint list:
Start Power Automate and create a new instant cloud flow. Every flow must have a trigger - the thing that starts it - and actions. We'll start the flow running manually so select Manually trigger a flow.
The first action will be the Excel for Business List rows present in a table. Click in the location field and select One Drive for Business. Click the the Document library field and select OneDrive. Click the folder icon in the File field and navigate to your spreadsheet and select it. Then in the Table field select the table name from the spreadsheet.
By default this action will only list up to 256 rows so to increase this click the 3 dots in the top right of the action and select Settings. Turn the pagination toggle on and in the Threshold field type 5000 then click Done at the bottom of the Settings pane:
Next, add an Apply to each action, click in the first field and select value from the list rows present in a table section of the dynamic content box that appears on the right.
Inside the apply to each add a Get files (properties only) action. Click in the site address field and select your SharePoint site. Click in the library name field and select Documents (or whichever library your documents are in). In this action click show advanced options and in the filter query field type FileLeafRef eq '' and inside the single quotes select Name from the list rows present in a table section of the dynamic content box.
What this does is to loop through the spreadsheet and for each row it gets the corresponding file in the library. Note, although your column name in the library is Name you must use FileLeafRef in the filter query or the flow will error.
You will need to increase the threshold for the get files action as it only brings back 100 by default. So clock the 3 dots in the top right, select Settings, turn the pagination toggle on and in the threshold field type 5000 then click Done.
Next, add another apply to each, click in the first field and select value from the Get files (properties only) section of the dynamic content box:
Still inside the apply to each, add a Delete file action, select your site and in the file identifier field select Identifier. Note, do not select ID or the flow will error.
Save and run the flow. It will delete all the files where the filename and extension matches the file in the SharePoint library. For 4000 items it will some time to complete.
Microsoft Power Automate Community Super User
Sep 02 2022 01:02 AM
@RobElliott Thank you for the detailed response :)
I am struggling to connect Power Automate to our "On Premise" SharePoint site. Do you know whether this solution would work in our case?
Sep 02 2022 01:51 AM