Category: Lists

How to create SharePoint Lists with MS Graph

In this post I want to showcase how to create a SharePoint Library and SharePoint List using MS Graph. You might need to create a SharePoint List with MS Graph if you have scenarios, where you need to ensure that your SharePoint Site is following a strict guideline in terms of predefined lists and libraries.

I am currently crafting an asset and whenever we deploy this asset to new customers, we need to make sure that certain lists and libraries are also created as part of the asset. I can imagine you were facing dozens of similar requirements.

The approach is quite simple:

Create an App registration with Sites.FullControll.All Permissions to manage permissions of sites – I’ll call this app registration the TenantAdminApp – unfortunately the sites.selected permission is as of 16. November 2024 not sufficient. You’ll get an access denied response with sites.selcted.
Obtain a Token with the TenantAdminApp App
Create the SharePoint Lists using MS Graph with the TenantAdminApp App

I’ll delve into each of the steps, so that you can create the same on your end and have it up and running.

The benefit using MS Graph is ultimately, that you don’t need PowerShell at all to do so. So if you want to do it with Logic Apps or PowerAutomate, Postman you name it, you could do it.

How to create the App registrations to create SharePoint Lists using MS Graph (TenantAdminApp)

Make sure that you create the App registration as per this article, but just use the Site.Fullcontroll.All Application Permission:

How to configure Azure App registration for MS Graph

The TenantAdminApp should look like this:

How to create the SharePoint List using MS Graph with PowerShell

Now as our TenantAdminApp has the necessary permissions, we can create SharePoint Lists with MS Graph using PowerShell:

Make sure to change the parameters to cover your use case.

# Define parameters for the tenant, application ID, site name, and mail nickname
Param (
    # The API version of MS Graph
    $ApiVersion = "v1.0",

    # The Azure AD tenant ID for authentication
    $Tenant = "m365x16735261", 
    # The Application (client) ID of the registered Entra ID application registration, which is supposed to create SharePoint List (TenantAdminApp)
    $TenantAdminAppID = "39180af4-ad90-442f-a4ca-028b9221b133",   

    # hostname of the tenant
    $HostName = "m365x16735261.sharepoint.com",
    # The relative path of the site, beginning with /sites/, /teams/, or another valid site type path.
    $ServerRelativePath = "sites/GraphSite1",
    
    # The displayname of the List
    $ListDisplayName = "Tools2"
)
function Get-GraphToken {
    param (
        [string]$Tenant,
        [PSCredential]$AppCredential
    )
    
    # Define the required scope for accessing the Microsoft Graph API
    $Scope = "https://graph.microsoft.com/.default"

    # Prepare the body for the token request with client ID, client secret, and scope
    $Body = @{
        client_id = $AppCredential.UserName                     # Client ID from app registration
        client_secret = $AppCredential.GetNetworkCredential().password   # Securely retrieve the client secret
        scope = $Scope                                         # API scope required for MS Graph API access
        grant_type = 'client_credentials'                      # Grant type for app-only authentication
    }

    # Azure AD endpoint to request an OAuth token
    $GraphUrl = "https://login.microsoftonline.com/$($Tenant).onmicrosoft.com/oauth2/v2.0/token"

    try {
        # Request the access token from Azure AD
        $AuthorizationRequest = Invoke-RestMethod -Uri $GraphUrl -Method "Post" -Body $Body
        # Retrieve the access token from the response
        return $AuthorizationRequest.Access_token
    } catch {
        # Handle authentication errors, if any
        Write-Error "Error during token retrieval: $_"
        return $null
    }
}

#region authentication - obtain bearer token
while ($null -eq $TenantAdminAppCredential)
{
    $TenantAdminAppCredential = Get-Credential -UserName $TenantAdminAppID -Message "Enter the client secret for the app TenantAdminAppID $TenantAdminAppID"
}

# Call the Get-GraphToken function and store the result
$Access_token = Get-GraphToken -Tenant $Tenant -AppCredential $TenantAdminAppCredential

# Check if access token is successfully obtained
if (-not $Access_token) 
{
    Write-Error "Failed to retrieve access token. Exiting script."
    return
}

# Set the Authorization header with the bearer token for API requests
$Header = @{
    Authorization  = "Bearer $Access_token"              # Bearer token for API authorization
    "Content-Type" = "application/json"                 # JSON content type for the request body
}
Write-Output "Access token retrieved successfully."
#endregion

#Obtain the SiteID of the Site

$Url = "https://graph.microsoft.com/$ApiVersion/sites/${HostName}:/$ServerRelativePath"
$Site = Invoke-RestMethod -Uri $Url -Headers $Header

#region create site
# Define the list properties for the new list creation request
$Body = @{
    displayName = $ListDisplayName
    list        = @{
        template = "genericList"
    }
}

# Set the endpoint URL for creating a list in Microsoft Graph API
$Url = "https://graph.microsoft.com/$ApiVersion/sites/$(($site.id -split ",")[1])/lists"

# Convert the list properties to JSON format required for the API request
$BodyJSON = $Body | ConvertTo-Json -Compress
 
try
{
    # Send the HTTP POST request to create the list with the defined properties
    $response = Invoke-RestMethod -Uri $Url -Headers $Header -Method Post -Body $BodyJSON
    # Output the ID of the newly created list for confirmation
    Write-Output "List $ListDisplayName created successfully"
    Write-Output $response
}
catch
{
    # Handle errors that may occur during the list creation request
    Write-Error "Failed to create list: $_"
}
#endregion

After running the script, following response is provided:

Access token retrieved successfully.
List Tools2 created successfully

@odata.context       : https://graph.microsoft.com/v1.0/$metadata#sites('e5320bcc-9e60-49b4-8ba4-2b63412c4e5c')/lists/$entity
@odata.etag          : 62e80bab-b52a-44c0-a669-6a20de47f86c,0
createdDateTime      : 16.11.2024 13:47:42
description          : 
eTag                 : 62e80bab-b52a-44c0-a669-6a20de47f86c,0
id                   : 62e80bab-b52a-44c0-a669-6a20de47f86c
lastModifiedDateTime : 16.11.2024 13:47:42
name                 : Tools2
webUrl               : https://m365x16735261.sharepoint.com/sites/GraphSite1/Lists/Tools2
displayName          : Tools2
parentReference      : @{siteId=m365x16735261.sharepoint.com,e5320bcc-9e60-49b4-8ba4-2b63412c4e5c,313e2cc9-70da-4859-8660-9ca104c56d1a}
list                 : @{contentTypesEnabled=False; hidden=False; template=genericList}

As we can see, we could create SharePoint List “Tools2” with MS Graph.

How to create the SharePoint Libraries with MS Graph using PowerShell

To create SharePoint Libraries with MSGraph using PowerShell, we just need to apply a different template, namely DocumentLibary:

# Define parameters for the tenant, application ID, site name, and mail nickname
Param (
    # The API version of MS Graph
    $ApiVersion = "v1.0",

    # The Azure AD tenant ID for authentication
    $Tenant = "m365x16735261", 
    # The Application (client) ID of the registered Entra ID application registration, which is supposed to create SharePoint List (TenantAdminApp)
    $TenantAdminAppID = "39180af4-ad90-442f-a4ca-028b9221b133",   

    # hostname of the tenant
    $HostName = "m365x16735261.sharepoint.com",
    # The relative path of the site, beginning with /sites/, /teams/, or another valid site type path.
    $ServerRelativePath = "sites/GraphSite1",
    
    # The displayname of the List
    $ListDisplayName = "Solutions",
    # The template, based on which the list/library must be created - for list it is genericList, for a library it is DocumentLibrary. Find here all templates: https://learn.microsoft.com/en-us/previous-versions/office/sharepoint-server/ms413878(v=office.15)?redirectedfrom=MSDN#members
    $ListTemplate = "DocumentLibrary"
)
function Get-GraphToken {
    param (
        [string]$Tenant,
        [PSCredential]$AppCredential
    )
    
    # Define the required scope for accessing the Microsoft Graph API
    $Scope = "https://graph.microsoft.com/.default"

    # Prepare the body for the token request with client ID, client secret, and scope
    $Body = @{
        client_id = $AppCredential.UserName                     # Client ID from app registration
        client_secret = $AppCredential.GetNetworkCredential().password   # Securely retrieve the client secret
        scope = $Scope                                         # API scope required for MS Graph API access
        grant_type = 'client_credentials'                      # Grant type for app-only authentication
    }

    # Azure AD endpoint to request an OAuth token
    $GraphUrl = "https://login.microsoftonline.com/$($Tenant).onmicrosoft.com/oauth2/v2.0/token"

    try {
        # Request the access token from Azure AD
        $AuthorizationRequest = Invoke-RestMethod -Uri $GraphUrl -Method "Post" -Body $Body
        # Retrieve the access token from the response
        return $AuthorizationRequest.Access_token
    } catch {
        # Handle authentication errors, if any
        Write-Error "Error during token retrieval: $_"
        return $null
    }
}

#region authentication - obtain bearer token
while ($null -eq $TenantAdminAppCredential)
{
    $TenantAdminAppCredential = Get-Credential -UserName $TenantAdminAppID -Message "Enter the client secret for the app TenantAdminAppID $TenantAdminAppID"
}

# Call the Get-GraphToken function and store the result
$Access_token = Get-GraphToken -Tenant $Tenant -AppCredential $TenantAdminAppCredential

# Check if access token is successfully obtained
if (-not $Access_token) 
{
    Write-Error "Failed to retrieve access token. Exiting script."
    return
}

# Set the Authorization header with the bearer token for API requests
$Header = @{
    Authorization  = "Bearer $Access_token"              # Bearer token for API authorization
    "Content-Type" = "application/json"                 # JSON content type for the request body
}
Write-Output "Access token retrieved successfully."
#endregion

#Obtain the SiteID of the Site

$Url = "https://graph.microsoft.com/$ApiVersion/sites/${HostName}:/$ServerRelativePath"
$Site = Invoke-RestMethod -Uri $Url -Headers $Header

#region create list

# Define the list properties for the new list creation request
$Body = @{
    displayName = $ListDisplayName
    list        = @{
        template = $ListTemplate
    }
}

# Set the endpoint URL for creating a list in Microsoft Graph API
$Url = "https://graph.microsoft.com/$ApiVersion/sites/$(($site.id -split ",")[1])/lists"

# Convert the list properties to JSON format required for the API request
$BodyJSON = $Body | ConvertTo-Json -Compress -Depth 3
 
try
{
    # Send the HTTP POST request to create the list with the defined properties
    $response = Invoke-RestMethod -Uri $Url -Headers $Header -Method Post -Body $BodyJSON
    # Output the ID of the newly created list for confirmation
    Write-Output "List $ListDisplayName created successfully"
    Write-Output $response
}
catch
{
    # Handle errors that may occur during the list creation request
    Write-Error "Failed to create list: $_"
}
#endregion

As you can see, the SharePoint Library Solutions was created with MSGraph:

How to add columns, when creating SharePoint Lists/ Libraries?

The approach, which was showcased before will create simple lists and libraries with default columns. The approach can be extended further by adding columns to the SharePoint Lists/ Libraries. This can be done by extending the JSON body of the POST /Lists call.

Add a Single Line of Text column to the SharePoint List

Here you can find an example to add a single line of text column to the SharePoint list. You need to create a hash table, which elaborates about your columns. I added the Comment column to the SharePoint list.

$Body = @{
    displayName = $ListDisplayName
    columns = @(
		@{
			name = "Comment"
            text = @{
                allowMultipleLines = $false
                appendChangesToExistingText = $false
                linesForEditing = 0
                maxLength = 255
            }
		}
	)
    list        = @{
        template = $ListTemplate
    }
}

Add a Integer Column to the SharePoint List

Below you can find an example, which shows how to add a integer column to a SharePoint List/Library:

$Body = @{
    displayName = $ListDisplayName
    columns = @(
		@{
			name = "Number of Entities"
			number = @{
                decimalPlaces= "none"
                displayAs = "number"
              }
		}
	)
    list        = @{
        template = $ListTemplate
    }
}

References

Here you can find the definition of the column resource type, which describes which properties need to be considered, when adding a column:
columnDefinition resource type – Microsoft Graph v1.0 | Microsoft Learn

You could might also want to create SharePoint Sites using MS Graph:
How to create SharePoint Sites using Graph | SPO Scripts

November 16, 2024

How to get all lists and libraries in SharePoint Online
Hey folks, at some time we come to a point, where we want to know, what is going on in our intranet. Either if we plan to migrate the content to another tenant or to clean up our intranet. I wrote a step-by-step instruction for you to find get all lists and libraries in your SharePoint environment. At the end of this blog post, you’ll find also the ready-to-use script.

Since the official module of Microsoft does not include a cmdlet for lists, I will use the PNP.PowerShell module.

Prerequisites

Before we start with coding, we should be aware, that there are some prerequisites, we need to fulfil in order to get all lists and libraries in our farm.
- Access to your tenant from your client
- Installed PNP PowerShell Module. For more read: Connect to SharePoint with PowerShell | SharePoint Online (workplace-automation.com/)
- Installed Microsoft.Online.SharePoint.PowerShell Module. For more read: SharePointOnlinePowerShell: How to connect to SharePoint (workplace-automation.com/)
- Service user with the role SharePoint administrator
Step-by-Step Instruction to get all lists and libraries

Add your service account as admin to all sites

You might ask yourself, why is it necessary to add a service account to all sites. In SharePoint Server it works as soon as you have access to the SharePoint Shell. You need it because the cloud model does not provide access to all sites just by having the SharePoint admin role. You have to enable your service account with the rights to access the sites, prior accessing them.

Even if you have connected to the admin site, you will notice, that it does not work:

To add the service account to all sites, you have to make use of the module Microsoft.Online.SharePoint.PowerShell. If you are not familiar with this, check out SharePointOnlinePowerShell: How to connect to SharePoint (workplace-automation.com/).

Connecting to the environment, can be achieved with this cmdlets:.
```
$AdminUrl = "https://m365x388358-admin.sharepoint.com/" 
$Credential = Get-Credential


Import-Module -Name Microsoft.Online.SharePoint.PowerShell
Connect-SPOService -Url $AdminUrl -Credential $Credential
```
You’ll get a prompt for credentials. Provide the service account with SharePoint admin role here. We will reuse it later for the PNP module.

So after connecting, we can add our service user as site collection administrator with this script. Keep in mind to change the user variable to your service account’s user principal name.
```
$User = "admin@M365x388358.onmicrosoft.com"

$SPOSites = Get-SPOSite 
foreach ($SPOSite in $SPOSites)
{
    Set-SPOUser -Site $SPOSite.Url -LoginName $User -IsSiteCollectionAdmin $true
}

Disconnect-SPOService
```
This is how the output looks for me:

Since our service user has access to all sites, we can now proceed with our analysis.

Get all lists and libraries with PowerShell

For the purpose of an interactive analysis of all lists and libraries, it is sufficient to connect interactively to the tenant with this script:
```
$AdminUrl = "https://m365x388358-admin.sharepoint.com/" 
$Credential = Get-Credential

Import-Module PNP.PowerShell
Connect-PnPOnline -Url $AdminUrl -Credentials $Credential
```
You have to replace the URL with your SharePoint admins URL.

If you run the cmdlet, credentials will be prompted. Please use a user account, which has the SharePoint administrator role granted. If you don’t know how to grant it, check out the official Microsoft references, they explain it with a video, which will help you.

After you have provided the credentials, you are connected. You can test it by querying all sites.
```
Get-PnPTenantSite
```
As you can see, I have a bunch of sites, which we will analyze further on.
```
$Export = New-Object System.Collections.Generic.List[object]
$Sites = Get-PnPTenantSite

$SitesCount = $Sites.Count
$i= 1


foreach ($Site in $Sites)
{
    Write-Host "($i / $SitesCount) Processing site $($Site.Url)"
    Disconnect-PnPOnline
    Connect-PnPOnline -Url $Site.Url -Credentials $Credential
    $Site = Get-PnPSite
    
    #get the information of the list
    Get-PnPList | ForEach-Object { 

        $NewExport = New-Object PSObject -Property @{
            Title = $_.Title
            Id = $_.ID
            DefaultViewUrl =   $_.DefaultViewUrl
            ItemCount = $_.ItemCount
            ParentWebUrl = $_.ParentWebUrl
        }
        $Export.Add($NewExport)
    }
    
    $i++
}
```
You can export the information like this:
```
$Export | Export-Csv -Path "C:\Users\Serkar\Desktop\lists.csv" -Delimiter ";" -NoTypeInformation
```
Based on your location, you have to change your delimiter to comma instead of semicolon.

The result of our scripting is, that we now have the possiblity to see all lists and libraries and also to identify lists and libraries with huge amount of data in it. Since it is a CSV file, you can open it with Excel to analyze the data:

You can group all data with a pivot table, to see all lists to the corresponding web.

Bonus: Ready-to-use Script
```
#Provided by workplace-automation.com/

$User = "admin@M365x388358.onmicrosoft.com"
$AdminUrl = "https://m365x388358-admin.sharepoint.com/"
$ExportPath = "C:\Users\Serkar\Desktop\lists.csv"

$Credential = Get-Credential

#region Set admin permissions

Import-Module -Name Microsoft.Online.SharePoint.PowerShell
Connect-SPOService -Url $AdminUrl -Credential $Credential

$SPOSites = Get-SPOSite 
foreach ($SPOSite in $SPOSites)
{
    Set-SPOUser -Site $SPOSite.Url -LoginName $User -IsSiteCollectionAdmin $true
}

Disconnect-SPOService

#endregion

#region get all lists and libraries
Import-Module PNP.PowerShell
Connect-PnPOnline -Url $AdminUrl -Credentials $Credential

$Export = New-Object System.Collections.Generic.List[object]
$Sites = Get-PnPTenantSite

$SitesCount = $Sites.Count
$i= 1


foreach ($Site in $Sites)
{
    Write-Host "($i / $SitesCount) Processing site $($Site.Url)"
    Disconnect-PnPOnline
    Connect-PnPOnline -Url $Site.Url -Credentials $Credential
    $Site = Get-PnPSite
    
    #get the information of the list
    Get-PnPList | ForEach-Object { 

        $NewExport = New-Object PSObject -Property @{
            Title = $_.Title
            Id = $_.ID
            DefaultViewUrl =   $_.DefaultViewUrl
            ItemCount = $_.ItemCount
            ParentWebUrl = $_.ParentWebUrl
        }
        $Export.Add($NewExport)
    }
    
    $i++
}
#endregion

$Export | Export-Csv -Path $ExportPath -Delimiter ";" -NoTypeInformation 
```
January 11, 2022