allBlogsList

SXC9 - Using Sitecore PowerShell to work with Commerce Catalog Data


Sitecore Experience Commerce (SXC9) provides different ways of interacting with Catalog data (Commerce APIs and Postman, Sitecore via Commerce Connect). Using Sitecore PowerShell can be a quick yet powerful way to read data from Commerce Catalog and update Sitecore items, associated with Commerce Catalog, something we needed to do quite often. Out of the box, Sitecore is using Commerce Connect Data Provider to read Commerce data, so Catalog appears to be a part of Sitecore content, which makes it so much easier to access from Sitecore without even knowing much about Commerce. We’ll use that in below examples.

Here are few things to keep in mind when working with Commerce data from Sitecore via Commerce Connect:

  • Commerce data provider is read-only, which means Commerce data itself cannot be updated with it, but Sitecore is adding few fields (such as “template ID” or “workflow state”), which can be modified from Sitecore
  • Be careful, backup before making changes. Even though Commerce data provider exposes Commerce data as read-only, it’s still possible to break things from Sitecore side. This post may help if Sitecore side of Commerce catalog data got corrupted.
  • When accessed through Connect data provider, Commerce data appears mostly the same in master and web databases. When Catalog items are published (publishing of Commerce Catalog items from Sitecore is available starting with SXC 9.0.2), then, unlike regular Commerce items, they’re not physically copied from master to web. “Workflow State” field is set to “Published” and then Sitecore Connect components do a few tricks to make Catalog items appear as if they’ve been published.
  • In below examples I’m using freshly installed SXC9 instance with catalogs path associated with default SXA storefront, so my catalog appears under /sitecore/content/Sitecore/Storefront/Home/Catalogs, but yours may be different (e.g. /sitecore/Commerce/Catalog Management/Catalogs), so if below scripts don’t work for youj, try replacing this path
  • And one more thing: PowerShell Scripting Environment can be found in Sitecore Launchpad -> “Sitecore ISE”, which links to this path /sitecore/shell/Applications/PowerShell/PowerShellIse. This document provider more details. If you’re not very familiar with Sitecore PowerShell, this site can be a good starting point.

So, without further ado, let’s jump to some of the examples…


Output all Categories, Sellable Items and Sellable Item Variants

Below script will read and output Categories, Sellable Items and Sellable Item Variants from all catalogs. If you need to work with a specific catalog, then just update the root path to point it to the root you need. This script can be modified of cause to, print, say, Sitecore IDs instead of item names, or maybe save output into file in a specific format (scroll to other examples below to see how).

$categories = New-Object 'System.Collections.Generic.List[String]'

$sellableItems = New-Object 'System.Collections.Generic.List[String]'

$sellableItemVariants = New-Object 'System.Collections.Generic.List[String]'

cd 'master:/sitecore/content/Sitecore/Storefront/Home/Catalogs'

$itemsToProcess = Get-ChildItem -Recurse . | Where-Object { $_.TemplateName -eq "Commerce Category" -or $_.TemplateName -eq "Commerce Product" -or $_.TemplateName -eq "Commerce Product Variant"}

if ($itemsToProcess -ne $null) {

    $itemsToProcess | ForEach-Object {

        if($_.TemplateName -eq "Commerce Category") { $categories.Add($_.Name) }

        if($_.TemplateName -eq "Commerce Product") { $sellableItems.Add($_.Name) }

        if($_.TemplateName -eq "Commerce Product Variant") { $sellableItemVariants.Add($_.Name) }

    }

}

$categoriesString = $($categories | sort -Unique) -join "|"

$sellableItemsString = $($sellableItems | sort -Unique) -join "|"

$sellableItemVariantsString = $($sellableItemVariants | sort -Unique) -join "|"

Write-Host "Categories---------------------------------"

Write-Host $categoriesString

Write-Host "Products---------------------------------"

Write-Host $sellableItemsString

Write-Host "SKUs---------------------------------"

Write-Host $sellableItemVariantsString


Find missing Sellable Items

Let’s say we have a list of product names and need to find out which ones are missing in a given product catalog. Below script will take an array of product names then iterate through Catalog items, removing the names of those it finds in the Catalog. That way what remains in the original list is what’s missing in catalog(s). This can be modified of cause to look for missing Categories or Product Variants.

[System.Collections.ArrayList]$productsToCheck = "6042058","1234567"

cd 'master:/sitecore/content/Sitecore/Storefront/Home/Catalogs/Habitat_Master'

$itemsToProcess = Get-ChildItem -Recurse . | Where-Object { $_.TemplateName -eq "Commerce Product" }

if ($itemsToProcess -ne $null) {

    $itemsToProcess | ForEach-Object {

        $productsToCheck.Remove($_.Name);

    }

}

$itemsString = $($productsToCheck | sort -Unique) -join "|"

Write-Host "Sellable Items not found:"

Write-Host $productsToCheck


Export product catalog data into XML file

This can be used to generate Categories and Products feed in XML format. Sometimes we needed to export catalog data and then post it to FTP or some external endpoint to integrate with 3<sup>rd</sup> party systems. You can modify below script as needed and then run it with Sitecore job, which can be triggered it on a regular basis based on a schedule or some event.

cd 'master:/sitecore/content/Sitecore/Storefront/Home/Catalogs/Habitat_Master'

[Sitecore.Context]::SetActiveSite("sxa.storefront.com")

$urlOptions = [Sitecore.Links.LinkManager]::GetDefaultUrlOptions()

$urlOptions.AlwaysIncludeServerUrl = $True

$urlOptions.ShortenUrls = $True

$urlOptions.SiteResolving = $True;

$siteUrl = 'sxa.storefront.com'

$protocol = 'https'

$rootUrl = 'https://sxa.storefront.com/shop/'

$saveFolderPath = $($SitecoreDataFolder) + '\XmlFeeds'

if (!(Test-Path $saveFolderPath))

{

      New-Item -ItemType Directory -Force -Path $saveFolderPath

}

$fileName = $saveFolderPath + '\CatalogExport_' + $(((Get-Date).ToUniversalTime()).ToString("yyyyMMddThhmmssZ")) + '.xml'

$xmlWriter = New-Object System.XMl.XmlTextWriter ($fileName,$Null)

$xmlWriter.Formatting = 'Indented'

$xmlWriter.Indentation = 1

$XmlWriter.IndentChar = "`t"

$xmlWriter.WriteStartDocument()

$xmlWriter.WriteStartElement('Feed')

$XmlWriter.WriteAttributeString('name','Storefront')

$XmlWriter.WriteAttributeString('incremental','false')

$categories = @{}

$itemsToProcess = Get-ChildItem -Recurse . | Where-Object { $_.TemplateName -eq "Commerce Category"}

if ($itemsToProcess -ne $null) {

    $itemsToProcess | ForEach-Object {

        $categoryUrl = $protocol + [Sitecore.Links.LinkManager]::GetItemUrl($_, $urlOptions)

             $xmlWriter.WriteStartElement('Category')

            $xmlWriter.WriteElementString('Name',$_."Name")

      $xmlWriter.WriteElementString('DisplayName',$_.Fields["__Display Name"])

             $xmlWriter.WriteElementString('Url', $categoryUrl)

           $xmlWriter.WriteElementString('ParentCategoryName', $_.parent."Name")

             $xmlWriter.WriteEndElement()

      }    

}

$itemsToProcess = Get-ChildItem -Recurse . | Where-Object { $_.TemplateName -match "Commerce Product" }

if ($itemsToProcess -ne $null) {

      $itemsToProcess | ForEach-Object {

             $xmlWriter.WriteStartElement('Product')

             $productUrl = $protocol + [Sitecore.Links.LinkManager]::GetItemUrl($_, $urlOptions)

           $xmlWriter.WriteElementString('ItemName',$_."Name")

      $xmlWriter.WriteElementString('DisplayName',$_.Fields["__Display Name"])

             $xmlWriter.WriteElementString('Url', $productUrl)

           $xmlWriter.WriteElementString('ParentCategoryName', $_.parent."Name")

             $xmlWriter.WriteEndElement()

      }

}

$xmlWriter.WriteEndElement()

$xmlWriter.WriteEndDocument()

$xmlWriter.Flush()

$xmlWriter.Close()

Send-File -Path $fileName


Generate a report with details on product pages that exist on the site

Iterate through given catalog and output some of the details on products available on the site (item names, paths, product page Urls).

[Sitecore.Context]::SetActiveSite("sxa.storefront.com")

$urlOptions = [Sitecore.Links.LinkManager]::GetDefaultUrlOptions()

$urlOptions.AlwaysIncludeServerUrl = $True

$urlOptions.ShortenUrls = $True

$urlOptions.SiteResolving = $True;

$siteUrl = 'sxa.storefront.com'

$rootUrl = $siteUrl + '/shop'

$protocol = 'https'

Write-Host 'Starting ...';

cd 'master:/sitecore/content/Sitecore/Storefront/Home/Catalogs/Habitat_Master'

$fpUrls = Get-ChildItem -Recurse . | Where-Object { $_.TemplateName -match "Commerce Product" } | ForEach  {

           $url = $protocol + [Sitecore.Links.LinkManager]::GetItemUrl($_, $urlOptions)

            $name = $_."Name"

            $displayName = $_."__Display name"

            $parentCategory = $_.Parent.Fields["__Display name"]

            $fullPath = $_.Paths.FullPath

            return New-Object PSObject -Property @{

                                    Url = $url

                                    Name = $name

                                    DisplayName = $displayName

                                    ParentCategory = $parentCategory

                                    FullPath = $fullPath

                                }

}

$fpCount = $fpUrls.Count;

Write-Host "Done with Item search. Found $fpCount Items.";

$fpUrls | Show-ListView -Title "Found items" -Property @{Label="Name"; Expression={$_.Name} }, `

                            @{Label="Url"; Expression={$_.Url} }, `

                            @{Label="DisplayName"; Expression={$_.DisplayName} }, `

                            @{Label="ParentCategory"; Expression={$_.ParentCategory} }, `

                            @{Label="FullPath"; Expression={$_.FullPath} }


Update select products based on a given criteria

Like I mentioned before, most Catalog fields live in Commerce database and cannot be updated because Connect Data Provider is read only, but those fields that come from Sitecore can be modified. For this example let’s say we need to publish all products created after certain date, your filtering criteria and field being updated might be different of cause, but this should give an idea…

$approvedWorkflowState = "{DC2FB05E-AE00-2010-0F27-E432AC8D62F1}"

$startDate = ([sitecore.dateutil]::IsoDateToDateTime("20281109T000003Z"))

cd 'master:/sitecore/content/Sitecore/Storefront/Home/Catalogs/Habitat_Master'

$itemsToProcess = Get-ChildItem -Recurse . | Where-Object { $_.TemplateName -eq "Commerce Category" -or $_.TemplateName -eq "Commerce Product" }

if ($itemsToProcess -ne $null) {

    $itemsToProcess | ForEach-Object {

        $created = ([sitecore.dateutil]::IsoDateToDateTime($_["__Created"]))

        if($created -gt $startDate) {

            $_.Editing.BeginEdit();

            $_."__Workflow State" = $approvedWorkflowState

            $_.Editing.EndEdit()

        }

    }

}