Recursive Sharepoint archiving

[OllyL]

Hi there,

I'm trying to build a flow that does the following:

• Find all items in Library #1 older than # Months (recursively into sub directories)
• Move to Library #2, recreating source folder structure from Library #1

I've managed to create a basic flow using get past time followed by Get files (properties only) with nested items included and using a filter Query on the 'Modified' property.  I'm a bit stuck on the next bit though. How could I go about build the folder structure in Library #2 then moving the file into the corresponding location?

Thanks very much in advance

Olly

[AnthonyAmador]

Hi @OllyL 

Can you explain what you mean by building the folder structure? I do not understand. 

I was thinking that you can use "Move File" or "Move Folder" actions.

Let me know if you need more help.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OllyL]

Hi Anthony,

Thanks for getting back to me. Sure:

So if we I have a file : /Library1/Some/Folder/Structure/file.docx

This is mirrored to: /Library2/Some/Folder/Structure/movedfile.docx

The flow would build any of the folder structure that is missing, leaving existing files/folders intact. So in some cases the folder structure would already exist, sometimes it might just have to make the top folder ("Structure"), sometimes it might have to create all the folders.

Does this make sense?

[AnthonyAmador]

Thanks for the explanation. 

I was thinking on how to it, but the scenario seems complex, what happens is that you can use the file path of the file to be copied, save it in a variable and split it to know the names of the folders, but you have to compare them with the names in the new folders to make the decision to create a new one or not, the problem is I have no idea how to get those folder names and I do not see any action that can help us here.

Kind regards. 

[OllyL · Accepted solution]

Just as an FYI I have something which seems to be working though I'm not sure if it works in every scenario (I've only tested it so much). It was easier that expected as "Create new folder" does the hard work for you and creates the whole folder tree. My process is just:

• Get past time (which controls the age of documents to be archived)
• Get items
  • For some reason using the option "Limit entries to folder" returns zero results for me so don't set this option
  • Filter Query is (Modified lt '@{body('Get_past_time')}') and (ContentType ne 'Folder')
  • Top Count - Make sure this is set. I think Apply to each (below) has a 5000 item limit so I set this that
  • Limit Columns by view - not sure if this is 100% required but I created a view with only the basic columns in there
  • As a side note I also enabled a new index on the Library in Sharepoint for Modified. Again not 100% sure this is necessary but I had to try a lot of things in order to get any output from this step - Our document library is pretty big hence the need to archive. 
• Apply to Each - operate on 'value' from Get items step
• [AtE] Create new folder
  • Specify archive destination and use "Folder Path" value from Get items as Folder Path
• [AtE] Get file content
  • Specify source and use "Identifier" from Get items as File Identifier
• [AtE] Create file
  • Specify archive destination for site
  • Folder path is 'test/@{items('Apply_to_each')?['{Path}']}' where 'test' is the name of my Archive library
  • File name is "File name with extension" from Get items
  • File Content is "File Content" from Get file content
• (Optional) [Ate] Delete File
  • As we're moving out of the source location, I use a delete here using the source site and the Identifier again to remove it from source library

So far in my testing this seems to work. It mirrors the source folder structure and seems to be able to deal with some partial folder structure already existing in the destination. The major drawback is that you can't target this at a specific folder in the source sharepoint but I think that's more an issue with us exceeding the max view list item threshold and all that pain rather than a particular issue with Power Automate. 

It produces errors too - Create File / Get File Content have a file size limit so it won't work with large files and other bits will also error out. For our purposes though the errors don't matter, so long as we are able to archive out some files it's still a success and we would plan to keep running the job on a loop to maintain the library. 

Hope this helps

Olly

[AnthonyAmador]

@OllyL thanks for sharing your findings, this is valuable information, please mark your own answer as a solution to help more users find it more easily in the future. 

Cheers. 

[BoabyBandy]

Any chance you can share screenshots of this Flow? It is exactly what I need but, I have never built a Flow before.

[OllyL]

Hi Boaby,

The Flow I had didn't actually work so well over large lists because SharePoint was failing internally and only returning some of the results, not all.

My solution in the end was to use Powershell and I have script now which I use to perform this kind of archiving. see script below (provided with no guarantees and full disclaimer, use at your own risk). You'll need the pnp.powershell module and it doesn't currently support 2FA on the login. I'd recommend setting up a new login for this as it will cause throttling over a large document library which stops you being able to use the account for other SharePoint admin tasks.

<#
    -----------------------------------------------------------
    SharePoint Archiving
    -----------------------------------------------------------

    Arguments:
    
    -Calling with zero arguments ./ArchiveDocumentLibrary.ps1 will run from root of doc libray
    -Use additional arguments to run from sub directories e.g. ./ArchiveDocumentLibrary.ps1 Directory1 ChildDirectory2 GrandChildDirectory3
    -Note that directories must be specified individually, in order, and with a blank space between each one

    Notes:

    - Moves files older than $ArchiveDate out of the source location into the Archive location
    - Deletes empty folders in the source location as it goes if $CleanupDirectories is set
    - Files are stored under a folder with $ListName in the archive location. In this way, you can archive multiple source lists to the same archive location (so long as they have different names). 
    - Liable to throttling by Microsoft so be patient, it will get there in the end!
#>

#Required Module for script
Update-Module -Name PnP.PowerShell

#-------------Setings----------------------

#Root Site Url
$SiteUrl = "https://<tenancy>.sharepoint.com"
$username = "<o365 user>"
$password = "<pwd>"

#Source Settings
$RelativeUrl = "/sites/<sitename>"
$ListName = "<documentlibrary>"

#Archive Settings
$ArchiveRelativeUrl = "/sites/<archivesite>"
$ArchiveListName = "<archivelist>"

#Job Settings
$ArchiveDate = get-date "2019-01-01" #Anything older than this will be archived
$FileLimit = -1 # Limits number of files processed at a time. use -1 to turn this off
$CleanupDirectories = $true # Whether to remove empty directories or not
$Debug = $false

#-------------------------------------------

function Recurse-Files($Folder, $Stack)
{        	
	#Write-Output $Folder
    $items = Get-PnPFolderItem -FolderSiteRelativeUrl $Folder
    if ($Debug -eq $true)
    {
        Write-Output ("Folder: " + $Folder)
        Write-Output ("Count: " + $items.count)
    }

        		
    foreach($item in $items)
    {         
        if ($Debug -eq $true)
        {
            Write-Output ("Item: " + $item.Name)
        }

        if ($item.Name -eq "Forms") { continue }                                        
        if ($item.Name -eq "_catalogs") { continue }                                                
        		
	    if ($item.TypedObject.ToString() -ne 'Microsoft.SharePoint.Client.Folder') 
	    { 			
		    $file = Get-PnPFile -AsListItem -Url $item.ServerRelativeUrl			 				
		    if ((get-date $file.FieldValues.Modified) -lt $ArchiveDate)
		    {				               
			    Archive-File $item $file $Folder $Stack
                $FileLimit = $FileLimit - 1
                if ($FileLimit -eq 0) { Exit }
		    }			
	    }		
	    else { 
		    $NewPath = ($Folder + "/" + $item.Name) 
                        
            $TempStack = $Stack.PSObject.Copy()            
            $TempStack.Push($item.Name)

		    Recurse-Files $NewPath $TempStack
	    }							    
    }
     
    if ($CleanupDirectories)
    {
        $items = Get-PnPFolderItem -FolderSiteRelativeUrl  $Folder
        if ($items.count -gt 0) {} else {
        Cleanup-Empty-Folder $Stack
        }
    }                  
}

function Archive-File($Item, $FileItem, $ParentFolder, $Stack)
{	
    Write-Output ("Archiving:  " + $Item.Name + " - " + $FileItem.FieldValues.Modified)    
    
    #Build Destination Path
    $temps = $Stack.PSObject.Copy()           
    $TargetUrl = ""
    while ($temps.count -gt 0) {  $TargetUrl = ("/" + $temps.Pop() + $TargetUrl) }    
    $TargetUrl = ($ArchiveListName + $TargetUrl)  

    #Generate Destination Folder if it doesn't exist
    if ($Debug -eq $false) 
    {
        Resolve-PnPFolder -SiteRelativePath $TargetUrl -Connection $ArchiveConnection | Out-Null
    }
    		
    #Move the File to Archive	    
    if ($Debug -eq $false) 
    {
        Move-PnPFile -SourceUrl ($RelativeUrl + "/" + $ParentFolder + "/" + $Item.Name) -TargetUrl ($ArchiveRelativeUrl + "/" + $TargetUrl) -Overwrite -AllowSchemaMismatch -IgnoreVersionHistory -Force
    }
}

function Cleanup-Empty-Folder($Stack)
{   
    $temps = $Stack.PSObject.Copy()           

    $Name = $temps.Pop()

    $TargetUrl = ""
    while ($temps.count -gt 1) {  $TargetUrl = ("/" + $temps.Pop() + $TargetUrl) }        
    $TargetUrl = ($ListName + $TargetUrl)

    Write-Output ("Clean-up:  " + $Name + " in " + $TargetUrl)    
    if ($Debug -eq $false) 
    {
        Remove-PnPFolder -Name $Name -Folder $TargetUrl -Force
    }
}

$encpassword = convertto-securestring -String $password -AsPlainText -Force
$credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $encpassword

#Get Connection for connecting to the archive (need to do this before the other connection as it also connects interactive)
$ArchiveConnection = Connect-PnPOnline -Url ($SiteUrl + $ArchiveRelativeUrl) -ReturnConnection -Credentials $credentials

#Start (Main Method)
Connect-PnPOnline -Url ($SiteUrl + $RelativeUrl) -Credentials $credentials

#Prep stack
$StartStack = new-object system.collections.stack
$StartStack.Push($ListName)

#Load Path from args
$StartPath = $ListName
for ( $i = 0; $i -lt $args.count; $i++ ) {
    $StartPath = ($StartPath + "/" + $args[$i])
    $StartStack.Push($args[$i]) 
}


if ($Debug -eq $true) { Get-PnPList }

#Go!
Recurse-Files $StartPath $StartStack