PowerShell to retrieve UserID of "Created By" user in a SharePoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-1447274%22%20slang%3D%22en-US%22%3EPowerShell%20to%20retrieve%20UserID%20of%20%22Created%20By%22%20user%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1447274%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Day%2C%20how%20can%20I%20retrieve%20the%20unique%20%22User%20ID%22%20of%20the%20%22Created%20By%22%20or%20%22Modified%20By%22%20User%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%23Get%20SQL%20column%20Definition%20for%20SharePoint%20List%20Field%3CBR%20%2F%3EFunction%20Get-ColumnDefinition(%5BMicrosoft.SharePoint.SPField%5D%24Field)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%24ColumnDefinition%3D%22%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSwitch(%24Field.Type)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Boolean%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bbit%5D%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Choice%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bnvarchar%5D(MAX)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Currency%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bdecimal%5D(18%2C%202)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22DateTime%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bdatetime%5D%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Guid%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Buniqueidentifier%5D%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Integer%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bint%5D%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Lookup%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bnvarchar%5D%20(500)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22MultiChoice%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5BnText%5D%20(MAX)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Note%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5BnText%5D%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Number%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bdecimal%5D(18%2C%202)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Text%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5BnVarchar%5D%20(MAX)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22URL%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bnvarchar%5D%20(500)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22User%22%26nbsp%3B%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bnvarchar%5D%20(255)%20NULL%20'%7D%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bdefault%20%7B%26nbsp%3B%24ColumnDefinition%26nbsp%3B%3D%26nbsp%3B'%5B'%2B%26nbsp%3B%24Field.InternalName%20%2B'%5D%20%5Bnvarchar%5D%20(MAX)%20NULL%20'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3Breturn%26nbsp%3B%24ColumnDefinition%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%23Function%20to%20get%20the%20value%20of%20given%20field%20of%20the%20List%20item%3CBR%20%2F%3EFunction%20Get-ColumnValue(%5BMicrosoft.SharePoint.SPListItem%5D%26nbsp%3B%24ListItem%2C%20%5BMicrosoft.SharePoint.SPField%5D%24Field)%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%24FieldValue%3D%26nbsp%3B%24ListItem%5B%24Field.InternalName%5D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%23Check%20for%20NULL%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bif(%5Bstring%5D%3A%3AIsNullOrEmpty(%24FieldValue))%20%7B%20return%26nbsp%3B'NULL'%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%24FormattedValue%26nbsp%3B%3D%26nbsp%3B%22%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BSwitch(%24Field.Type)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Boolean%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%26nbsp%3B%20Format-BooleanValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Choice%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-StringValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Currency%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%26nbsp%3B%24FieldValue%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22DateTime%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-DateValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Guid%22%26nbsp%3B%7B%26nbsp%3B%24FormattedValue%26nbsp%3B%3D%20Format-StringValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Integer%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%26nbsp%3B%24FieldValue%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Lookup%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-LookupValue(%24FieldValue)%20%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22MultiChoice%22%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-StringValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Note%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-StringValue(%24Field.GetFieldValueAsText(%24ListItem%5B%24Field.InternalName%5D))%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Number%22%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%26nbsp%3B%24FieldValue%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Text%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-StringValue(%24Field.GetFieldValueAsText(%24ListItem%5B%24Field.InternalName%5D))%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22URL%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%26nbsp%3B%20Format-StringValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22User%22%26nbsp%3B%26nbsp%3B%7B%24FormattedValue%26nbsp%3B%3D%20Format-UserValue(%24FieldValue)%20%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%23Check%20MMS%20Field%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%22Invalid%22%26nbsp%3B%7B%20if(%24Field.TypeDisplayName%26nbsp%3B-eq%26nbsp%3B%22Managed%20Metadata%22)%20%7B%26nbsp%3B%24FormattedValue%26nbsp%3B%3D%20Format-MMSValue(%24FieldValue)%20%7D%20else%20%7B%26nbsp%3B%24FormattedValue%26nbsp%3B%3DFormat-StringValue(%24FieldValue)%7D%26nbsp%3B%20%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3Bdefault%26nbsp%3B%20%7B%24FormattedValue%26nbsp%3B%3D%20Format-StringValue(%24FieldValue)%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%7D%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3BReturn%26nbsp%3B%24FormattedValue%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.sharepointdiary.com%2F2016%2F06%2Fexport-sharepoint-list-data-to-sql-server-table-using-powershell.html%23comment-form_8909174904188534977%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.sharepointdiary.com%2F2016%2F06%2Fexport-sharepoint-list-data-to-sql-server-table-using-powershell.html%23comment-form_8909174904188534977%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1447274%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1447346%22%20slang%3D%22en-US%22%3ERe%3A%20PowerShell%20to%20retrieve%20UserID%20of%20%22Created%20By%22%20user%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1447346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F242375%22%20target%3D%22_blank%22%3E%40Samuel%20Thomas%3C%2FA%3E%26nbsp%3B%3A%20Try%20below%20code%3A%3C%2FP%3E%3CP%3E%24CreatedBy%3D%24item%5B%22Author%22%5D%3C%2FP%3E%3CP%3E%24CreatedByObj%20%3D%20New-Object%20Microsoft.SharePoint.SPFieldUserValue(%24web%2C%20%24CreatedBy)%3C%2FP%3E%3CP%3E%24CreatedByUserID%20%3D%20%24CreatedByUserObj.User.ID%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20helps%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448591%22%20slang%3D%22en-US%22%3ERe%3A%20PowerShell%20to%20retrieve%20UserID%20of%20%22Created%20By%22%20user%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F679665%22%20target%3D%22_blank%22%3E%40Ashish_Kohale%3C%2FA%3E%26nbsp%3BI%20have%20added%20this%20suggested%20code%20but%20did%20not%20get%20any%20output%20for%20Author.%20Please%20advice%20where%20to%20add%20or%20modify%20the%20existing%20code%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1448961%22%20slang%3D%22en-US%22%3ERe%3A%20PowerShell%20to%20retrieve%20UserID%20of%20%22Created%20By%22%20user%20in%20a%20SharePoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448961%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F242375%22%20target%3D%22_blank%22%3E%40Samuel%20Thomas%3C%2FA%3E%26nbsp%3B%3A%20Try%20replacing%20%22Author%22%20with%20Created%20By%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Good Day, how can I retrieve the unique "User ID" of the "Created By" or "Modified By" User?


#Get SQL column Definition for SharePoint List Field
Function Get-ColumnDefinition([Microsoft.SharePoint.SPField]$Field)
{
    $ColumnDefinition=""
    Switch($Field.Type)
    {
      "Boolean" { $ColumnDefinition = '['+ $Field.InternalName +'] [bit] NULL '}
      "Choice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar](MAX) NULL '}
      "Currency" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "DateTime" { $ColumnDefinition = '['+ $Field.InternalName +'] [datetime] NULL '}
      "Guid" { $ColumnDefinition = '['+ $Field.InternalName +'] [uniqueidentifier] NULL '}
      "Integer" { $ColumnDefinition = '['+ $Field.InternalName +'] [int] NULL '}
      "Lookup" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "MultiChoice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] (MAX) NULL '}
      "Note" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] NULL '}
      "Number" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "Text" { $ColumnDefinition = '['+ $Field.InternalName +'] [nVarchar] (MAX) NULL '}
      "URL" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "User" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (255) NULL '}     
      default { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (MAX) NULL '}
  }
  return $ColumnDefinition
}

#Function to get the value of given field of the List item
Function Get-ColumnValue([Microsoft.SharePoint.SPListItem] $ListItem, [Microsoft.SharePoint.SPField]$Field)
{
    $FieldValue= $ListItem[$Field.InternalName]
     
    #Check for NULL
    if([string]::IsNullOrEmpty($FieldValue)) { return 'NULL'}
     
    $FormattedValue = ""
     
    Switch($Field.Type)
    {
    "Boolean"  {$FormattedValue =  Format-BooleanValue($FieldValue)}
    "Choice"  {$FormattedValue = Format-StringValue($FieldValue)}
    "Currency"  {$FormattedValue = $FieldValue}
    "DateTime"  {$FormattedValue = Format-DateValue($FieldValue)}
    "Guid" { $FormattedValue = Format-StringValue($FieldValue)}
    "Integer"  {$FormattedValue = $FieldValue}
    "Lookup"  {$FormattedValue = Format-LookupValue($FieldValue) }
    "MultiChoice" {$FormattedValue = Format-StringValue($FieldValue)}
    "Note"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "Number"    {$FormattedValue = $FieldValue}
    "Text"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "URL"  {$FormattedValue =  Format-StringValue($FieldValue)}
    "User"  {$FormattedValue = Format-UserValue($FieldValue) }
     #Check MMS Field
     "Invalid" { if($Field.TypeDisplayName -eq "Managed Metadata") { $FormattedValue = Format-MMSValue($FieldValue) } else { $FormattedValue =Format-StringValue($FieldValue)}  }
    default  {$FormattedValue = Format-StringValue($FieldValue)}
  }
  Return $FormattedValue
}

 

https://www.sharepointdiary.com/2016/06/export-sharepoint-list-data-to-sql-server-table-using-powers...

3 Replies

@SamTech : Try below code:

$CreatedBy=$item["Author"]

$CreatedByObj = New-Object Microsoft.SharePoint.SPFieldUserValue($web, $CreatedBy)

$CreatedByUserID = $CreatedByUserObj.User.ID;

 

Hope it helps !

@Ashish_Kohale I have added this suggested code but did not get any output for Author. Please advice where to add or modify the existing code

@SamTech : Try replacing "Author" with Created By"