SharePoint List Updates with PowerShell and Microsoft Graph

In this blog post, we'll explore how to automate the process of updating a SharePoint list based on information obtained from the Microsoft Graph API. Specifically, we'll focus on dynamically changing the email address in the API URL using PowerShell.

In this blog post, we'll explore how to automate the process of updating a SharePoint list based on information obtained from the Microsoft Graph API. Specifically, we'll focus on dynamically changing the email address in the API URL using PowerShell.

Prerequisites

Before we dive in, make sure you have the following:

PnP PowerShell module installed

Install-Module -Name PnP.PowerShell -Force -AllowClobber

Connecting to SharePoint

Let's start by connecting to your SharePoint site.

$siteUrl = "https://your-sharepoint-site-url"
$username = "your-username"
$password = "your-password"

$securePassword = ConvertTo-SecureString $password -AsPlainText -Force
Connect-PnPOnline -Url $siteUrl -Credentials (New-Object System.Management.Automation.PSCredential ($username, $securePassword))

Replace placeholders with your actual SharePoint site URL, username, and password.

Retrieving Items from SharePoint List

Specify the list information and retrieve items.

$listName = "YourListName"
$emailColumnName = "EmailColumn"
$updatedEmailColumnName = "UpdatedEmailColumn"
$statusColumnName = "StatusColumn"

$items = Get-PnPListItem -List $listName

Dynamic Email Address in Microsoft Graph API URL

Iterate through SharePoint list items, retrieve email addresses, and call Microsoft Graph API.

foreach ($item in $items) {
    $email = $item[$emailColumnName]

    $apiUrl = "https://graph.microsoft.com/beta/users?$filter=proxyAddresses/any(x:x eq 'smtp:$email')&$select=onPremisesSamAccountName,accountEnabled,mail"
    $apiResponse = Invoke-RestMethod -Uri $apiUrl -Method Get -Headers @{ Authorization = "Bearer YOUR_ACCESS_TOKEN" }

    $onPremisesSamAccountName = $apiResponse.value[0].onPremisesSamAccountName
    $accountEnabled = $apiResponse.value[0].accountEnabled
    $mail = $apiResponse.value[0].mail

    Set-PnPListItem -List $listName -Identity $item.Id -Values @{
        $updatedEmailColumnName = $mail
        $statusColumnName = $accountEnabled
    }
}

Replace YOUR_ACCESS_TOKEN with the actual access token.

Wrapping Up

This script automates the process of updating a SharePoint list based on information retrieved from the Microsoft Graph API. It dynamically changes the email address in the API URL for each item in the SharePoint list, making it a powerful tool for keeping your SharePoint data up-to-date.

Remember to disconnect from your SharePoint site after the script execution.

Disconnect-PnPOnline

Feel free to customize the script based on your specific list structure and requirements. Happy automating!