Power Automate Desktop recently released a whole new suite of SharePoint actions that enable users to work directly with SharePoint, improving performance and ease of use for users who need to loop between cloud and desktop actions.
Recently when working with one of the Power users, he told me that the actions provided by Microsoft are not complete as he could not find an action to Read the Data from the SharePoint list.
Use Case :
Create a desktop flow that reads through SharePoint List and Export the Output to an Excel file on the desktop.
Solution :
There are two possible solutions available:
- Use the “Invoke web service” action and use the SharePoint REST API to Get the list items.
- Use the “PowerShell action” action and use the SharePoint client object model (CSOM) to retrieve, update, and manage data in SharePoint.
In this blog we will use option 2 (SharePoint client object model (CSOM)). So without wasting any further time, lets implement out Power Automate Desktop Solution.
Step 1:
Download and Save the Script on the Computer(Update the Credentials).
Step 2
Add the “Read text from file” action to your Power Automate Desktop Canvas and set the Parameters with reference to the screenshot below.
- FilePath : Th path of the .ps1 file you saved in Step 1
- Store content as: Single text value
- Encoding: UTF-8
Step 3:
Add the “Run PowerShell Script” action to the Power Automate Desktop action and pass the “FileContent” variable to the
Final Power Automate Desktop Solution –
That is it, run the Power Automate Desktop Flow and in a few seconds you should have a CSV file with all the list items in your “C” drive
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
#Variables for Processing
$SiteUrl = "https://xxxxxxxxxxxxxx.sharepoint.com"
$ListName="Employee List"
$UserName="admin@xxxxxxx.onmicrosoft.com"
$Password ="xxxxxxxx"
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $credentials
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
#sharepoint online get list items powershell
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Context.LSad($ListItems)
$Context.ExecuteQuery()
#Create a Data Table
$dt = New-Object system.Data.DataTable 'TestDataTable'
$col1 = New-Object system.Data.DataColumn Title,([string])
$col2 = New-Object system.Data.DataColumn Address2,([string])
$dt.columns.add($col1)
$dt.columns.add($col2)
#Add List Item to Data Table
foreach($listItem in $ListItems)
{
$row = $dt.NewRow()
$row.Title = $listItem["Title"]
$row.Address2 = $listItem["Address2"]
$dt.Rows.Add($row)
}
$dt | export-csv C:\SharePointList.csv -notypeinformation
Subscribe to this blog for the latest updates about SharePoint Online, Nintex, Microsoft Flow, Power Apps, and document conversion and manipulation.
Clavin's Blog 