Automate SharePoint List Retrieval with AWS Lambda and PnP PowerShell

Create a Lambda function to execute the PowerShell script using the following steps.

1. Launch PowerShell 7.
2. Change to your desired working directory.
3. Run the following command to create a sample script based on the Basic template.

   New-AWSPowerShellLambda `
       -ScriptName RetrieveSharePointListItem `
       -Template Basic

4. Select Create new IAM role and enter the role name.
5. Select AWSLambdaBasicExecutionRole policy.
   
6. Update the script in your working directory to include the SharePoint Online list item retrieval logic.

   # Requires AWS and PnP PowerShell modules
   #Requires -Modules @{ModuleName='AWS.Tools.Common';ModuleVersion='4.1.518'}
   #Requires -Modules @{ModuleName='AWS.Tools.SecretsManager';ModuleVersion='4.1.763'}
   #Requires -Modules @{ModuleName='PnP.PowerShell';ModuleVersion= '2.12.0' }
   
   # Accessing AWS Lambda environment variables
   $secretName = $env:secretName
   $siteURL = $env:siteURL
   $listName = $env:listName
   
   # Lambda Input: Get the request ID
   $input = ConvertTo-Json -InputObject $LambdaInput
   $requestId = $LambdaInput.requestId
   
   Write-Host "Starting process for RequestId: $requestId"
   
   try {
       # Retrieve the secret values from AWS Secrets Manager
       Write-Host "Retrieving secret values from AWS Secrets Manager..."
       $secretValues = Get-SECSecretValue -SecretId $secretName
   
       # Parse the SecretString from JSON format
       $secretJson = $secretValues.SecretString | ConvertFrom-Json
   
       # Retrieve the clientId, tenantName, and certificate details from the secret
       $clientId = $secretJson.clientId
       $tenantName = $secretJson.tenantName
       $base64Encoded = $secretJson.base64Encoded
       Write-Host "Secret values retrieved successfully."
   
       # Connect to SharePoint Online site using the retrieved credentials
       Write-Host "Connecting to SharePoint Online site: $siteURL"
       Connect-PnPOnline -Url $siteURL -ClientId $clientId -Tenant $tenantName -CertificateBase64Encoded $base64Encoded
   
       # Retrieve the list item based on the RequestId
       Write-Host "Querying SharePoint list for RequestId: $requestId"
       $listItem = Get-PnPListItem -List $listName -Query "<View><Query><Where><Eq><FieldRef Name='RequestId'/><Value Type='Text'>$requestId</Value></Eq></Where></Query></View>"
   
       # Check if the list item is found
       if ($listItem) {
           Write-Host "List item found, converting to JSON format..."
           # Convert the FieldValues to JSON format
           $fieldValuesJson = $listItem.FieldValues | ConvertTo-Json
           Write-Host "FieldValues JSON output: $fieldValuesJson"
           
           # Output the JSON
           return $fieldValuesJson
       } else {
           Write-Host "No list item found for RequestId: $requestId"
           return "No list item found"
       }
   } catch {
       # Exception Handling
       Write-Host "An error occurred: $_"
       return "Error: $($_.Exception.Message)"
   } finally {
       Write-Host "Process completed."
   }
   

7. Save the changes.
8. Run the following command to publish the script to Lambda.

   Publish-AWSPowerShellLambda `
       -ScriptPath .\RetrieveSharePointListItem.ps1 `
       -Name RetrieveSharePointListItem `
       -Region us-east-1
   

Add the environment variables

Add environment variables to your Lambda function using the following steps.

1. Sign in to the AWS Management Console.
2. Navigate to AWS Lambda and select your function GetSharePointListItem.
3. Choose the Configuration tab and select Environment variables.
4. Choose Edit, then Add environment variable to configure the following.

   listName - SharePoint list name
   secretName - Secret name copied in previous step
   siteURL - SharePoint Online site URL

5. Choose Save to apply your changes.
   

Update the Lambda execution role permissions

Update the Lambda execution role permissions to access Secrets Manager using the following steps.

1. Sign in to the AWS Management Console.
2. Navigate to AWS Lambda and select your function GetSharePointListItem.
3. Choose the Configuration tab and select Permissions.
   
4. Choose your execution role and add SecretsManagerReadWrite permissions to the role.
   

Note. The SecretsManagerReadWrite permission is recommended only for testing. For production environments, follow the principle of least privilege by granting only the minimum required permissions

Test the Lambda function

Invoke the Lambda function using the following steps.

1. Sign in to the AWS Management Console.
2. Navigate to AWS Lambda and select your function GetSharePointListItem.
3. Choose the Test tab and enter the following JSON.

   {
     "requestId": "REQ21022025001"
   }

4. Choose Test to invoke the function.
   
5. View the execution results.
   

References

https://docs.aws.amazon.com/lambda/latest/dg/lambda-powershell.html

Summary

This article describes how to build a serverless solution using AWS Lambda and PnP PowerShell to automate SharePoint list item retrieval.