Get SharePoint items from lists by using PowerShell

This post describes how to get SharePoint items from any lists by using PowerShell.

In some BI architectures, where the user wants to store the data in spreadsheets or somewhere that is not stored within a database, we can usually offer the option of storing that data within a list in SharePoint.

In this way, we can create lists and store data, creating a more complete organization, security, access via MS Office 365, history of data changes, etc.

Again, I’m not a PowerShell expert, but after looking at forums, official documentation and even talking to Microsoft support, I managed to solve the problem we had with the solutions below.

So, before showing the PowerShell script, it is necessary that the user who will access the service must be an admin user in order to have access to the data without restriction.

To run the PowerShell script it will also be necessary to install the Sharepoint module available at the link below, if the module is not installed, it will not be possible to run the script successfully. Preferably install the module “SharePointPnPPowerShellOnline”, which is the module for SharePoint Cmdlets, but it’s necessary to install the modules for each problem.

PnP PowerShell OverviewSharePoint Patterns and Practices (PnP) contains a library of PowerShell commands (PnP PowerShell) that allows you to…docs.microsoft.com

After installing the module successfully, you should save the script below and run it in PowerShell.

The first script, obtains the data using the module “Get-PnPListItem”, and its function is to retrieve items from lists, all documentation can be found in the link below as well.

Get-PnPListItem (SharePointPnPPowerShell)Get-PnPListItem -List [-Id ] [-Fields ] [-Web ] [-Connection ] Get-PnPListItem -List [-UniqueId ] [-Fields ] [-Web ]…docs.microsoft.com

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Import the Sharepoint Online module
Import-Module SharePointPnPPowerShellOnline

# Sharepoint website URL that will connect
# The URL can be something like https://example.sharepoint.com/sites/BI
$SiteURL = "Your-URL"

# List name that will get the list items
$ListName = "Your-List-Name"

# Email to connect to Sharepoint
$UserName = "Your-Email"

# Password from your email to connect to Sharepoint
$Password = "Your-Password"

# Connects to the Sharepoint service
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
$creds = (New-Object System.Management.Automation.PSCredential $UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

# Connect to the PNP module using the variables previously informed
Connect-PnPOnline -Url $SiteURL -Credentials $creds

# Variable to define the columns belonging to the list
$ListItems = Get-PnPListItem -List $ListName -Fields "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"

# Variable to save the final result
$results = @()

# Loop for each item, get the data from the column below
foreach($ListItem in $ListItems)
{ 

            $results += New-Object psobject -Property @{
                RenamedColumn1  = $ListItem["Column1"]
                RenamedColumn2  = $ListItem["Column2"]
                RenamedColumn3  = $ListItem["Column3"]
                RenamedColumn4  = $ListItem["Column4"]
                RenamedColumn5  = $ListItem["Column5"]
                RenamedColumn6  = $ListItem["Column6"]
                RenamedColumn7  = $ListItem["Column7"]
                RenamedColumn8  = $ListItem["Column8"]
            }
}

# Defines the directory and name of the file to be exported to the CSV file
$Dir = "YOUR_DIR\SHAREPOINTLIST_CSV.csv"

# Exports the result to the CSV file in the directory informed above
$results |
    Select-Object "RenamedColumn1", "RenamedColumn2", "RenamedColumn3", "RenamedColumn4", "RenamedColumn5", "RenamedColumn6", "RenamedColumn7", "RenamedColumn8" |
    Export-Csv -Path $Dir -NoTypeInformation -Encoding UTF8

# Disconnects from PnP module
Disconnect-PnPOnline

If your script is showing the error below, you must add the “PnP Management Shell” in the business applications on the Azure portal.

The user or administrator has not consented to use the application with ID "00000000-0000-0000-0000-000000000000"(the id code is just an example). Send an interactive authorization request for this user and resource.

If you want to add via hyperlink, the documentation is below too.

Configuring authenticationThe PnP Core SDK works with both SharePoint REST as Microsoft Graph in a transparent way, this also means that the…pnp.github.io

Or just replace contoso.onmicrosoft.com with your Azure AD tenant name, which typically is company.onmicrosoft.com.

https://login.microsoftonline.com/contoso.onmicrosoft.com/adminconsent?client_id=31359c7f-bd7e-475c-86db-fdb8c937548e&state=12345&redirect_uri=https://aka.ms/sppnp

Thas it! We can now obtain data from Sharepoint lists, through PowerShell using Cmdlets commands.

The script for this post can be found on my github.

guimatheus/Get-SharePoint-items-from-lists-by-using-PowerShellYou can’t perform that action at this time. You signed in with another tab or window. You signed out in another tab or…github.com

This blog is part of SharePoint Week. For more great content, click here

Check out Guilherme’s website here

About the Author:

Brazilian, engineer and developer well versed in BI, SQL, Data Modelling, Analysis Services, ETL Process, Python, VBA, Power BI, etc. Passionate about technology, who uses agile pratices and decision-making processes data-driven. You can find me in my LinkedIn profile, from my website and also from my Medium profile.