Connect to SharePoint Online List via Powershell

In certain scenarios, it becomes necessary to establish a connection between a PowerShell script and a database in order to retrieve data. While it is widely acknowledged that SharePoint is not a conventional database, it serves as a repository for a vast amount of information within an enterprise leveraging Microsoft 365.

In this particular scenario, we are utilizing PowerShell Version 5.XX as it is commonly the standard version in most cases. While PowerShell version 7.XX offers more options and can be easier to use in certain situations, it is not a standard installation.

Scenario

In our specific scenario, we encounter a situation where we are working with a dataset that includes various columns and rows of information. Within this dataset, we have a specific value, let’s call it „Potatoes,“ that holds significance to our task at hand. Our objective is to retrieve the corresponding value from the column labeled „Species“ within the dataset.

How to Connect to SharePoint Online

In the context of connecting to SharePoint, there exist several methods; however, in our specific scenario, the requirement to run the script in an automated manner presents a challenge when it comes to utilizing credentials due to the implementation of Multi-Factor Authentication (MFA).

In order to overcome the limitations posed by using credentials with Multi-Factor Authentication (MFA) in our scenario, we opted to utilize App Authentication as a solution to connect to SharePoint once, retrieve the list and close the connection
To connect to the App we decide to use a Certificate.
The following steps outline our approach and provide guidance on how you can replicate the process.

Register App

1. Visit the Azure portal at portal.azure.com and sign in using your Azure account credentials.
2. In the Azure AD management interface, select „App registrations“ .
3. Click on „New registration„: Click on the „New registration“ button to create a new app registration.
4. In the „Register an application“ page, enter a name for your app registration in the „Name“ field.
   Optionally, you can choose the supported account types for your application (single tenant, multi-tenant) and redirect URI settings based on your requirements.
5. Click on „Register„.
6. Configure required permissions: To grant your app registration the necessary permissions to access SharePoint resources, follow these steps:
   • In the „API permissions“ tab of your app registration, click on the „Add a permission“ button.
   • In the „Request API permissions“ window, select the „SharePoint“ option.
   • In the „Delegated permissions“ section, click on the „Application“ category.
   • Search for „Sites.ReadAll“ in the search bar and select the „Sites.ReadAll“ permission.
   • Click on the „Add permissions“ button to add the selected permission to your app registration.
7. After that, give grant admin consent to activate the API
8. Now Navigate to Certificates & Secrets
9. Select the Tab Certificate and Upload the Certificate you created.

PowerShell

Use the PnP PowerShell Module. If the PnP.PowerShell module is not already installed, you can install it by running the following command in PowerShell:

Install-Module -Name PnP.PowerShell 

If it is Installed we use the following code in the Beginning of the script

Import-Module -Name PnP.PowerShell

Obtain Client ID, Tenant ID, and Certificate Thumbprint: These values can be obtained from the Azure app registration. Here’s how you can find them:

Client ID: On the app registration overview page, locate the „Application (client) ID“ value and replace [Your Client ID] in the code with it.
Tenant ID: On the app registration overview page, locate the „Directory (tenant) ID“ value and replace [Your Tenant ID] in the code with it.

The Thumbprint can be read out of the Certificate Details -> Thumbprint or in the App Registration under Certificates

With those Details we can now define the values we need to Connect

$siteURL = "https://ENTERPRISENAME.sharepoint.com/sites/YOURSITE"
$clientID = "Your Client ID" 
$thumbprint = "Your Certificate Thumbprint" 
$tenantID = "Your Tenant ID"

To connect to the SharePoint we only need that code now:

$ListItems = Get-PnPListItem -List "YOURLISTNAME"

Work with the ListItems

Now to get any value we need we need to loop through the items.
I make one example for our scenario

foreach($ListItem in $ListItems){
    if($ListItem["Vegtable"" -eq "Potatos"){
        Write-Host $ListItem["Species"]
    }
}