CRUD operation using Send request to SharePoint Action in Power Automate.

 CRUD operation using Send request to SharePoint Action

Send request to SharePoint Action is an important Action in Power Automate.

Perfect use case for this Action will be when Out of Box actions are failing to do the job, like when your flow is failing to read the JSON that you have passed to it. This action can be used to do all the CRUD operations on SharePoint lists using the below methods.

  • GET
  • POST
  • PATCH
  • DELETE
However, the best part I like about it is that you don't even need to refresh the SharePoint Pages to see the end result.

Lets dig into this.

We have a simple Flow testing list created in SharePoint.

It has a Title column, a choice column and a People picker with single values column.

GET Method.

For all the flows that I showcase here I will only trigger them using Manual trigger. But that is no way a dependency and in actual practice you can trigger your flows based on any conditions and even from Power Apps and they will work like a charm.



Site Address : Select your site from the dropdown, if your site doesn't show up in the dropdown and Power Automate is acting grumpy just select custom and type in your site address.

Method: Select GET from the dropdown as we just want to read the items form the SharePoint list.

Uri: Here is where the magic happens type in the below statement.
_api/web/lists/getbytitle('yourlistName')/items?

Headers: accept as key and application/json; odata = verbose.

we can odata = verbose in dev environment.


POST Method

In the next method, there are two couple of changes.

Site Address : Select your site from the dropdown, if your site doesn't show up in the dropdown and Power Automate is acting grumpy just select custom and type in your site address.

Method: Select POST from the dropdown as we want to add an item to the SharePoint list.

Uri: Here is where the magic happens type in the below statement.
_api/web/lists/getbytitle('yourlistName')/items

Headers: accept as key and application/json; odata = verbose.

The body looks like 
{
'__metadata':{ 'type':'SP.Data.FlowTestingListItem'}, 'Color':'Yellow'
}

PATCH Method

Patch method is nothing but what we call Update method. Its used to update existing SharePoint list item values.

So we will select the same site address and then the below fields will be updated as below

Site Address : Select your site from the dropdown, if your site doesn't show up in the dropdown and Power Automate is acting grumpy just select custom and type in your site address.

Method: Select PATCH from the dropdown as we want to update an item to the SharePoint list.

Uri: Here is where the magic happens type in the below statement.
_api/web/lists/getbytitle('yourlistName')/items(ID)
we can get ID, via the SharePoint ID column.

Headers: accept as key and application/json; odata = verbose.

Also, here i wish to tell you guys you can also pass this ID to the flow, or set it when calling the Flow via PowerApps. So this makes it very dynamic.

wait but we do have a change in Body

The body looks like 
{
'__metadata':{ 'type':'SP.Data.FlowTestingListItem'}, 'Color':'Yellow','PeoplePickerSingleId':7
}

yes __metadata is nothing but two successive underscore before metadata.
SP.Data.YourlistNameItem. Following this i am setting values to SharePoint columns.
Color as Yellow and PeoplePickerSingle as 7 as that is what my JSON retrieves when i do a GET item on the SharePoint list. So if you wish to know your ID go back to the flow and test it.


DELETE Method



In this method what we are doing is making changes to the below 

Site Address : Select your site from the dropdown, if your site doesn't show up in the dropdown and Power Automate is acting grumpy just select custom and type in your site address.

Method: Select DELETE from the dropdown as we want to update an item to the SharePoint list.

Uri: Here is where the magic happens type in the below statement.
_api/web/lists/getbytitle('yourlistName')/items(ID)
we can get ID, via the SharePoint ID column.

Headers: IF-MATCH as key and  value as * and you can also add 
X-HTTP-Method : DELETE.

Run the flow if you get Success Code: 200  the item will be successfully deleted.


Conjecture:  I believe all these methods and Send request to SharePoint as an Action is super useful when Out of box Actions in Power Automate fail.

Comments

Post a Comment

Popular posts from this blog

Creating Nested Galleries in Power Apps. (Parent-Child Relationship)

Image
 This is another most common scenario where in client has two parent child lists or any two lists having a common SharePoint column. And clients wishes to have a relationship on the PowerApps screen where in when he/she selects a particular lets say Program in our example. All the respective Projects following that Projects are shown. In such a scenario we can leverage nested galleries. So what we do in here is we add two galleries on our Power App screen. For Programs gallery the Left one on the screenshot above, we connect it to the Programs list For Second gallery we will connect it to the Projects list. But this is where the magic happens. Here we use Filter () function. So what we do in here is we set the Program's column value in Project's list equal to the Selected Program of left Gallery. i.e. the gallery connected to my Program's list. A more complex version of the same is where you inset the nested gallery inside your parent gallery. In this too everything remains
Read more

How to reset a datepicker control in PowerApps?

Image
 Scenario: Simple reset a date picker control. Issue, you can't use normal Reset() function to reset the datepicker control. Solution: Simple create a variable Set(varResetCD, True) and use this variable to update the reset property of DatePicker control. The below Reset() won't work in case of datepicker control We need to create a variable and use it at the Reset property of the datepicker control. Also, if you still see some text inside your date field there are two other places you need to check in one is the datepicker control advanced properties. In the above screenshot all you need to do is clear InputTextPlaceholder. And then select the control and check the DefaultDate property of the date.  This should be set to Today(), you can also change it to something like below If(varResetCD,Blank(),Today()) What this does is sets the control to blank if the variable you have declared above is true. 
Read more

Reset () function failing to work on Edit form for Combo boxes in Power App.

Image
 Hi Good day, Today we have a very interesting scenario. We have a cascading dropdowns on our Power App screen. The flow for these dropdowns is that a user selects the Division, and a list of Business units containing the selected Division is popped down in your Business Unit dropdown. Now to make it user friendly, we have on change code written on each dropdown. So that if the user decides to change the Division dropdown, the Business Unit dropdown is reset. Please remember in here we are talking in regarding to New Form. On Resetting the above Division combo box, since we have written the Reset function code on Change property of the Division Combo box.   Reset(ddBU);Reset(ddBPI);Reset(ddTeams). The above becomes Up till here everything is hunky dory. This is the classic case of how the Reset() function should behave. But that's not, when you move from new form to Edit form.   The Reset function behaves weirdly. It is able to reset the cascading dropdown values like above. But i
Read more