Article: Creating a SharePoint Online List and Loading World Bank GDP Data

Introduction

The World Bank API offers a wealth of global data, including key economic indicators like Gross Domestic Product (GDP), which tracks the economic performance of countries over time. Accessing this data programmatically can provide valuable insights for analysis or integration into various platforms. In this tutorial, we will demonstrate how to connect to SharePoint Online, create a custom list, and load GDP data from the 20 largest economies, plus Brazil and Argentina, directly into SharePoint.

Step 1: Connect to SharePoint Online and Create the List

We begin by connecting to SharePoint Online and creating a custom list where we will store the GDP data. This list will contain the following columns:

Title: The name of the country.
Country Code: The ISO code for the country.
GDP: The GDP value for the given year.
Year: The year for which the GDP data is recorded.

Script to Create the List

# Connect to SharePoint Online (with MFA)
$url=$args[0]
$clientID=$args[1]
Connect-PnPOnline -Url $url -Interactive -ClientId $clientID

# Name of the SharePoint list
$listName = "GDP Data"

# Check if the list already exists
$list = Get-PnPList -Identity $listName -ErrorAction SilentlyContinue
if ($null -ne $list) {
    # Delete the existing list
    Remove-PnPList -Identity $listName
    Write-Host "List '$listName' deleted successfully."
}

# Create a new list
New-PnPList -Title $listName -Template GenericList
Write-Host "List '$listName' created successfully."

# Add columns to the list
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 Text -AddToDefaultView

Write-Host "Columns added successfully to the GDP Data list."

This script will connect to SharePoint using MFA, check if the “GDP Data” list already exists, delete it if necessary, and then create a new list with the required columns.

Step 2: Fetching GDP Data from the World Bank API

In this step, we use the World Bank API to fetch GDP data for Brazil, Argentina, and the 20 largest economies for the years 1983 to 2023. The API provides data in JSON format, which we can process and prepare for loading into SharePoint.

Script to Fetch GDP Data

# Define countries and their ISO codes
$countries = @(
    @{ Name = "Brazil"; Code = "BR" },
    @{ Name = "Argentina"; Code = "AR" },
    @{ Name = "United States"; Code = "US" },
    @{ Name = "China"; Code = "CN" },
    @{ Name = "Japan"; Code = "JP" },
    @{ Name = "Germany"; Code = "DE" },
    @{ Name = "India"; Code = "IN" },
    @{ Name = "United Kingdom"; Code = "GB" },
    @{ Name = "France"; Code = "FR" },
    @{ Name = "Italy"; Code = "IT" },
    @{ Name = "Canada"; Code = "CA" },
    @{ Name = "South Korea"; Code = "KR" },
    @{ Name = "Australia"; Code = "AU" },
    @{ Name = "Spain"; Code = "ES" },
    @{ Name = "Mexico"; Code = "MX" },
    @{ Name = "Indonesia"; Code = "ID" },
    @{ Name = "Netherlands"; Code = "NL" },
    @{ Name = "Saudi Arabia"; Code = "SA" },
    @{ Name = "Turkey"; Code = "TR" },
    @{ Name = "Taiwan"; Code = "TW" },
    @{ Name = "Switzerland"; Code = "CH" }
)

# Fetch GDP data for years of interest
$startYear = 1983
$endYear = 2023
$gdpData = @()

foreach ($country in $countries) {
    $cr = $country.Code
    $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]) {
        $Title = $item.country.value
        $CountryCode = $item.countryiso3code
        $GDP = $item.value
        $Year = $item.date

        $gdpData += [PSCustomObject]@{
            Title       = $Title
            CountryCode = $CountryCode
            GDP         = $GDP
            Year        = $Year
        }
    }
}

This script loops through the list of countries, sends a request to the World Bank API, and collects the GDP data for each year within the range 1983 to 2023.

Step 3: Loading the Data into SharePoint

Once we have the GDP data, we proceed by adding it to the SharePoint list created in Step 1.

Script to Load Data into SharePoint

# Add the GDP data to the SharePoint list
foreach ($gdpItem in $gdpData) {
    Add-PnPListItem -List $listName -Values @{
        Title       = $gdpItem.Title
        CountryCode = $gdpItem.CountryCode
        GDP         = $gdpItem.GDP
        Year        = $gdpItem.Year
    }
}

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

This script iterates over the data collected from the World Bank API and adds each entry as a list item in the SharePoint “GDP Data” list.

Conclusion

In this tutorial, we demonstrated how to integrate the World Bank’s GDP data into SharePoint Online. We connected to SharePoint, created a list, fetched data from the World Bank API, and loaded it into our custom SharePoint list.

For more information on using the PnP PowerShell module for SharePoint, refer to the official documentation at PnP PowerShell.

Summary of Commands

Connect-PnPOnline – Connects to SharePoint Online.
Get-PnPList – Retrieves the list from SharePoint.
Remove-PnPList – Deletes a SharePoint list.
New-PnPList – Creates a new SharePoint list.
Add-PnPField – Adds a column to the SharePoint list.
Invoke-RestMethod – Sends an API request to fetch GDP data.
Add-PnPListItem – Adds items to the SharePoint list.

edvaldo b. guimarães filho

Article: Creating a SharePoint Online List and Loading World Bank GDP Data