Practical Graph: Working with Microsoft Lists using the Microsoft Graph PowerShell SDK

Create, Read, Update, and Delete Items in Microsoft Lists with PowerShell

Microsoft Lists is a popular application for organizing personal and shared data in tables. Personal lists are stored in OneDrive for Business while shared lists are held in a SharePoint Online site. Many PowerShell scripts depend on inputs like a CSV file. A major advantage of using lists rather than CSV files is that it’s easier for users to maintain data using the GUI elements available to format and present data in lists, which is one reason why I chose to use a list to hold details of bad domains and spammers to drive automated updates of my tenant’s block list for email. Another advantage is that lists support Microsoft 365 features like retention policies.

On the other hand, CSV files are very easy to deal with in PowerShell and developers are very accustomed to importing data from a CSV into an array, processing the data, and exporting the results back to a CSV file. Familiarity is a difficult hurdle to overcome. This article attempts to expand the community’s knowledge about using lists through the Microsoft Graph PowerShell SDK. After that, it’s up to you to decide whether to stay with CSV files or move to lists

Defining Microsoft Lists Terminology

To begin, let’s define some terms:

• A list is an object in a SharePoint Online site. Document libraries are a specialized form of a list. In this context, we mean a list used to store non-document data.
• A list is composed of a collection of items.
• An item has a set of columns (fields) used to store data. Some fields are added automatically to a list because of the template used to create the list. Others are defined by the list owner.
• In addition to fields, each item has a set of properties.
• SharePoint Online comes with a standard set of templates used to create lists. If a template is not specified, SharePoint Online uses the genericList template to create a list with a single Title column. Once a list is created, its template cannot be changed.
• Columns hold different types of information such as Boolean, dateTime, person, text, number, personOrGroup, and currency.

My usual approach is to create new lists through the GUI to take advantage of the capabilities to enter and present data in lists built into SharePoint Online. It’s always easier to use the GUI than to create code to create a bespoke list. If I create a list programmatically, I invariably use the simplest list template (genericList) and customize it.

Creating a Microsoft List

As my example, I go back to a topic covered in 2021 in a discussion about creating organizational contacts in the mailboxes of new accounts. The idea is that new people who join an organization probably don’t know how to contact units like HR and the help desk, so after they join, a background job creates contacts for important units and people in their mailboxes. The original idea uses an Exchange Online custom attribute to mark organizational contacts. This iteration uses a SharePoint list.

The first thing is to make sure to connect to the Graph with the correct permission. Two things are necessary. First, the signed-in account must be an owner of the site that hosts the list. Second, the connection must have consent to use the Sites.Manage.All and Sites.ReadWrite.All scopes:

Connect-MgGraph -NoWelcome -Scopes Sites.Manage.All

We can now run the Get-MgSite cmdlet to retrieve the details of the target site. The id (identifier) is the most important piece of information because this is passed to other cmdlets to create the list, fetch items, and so on:

$Site = Get-MgSite -Search 'Office 365 News and Views'
$Site | Format-Table DisplayName, Name, Id

DisplayName               Name          Id
-----------               ----          --
Office 365 News and Views Office365News office365itpros.sharepoint.com,bf887032-2619-44a2-a8b0-2b9cfb1cf701,e95c0fd7-42a6-4bd8-808f-e4683035ed6e

Several sites might match the search criteria so some additional filtering might be necessary to find the right target site.

With the correct site identified, the next step is to create a list. The New-MgSiteList cmdlet creates a list using a body parameter structure containing the definitions of columns to add to the list and the template to use. The Contacts template is an appropriate choice. As expected, the template contains many of the columns that you’d want to use to capture information about a contact. All we need to add are custom fields that don’t exist in the template.

$NewListParameters = @{
	displayName = "Organizational Contacts"
    description = "Contacts to add to User Address Book"
	columns = @(
		@{
		 name = "Building"
            	 description = "Contact's building"
		 text = @{ texttype = "plain | richtext" }
        }
		@{
		 name = "BusinessUnit"
            	 description = "Contact's business unit"
		 text = @{ texttype = "plain | richtext" }
		}
        	@{
		 name = "Description"
            	 description = "Brief description of the contact"
		 text = @{ texttype = "plain | richtext" }
		}
	)
	list = @{
		template = "contacts"
	}
}

The syntax used for the example in Microsoft’s documentation doesn’t work because it doesn’t include a column type definition for each field (like text = @{ }). When definitions are omitted, the New-MgSiteList cmdlet fails with:

New-MgSiteList -SiteId $Site.Id -BodyParameter $NewListParameters
New-MgSiteList_Create: Unable to determine type of provided column definition

Status: 400 (BadRequest)

The workaround is either to use Invoke-MgGraphRequest to run the request or include precise definitions of what each column will accept. See the online documentation for column definitions.

$Uri = ("https://graph.microsoft.com/v1.0/sites/{0}/Lists" -f $Site.Id)
$NewList = Invoke-MgGraphRequest -Uri $Uri -Method POST -Body $NewListParameters

The list identifier is needed to add items. Because the $NewList variable received the output of the Invoke-MgGraphRequest cmdlet, the identifier is available in $NewList.Id. Otherwise, the new list metadata can be fetched with the Get-MgSiteList cmdlet. After running the code below, the list identifier is available in $List.Id:

$List = Get-MgSiteList -SiteId $Site.Id -Filter "displayName eq 'Organizational Contacts'"

The list can now be edited to finalize columns. For instance, an Attachments column is added by the Contacts template. I don’t need this column, so I hid it. Likewise, I don’t need the fax number or home phone. I also moved some columns around. Figure 1 shows the new list with some items added.

Adding Items to a Microsoft List

The New-MgSiteListItem cmdlet adds a new item to a list. The payload for the new item is passed in a hash table with an embedded hash table containing the column names and values. The important thing here is that the names of the columns must match the spelling and casing of those defined in the list.

$NewItemParameters = @{
	fields = @{
	Title          = 'Payroll'
        FullName       = 'Employee Payroll'
        FirstName      = 'Employee' 
        Description    = 'Employee Payroll Queries'
        WorkPhone      = '877-0012'
        Company        = 'Office 365 for IT Pros'
        Email          = 'Employee.Payroll@Office365itpros.com'
        WorkCity       = 'Dallas'
        }
}

To avoid errors in the hash table, it’s helpful to extract the names of the columns to check against. This is easily done using the Get-MgSiteList cmdlet. For example:

$Fields = Get-MgSiteList -SiteId $Site.Id -ListId $List.Id -ExpandProperty Columns | Select-Object -ExpandProperty Columns | Select-Object DisplayName, Name

With values in the hash table populated, the New-MgSiteListItem cmdlet can add the item to the target list in the target site:

$NewItem = New-MgSiteListItem -SiteId $site.Id -ListId $List.Id -BodyParameter $NewItemParameters

Retrieving Items from a Microsoft List

It would be nice if the Get-MgSiteListItem cmdlet retrieved items without much fuss, but because fields store information in key-value pairs, the call must expand the fields property and specify (select) the names of the fields to use. In this example, you see how to specify fields as Get-MgSiteListItem retrieves all items in a list. To make the fields easier to manipulate, their values are written into a PowerShell list.

[array]$ListItems = Get-MgSiteListItem -ListId $List.Id -SiteId $Site.Id -ExpandProperty "fields(`$select=id,title,fullname,firstname,workphone,email,workcity,description)" -PageSize 999 -All

$ItemData = [System.Collections.Generic.List[Object]]::new()
ForEach ($Item in $ListItems.fields) { 
    $FullName = ($Item.AdditionalProperties.FullName)
    $ReportLine = [PSCustomObject] @{ 
        Id          = $Item.Id
        FullName    = $FullName
        FirstName   = $Item.AdditionalProperties.FirstName
        LastName    = $Item.AdditionalProperties.Title
        PhoneNumber = $Item.AdditionalProperties.WorkPhone
        Email       = $Item.AdditionalProperties.Email
        City        = $Item.AdditionalProperties.WorkCity
        Description = $Item.AdditionalProperties.Description
    }
    $ItemData.Add($ReportLine)
}

Details for an individual item in the list look like this:

Id          : 4
FullName    : Employee Payroll
FirstName   : Employee
LastName    : Payroll
PhoneNumber : 877-0012
Email       : Employee.Payroll@Office365itpros.com
City        : Dallas
Description : Employee Payroll Queries

Deleting Items from a Microsoft List

Each item has an item identifier starting from 1 and incrementing by 1 as users add new items to the list. If you know the identifier for an item, you can remove it by running the Remove-MgSiteListItem cmdlet. This example removes the item with identifier 2:

Remove-MgSiteListItem -SiteId $Site.Id -ListId $List.Id -ListItemId 2

Notice that there’s no confirmation required to delete an item from a list. This means that code to delete items must be careful to remove the correct items.

Finding an item with a specific value in a field requires an intermediate step to extract the data about items into an array and then filter against the array to find the correct item. For instance, the code to fetch items created an array (list) called $ItemData. To find and delete any items with a value of “Dallas” in the city property, we can do this:

[array]$ItemsToDelete = $ItemData | Where-Object {$_.City -eq "Dallas"}
ForEach ($Id in $ItemsToDelete.Id) {
  Write-Host ("Removing item {0}…" -f $Id)
  Remove-MgSiteListItem -SiteId $Site.Id -ListId $List.Id -ListItemId $Id
}

To remove all items from a list, fetch the items and then loop through the set to remove each item:

[array]$ListItems = Get-MgSiteListItem -ListId $List.Id -SiteId $Site.Id -All -PageSize 999
ForEach ($Id in $ListItems.Id) {
  Write-Host ("Removing item {0}…" -f $Id)
  Remove-MgSiteListItem -SiteId $Site.Id -ListId $List.Id -ListItemId $Id
}

Sometimes it’s easier to delete the entire list and start over. This can be done by running the Remove-MgSiteList cmdlet:

Remove-MgSiteList -SiteId $Site.Id -ListId $List.Id

Deleting a list puts it into the site recycle bin. SharePoint Online won’t remove a list if a retention policy or label prohibits its deletion.

The PnP Alternative

The SharePoint PnP module can also be used to create and maintain SharePoint Online lists. Because of the tight affinity between PnP and SharePoint, it’s easier to work with the PnP cmdlets (here’s an example of creating a list of teams). The downside is that PnP becomes an extra module for script maintenance, which won’t be a problem if you use PnP for other purposes.

The Next Step is to Write Some Real Code

After all that, it must be time to use the Organizational Contacts list to create contacts in Exchange Online mailboxes. Describing how to do that requires more space than I have available, so I’ll cover the topic in another article.