potatoqualitee / spreplicator

♻ Replicates SharePoint Lists

SPReplicator

SPReplicator is a PowerShell module that helps replicate SharePoint list data.

This module uses the SharePoint Client Side Object Model (CSOM), the PnP.PowerShell module and all required libraries and dlls are included. Installing the SharePoint binaries is not required for the replication to work 👍 Thank you Microsoft for the redistributable nuget and PnP.PowerShell.

SPReplicator works with both on-prem and SharePoint Online and is currently in beta. It also works on .NET Core, so it's cross-platform, and supports Windows, macOS and Linux.

Please report any issues to [email protected].

Installer

SPReplicator is now in the PowerShell Gallery. Run the following from an administrative prompt to install SPReplicator for all users:

Install-Module SPReplicator

Or if you don't have administrative access or want to save it locally (just for yourself), run:

Install-Module SPReplicator -Scope CurrentUser

If you're scheduling tasks via Task Schedule or SQL Server agent, installing the module with administrative privileges is best because it will ensure all users have access via Program Files.

Command Reference

For more details about commands, visit the wiki or use Get-Help.

Usage scenarios

This module can be used for replicating data in a number of ways.

• Between air gapped (offline) servers that do not have direct access to each other
• Directly from SharePoint site collection to SharePoint site collection
• From SQL Server to SharePoint
• From SharePoint to SQL Server
• From CSV to SharePoint
• From SharePoint to CSV
• From On-prem to SharePoint Online and back

Usage examples

SPReplicator has a number of commands that help you manage SharePoint lists. You can view, delete, and add records easily and there's even a command that makes it easy to see internal column names and datatypes.

Export from SharePoint List

Export-SPRListItem -Site https://intranet -List Employees -Path \\nas\replicationdata\Employees.csv

Establish a session to the SharePoint site

You can specify -Site and -Credential with every command. Or you can establish a connection and not worry about specifying the Site or Credentials in subsequent command executions.

There is no need to assign the output to a variable, as it creates a reusable global variable $global:spsite.

# using your own account credentials
Connect-SPRSite -Site https://intranet

# specifying other credentials
Connect-SPRSite -Site https://intranet -Credential ad\otheruser

# using your own account credentials and SP Online
Connect-SPRSite -Site https://corp.sharepoint.com -Credential [e-mail]

# using MFA
Connect-SPRSite -Site https://corp.sharepoint.com -AuthenticationMode WebLogin

# using app login
Connect-SPRSite -Site https://corp.sharepoint.com -AuthenticationMode AppOnly -Credential 1e36c5cc-5281-4235-a84f-c94dc2de8800

Import to SharePoint List

Now that we've established a connection via Connect-SPRSite, we no longer need to specify the Site.

We can import data two ways, using Import-SPRListItem or Add-SPRListItem

# Import from CSV
Import-SPRListItem -List Employees -Path \\nas\replicationdata\Employees.csv

# Import from SQL Server
Invoke-DbaQuery -SqlInstance sql2017 -Query "Select fname, lname where id > 100" | Add-SPRListItem -List emps

# Import any PowerShell object, really. So long as it has the properly named columns.
Get-ADUser -Filter * | Select SamAccountName, whateverelse | Add-SPRListItem -List ADList

# Didn't have time to create a good SharePoint list? Use -AutoCreateList
Get-ADUser -Filter * | Add-SPRListItem -List ADList -AutoCreateList

Find out more

This was just a subset of command examples. For more command examples, visit the wiki or use Get-Help.

Selected screenshots

Connect to a site

Add a generic object to a list

Add SQL data to a list and auto create the list if it doesn't exist

This is what it looks like!

Get details about columns to help you format your input/output

Results of built-in logger (New-SPRLogList and -LogToList)

Power BI

A Power BI Template can be downloaded from here.

Pester tested

This module comes with integration tests! If you'd like to see how I test the commands, check out Integration.Tests.ps1

Learn more

To find out more about any command, including additional examples, use Get-Help.

Get-Help Get-SPRColumnDetail -Detailed

spreplicator's Issues

add folder support to new commands

update readme

Some code

Right now, creating a site collection is out of scope but if anything changes, here's some code for New-SPRSiteCollection

https://blogs.u2u.be/u2u/post/Batch-create-Site-Collections-with-a-custom-template-in-SharePoint-Online-using-PowerShell

modify log url

FieldUrlValue url = new FieldUrlValue();
url.Url = "Your Url here";
url.Description = "Your description here";
oListItem["URL"] = url;

ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
var lib = ctx.Web.Lists.GetByTitle("All Project List");
li = lib.AddItem(itemCreateInfo);
li["Title"] = "New Item";
Microsoft.SharePoint.Client.FieldUrlValue u = new Microsoft.SharePoint.Client.FieldUrlValue();
u.Url = "http://www.espn.com";
u.Description = "title";
li["Project"] = u;
li.Update();
ctx.Load(li);
ctx.ExecuteQuery();

try piping a list again and fix

get item | add item to new list doesnt work

consider switch-spruser

to

• get a user
• clone it to new
• remove it

Ignore Resources? Or how to handle

fix ugly error message

demo video

add support for saving templates to reuse with new

with without/content, etc

Make a dictionary that tracks what's already been imported

If there's no column key.

Add another segment to the XML. key.. keep it in json on both the distributor and the subscriber. and include it in the exported XML

Check for column name created as column name

facilities is giving an issue?? create a column named facilities see if it can repro

AutoCreateList does not support Lookups

Export-SPRListItem -List twitch2 -Path C:\temp\blah.dat | Import-SPRListItem -List twitch4 -AutoCreateList

Multiline showing up as System.Collections.ArrayList

Multiyall : System.Collections.ArrayList

for log list

add duration and whodunit

do attachments

use splibrary from craig

rethink site to web?

add auto complete cacher

Recurse

Certificate auth

add support

uninformative failure

new-sprlist should ahve a better failure message all the time but especially if a duplicat elist is attempted

random potential features

• Explore hooks, such as the check-in or check-out of a document.
• File copy using BITS and zips and maybe Microsoft Remote Differential Compression?
• Explore ListItemCollectionPosition - no need cuz i used a different position tracker in delete
• Make a dictionary that tracks what's already been imported
  • Taken care of by Column Key, but maybe.

fix tab completion back to enumerator

Register-PSFTeppScriptblock -Name Template -ScriptBlock { Get-SPRListTemplate | Where-Object Id -ne -1 | Select-Object -ExpandProperty Template }

remove dupe verboses within whatifs

enable pipe right to -autonew

list -> export -> import works. List -> add item does not

write whatif tests

check for objects existing

remove list is missing name in confirm

potential workaround for auth issue in core

http://sharepointconnoisseur.blogspot.com/2015/01/workaround-to-make-sharepoint-online.html

#Bind to Site Collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$Context.Credentials = $Creds

#Identify users in the O365.User group
$root = [ADSI]''
$searcher = New-Object System.DirectoryServices.DirectorySearcher($root)

# Please change user group for your query
$searcher.filter = "(&(objectcategory=user)(memberof=CN=O365.Users,OU=Distribution Lists,OU=Exchange,DC=na,DC=qualcomm,DC=com))"
$users = $searcher.findall()

$adminCookie = $Creds.GetAuthenticationCookie($adminUrl)

# Get the authentication cookie by passing the url of the web service
$siteCookie = $Creds.GetAuthenticationCookie($siteUrl);

# Create a CookieContainer to authenticate against the web service
$authContainer = New-Object System.Net.CookieContainer;

# Put the authenticationCookie string in the container
$authContainer.SetCookies($adminUrl, $authCookie);

# Concatenate the URL for Web Service / REST API
$url = $adminUrl  + "/_vti_bin/userprofileservice.asmx";


# Create the O365 REST service            
$UserProfileWS = $null;           

try{            
 $UserProfileWS=New-WebServiceProxy -Uri $url -Namespace 'SPOUserProfileService';    
    $UserProfileWS.UseDefaultCredentials = $false;


    $UserProfileWS.CookieContainer = New-Object System.Net.CookieContainer;
    $UserProfileWS.CookieContainer.SetCookies($adminUrl, $adminCookie);   

    # Assign previously created auth container to web service
    #$UserProfileWS.CookieContainer = $authContainer;    

    $targetSite = New-Object Uri($adminUrl);

    #$UserProfileWS.CookieContainer = New-Object System.Net.CookieContainer;

    Write-Host $authCookie;
    $secondPartOfCookie = $adminCookie.TrimStart("SPOIDCRL=".ToCharArray());

    $cookie = New-Object System.Net.Cookie;
    $cookie.Name = "FedAuth";
    $cookie.Value = $secondPartOfCookie;
    

    $UserProfileWS.CookieContainer.Add($cookie);

}            
catch{             
    Write-Error $_ -ErrorAction:'SilentlyContinue';             
}

# Create People Manager object to retrieve O365 profile data
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)

for ($i=0; $i -le $users.Count – 1; $i++)  
{

    $user = $users.Item($i)
    $email = $user.Properties["mail"] 
    $login = "i:0#.f|membership|" + $user.Properties["mail"]


    $UserProfile = $PeopleManager.GetPropertiesFor($login)

    try
    {
        Write-Host "User LoginName:" $User.LoginName -ForegroundColor Green
        Write-Host "User Email:" $User.Email -ForegroundColor Green

        # Query AD to get user UPN value through email

        $searcher.filter = "(&(objectClass=user)(mail= $email))"
        $aduser = $searcher.findall()
        $UPNValue = $aduser[0].Properties["userprincipalname"] 
       
        Write-Host "UPN Value:" $UPNValue.Item(0) -ForegroundColor Green

        # Update the SIP of O365 UPS for this user

        $userProperty = $UserProfileWS.GetUserPropertyByAccountName($login, 'SPS-SipAddress')
        $currentsid = $userProperty[0].Values[0].Value;
        $userProperty[0].Values[0].Value = $UPNValue.Item(0);
        $userProperty[0].IsValueChanged = $true;
        $UserProfileWS.ModifyUserPropertyByAccountName($login, $userProperty)


        $line = $login + “,” + '"' + $email  + '"'  + “,” + $currentsid + “,” + '"' + $UPNValue.Item(0) + '"'
        Add-Content $reportFile $line

    }
    catch
    {             
        Write-Error $_ -ErrorAction:'SilentlyContinue';             
    }    

}

$b = Get-Date
Write-Host "Time after:" $b -ForegroundColor Green

Connect to spo site with MFA

Hello, is MFA supported?

itemcount 0 when piped from get item to export item

clear-sprconfig

to start from scratch

Ignore clears in PBI for counts

no title when piped sometimes, too

add new tests for new commands

update all docs ensuring examples are right

fix default view on log list

Wrap exporter / backup restore

so that people can export sites

potential solution for auth problems

$client.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")

per https://stackoverflow.com/questions/18866958/downloading-a-file-from-sharepoint-online-with-powershell

Set field value command

change scope of global vars