Contents
Bạn đang tìm kiếm từ khóa Microsoft Flow update SharePoint list from Excel được Cập Nhật vào lúc : 2022-11-18 12:05:00 . Với phương châm chia sẻ Bí kíp Hướng dẫn trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi tìm hiểu thêm tài liệu vẫn ko hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Admin lý giải và hướng dẫn lại nha.
In this Power Automate tutorial, we will discuss how to get data from excel on SharePoint in Power automate. This is a complete example of Power Automate Get Data from Excel on SharePoint. So we will discuss the below points:
- Microsoft Flow import excel data into SharePoint list.Add or update Excel data to SharePoint list using Power Automate.
Here we will see how to import excel data into an existing SharePoint list using Power automate.
So here I have an Excel sheet i.e. Employee Information and then uploaded this Excel sheet in SharePoint Online Document Library. Also, you can upload an excel sheet to One Drive Business.
As we have an existing SharePoint list based on the Employee Information excel sheet.
So here we will create a Microsoft flow that will import data from Excel to the existing SharePoint list.
Step 1: Create an Excel sheet
Create an Excel sheet for the Employee information, and make sure the excel file has a .xlsx extension.
Then create the below columns in the excel sheet of the Employee information.
- Full nameDepartmentManagerHireDateAddressPhone NumberDate Of BirthSocial Insurance Number
Then select the data in excel and change the data into table format.
power automate get data from excel
Once the Excel spreadsheet is done, upload the excel file into SharePoint Document Library or OneDrive Business.
Step 2: Create a SharePoint list
Now in this step create a Sharepoint list that will be similar to the excel sheet column name and data type.
Column NameColumn TypeTitleSingle Line Of TextDepartmentChoiceHire DateDate and TimeManagerPersonAddressSingle Line Of TextPhone NumberNumberDate Of BirthDate and TimeSINSingle Line Of textEmployee Information Listpower automate get data from excel sheet
If you have an existing SharePoint list, check the list the column name and column type are similar to the Excel sheet.
Once the SharePoint list and excel sheet are ready, we will move towards creating a flow in the Power Automate.
Step 3: Create a Flow in the Power Automate
Now we will create a flow that will import excel data into the SharePoint list.
To create a Flow, log in to Power Automate and then click on Create -> choose Instant cloud Flow.
Microsoft flow get data from excel
Now provide the flow name, and then select the trigger Manually trigger a flow. Then click on Create.
Microsoft Flow imports excel data into SharePoint list
Step 4: List the rows present in the Excel sheet
To list all the rows present in the excel table, we will create an action by clicking on the Next step. Then choose List rows present in a table -Excel Online.
Power Automate update SharePoint list from Excel
Now provide the Location, Document Library, File, and Table values of the Excel sheet
Microsoft flow update sharepoint list from excel file
Now in this step, we targeted the date data present in the excel sheet because excel dates dont carry over the way we expect. So, we will use the variable to store the dates, and later we will apply an expression to do the conversion.
We will need two variables for importing i.e. varDate1 and varDate2, as our excel table contains two date columns.
So to initialize a variable, click on the next step then choose the Initialize variable action. Then provide the variable name and choose the type as String.
power automate get data from excel on SharePoint
In a similar way add another initialize variable action for var Date2.
power automate get data from excel table
Step 6: Apply to each output value of excel
In this step, we will add Apply to each action to apply each output value of the List rows present in a table.
power automate get data from excel one drive
Step 7: Set the variable for the Date column
In this step, we will use the set variable action to set the variable for each of your date columns and set the values of each dates column.
power automate get data from excel one drive
Step 8: Compose the excel number column
In this step, we will use the compose action to take the input of the number column from the List rows present in the table, so later we will use the output in mapping.
Import data from Excel to SharePoint list
Step 9: Create an item in a SharePoint list
Now we will add a Create item action to map the data from the Excel sheet to the SharePoint list.
So click on the next step, and then select the Create item action. And then provide the site address and List name.
Then set the column to align with your excel data and for the Phonenumber column add the output from the compose.
But for the date column, we will add the below expression
addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)addDays(‘1899-12-30’,int(variables(‘varDate2′)),’yyyy-MM-dd’)power automate get data from excel sheet
Note:
As Power automate doesnt tư vấn location column(SharePoint), therefore if the column information is necessary change the column type to Single Line of Text. So it will show in the Create item action and you can map the data of excel table.
Once the Flow is ready, click on the save and run the flow.
Step 10: Run the Flow
Now to run the Flow click on the Save then Test the flow manually. So the flow will trigger manually.
As the Employee information table contains two information so, when the flow will trigger it will import the two pieces of information from the Excel table to the SharePoint list.
import data from excel to sharepoint list power automate
Once your Flow ran successfully, you can check the imported data in the SharePoint list.
How do I import Excel data to SharePoint list with Power automate MS flow?
Here we will see how to add and update the Excel data to the SharePoint list using Power Automate.
Here also we will use the same Employee Information excel sheet and SharePoint list. We will use the title column as a unique column.
So, we will update the item to the SharePoint list from the excel table, when the item is already present in the SharePoint list and also if the title in excel is equal to the SharePoint item title.
Otherwise, create an item in the SharePoint list if there is no item present and if the title of each item is not equal to the excel title field.
Step 1: Create a Flow using Power Automate
To create a Flow, Log in to Power automate, then click on create -> Scheduled Cloud Flow.
Power automate scheduled Cloud Flow
Now provide the Flow name, then select the start date and time. Next choose repeat every 1 and day. So, this flow will run every day. Then click on create.
Update Excel from SharePoint list using flow
Step 2: List rows present in a table
In this step we will fetch all the data from the Excel table, so click on the Next step then select List rows present in the table.
microsoft flow update sharepoint list item from excel
Now set the Location, Document Library, File and table values in the List rows present in a table.
Add & Update Excel Table Row from SharePoint List
Step 3: Initialize the variable Date
So, in this step, we targeted the date data present in the excel sheet because excel dates dont carry over the way we expect, so we will initialize variable per date column. So in Employee information list contain two date list, therefore we will initialize to variable i.e. varDate1 and varDate2.
Later we will use the variable to store the date from excel and then apply the expression to do the conversion.
So click on the next step, select the initialize variable, then set the name varDate1 and type String. Similarly, choose the initialize variable action and then set the name to varDate2 and type String.
Update existing SharePoint list from Excel using Microsoft flow
Step 4: Apply to each Excel value
In this step, we will add Apply to each action which will apply to each output value from the List rows present in table action Excel Online.
So, click on the next step then select Apply to each action and then from the dynamic content select the value List rows present in a table.
power automate update SharePoint list from excel table
Step 5: Set the Date variable
In this step we will set the date variable, so click on the next step then select the Set variable action.
Now set the variable for varDate1, from the value select Hire date from the dynamic content.
Similarly set the variable for the varDate2 and select the value Date of Birth from dynamic content.
power automate update sharepoint list from excell llist
Step 6: Compose excel number
In this step, we will use the compose action to set the Phone number because when we will map the excel number field to the SharePoint number column, Power automate doesnt allow it.
So, click on the next step inside Apply to each, then select Compose action. Set the inputs as a Phone numbers from the dynamic content.
power automate flow update sharepoint list from excel
Step 7: Get items from the SharePoint list
In this step we will Get items from the SharePoint list, so for this click on the Next step, then select the Get items action.
Now Provide the site address and List name of the SharePoint. Next, we will filter the items if the title column of SharePoint is equal to the Title column of Excel then only it will fetch the items from the SharePoint list.
Add & Update Excel Table Row from SharePoint List
Step 8: Condition to check the SharePoint list items
Here we will check that the Sharepoint list contains items or not, if the SharePoint list contains items then any changes are done in the excel sheet, that will be updated to the SharePoint list else the new items will be created in the list.
So for this we will check the length of the item in the SharePoint list, write the below expression is not equal to 0:
length(outputs(‘Get_items’)?[‘body/value’])microsoft flow update sharepoint list item from excel
Step 8: Update item in SharePoint List
To update each item we will use apply to each loop, so click on the next step( in the If yes part of condition), then select Apply to each loop action and set the value- Get items- SharePoint.
Next select the Update Item action inside the Apply to each SharePoint item, and provide the Site address, List name, Id, and map the SharePoint Field with Excel field from the dynamic content. In the Phone number field map the output- Compose from the dynamic content
In Hire date add the below expression:
addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)
Then in the Date of Birth add the below expression:
addDays(‘1899-12-30’,int(variables(‘varDate2′)),’yyyy-MM-dd’)How do I automatically update a SharePoint list in Excel?
Step 9: Create an item in a SharePoint list
If the condition does not match we will create an item in if no part of the condition.
So to create an item, click on the Add an action ( if no part) and select Create item– SharePoint.
Now provide the site address, list name, and then map the SharePoint field with the Excel field. In the Phone number column map the output Compose from the dynamic content.
In the Hire date column provide the below expression:
addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)
And in the Date of birth provide the below expression:
addDays(‘1899-12-30’,int(variables(‘varDate2′)),’yyyy-MM-dd’)Power Automate Excel date to SharePoint list
Once the Flow is ready, click on the Save and run the flow manually.
Step 10: Run the Flow
Case 1: No item in SharePoint list Create Item
Now run the flow manually, as our SharePoint list does not contain any item, so power automate will create items.
Flow excel date to sharepoint
Now the employee data present in the excel sheet is imported to SharePoint list.
power automate get data from excel
Case 2: Update item in a SharePoint list
If we made changes in the excel sheet, for example, I change the department of Haripriya to HR, so now run the flow manually to see the changes. As our condition is true i.e. length of the item is not equal to zero, so the item gets updated.
microsoft flow update sharepoint list item from excel
Now you can check the SharePoint list item is get updated by the Power automate.
power automate get data from excel sheet
Case 3: SharePoint list contains items- Create item
Now if you want to change the title in the excel sheet, then the condition of get items filter query is not satisfied.
For example, I change the title Haripriya to Haripriya Dhall, so in this case, Sharepoint title column is not equal to Excel title column, power automate will create a new item in the SharePoint list.
Add or update Excel data to SharePoint list using Power Automate
And also you can check the SharePoint list the new item is created.
Add or update Excel data to SharePoint list using Power Automate
You may like the following Power Automate tutorials:
In this Power Automate tutorial, we learned how to get data from excel using Power Automate. And also we discuss the below points:
- Import excel data into SharePoint list using power automate.Add/update Excel data in SharePoint list using Power automate.
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint and SPGuides
Bạn vừa Read Post Với Một số hướng dẫn một cách rõ ràng hơn về Clip Microsoft Flow update SharePoint list from Excel tiên tiến và phát triển nhất
You đang tìm một số trong những Chia Sẻ Link Down Microsoft Flow update SharePoint list from Excel Free.
Nếu sau khi đọc nội dung bài viết Microsoft Flow update SharePoint list from Excel vẫn chưa hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Tác giả lý giải và hướng dẫn lại nha
#Microsoft #Flow #update #SharePoint #list #Excel