Auto-Archive SharePoint List Items using PowerShell

Posted By Posted by: Peter Williams on November 7, 2016

Objective

To implement business rules around archiving SharePoint list items. For example, if an item is marked complete and the last modified date is over one-year-old, then move item to a nominated archive list.

Considerations

A client requested the above objective recently. They were several lists on several sites where he wanted to implement an auto-archiving solution. Specifically, the intention was to create an exact copy (with regard to structure) of a monitored list and have items moved to that list when the nominated business rules were met.

Consideration was given to using the out of the box Information Management Policy feature or to utilize SharePoint Designer workflows. Neither of these options met his requirements. Also, the client didn’t want to use SharePoint list views to filter out items, but instead wanted the items moved to a nominated archive list.

Since the client was using SharePoint 2013 On-Premise (as opposed to SharePoint Online), using a PowerShell script that executed daily was an option. Specifically, the client was using the EPMonDemand service.

The final solution utilized a SharePoint list where the client could provide details on:

  • Which site/list was to have auto-archiving enabled
  • What business rules to implement

Each item on the list also would be updated by the script to indicate when(if) it was processed correctly by the background PowerShell script.

NOTE: I’m not a particularly experienced PowerShell “programmer”, which hopefully should impress upon you the power of PowerShell script composition and encourage you (or your IT guys) to try it for yourself – it’s not too hard to learn. I have had lot of experience in VBA Excel programming though, which does help with understanding the basic principles of programming; i.e. loops, conditional statements, variables, objects etc.

Feature Highlight

Use a PowerShell script that executes daily and which derives it’s settings from a SharePoint list. The combination of using a PowerShell script periodically running in the background in conjunction with a SharePoint “Settings” list provided the customer with the required manageability of the solution.

Pre-Requisites

The list(s) nominated as an archive list (i.e. where archived items are stored) must be a copy (with regard to structure) of the list that is being setup for auto-archiving. Put another way, where an item is being moved from list A to list B, both list A and list B should be identical in structure. One way to achieve this is to create a list template from list A and then create a new list using the list template.

Access to the server hosting SharePoint Server is required – thus ruling out SharePoint online. Also, your user account will require SharePoint Shell access. The following article explains this well: link. Essentially, the following PowerShell commands need to be executed for your account:

$contentDB = Get-SPDatabase | ?{$_.Name -eq “name of content database”}

Add-SPShellAdmin -UserName domain\username -database $contentDB


Walkthrough of Final Solution Implemented

Let’s walkthrough the final solution that was implemented, which will assist you in determining if this blog can be of any benefit to you.

The tables below provide details on the SharePoint “ArchivingSettings” List. The PowerShell script iterates through each item in this list, using the values from each item to determine which list to “process” for auto-archiving and what business rules to apply. Take not of the Description field in the tables below as this will help with understanding the solution implemented.

table-1

pic-1

Figure 1 – A sample ArchivingSettings list item. List “SourceList” has been configured for auto-archiving. Note the SettingsVerifiedAsValidbyScript field has been date-stamped with 5th October 8:41am. This indicates the script processed the SourceList at this time successfully.

Below are screenshots of sample lists used for testing which are referenced in the screenshot figure 1.

pic-2

Figure 2 – These three items in the list have not been archived (or moved to the archive list) as the business rules have not been met.

pic-3
Figure 3 – These items have been moved from “SourceList” list to this list as the business rules have been met. i.e. Status equals completed AND Status2 contains “res” AND the DateCompleted date is over 365 old


Step by Step

Now that I have run through what the script accomplishes, I will detail the steps required to implement the solution.

Step 1 – Create the Settings List
All the details have been provided earlier in this blog to create this list.


Step 2 – Save the PowerShell script onto the App Server

Save the Powershell script as found in the Appendix of this blog onto the SharePoint application server. For example, C:\Scripts\ArchiveListItems.ps1

pic-4


Step 3 – Test that the script functions as expected

Open the SharePoint Powershell Management Console and ‘run as Administrator’.

pic-5

Execute the script and view the results.

pic-6


Step 4 – Setup scheduled task using Windows Task Scheduler to execute script daily

In the Program/script field, enter:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

In the Add arguments (optional) field, ensure the full path to the PowerShell script is entered. E.g.
-file “C:\ScriptsArchiveListItems.ps1”

pic-7

pic-8


Step 5 – Check the SettingsVerifiedAsValidbyScript field on all of your items in the ArchiveSettings list to ensure the item is being processed correctly

pic-9

Appendix

The entire script used can be found here: archivelistitems


Blog Posted In Blog Posted In: How to, Project Online, SharePoint
Blog Posted In Comments Off on Auto-Archive SharePoint List Items using PowerShell