Automating SharePoint Lists for Global GDP Data: Power BI & Power Apps Integration

In this article, we’ll walk through a PowerShell script that automates the creation of SharePoint lists populated with GDP (Gross Domestic Product) data for various countries, sourced directly from the World Bank API. This solution is ideal for integrating real-time economic data into Power BI dashboards or Power Apps applications, offering seamless data management and visualization capabilities.

Why Automate GDP Data with SharePoint?

By automating the collection and storage of GDP data in SharePoint lists, you remove the need for manual data updates and ensure consistency across your data sources. This approach simplifies the process of keeping economic data current and available for business intelligence (BI) tools like Power BI and app-building platforms like Power Apps.

With Power BI, you can visualize trends over time for different countries, while Power Apps can be used to build custom interfaces that interact with this data. This end-to-end solution enhances productivity by leveraging SharePoint as a central hub for data storage.

The PowerShell Script: Step by Step

The following script automates the creation of SharePoint lists for each country, fetches the GDP data from the World Bank API, and populates it into the corresponding lists. Once the lists are populated, they can be easily connected to Power BI for data visualization or Power Apps for building interactive applications.

1. Connecting to SharePoint Online

We start by connecting to SharePoint Online using Connect-PnPOnline. This command establishes an authenticated connection to your SharePoint site using Multi-Factor Authentication (MFA).

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

2. Defining Countries and GDP Data

Next, we define a list of countries and their respective ISO codes. These country codes will be used to query the World Bank API for GDP data.

# Define countries and their ISO codes
$countries = @(
    @{ Name = "Brazil"; Code = "BR" },
    @{ Name = "United States"; Code = "US" },
    @{ Name = "Germany"; Code = "DE" },
    # Add more countries here...
)

3. Fetching and Storing GDP Data

The script loops through each country, querying the World Bank API to retrieve GDP data for a specified date range (1989-2023). For each country, it creates a new SharePoint list to store the data. If the list already exists, it deletes and recreates it to ensure it contains fresh data.

# Loop through countries and fetch GDP data
foreach ($country in $countries) {
    $listName = "GDP Data - " + $country.Name
    $cr = $country.Code
    $apiUrl = "https://api.worldbank.org/v2/country/$cr/indicator/NY.GDP.MKTP.CD?date=1989:2023&format=json"
    $response = Invoke-RestMethod -Uri $apiUrl

    # Create or replace SharePoint list
    Remove-PnPList -Identity $listName -Force -ErrorAction SilentlyContinue
    New-PnPList -Title $listName -Template GenericList -OnQuickLaunch -EnableContentTypes

    # Add fields to SharePoint 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 Number -AddToDefaultView

    # Add GDP data to the list
    foreach ($item in $response[1]) {
        Add-PnPListItem -List $listName -Values @{
            Title       = $item.country.value
            CountryCode = $item.countryiso3code
            GDP         = $item.value
            Year        = $item.date
        }
    }
}

4. Integrating with Power BI and Power Apps

Once the GDP data is stored in SharePoint lists, it becomes readily available for integration with Power BI or Power Apps. In Power BI, you can create dashboards that visualize GDP growth over time for each country. With Power Apps, you can build interactive apps to manage, filter, or display this data in custom layouts.

By connecting your SharePoint lists to Power BI or Power Apps, you can take advantage of features like automated refreshes, enabling real-time updates of your dashboards or apps whenever new data is fetched.

World Bank API Documentation

For those interested in learning more about how the World Bank API works and how to query it, the full documentation is available at:

World Bank API Documentation

This API offers various endpoints for accessing global development data, including indicators like GDP, population, education, and more.

Original PowerShell Script

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

# Definir os países e suas siglas
$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" }
)

# Obter dados do PIB para os anos de interesse
$startYear = 1989
$endYear = 2023
$gdpData = @()

foreach ($country in $countries) {

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

    # Criar a nova lista
    $list = Get-PnPList -Identity $listName -ErrorAction SilentlyContinue
    if ($null -ne $list) {
        # Apagar a lista existente
        Remove-PnPList -Identity $listName -Force
        Write-Host "List '$listName' deleted successfully."
    }

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

    # Adicionando campos à lista
    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

    # Mensagem de sucesso
    Write-Host "Lista '$listName' criada com sucesso no site '$Url'."

    $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
        }
    }

    # Adicionar os dados à lista do SharePoint
    foreach ($gdpItem in $gdpData) {
        Add-PnPListItem -List $listName -Values @{
            Title         = $gdpItem.Title
            CountryCode   = $gdpItem.CountryCode
            GDP           = $gdpItem.GDP
            Year          = $gdpItem.Year
        }
    }

    $gdpData = @()
    Write-Host "Data added successfully to the '$listName' list."
}

edvaldo b. guimarães filho

Automating SharePoint Lists for Global GDP Data: Power BI & Power Apps Integration