As a Power BI professional, I often interact with SharePoint as one of the primary data sources. However, managing and manipulating SharePoint data can become challenging, especially when dealing with large data sets.
In this blog post, I will dive into a script I created that serves two purposes: start with PnP PowerShell for SharePoint for Power BI users and create test (and production) SharePoint lists.
Now let's walk through the script that creates a new SharePoint list, fills it with random elements, counts them, and deletes them.
PnP PowerShell for SharePoint for Power BI users Script Breakdown
Opening Power Shell
I always run the PowerShell ISE (x86) version as an administrator.
Installing PnP Power Shell
If this is your first time using PowerShell, you must install it.
Define variables
In PowerShell, variables are defined using a dollar sign $ followed by the variable name. There's no need to declare the variable type; PowerShell will automatically determine the type based on the assigned value.
Connecting to SharePoint
In this section, we first connect to the SharePoint site using `Connect-PnPOnline`. The `-UseWebLogin` parameter specifies that we should authenticate using a browser pop-up. However, there are other authentication methods as well. For instance, if we have a username and password, we could use the `-Credentials` parameter. There are many other parameters available, such as `-Interactive` for an interactive login experience, `-PnPManagementShell` to use the PnP Management Shell multi-tenant application, and `-SPOManagementShell` to use the SharePoint Online Management Shell multi-tenant application.
Creating a New List
The `New-PNPList` command creates a new list in SharePoint using the specified list name and the "genericlist" template. The `-OnQuickLaunch` flag adds the list to the site's quick launch for easy access.
Adding Fields to the List
In this script portion, we add new fields to the SharePoint list using `Add-PNPField`. The `-type` parameter determines the data type of the new field. In our case, we are creating text fields for "Color" and "Animal" and a user field for "Owner".
SharePoint supports several types of fields, some of which include:
- Text: A basic text field.
- Note: A field for longer texts.
- Number: A field for numeric values.
- Currency: A numeric field that represents currency.
- DateTime: A field for date and time.
- Choice: A field where the user can choose from a pre-defined set of choices.
- Lookup: A field that allows users to look up values from another list.
- Boolean: A field representing True/False values.
- User: A field to represent a user or group.
Filling in the List with Random Elements
We use a `for` loop to populate the list with random elements. In each iteration of the loop, `Get-Random` is used to pick a random 'Color' and 'Animal' from predefined choices. Then `Add-PNPListItem` creates a new list item with these random values and a static 'Owner' value.
The `$i -le $total` condition in the `for` loop specifies that the loop will continue as long as `$i` is less than or equal to `$total`. As `$i` increments with each iteration, the loop will run exactly `$total` times.
The `Write-Progress` command visually represents the script's progress in the PowerShell console. It uses the calculated `$currentProgress` percentage to update the progress bar after adding each new item.
Deleting All Elements from the List
We delete all items from the list using the `Remove-PnPListItem` command. We use a `foreach` loop to iterate through all items in the list.
The `try` block contains the code that could cause an exception, such as when trying to delete an item that doesn't exist. If such an exception occurs, the code in the `catch` block is executed. In this case, it writes an error message to the console.
By utilising `try` and `catch`, we can handle errors gracefully, ensuring our script can continue running even when minor errors occur.
Deleting the List
Finally, we will delete the list; for this, we use `Get-PnPList` to verify if the list exists, and if it does, `Remove-PnPList` to delete the list.
Thank you for taking the time to read this post. I hope that the information provided has been helpful and informative. Please feel free to share your thoughts and experiences in the comments section below.