Creating a Coffee Machine Sales List in SharePoint with PowerShell
Introduction
Creating lists in SharePoint to manage information is a common practice in many organizations. In this article, we will explore how to create a list called “Coffee Machine Sales” using PowerShell with the PnP PowerShell module. The goal is to ensure that the list stores information about different coffee machines and their sales over the past 10 years, with a special focus on how to remove the previous list if it exists.
Requirements
To follow this tutorial, you need to have:
- Access to SharePoint Online.
- The PnP PowerShell module installed in your environment.
- Appropriate credentials to connect to SharePoint.
Steps to Create the List
1. Connect to SharePoint
First, you need to connect to your SharePoint site. For this, we will use interactive authentication, which allows you to enter your credentials in a popup window.
2. Remove the Existing List
Before creating the new list, it is a good practice to check if a list with the same name already exists. If so, we will remove that list to avoid conflicts.
3. Create the New List
After checking and removing the previous list, we will create the new list and add the necessary columns.
4. Add Example Data
Finally, we will add example data about coffee machines and their sales over the last 10 years.
PowerShell Code
Below is the complete code that executes all the described steps:
# Connect to SharePoint using Interactive Authentication with Client ID
param (
[string]$clientId,
[string]$siteUrl
)
# Authenticate to SharePoint Online
Connect-PnPOnline -Url $siteUrl -Interactive -ClientId $clientId
# Remove the list if it exists
if (Get-PnPList -Identity "Coffee Machine Sales" -ErrorAction SilentlyContinue) {
Remove-PnPList -Identity "Coffee Machine Sales" -Force
}
# Create a sales list for Coffee Machines
New-PnPList -Title "Coffee Machine Sales" -Template GenericList -OnQuickLaunch
# Add columns
Add-PnPField -List "Coffee Machine Sales" -DisplayName "Product Name" -InternalName "ProductName" -Type Text -AddToDefaultView
Add-PnPField -List "Coffee Machine Sales" -DisplayName "Product Code" -InternalName "ProductCode" -Type Text -AddToDefaultView
Add-PnPField -List "Coffee Machine Sales" -DisplayName "Year" -InternalName "Year" -Type Text -AddToDefaultView # Using Text for year representation
# Add columns for each month with currency formatting for sales
$months = @("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
foreach ($month in $months) {
Add-PnPField -List "Coffee Machine Sales" -DisplayName "$month (Sales)" -InternalName "$month" -Type Currency -AddToDefaultView
}
# Add example products and sales data for the past 10 years
$products = @(
@{ Name = "Espresso Machine"; Code = "ESP001" },
@{ Name = "Drip Coffee Maker"; Code = "DCM002" },
@{ Name = "French Press"; Code = "FP003" },
@{ Name = "Coffee Maker"; Code = "CM004" },
@{ Name = "Electric Coffee Pot"; Code = "ECP005" }
)
$startYear = (Get-Date).Year
# Generate sales data for the past 10 years
for ($year = 0; $year -lt 10; $year++) {
$currentYear = $startYear - $year # Generate years going back in time
foreach ($product in $products) {
# Generate a price for the product (in a realistic range)
$price = [math]::Round((Get-Random -Minimum 200 -Maximum 2000), 2) # Rounded to two decimal places
# Set the sales amount to be a realistic proportion of the price
$salesMin = [math]::Ceiling($price * 0.1)
$salesMax = [math]::Ceiling($price * 0.5) # Adjusted max sales value
$sales = Get-Random -Minimum $salesMin -Maximum $salesMax
# Add list item for each product with sales data for the given year
Add-PnPListItem -List "Coffee Machine Sales" -Values @{
"Title" = $product.Name; # Set the Title column
"ProductName" = $product.Name;
"ProductCode" = $product.Code;
"Year" = $currentYear; # Use formatted year
"January" = [math]::Round($sales, 2);
"February" = [math]::Round($sales, 2);
"March" = [math]::Round($sales, 2);
"April" = [math]::Round($sales, 2);
"May" = [math]::Round($sales, 2);
"June" = [math]::Round($sales, 2);
"July" = [math]::Round($sales, 2);
"August" = [math]::Round($sales, 2);
"September" = [math]::Round($sales, 2);
"October" = [math]::Round($sales, 2);
"November" = [math]::Round($sales, 2);
"December" = [math]::Round($sales, 2)
}
}
}
Summary Table
| Step | Action | Description |
|---|---|---|
| 1 | Connect to SharePoint | Authenticate using interactive authentication with Client ID. |
| 2 | Remove Existing List | Check if the “Coffee Machine Sales” list exists and remove it. |
| 3 | Create New List | Create a new list titled “Coffee Machine Sales” using a generic list template. |
| 4 | Add Columns | Add columns for product name, code, year, and monthly sales (currency formatted). |
| 5 | Populate Example Data | Add sample products and generate sales data for the last 10 years. |
Conclusion
In this article, we demonstrated how to create a list in SharePoint to track coffee machine sales over the past 10 years. The PowerShell script presented performs authentication, removes the existing list, creates a new list, and populates it with example data. This approach is useful for managing sales and can be adapted to other contexts and types of products.
edvaldo b. guimarães filho 
Leave a comment