In this Power Automate Desktop tutorial, we will see how to get items from a SharePoint list using Power Automate. Once we get the items, we will write them down in Excel.
For example, I have created a Sharepoint list called Product List, which contains the following columns:
- Title-Single line of text
- Name: Single line of text
- Description: Multiple Lines of text
- Price: Currency
- Quantity: Number
Now, we’ll create a desktop flow that will get items from the above SharePoint list using Power Automate Desktop. Then, loop through each item and write in the Excel worksheet.
For this, i have already created a Product.xlsx worksheet, which contains the below columns:
- Id
- Name
- Price
- Quantity
Get items from a SharePoint list using Power Automate Desktop
Here, we will see how to get items from the list using Power Automate Desktop.
Before we start creating the flow, set the SharePoint list, add some items to it, and set up an Excel file in the local system.
Step 1: Open Power Automate Desktop, provide the flow name, and click on Create.
Step 2: Now we will get items action, for this, expand the SharePoint section, then drag and drop the Get items action to Workspace. Then provide the below information:
- Site address: Select the SharePoint site address from dropdown
- List name: Select the List name from the dropdown.
Step 3: Now we will convert JSON to object; drag and drop the Convert JSON to object action. Then provide the below information:
- JSON: So, click on the variable icon{x}, then select ‘GetItemResponse’ and click on Select.
Step 4: Now we will open the Excel, so drag and drop the Launch Excel action to the Canvas. This action will generate a variable ‘ExcelInstance’, or you can change the variable name based on your requirement.
Step 5: Now we will loop through each item in an Excel, so, expand the loop section and then drag and drop the For each action to Workspace. Then provide the below information:
- Value to Iterate: Here, we will select the value array, for this, provide like below
%JsonAsCustomObject['value']%- Store into: The value will be stored in CurrentItem.
Step 6: We will get the free row in Excel after each iteration, so, expand the Excel section, then drag and drop the ‘ Get first free row/column from Excel worksheet’ action to canvas. Then provide the below information:
- ExcelInstance: Select the ExcelInstance from the variable option.
This action will generate two variables, i.e. FirstFreeRow and FirstFreeColumn.