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 Overview

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)

# 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 authentication

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-PowerShell