💡 Must-Know PowerApps Functions for Building Powerful Apps

Microsoft PowerApps enables users to build custom business apps with minimal code. One of its core strengths is the rich set of built-in functions that can handle logic, data, user input, and more. Whether you’re a beginner or an experienced app maker, these functions are essential for building flexible, user-friendly applications.

In this post, we’ve grouped the most useful PowerApps functions into categories, so you can quickly find what you need for any scenario.

🔁 Logic & Conditional Functions

These functions control decision-making within your app.

If(condition, trueResult, falseResult) – Basic IF logic.

Switch(expression, case1, result1, ..., defaultResult) – Simplifies multiple conditional paths.

IsBlank(value) – Checks if a field or variable is empty.

Coalesce(value1, value2, ...) – Returns the first non-blank value in a list.

Not(condition) – Inverts a Boolean value (true → false and vice versa).

🔤 Text Functions

Work with strings, user input, and formatted data.

Concatenate(text1, text2, ...) – Joins multiple text strings.

Text(value, format) – Formats numbers and dates as text.

Left(text, n) / Right(text, n) – Extracts characters from the start or end.

Mid(text, start, n) – Extracts a substring from the middle.

Len(text) – Returns the number of characters.

Lower(text) / Upper(text) – Converts to lowercase or uppercase.

Trim(text) – Removes extra whitespace.

Replace(text, start, count, newText) – Replaces part of a string.

🔢 Math Functions

Basic arithmetic and numerical operations.

Sum(table, column) – Totals values in a column.

Average(table, column) – Returns the average.

Round(number, decimalPlaces) – Rounds to a specific number of decimals.

Rand() – Returns a random number between 0 and 1.

Mod(number, divisor) – Gets the remainder after division.

📅 Date & Time Functions

Manipulate and format dates and times easily.

Now() – Current date and time.

Today() – Current date only.

DateValue("2025-05-11") – Converts a string to a date.

TimeValue("10:30 AM") – Converts a string to a time.

DateAdd(date, number, unit) – Adds days, months, or years.

DateDiff(start, end, unit) – Calculates the difference between two dates.

Weekday(date) – Returns a number representing the day of the week.

🔄 Collection & Data Functions

Manage and interact with collections and external data.

Collect(collection, item) – Adds a new item to a collection.

Clear(collection) – Clears all items.

ClearCollect(collection, items) – Clears and then fills a collection.

Patch(dataSource, record, changes) – Updates or creates records.

Remove(dataSource, record) – Deletes a record.

Filter(source, condition) – Returns matching records.

Sort(source, column, Ascending/Descending) – Sorts a data source.

LookUp(source, condition) – Finds the first match.

Search(table, text, columns) – Searches across specific columns.

🧠 User & App Info Functions

Get details about the user or app environment.

User() – Returns name, email, and image of the current user.

Param("paramName") – Retrieves URL parameters (useful for deep linking).

App.StartScreen – Defines the default screen when the app opens.

⚙️ Control Functions

Manage screens, variables, and user interface elements.

Navigate(screen, transition) – Changes to a different screen.

Reset(control) – Resets a control to its default state.

UpdateContext({var: value}) – Creates or updates local variables.

Set(globalVar, value) – Sets a global variable for use across screens.

Toggle(control.Visible) – Toggles visibility or Boolean values.

🧭 Wrapping Up

With these functions at your fingertips, you're well-equipped to build dynamic, responsive PowerApps solutions. Whether you're creating forms, dashboards, or data-driven apps, mastering these functions will help you bring more control and power to your apps.

🚀 Essential SPFx (SharePoint Framework) Commands for Every Developer

Whether you're new to SharePoint Framework (SPFx) or a seasoned pro, having a go-to list of core commands can significantly improve your development workflow. In this post, we'll walk through the most important SPFx CLI commands—from setting up your environment to packaging and deploying your solution.

🛠️ Setting Up Your SPFx Project

Start by scaffolding a new project using the Yeoman generator provided by Microsoft. Make sure Node.js and npm are installed before you proceed.

yo @microsoft/sharepoint

After scaffolding the project, install all dependencies:

npm install

To update dependencies later:

npm update

🔐 Trusting the Development Certificate

If you're using the local workbench, you need to trust the developer certificate for HTTPS support:

gulp trust-dev-cert

🧪 Running the Local Workbench

To build and serve your project locally, use:

gulp serve

This command starts a local server at:

https://localhost:4321/temp/workbench.html

You can also test your solution in SharePoint Online:

https://your-sharepoint-site/_layouts/15/workbench.aspx

🏗️ Building and Bundling Your Solution

During development or before packaging for production, build your solution with:

gulp build

For production-ready output:

gulp bundle --ship

📦 Packaging the Solution

Once bundled, create the .sppkg file for deployment using:

gulp package-solution --ship

The output will appear in the sharepoint/solution/ folder.

🚀 Deploying the Solution

There is no CLI command for uploading the .sppkg file—you must manually upload it to the SharePoint App Catalog via the SharePoint admin center.

If you're using an Azure CDN to host assets, you can deploy to it (assuming it's configured) with:

gulp deploy-azure-storage

🔍 Cleaning and Managing Dependencies

To clean the build output:

gulp clean

Useful npm commands for global tools:

npm install -g yo gulp

npm install -g @microsoft/generator-sharepoint

Check for outdated npm packages:

npm outdated

🧠 Final Thoughts

SPFx offers a powerful, modern way to build SharePoint customizations using familiar web development tools. Knowing these commands will help you build, test, and deploy solutions more effectively. Bookmark this list or print it out—it's a great cheat sheet for any SPFx project.

🚀 Mastering Pagination in Power Apps: Handle Large Data Efficiently!

📌 What is Pagination in Power Apps?

Pagination in Power Apps is a method used to display large datasets in smaller, manageable chunks (pages) instead of loading everything at once. This improves app performance, avoids delegation issues, and enhances the user experience.

🔹 Why is Pagination Needed in Power Apps?

Power Apps has a delegation limit when working with data sources like SharePoint, SQL, and Dataverse. By default, it can retrieve only 500 records, and the maximum limit is 2000 records.

If your dataset has thousands of records, trying to load everything at once will:

 ❌ Slow down the app

 ❌ Cause delegation warnings

 ❌ Fail to retrieve all data

✅ Solution: 

Use Pagination Instead of loading everything, fetch only a specific number of records at a time (e.g., 50 per page) and let the user navigate between pages.

Example: Employee Directory App (SharePoint List)

🔹 Suppose you are creating an Employee Directory App that fetches employee details from a SharePoint list with 5000+ records.

 🔹 Since SharePoint has delegation limitations, loading all data at once will fail or slow down the app.

 🔹 Instead, pagination allows users to see only 50 employees per page, with navigation buttons to view more.

🛠️ Expected Outcome:

 ✅ Improved loading speed

 ✅ Smooth user experience

 ✅ Efficient data handling

🔹 How to Implement Pagination in Power Apps?

We can implement pagination using Skip() and FirstN() functions.

Step 1: Define Pagination Variables

Set(CurrentPage, 1);

Set(PageSize, 50);

CurrentPage → Stores the current page number.

PageSize → Defines the number of records per page.

Step 2: Fetch Paginated Data

ClearCollect(

 PaginatedData,

 FirstN(

 Skip(EmployeeList, (CurrentPage - 1) * PageSize),

 PageSize

 )

)

Explanation:

 ✅ Skip(EmployeeList, (CurrentPage - 1) * PageSize) → Skips previous pages.

 ✅ FirstN(..., PageSize) → Retrieves only PageSize (50) records.

Step 3: Add Navigation Buttons

🔹 Next Page Button

If(

 (CurrentPage * PageSize) < CountRows(EmployeeList),

 Set(CurrentPage, CurrentPage + 1)

)

🔹 Previous Page Button

If(

 CurrentPage > 1,

 Set(CurrentPage, CurrentPage - 1)

)

📌 Functionality:

 ✅ Next Page: Increases CurrentPage by 1 if more records are available.

 ✅ Previous Page: Decreases CurrentPage by 1 if not on the first page.

🔹 Alternative Approaches for Pagination

🔹 Lazy Loading (Load More Button)

Instead of pages, fetch additional data when the user clicks "Load More".

Efficient for infinite scrolling apps.

🔹 Server-Side Filtering

Instead of fetching all data, apply filters before retrieving data.

Example: Fetch only employees from a specific department.

How to List All SharePoint Lists with PowerShell Using PnP

When managing SharePoint Online, administrators often need to retrieve a list of all libraries and lists in a site. With the PowerShell PnP module, this process is quick and efficient. In this post, we’ll walk through a simple script that connects to a SharePoint site and retrieves the title and description of all lists.

Prerequisites

To follow along, make sure you have the PnP PowerShell module installed. You can install it using:

Install-Module -Name PnP.PowerShell

Script Overview

Here's the script that connects to your SharePoint Online site and displays the title and description of each list:

# Connect to SharePoint site

Connect-PnPOnline -Url "https://yourtenant.sharepoint.com/sites/yoursite" -Interactive

# Get all lists

$lists = Get-PnPList

# Select and display Title and Description

$lists | Select-Object Title, Description | Format-Table -AutoSize

Use Cases

• Auditing site contents

• Documenting SharePoint structure

• Identifying unused or unnamed lists

PowerShell Script to Bulk Update "Next Review Date" for PDFs in SharePoint Online

  In document management scenarios, it's common to have review schedules for files such as policies, SOPs, or manuals. This blog post walks you through a PowerShell script that automates the process of updating the “Next Review Date” field for all PDF documents in a SharePoint Online document library.

We’ll use the PnP PowerShell module to fetch and update items efficiently, while also handling time zone specifics like CST/CDT.

Prerequisites

Before running the script, ensure:

• You have the PnP PowerShell module installed:
  Install-Module PnP.PowerShell -Scope CurrentUser

• You have the correct Site Collection URL and Library Internal Field Name.

• Your account has permission to modify list items in the target document library.

What the Script Does

1. Connects to SharePoint Online using PnP PowerShell.

2. Filters all PDF documents in the specified library.

3. Calculates a future "Next Review Date" (e.g., 9999-09-09) adjusted for CST or CDT.

4. Updates the metadata field (Next Review Date) for each filtered document.

PowerShell Script

$SiteUrl = "[SiteCollection]"

Connect-PnPOnline -Url $SiteUrl -UseWebLogin 

# Define the Document Library and the field name for Next Review Date

$libraryName = "[LibraryName]"  # Adjust the library name accordingly

$reviewDateField = "Next_x0020_Review_x0020_Date"  # Internal name of the field

# Get all items from the document library, including FileLeafRef

$documents = Get-PnPListItem -List $libraryName -Fields "FileLeafRef"

# Filter for PDF files only

$filteredDocuments = $documents | Where-Object { $_["FileLeafRef"] -like "*.pdf" }

# Set the new review date to a far future date

$newReviewDate = [datetime]"9999-09-09"

# Determine if the date falls under CDT or CST

$centralTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Central Standard Time")

$isDST = $centralTimeZone.IsDaylightSavingTime($newReviewDate)

# Adjust for time difference from UTC to IST

if ($isDST) {

    $newReviewDate = $newReviewDate.AddHours(10).AddMinutes(30)

} else {

    $newReviewDate = $newReviewDate.AddHours(11).AddMinutes(30)

}

# Loop through filtered documents and update the field

foreach ($document in $filteredDocuments) {

    $fileLeafRef = $document["FileLeafRef"]

    Write-Host "$libraryName Document FileLeafRef: $fileLeafRef"

    Set-PnPListItem -List $libraryName -Identity $document.Id -Values @{ $reviewDateField = $newReviewDate }

}

Notes

• Next_x0020_Review_x0020_Date is the internal name for the “Next Review Date” field. You can find this in list settings or by inspecting the field using PowerShell or the browser.
• The script uses a placeholder date 9999-09-09 which might be used for archiving or indefinite review.
• Adjusting time based on DST (Daylight Saving Time) ensures that timestamps align correctly across regions.

Use Cases

• Automating document review schedules.
• Archiving outdated or permanent documents.
• Preparing metadata for records management.

Convert IST to CST/CDT in PowerShell – Handling Daylight Saving Time (DST)

Working across time zones can be tricky, especially when Daylight Saving Time (DST) is involved. If you’re dealing with automation, scheduling, or data transformation tasks, converting between Indian Standard Time (IST) and Central Time (CST/CDT) becomes essential.

In this post, I’ll walk you through a PowerShell function that smartly handles the conversion of an IST datetime to either CST (Central Standard Time) or CDT (Central Daylight Time), depending on the date and DST rules.

The PowerShell Function

Below is a reusable PowerShell function that converts IST to CST/CDT accurately by accounting for Daylight Saving Time.

function Convert-ISTToCSTorDST {

    param (

        [datetime]$ISTDate    # Input IST DateTime to be converted

    )

    # Central Time Zone Information

    $centralTimeZone = [System.TimeZoneInfo]::FindSystemTimeZoneById("Central Standard Time")

    

    # Check if the given ISTDate is in Daylight Saving Time (CDT) or Standard Time (CST)

    $isDST = $centralTimeZone.IsDaylightSavingTime($ISTDate)

    # Convert IST to UTC (since IST is UTC +5:30)

    $utcDate = $ISTDate.AddHours(-5).AddMinutes(-30)

    # Convert to CST or CDT

    if ($isDST) {

        # If in Daylight Saving Time (CDT), subtract 5 hours from UTC to get CDT

        $convertedDate = $utcDate.AddHours(-5)

        Write-Host "Converted Date (CDT): $convertedDate"

    } else {

        # If in Standard Time (CST), subtract 6 hours from UTC to get CST

        $convertedDate = $utcDate.AddHours(-6)

        Write-Host "Converted Date (CST): $convertedDate"

    }

    return $convertedDate

}

Example Usage

$ISTDate = Get-Date "2025-03-15 10:00:00" # Example IST datetime

$convertedDate = Convert-ISTToCSTorDST -ISTDate $ISTDate

How It Works

1. Finds the Central Time zone using system identifiers.

2. Determines if DST is in effect using .IsDaylightSavingTime().

3. Converts IST to UTC by subtracting 5 hours and 30 minutes.

4. Adjusts from UTC to CST or CDT by subtracting either 6 or 5 more hours.

Bulk Import Excel Data to SharePoint List Using PowerShell and PnP

 

Managing large datasets in SharePoint can be tricky, especially when you're dealing with Excel files and need to avoid list view threshold issues. In this guide, I’ll walk you through a PowerShell script that efficiently imports data from Excel into a SharePoint Online list using PnP PowerShell — with batching support for performance.

Prerequisites

Make sure you have the following before running the script:

• SharePoint Online site URL

• Excel file with data properly formatted

• PnP PowerShell module installed (Install-Module PnP.PowerShell)

• Appropriate SharePoint permissions

What the Script Does

• Connects to your SharePoint site

• Loads and reads an Excel file

• Converts Excel date values

• Batches records in groups (to avoid the 5000 item threshold)

• Adds the items to your SharePoint list or library

• Logs execution time

PowerShell Script

$siteUrl = "[Site Collection URL]"

Connect-PnPOnline -Url $siteUrl -UseWebLogin

# Capture the start time

$startTime = Get-Date

# Define lists and field mappings

$LibraryName = "[LibraryName]"

$excelFilePath = "[excelFilePathwithExtention]" 

# Import data from the Excel file using Import-Excel

$excelData = Import-Excel -Path $excelFilePath

# Define the batch size (to avoid SharePoint's list view threshold of 5000 items)

$batchSize = 200

# Initialize a list to hold batches of items

$batch = @()

# Loop through each row in the Excel data

foreach ($row in $excelData) {

    # Calculate the OBSERVATION_DATE

    $baseDate = [datetime]"1900-01-01"

    $convertedDate = $baseDate.AddDays($row.OBSERVATION_DATE - 2)

    # Map Excel data to SharePoint fields

    $Values = @{

        "Title"                     = $row.Title

        "SHIFT"                     = $row.SHIFT

        "OBSERVATION_DATE"         = $convertedDate

        "BEHAVIOR"                 = $row.BEHAVIOR

        "ROLE"                      = $row.ROLE

        "Behaviour_unsafeCondition" = if ($row.AT_RISK_BY_UNSAFE_COND -eq "N") { "No" } 

                                      elseif ($row.AT_RISK_BY_UNSAFE_COND -eq "Y") { "Yes" } 

                                      else { "" }

        "LOCATION_ID"               = $row.LOCATION_ID

        "AREA_ID"                   = $row.AREA_ID

        "SUB_AREA_ID"               = $row.SUB_AREA_ID

        "LINE_ID"                   = if ($null -eq $row.LINE_ID -or $row.LINE_ID -eq "") { $null } else { $row.LINE_ID }

        "CATEGORY_ID"               = $row.CATEGORY_ID

        "SUB_CATEGORY_ID"           = $row.SUB_CATEGORY_ID

    }

    $batch += $Values

    # Process the batch if it reaches the threshold

    if ($batch.Count -ge $batchSize) {

        foreach ($item in $batch) {

            Add-PnPListItem -List $LibraryName -Values $item -Verbose

        }

        Write-Host "Added $($batch.Count) items to $LibraryName"

        $batch.Clear()

    }

}

# Add remaining items if any

if ($batch.Count -gt 0) {

    foreach ($item in $batch) {

        Add-PnPListItem -List $LibraryName -Values $item -Verbose

    }

    Write-Host "Added the last batch of $($batch.Count) items to $LibraryName"

}

# Calculate execution time

$endTime = Get-Date

$executionTime = $endTime - $startTime

Write-Host "Script executed in:" $executionTime

Key Benefits

• Efficient batching: Avoids the 5000-item limit by processing in chunks.

• Dynamic date conversion: Transforms Excel numeric dates into valid DateTime objects.

• Safe and robust: Skips null values and handles conditional logic cleanly.

How to Split a Large Excel File into Smaller Chunks Using PowerShell

Working with massive Excel files can be cumbersome—slow to open, hard to process, and error-prone in automation. If you’re dealing with a large dataset and need to split it into smaller, manageable files, PowerShell offers a powerful and efficient way to do it—especially with the help of the ImportExcel module.

In this guide, I’ll walk you through a simple script that takes a large Excel file and splits it into multiple smaller Excel files, each containing a defined number of records.

Requirements

• PowerShell

• ImportExcel module
  You can install it via PowerShell with:

          Install-Module -Name ImportExcel

# Import the ImportExcel module

Import-Module ImportExcel

# Path to the large Excel file

$excelFilePath = "[LocalFilePathwithFileExtention]"

# Define the chunk size (e.g., 10,000 records per chunk)

$chunkSize = 10000

# Read the Excel file

$excelData = Import-Excel -Path $excelFilePath

# Calculate how many chunks are needed

$totalRows = $excelData.Count

$chunkCount = [math]::Ceiling($totalRows / $chunkSize)

# Loop to split and save each chunk

for ($i = 0; $i -lt $chunkCount; $i++) {

    $startIndex = $i * $chunkSize

    $endIndex = [math]::Min(($startIndex + $chunkSize), $totalRows)

    # Extract the subset of data

    $chunkData = $excelData[$startIndex..($endIndex - 1)]

    # Define output file path

    $chunkFilePath = "[LocalFilePathwithFileLocation]chunk_$($i + 1).xlsx"

    # Export to a new Excel file

    $chunkData | Export-Excel -Path $chunkFilePath

    Write-Host "Saved chunk $($i + 1) to $chunkFilePath"

}

Write-Host "Splitting completed!"

What This Script Does

1. Imports the Excel file using the Import-Excel cmdlet.

2. Divides the data into smaller chunks (10,000 rows by default).

3. Exports each chunk into a separate .xlsx file.

4. Prints progress messages to the console for visibility.

Why Use This Approach?

• Avoids Excel file size limitations.

• Ideal for batch processing or feeding smaller files into automated workflows.

• Fast and memory-efficient compared to loading Excel in the GUI.

Customization Tips

• Change $chunkSize to adjust how many records go into each file.

• Modify $chunkFilePath to suit your file naming or storage structure.

• Integrate this into a scheduled task or data pipeline for regular use.

Import Sharepoint List data with PnP Powershell from excel

 

$siteUrl = "[SiteURL]"

$listName = "[List NAME]"

$excelFilePath = "C:\Users\user254\Downloads\ObservationsImport1.xlsx" 

$lookupColumns = @{

    "Location_ID" = "WS_Locations"

    "Line_ID" = "WS_Line"

    "Area_ID" = "WS_Area"

    "Sub_Area_ID" = "WS_Sub_Area"

    "Category_ID" = "WS_Category"

    "Sub_Category_ID" = "WS_Sub_Category"

}

$nonLookupColumns = @("OBSERVATION_DATE", "SHIFT", "TYPE", "BEHAVIOR", "AT_RISK_BY_UNSAFE_COND", "COMMENTS", "TIME_ZONE", "ROLE")

Connect-PnPOnline -Url $siteUrl -UseWebLogin

$excelData = Import-Excel -Path $excelFilePath

$excelData.Count

$lookupDataCache = @{}

function Get-LookupId {

    param (

        [string]$lookupColumn,

        [string]$lookupValue

    )

    $itemId=0

    

     

        $lookupListName = $lookupColumns[$lookupColumn]

        $lookupItems = Get-PnPListItem -List $lookupListName -Fields "Id",$lookupColumn

        $lookupDataCache[$lookupColumn] = @{}

        foreach ($item in $lookupItems) {

            if($item.FieldValues[$lookupColumn] -eq $lookupValue) {

                $itemId=$item.Id

            }

           

        }

    

    return $itemId

    

}

foreach ($row in $excelData) {

 

    $itemProperties = @{}

    foreach ($column in $nonLookupColumns) {

        if (($column -eq "OBSERVATION_DATE")) {

            $excelDate = $row.$column

            $baseDate = [datetime]"1900-01-01"

            $convertedDate = $baseDate.AddDays($excelDate - 2)

            $itemProperties[$column] = $convertedDate

        }else {

            $itemProperties[$column] = $row.$column

        }

       

    }

   

    foreach ($lookupColumn in $lookupColumns.Keys) {

        $lookupValue = $row.$lookupColumn

        if ($lookupValue) {

            $lookupId = Get-LookupId -lookupColumn $lookupColumn -lookupValue $lookupValue

            $itemProperties[$lookupColumn] = $lookupId

        }

    }

    try {

         Add-PnPListItem -List $listName -Values $itemProperties

        #Write-Host "Item added successfully!" -ForegroundColor Green

    } catch {

        Write-Host "An error occurred while adding the item: $($_.Exception.Message)" -ForegroundColor Red

    }

   

}

Disconnect-PnPOnline

How to Duplicate a SharePoint List Using PnP PowerShell

When working with SharePoint, you might find yourself in situations where you need to create a duplicate of an existing list. This could be for backup purposes, testing, or simply as a template for new content. Fortunately, using PnP PowerShell, you can automate this process in just a few easy steps.

In this blog post, I'll walk you through how to create a duplicate of a SharePoint list, including its structure and items, using a simple PowerShell script with PnP PowerShell commands.

What is PnP PowerShell?

PnP PowerShell is a powerful library of cmdlets that helps you interact with SharePoint and Office 365. It allows you to manage SharePoint Online or SharePoint Server environments from the command line, automating common tasks and simplifying your SharePoint administration.

Prerequisites:

Before we dive into the script, make sure you have the following:

1. PnP PowerShell module installed. If you haven't installed it yet, you can do so by running this command:

   Install-Module -Name PnP.PowerShell -Force -AllowClobber
   

2. Permissions: Ensure you have appropriate permissions to access the source list and create new lists on the SharePoint site.

---

Steps to Duplicate a SharePoint List Using PnP PowerShell:

1. Connect to Your SharePoint Site

The first step is to connect to your SharePoint site where the source list is located. This can be done using the Connect-PnPOnline cmdlet.

Connect-PnPOnline -Url "https://yoursharepointsiteurl" -UseWebLogin

Replace "https://yoursharepointsiteurl" with your actual SharePoint site URL. You will be prompted to log in if necessary.

2. Get the Source List

Next, retrieve the list you want to duplicate. We use the Get-PnPList cmdlet for this.

$sourceList = Get-PnPList -Identity "Source List Name"

Make sure to replace "Source List Name" with the name of the list you wish to copy.

3. Create the New Duplicate List

Now, let's create a new list that will serve as the duplicate of the source list. The new list will have the same template as the original.

New-PnPList -Title "New Duplicate List" -Template $sourceList.Template -Description "Duplicate of the Source List" -OnQuickLaunch $false

In this command:

• "New Duplicate List" is the title of the new list.
• The template is copied from the source list to ensure the new list has the same layout.
• The -OnQuickLaunch $false option means the list won't be added to the Quick Launch bar, but you can adjust this to your preference.

4. Copy Columns (Fields) from the Source List

SharePoint lists often have custom columns (fields) that you may want to duplicate. This step ensures that the new list will have the same columns as the original.

$sourceFields = Get-PnPField -List $sourceList
foreach ($field in $sourceFields) {
    Add-PnPField -List "New Duplicate List" -DisplayName $field.Title -InternalName $field.InternalName -Type $field.TypeAsString
}

This loop retrieves all fields from the source list and adds them to the new list.

5. Copy Items from the Source List to the New List

Next, you'll copy all items (rows of data) from the source list to the new list. This ensures that the new list will contain the same data as the original.

$sourceItems = Get-PnPListItem -List $sourceList
foreach ($item in $sourceItems) {
    Add-PnPListItem -List "New Duplicate List" -Values $item.FieldValues
}

This loop retrieves each item from the source list and adds it to the new list, preserving the field values.

6. Disconnect from the SharePoint Site

Finally, after the operation is complete, disconnect from the SharePoint site.

Disconnect-PnPOnline

---

Full Script Overview

Here is the complete script for duplicating a SharePoint list:

# Connect to SharePoint site
Connect-PnPOnline -Url "https://yoursharepointsiteurl" -UseWebLogin

# Get the source list
$sourceList = Get-PnPList -Identity "Source List Name"

# Create a new duplicate list
New-PnPList -Title "New Duplicate List" -Template $sourceList.Template -Description "Duplicate of the Source List" -OnQuickLaunch $false

# Copy columns (fields) to the new list
$sourceFields = Get-PnPField -List $sourceList
foreach ($field in $sourceFields) {
    Add-PnPField -List "New Duplicate List" -DisplayName $field.Title -InternalName $field.InternalName -Type $field.TypeAsString
}

# Copy items from the source list to the new list
$sourceItems = Get-PnPListItem -List $sourceList
foreach ($item in $sourceItems) {
    Add-PnPListItem -List "New Duplicate List" -Values $item.FieldValues
}

# Disconnect from SharePoint site
Disconnect-PnPOnline

---

Conclusion

Duplicating a SharePoint list with PnP PowerShell is a simple and efficient process. With just a few commands, you can easily create an identical list, including its structure and content. This method is particularly useful for bulk operations or for backing up data without having to manually recreate everything.

Have any questions or need further customization for your specific SharePoint environment? Feel free to leave a comment below!

Happy scripting! 😊

Automating SharePoint List Updates with PnP PowerShell and Excel Integration

In this post, we will walk through a PowerShell script that automates updating SharePoint list items using PnP PowerShell commands, including reading data from an Excel file to add new items to a secondary SharePoint list. This is useful for syncing or updating information across SharePoint lists, saving time and reducing manual effort.

Prerequisites

Before diving into the script, ensure you have the following:

1. PnP PowerShell Module: You can install this via PowerShell by running Install-Module -Name "PnP.PowerShell" -Force -AllowClobber.
2. Access to SharePoint Online Site: Ensure you have the necessary permissions to read and update SharePoint lists.
3. Excel File: An Excel file with the data you want to import into the SharePoint list. The script uses the Import-Excel command, which requires the ImportExcel PowerShell module. You can install it using Install-Module -Name ImportExcel.

Script Overview

This script connects to a SharePoint Online site, retrieves items from a Locations List, and for each item, checks if certain conditions are met (in this case, skipping items with specific IDs). If the conditions are not met, it reads an Excel file and adds new items to another SharePoint list called Shifts List.

Here’s a breakdown of the script:

$SiteUrl = "https://[Domain]/sites/SiteCollectionName"

$ListName = "Locations List"

# Connect to the SharePoint site

Connect-PnPOnline -Url $SiteUrl -UseWebLogin 

# Retrieve items from the Locations List

$ListItems = Get-PnPListItem -List $ListName -Fields "Title" -PageSize 2000

# Loop through each item in the Locations List

foreach($ListItem in $ListItems)

{

    Write-Host "ID:" $ListItem["ID"]

    Write-Host "Location Name:" $ListItem["Title"]

    

    # Skip processing for specific items

    if ($ListItem["ID"] -eq "15" -or $ListItem["ID"] -eq "8") {

        Write-Host "ID matches, skipping execution."

        #return  # This would skip the rest of the script if uncommented

    }

    else {

        # Import data from the Excel file

        $data = Import-Excel -Path "C:\FilePath.xlsx"

        

        # Name of the list to add items to

        $ListNameShift = "Shifts List"

        

        # Add records to the Shifts List

        try {

            ForEach($record in $data) {

                Write-Host "Adding record $($ListItem["Title"])"

                

                # Add items to the Shifts List, mapping Excel data to SharePoint fields

                Add-PnPListItem -List $ListNameShift -Values @{

                    "Title" = $($record.'Title')

                    "Location" = $($ListItem["ID"])

                    "SORT_ORDER" = $($record.'SORT_ORDER')

                    "ACTIVE_FLAG" = $($record.'ACTIVE_FLAG')

                }

            }

        } catch {

            Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red

        }

    }

}

Step-by-Step Explanation

1. Connect to SharePoint Site: The script begins by defining the SharePoint site URL and list names. Then, it uses the Connect-PnPOnline command to authenticate and connect to the SharePoint site using the -UseWebLogin option. This method prompts for credentials if needed.

2. Retrieve Items from the Locations List: Using Get-PnPListItem, the script fetches all items from the Locations List. It specifies to fetch the "Title" field and limits the number of items fetched to 2000 for performance reasons.

3. Loop Through List Items: For each item in the Locations List, the script:

   • Displays the ID and Title of the list item.
   • Checks if the item’s ID matches certain values (15 and 8 in this case). If it matches, it skips the rest of the loop and moves to the next item. You can adjust this condition to suit your needs.

4. Read Excel Data: If the ID does not match the skip condition, the script imports data from an Excel file located at C:\FilePath.xlsx. Each row in the Excel file contains data that will be added to the Shifts List.

5. Add Items to the Shifts List: The script iterates through the Excel data and adds each record to the Shifts List. The Add-PnPListItem cmdlet is used to create a new item in the list, mapping the relevant fields (e.g., Title, SORT_ORDER, ACTIVE_FLAG) from the Excel file to the SharePoint list columns.

6. Error Handling: If there are any errors during the process (e.g., issues with the Excel file or SharePoint list), the script will catch the error and display an appropriate message.

Conclusion

This script demonstrates how to automate the process of updating a SharePoint list based on data from another list and an Excel file. By leveraging the PnP PowerShell module, you can streamline SharePoint list management tasks, making it easier to keep your data in sync without manual intervention. Whether you're dealing with large datasets or need to automate repetitive tasks, PowerShell provides a powerful toolset for SharePoint administration.

Let me know if you need help customizing this script for your specific use case or if you have any questions!

Clear Approval History from Data verse Approval table

To Automate Delete Approval history we need to delete the records data from Approval History Data verse table. To Delete Approvals History there are 2 approaches

1.    1. Using Data verse Actions in Power automate which is premium connector.

1.    2. Using HTTP Action in Flow which is also a premium connector.

Using Data Verse Connector

Step 1: 

Step 2: