网站搜索

SharePoint Online：使用 PowerShell 将计算列添加到列表

要求：将计算列添加到 SharePoint Online 列表。

如何将计算列添加到 SharePoint Online 列表？

SharePoint Online 中的计算列用于操作列表项中的其他列。这些字段值不是由用户输入，而是根据公式自动计算。计算字段不会出现在“新建表单”或“编辑表单”上，但在“查看表单”和 SharePoint 视图中可见。要将计算列添加到 SharePoint Online 列表，请执行以下操作：

  1. 浏览到 SharePoint Online 网站并导航到要在其中创建计算列的目标列表。
  2. 在“列表”选项卡下，单击功能区中的“创建列”按钮。

  3. 为新列提供名称，将类型指定为“已计算（基于其他列的计算）”。

  4. 在“公式”字段中输入计算列的公式，并指定数据的返回类型。
  5. 向下滚动，设置小数位和数据格式等参数，然后单击“确定”在 SharePoint Online 列表中创建计算字段。

PowerShell 创建计算列以在 SharePoint Online 中列出

您可以使用 SharePoint Online PowerShell 将计算列添加到列表中：


#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

#Custom function to add column to list
Function Add-CalculatedColumnToList()
{ 
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $ListName,
        [Parameter(Mandatory=$true)] [string] $Name,
        [Parameter(Mandatory=$true)] [string] $DisplayName,
        [Parameter(Mandatory=$false)] [string] $Description=[string]::Empty,
        [Parameter(Mandatory=$true)] [string] $FieldsReferenced,
        [Parameter(Mandatory=$true)] [string] $Formula,
        [Parameter(Mandatory=$true)] [string] $ResultType
    )

    #Generate new GUID for Field ID
    $FieldID = New-Guid

    Try {
        $Cred= Get-Credential
        $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
        
        #Get the List
        $List = $Ctx.Web.Lists.GetByTitle($ListName)
        $Ctx.Load($List)
        $Ctx.ExecuteQuery()

        #Check if the column exists in list already
        $Fields = $List.Fields
        $Ctx.Load($Fields)
        $Ctx.executeQuery()
        $NewField = $Fields | where { ($_.Internalname -eq $Name) -or ($_.Title -eq $DisplayName) }
        if($NewField -ne $NULL)  
        {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            #Frame FieldRef Field
            $FieldRefXML=[string]::Empty
            $FieldRefs = $FieldsReferenced.Split(",")
            foreach ($Ref in $FieldRefs)
            {
                $FieldRefXML = $FieldRefXML + "<FieldRef Name='$Ref' />"
            }

            #Create Column in the list
            $FieldSchema = "<Field Type='Calculated' ID='{$FieldID}' DisplayName='$DisplayName' Name='$Name' Description='$Description' ResultType='$ResultType' ReadOnly='TRUE'><Formula>$Formula</Formula><FieldRefs>$FieldRefXML</FieldRefs></Field>"
            $FieldSchema
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()    
            
            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green  
        }
    }
    Catch {
        write-host -f Red "Error Adding Column to List!" $_.Exception.Message
    }
} 

#Set parameter values
$SiteURL="https://crescent.sharepoint.com"
$ListName="Projects"
$Name="TotalInvestmentBuffer"
$DisplayName="Total Investment Buffer"
$Description="5 Percentage in Total Investment"
$Formula = "=([Total Investment]*5)/100"
$ResultType="Currency" 
$FieldsReferenced="[Total Investment]" #Fields Names participating in Formula. Should be Comma (,) Separated

#Call the function to add column to list
Add-CalculatedColumnToList -SiteURL $SiteURL -ListName $ListName -Name $Name -DisplayName $DisplayName -Description $Description -Formula $Formula -ResultType $ResultType -FieldsReferenced $FieldsReferenced

PnP PowerShell 将计算列添加到 SharePoint Online 中的列表

以下是如何使用 PnP PowerShell 将计算列添加到列表中：


#Parameter
$SiteURL = "https://crescent.sharepoint.com/sites/pmo"
$ListName= "Projects"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Add a calculated column to list
Add-PnPField -List $ListName -Type Calculated -InternalName "Buffer" -DisplayName "Buffer" -Formula "=[Budget]*0.10"

相关文章