Finally, AutoFill columns can now be created as Site Columns, well... kind off

In the summer of '24 Leon Armston wrote a blog post about the hoops you would have to jump through in order to deploy AutoFill columns, and this blog post is a follow up on that, as Microsoft has updated the feature.

AutoFill columns is one of the best new features from the SharePoint team in a long time. It allows us to create a field in a Library and define the prompt that IA should use to set a value in that field and thus automating one of tasks that end users hate the most: entering the required metadate.

"Let IA do the boring work"

So, what is the catch, I hear you say? The AutoFill column service is a Pay-As-You-Go service (currently at $0.005/transaction).

As with each of the Pay-As-You-Go the total cost is very hard to predict as it depends on how popular the service will be with the employees.

Why are you droning on about Site Columns and Content Types?

As long as you are working within the same document library there is no need for site columns nor content types, but as soon as you have the same field/column in multiple libraries within the same site collection, then you need to use a site column, otherwise the internal name will differ and you can forget about aggreging/rolling up the content.

If you plan to use the same site column and/or content type across site collections then the best approach is to define the site columns and the content type in the Gallery and deploy/copy it from there. This will ensure good Information Architecture, easy maintenance and governance.

PS it is also a prerequisite for Search ;-)

Create the content type and site columns in the Gallery

As with any kind of Content Type that you expect to use across multiple Site Collections, it should be created in the Content Type Gallery:

You can not specify anything AutoFill related in the Gallery, so the Site Columns are just regular Site Columns, for now....

Once the Content Type is complete, you must publish it.

Add the new content type to a library of your choice (the reference library) and add the prompt to each of the fields

Upload a couple of sample documents to verify that the prompts give the expected results.

Once the prompts work, it is time to extract the configuration using a simple PnP-PowerShell script:

$siteUrl = "https://contoso.sharepoint.com/sites/ContosoInvoiceAutoFillColumns"
$listName  = "test area"
$pnpClientId = "your-PnP-client-id"
$conn = Connect-PnPOnline -Url $siteUrl -Interactive -ClientId $pnpClientId -ReturnConnection

function Export-AutoFillColumnsToCsv 
{
 
    $columns = Get-PnPField -List $listName -Connection $conn
    $autoFillColumns = $columns | Where-Object {$_.InternalName -like "*AutoFill*"}
    $configs = @()
    foreach ($col in $autoFillColumns) 
    {
        $config = New-Object PSObject
        $config | Add-Member -MemberType NoteProperty -Name "InternalName" -Value $col.InternalName
        $config | Add-Member -MemberType NoteProperty -Name "AutofillInfo" -Value $col.AutofillInfo
        $configs += $config
    }
    $configs | Export-Csv -Path "c:\temp\AutoFillColumns.csv" -NoTypeInformation -Encoding UTF8
}
Export-AutoFillColumnsToCsv

This will same the internal names of the fields and the config to a csv for later use:

Using the data stored in the csv file we can now update the fields in any library using the content type. I will assume that you already have a provisioning method that will copy the content type to the site and the library.

This script loads the csv, finds the site columns based on the internal name, sets the property AutoFillInfo to the value from the csv file.

Finally, the List Property bag value "Vti_AutofillColumnSettings" is updated to match the value it has in the reference library.

function Set-AutoFillColumnsFromConfig {
    param (
        [array]$configData,
        [string]$ListName
    )

    foreach ($row in $configData) 
    {
        $col = Get-PnPField -List $ListName -Identity $row.InternalName -Connection $conn
        if ($col) 
        {
         
            Set-PnPField -List $ListName -Identity $row.InternalName -Values @{AutofillInfo = $row.AutofillInfo} -Connection $conn
        }
    }
}
$targetLibraryName = "test3"
$data = Import-Csv -Path "c:\temp\AutoFillColumns.csv"
Set-AutoFillColumnsFromConfig -configData $data -ListName $targetLibraryName


#update the list property bag to enable autofill in the context menu
$list = Get-PnPList -Identity $targetLibraryName -Connection $conn -Includes RootFolder
$list.RootFolder.Properties["Vti_AutofillColumnSettings"] = '{"scenarioName":"Autofill"}'
$list.RootFolder.Update()
$list.Update()
Invoke-PnPQuery -Connection $conn -ErrorAction Stop

I have not yet been able to figure out how

I force the "Autofill columns" menu item

to show up programmatically.

The workaround is to open the AutoFill config in one of the fields and click "save"