Automating SharePoint List Creation and Data Population with PnP PowerShell

In this article, we will walk through a PowerShell script that connects to SharePoint Online using the PnP PowerShell module and automates the creation of SharePoint lists. The script dynamically creates lists for multiple countries, retrieves GDP data from the World Bank API for each country, and populates the SharePoint lists with this data. The solution is ideal for scenarios where you need to automate data collection and storage in SharePoint from an external source.

Key Components of the Script

1. Connecting to SharePoint Online (with MFA Support)

The script starts by connecting to SharePoint Online using the Connect-PnPOnline cmdlet. This cmdlet supports multi-factor authentication (MFA), allowing secure access to your SharePoint environment. The connection parameters, including the SharePoint site URL and Client ID, are passed via command-line arguments.

$url = $args[0]
$clientID = $args[1]
Connect-PnPOnline -Url $url -Interactive -ClientId $clientID

2. Countries and Codes Definition

Next, an array of country objects is defined, each containing the country’s name and ISO code. This list will be used to create individual SharePoint lists and fetch the GDP data from the World Bank API.

$countries = @(
    @{ Name = "Brazil"; Code = "BR" },
    @{ Name = "Argentina"; Code = "AR" },
    @{ Name = "United States"; Code = "US" },
    @{ Name = "China"; Code = "CN" },
    @{ Name = "Japan"; Code = "JP" },
    ...
)

3. Creating and Managing SharePoint Lists

For each country, the script checks if a SharePoint list for that country already exists. If it does, the existing list is deleted to ensure the new data is fresh. Then, a new list is created with custom fields to store the country code, GDP, and year.

$listName = "GDP Data - " + $country.Name

$list = Get-PnPList -Identity $listName -ErrorAction SilentlyContinue
if ($null -ne $list) {
    Remove-PnPList -Identity $listName -Force
}

New-PnPList -Title $listName -Template GenericList -OnQuickLaunch -EnableContentTypes

# Adding custom fields
Add-PnPField -List $listName -DisplayName "Country Code" -InternalName "CountryCode" -Type Text -AddToDefaultView
Add-PnPField -List $listName -DisplayName "GDP" -InternalName "GDP" -Type Number -AddToDefaultView
Add-PnPField -List $listName -DisplayName "Year" -InternalName "Year" -Type Number -AddToDefaultView

4. Fetching GDP Data from the World Bank API

The script makes API calls to the World Bank API using Invoke-RestMethod, retrieving GDP data for each country between the specified years (1989 to 2023). The data is parsed and stored in an array for later processing.

$apiUrl = "https://api.worldbank.org/v2/country/$cr/indicator/NY.GDP.MKTP.CD?date=$startYear:$endYear&format=json"
$response = Invoke-RestMethod -Uri $apiUrl

foreach ($item in $response[1]) {
    $gdpData += [PSCustomObject]@{
        Title       = $item.country.value
        CountryCode = $item.countryiso3code
        GDP         = $item.value
        Year        = $item.date
    }
}

5. Adding Data to SharePoint Lists

Finally, the script loops through the retrieved GDP data and adds each entry to the corresponding SharePoint list using the Add-PnPListItem cmdlet.

foreach ($gdpItem in $gdpData) {
    Add-PnPListItem -List $listName -Values @{
        Title       = $gdpItem.Title
        CountryCode = $gdpItem.CountryCode
        GDP         = $gdpItem.GDP
        Year        = $gdpItem.Year
    }
}

6. Output and Confirmation

The script provides feedback at various stages, confirming when a list is created, data is added, or a list is deleted. This ensures the user is aware of the script’s progress.

Write-Host "List '$listName' created successfully."
Write-Host "Data added successfully to the '$listName' list."

Conclusion

This script demonstrates how to automate the creation of SharePoint lists and populate them with external data using PnP PowerShell and REST APIs. It can be extended to retrieve additional data or work with more complex data structures as needed.

Summary of Commands

Documentation

For more detailed information on the PnP PowerShell module, please refer to the official documentation:
PnP PowerShell Documentation