SharePoint Migration with ShareGate PowerShell

ShareGate application supports content migration and content transformation, using the following procedure: ShareGate application supports content migration and content transform.

According to the article, "You cannot export the metadata file with PowerShell. It can be created within the application and then imported into your PowerShell script."

However, comparing the Excel file created by using the "Export" option of the ShareGate app with the file created by the Export-List PowerShell command I see very similar structure. The main difference is that SG app is "aware" of the target site and:

• matches user names to the target tenant
• uses column names from the target site

The Export-List simply exports list content, using domain\alias in the user fields, and the column names from the source site.

Why the warning then?

According to Nicholas from Sharegate support team team, the recommendation not to use the downloaded Excel mainly applies when using the actual downloaded content; for example, when exporting Documents library to a file share and importing from that file share to SharePoint with the created Excel file:

By default, the source path in that excel file will show "Documents/" at the beginning of the file's path as well as have the file's version in the file name.

This happens because we're putting the path to the downloaded file; files are downloaded to a new Documents folder and their version number is added to the file name to differentiate each version of the file. If you're migrating list items, then this won't happen, but there will be a column for the attachments.

However, if your files are still in the source site, you could connect to your source site and your destination site as usual and then use your excel file from the Export-list on your Copy-Content line. The expected behavior here is that the files from the source site will be migrated to the destination, but with the metadata from the excel file.

For list items, I tested it, and it worked without modifications.
For documents, the excel file would need modification for the source path. Removing "Documents/" at the beginning and the file version number from the SourcePath column sufficed in my tests because nothing changed at the source.

That is great news indeed! =)

Migrating SP lists with ShareGate PowerShell

I have four SharePoint lists that need to be migrated with version history, authors and timestamps. Three of these lists require content transformations.

Not only I want to test (and re-test) the migration process before the D-day, but also I need to copy the content to additional environments (Dev, UAT). Performing all these tasks manually is a no-go.

Extract, Transform, Load

Luckily, I can use the Export-Excel command to export list contents and use it as a file supporting the migration process.
The Copy-Content uses -SourceList and -DestinationList to perform the migration and the file referenced in -ExcelFilePath only provides additional metadata. Or, in my case, updated metadata.

Extract

I'm using the Export-Excel command to export list contents. The -Name parameter accepts multiple list names, and also works with wildcards. Neat!

$listNames =  Requests,Portfolio,Schedule,Archive
Export-List -SourceSite $srcSite -Name $listNames -DestinationFolder $currentLocation -NoVersionHistory:$noVersionHistory

One advantage of using this command is that the resulting Excel reflects the content and the structure of the source list- meaning it can be reused in multiple "downstream" environments.

Transform

Data in each of the generated Excel files must be updated, to correct values or to generate new columns with calculated values.
I'm using Import-Excel module, which very conveniently imports the contents of Excel to PSObject[] and allows me to easily manipulate them.

If you haven't used the Import-Excel module yet, have a look at the ImportExcel GitHub repo, which contains more than 100 examples to get you started.

# $worksheetName = 'Data',
$data = Import-Excel $sourceFilePath -WorkSheet $worksheetName
$data = Set-Dates -data $data
$data   | Export-Excel $targetFilePath -WorkSheet $worksheetName -ClearSheet

Important: Make sure to check Considerations section in the Export metadata and import and copy with Excel or CSV article. For example, if you modify values in the SourcePath, ID, or Version column, the migration tool will not be able to identify your items correctly during the import.

Load

Just like when using ShareGate application, I need to provide mapping files.
The 'content type mapping' and 'user mappings' are easy enough - they can be exported from the ShareGate app and reused in PowerShell "as-is".
The 'properties mapping' is more interesting; unlike ShareGate application, the Copy-Content command only accepts one mappings file for mapping properties which has to handle both sources: the SharePoint site and the Excel file.
Once again, Nicholas from Sharegate support team stepped in:

Since we're only interested in the property mapping, the source list is required with a Copy content operation so that we can compare the columns from both lists, and you can associate them the way you would like. Since an export metadata CSV file will be used, you are correct that the metadata from the CSV will be used, but you can apply the property mappings on top of it.

Technically, if you have the CSV file with exported metadata, the necessary changes can be made in the CSV file and imported without a property mapping. The option is always available and will take priority for the property mappings.

When performing content transformation, I am creating new columns to store the updated values, because it allows me running tests to ensure data validity.
For this reason my 'properties mapping' file is referencing both sources. To make my life easier, I exported the mapping file from ShareGate and only updated specific property mappings:

    <Mapping IsKey="false">
      <SourceProperties>
        <Property InternalName="RoomFrom_Excel" Title="RoomFrom_Excel" />
      </SourceProperties>
      <DestinationProperty InternalName="Room From" Title="Room From" />
    </Mapping>
    <Mapping IsKey="false">
      <SourceProperties>
        <Property InternalName="Setup" Title="Setup" />
      </SourceProperties>
      <DestinationProperty InternalName="Setup" Title="Setup" />
    </Mapping>

Last but not least, the Copy-Command requires property template defining general configuration: e.g. whether permissions or versions should be carried over. This template caused me some problems; after exporting it from the ShareGate app, I couldn't reuse it in PowerShell when targeting another site. Instead of spending any more time troubleshooting it, I'm creating it in PowerShell with New-PropertyTemplate

$propertyTemplate = New-PropertyTemplate -AuthorsAndTimestamps -VersionHistory
$mappingSettings = New-MappingSettings
$mappingSettings = Import-ContentTypeMapping -Path $contentTypeMappingFile
$mappingSettings = Import-PropertyMapping -Path $propertyMappingFile  -MappingSettings $mappingSettings
$mappingSettings = Import-UserAndGroupMapping -Path $userMappingFile -MappingSettings $mappingSettings
$copysettings = New-CopySettings -OnContentItemExists IncrementalUpdate

$result = Copy-Content  -SourceList $srcList -DestinationList $dstList  `
                        -ExcelFilePath "$excelFile"  `
                        -Template $propertyTemplate `
                        -MappingSettings $mappingSettings  -CopySettings $copysettings  `
                        -TaskName "$srcListName to $dstListName ($buildId)" `
                        -InsaneMode -WaitForImportCompletion -WhatIf:$WhatIf
Export-Report -CopyResult $result -Path "$resultsFile"