Auto-Sync external data with a native SharePoint list

As you are probably aware, in SharePoint Server 2013 we can utilise external data thanks to Business Connectivity Services (BCS).

We can create External Content Types in SharePoint Designer, define what we want to do with our data (all CRUD options available) and display that data in an External List.

One would say it is brilliant. We can use SharePoint to CRUD data in another LOB systems e.g. another SQL database. It is good but not perfect. If you ever worked with SharePoint External List, you had noticed that we cannot utilise SharePoint core functionality available in native SharePoint lists. These are the features which are currently not supported:

- Per-location view settings

- Metadata navigation settings

- Metadata & Custom columns

- IMP settings

- Alerts

- Export to Excel (this may work arguably)

- Versioning

Yes, instead of an External List you can just create External Columns an use them in your native SP lists to have above features available. This however was not an option for me as lookup columns will not take into account any new items that have been created in an external system.

I have decided to replicate my External List to a native SharePoint list and create a PowerShell script to automate the process. Now, many scripts I have looked at do the similar thing but still it was not quite what I was looking for. The scripts I found on the internet were exporting External List into a CSV file and importing the values into a native SP List. One script was checking if an item exists in a SP List and if not, it created a new one. Some of the scripts were also updating ALL items instead of the ones that have changed.

My requirements were:

1. Export an External List into a CSV file (always up to date info)

2. Import a CSV file into a SP Native List

a) Check if an item exists, if not - create it

b) Check if an update is needed, if yes- update JUST the items that changed (needed for efficient versioning)

3. Run the script on a scheduled basis for automation

4. Utilise SharePoint functionality (alerts, metadata etc. in a Native List)

Here is my script to accomplish points 1 and 2 above

Thank you Dan Christian for ideas in this post:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Get the context
$ctx = Get-SPServiceContext https://intranet.company.com

#Get the scope
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

#Get the target site collection
$webTarget = Get-SPWeb -identity "https://intranet.company.com/site"

#Get the Target List
$list = $webTarget.Lists["Name of External List"]

#Array to Hold Result - PSObjects
$ListItemCollection = @()

#Get All List items (ColumnID is greater than or equal to 1)
$list.Items | Where-Object { $_["ColumnID"] -ge 1} | foreach {
$ExportItem = New-Object PSObject
$ExportItem | Add-Member -MemberType NoteProperty -name "Column1" -value $_["Column1"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Column2" -value $_["Column2"]
$ExportItem | Add-Member -MemberType NoteProperty -name "Column3" -value $_["Column3"]
$ExportItem | Add-Member -MemberType NoteProperty -name "ModifiedDate" -value $_["ModifiedDate"]
$ExportItem | Add-Member -MemberType NoteProperty -name "ColumnID" -value $_["ColumnID"]
#Add more columns if required

#Add the object with property to an Array
$ListItemCollection += $ExportItem
}
#Export the result Array to CSV file
$ListItemCollection | Export-CSV "\\fileserver\directory\export.csv" -NoTypeInformation

#Dispose the web Object
$webTarget.Dispose()

#Pause for 10 seconds for the CSV file to populate all the way.
Start-Sleep 10
function Update-SPList()
{
#Import to destination list
#This section of the PowerShell will loop through the csv file we created, compare the inventory list and add new item if it didn't exist.
$csvVariable= Import-CSV -path "\\fileserver\directory\export.csv"

# Destination site collection
$WebURL = "https://intranet.company.com/site"

# Destination list name
$listName = "SP Native List"

#Get the SPWeb object and save it to a variable
$webDestination = Get-SPWeb -identity $WebURL

#Get the SPList object to retrieve the list
$list = $webDestination.Lists[$listName]

#Get all items in this list and save them to a variable
$items = $list.items
#loop through csv file
foreach($row in $csvVariable)
{
#set variable for adding new items
$added = 0
#set variables for specifying a query needed for updating items
$spQuery = New-Object Microsoft.SharePoint.SPQuery
$camlQuery = '<Where><Eq><FieldRef Name="ColumnID" /><Value Type="Text">'+ $row.ColumnID +'</Value></Eq></Where>'
$spQuery.Query = $camlQuery
$listItems = $list.GetItems($spQuery)
$updateitem = $list.Items | Where { $_["ColumnID"] -eq $row.ColumnID }
#loop through SharePoint list
foreach($item in $items)
{
#check if item exists
if($item["ColumnID"] -eq $row."ColumnID")
{
Write-Host "Item already on the list"
$added++
}
}
#add new item if item does not exist
if($added -eq 0)
{
Write-Host "Adding a new item"
$newItem = $list.items.Add()
$newItem["Column1"] = $row."Column1"
$newItem["Column2"] = $row."Column2"
$newItem["Column3"] = $row."Column3"
$newItem["ModifiedDate"] = $row."ModifiedDate"
$newItem["ColumnID"] = $row."ColumnID"
$newItem.Update()
}
#check if ModifiedDate in CSV not equals ModifiedDate column in SharePoint list- item needs to be updated
elseif ($updateitem["ModifiedDate"] -ne $row.ModifiedDate)
{
Write-Host "Updating" $updateitem["Column1"]
$updateitem["Column1"] = $row."Column1"
$updateitem["Column2"] = $row."Column2"
$updateitem["Column3"] = $row."Column3"
$updateitem["ModifiedDate"] = $row."ModifiedDate"
$updateitem["ColumnID"] = $row."ColumnID"
$updateitem.Update()
}
#otherwise no update needed
elseif ($updateitem["ModifiedDate"] -eq $row.ModifiedDate)
{
Write-Host "No need to update"
}
}
#dispose of SPWeb variable
if ($webDestination)
{
$webDestination.Dispose()
}
}
Update-SPList

You can accomplish Point 3 from my requirements with a Windows Task Scheduler and run this script how often you want to keep your External and Native SharePoint lists in sync.

Once in your Native SharePoint list, you may add metadata and other columns, version control, alerts etc. you know sky is the limit.

You will soon start to notice advantages of this approach because you can work on external data in SharePoint in a much more flexible way.

Enjoy!