Power Automate - Sharepoint access considerations

When we use sharepoint related actions on Power Automate, we may come across any of the below situations. 

1. Lists / libraries with more than 12 complex columns like people picker / lookup / choice etc cant be queried in one go. Could be in a trigger / action like get item or Update item too.

Need to create a View by limiting the number of columns required for our execution and use it in these actions.  We can also use HTTP rest APIs.

2. If flow trigger is on item / file properties update and we have action to update current item, this may become infinite loop as the update item in flow logic would trigger another instance of flow.

We need to use trigger conditions to restrict flow execution. We may go by some logic for status column or so. Or, can create a dedicated account for flows and filter by editor field with this account Or, can create a datetime, FlowRunDate and use it while updating on flow and use this in trigger condition. 

@not(contains(triggerBody()['Editor']?['DisplayName'],'<<account name>>'))

@or(empty(triggerBody()?['FlowRunDate']),not(lessOrEquals(ticks(triggerBody()?['Modified']),ticks(addSeconds(triggerBody()?['FlowRunDate'],60)))))

3. Update / set Hyperlink column, no option to set description.

4. Using date / datetime fields in filters is different as below

DateField eq '2020-07-23'

DateTimeField le '2020-07-23T23:59:59Z' and DateTimeField ge '2020-07-23T00:00:00Z'

5. Columns created in a list, but not added to any content type cant be fetched directly through trigger.

Create a view with all the required columns and refer it in the trigger action.

Sharepoint Online - Set a user as SCA using Rest API

In order to set a user / Security group as a Site Collection administrator, we can use Rest API. 

Below is the Post call, with request body details

 //Rest URI  
 _api/web/siteusers  
 //headers  
 {  
   "Accept": "application/json; odata=verbose",  
   "content-type": "application/json; odata=verbose"  
 }  
 //Body  
 { "__metadata": { "type": "SP.User"},  
  "LoginName": "<<Login Name>>",  
  "IsSiteAdmin":"true"  
 }  

we are actually setting the IsSiteAdmin flag to true, if we want to remove SCA, we can set it to false.

Note: If the site already has this user, it will update the existing user

Microsoft Flows Approvals with Reminders functionality!

The default approvals provided through Microsoft Flows doesn't have reminders capability. We do have lots of alternate custom approaches to send reminder emails on our own. In this article will go through a custom approach which has below functionalities

• Reminders to the participants who haven't responded - Crucial, as we don't want to confuse users with reminders who already responded to it.
• Reminders with Respond Link
• Reminders with the default Adaptive card through Teams

We can extend this approach for approvals when it crossed 30 days too. 

To identify who already responded to the approval, we are accessing Common Data Service entities. So, we need the Premium connector for CDS actions. Also, the account we use (admin account) should have full access to the CDS Approval entities.

Approach in below steps: 

Initialize Variable - to hold Pending Assignees list, used later.

Create an Approval - with custom response options for Approve and Reject as i want all responses from participants.

Then, created a parallel branch for Approvals and reminders.

Wait for an Approval- this action is to hold till approval is complete and perform actions based on output

Email notification- to notify users about completion

Terminate process - As we have it as parallel action, once approval actions are complete, we don't want the other branch (reminder) to run.

On the other parallel branch, Reminder functionality

Delay - used delay action to hold for 3 minutes, we can configure delay until too.

List records - CDS action to list Approval Requests sent for above created approval id. we already know this info, but we need IDs of users as stored in CDS entities.

Select - as the response would have lots of meta data, we just need OwningUserID, using this action we pull required property of all requests. item()?['msdyn_flow_approvalrequestidx_owninguserid']

List records - this action is to list records from Approval Responses entities with the approval id created in start.

Select - to extract OwningUserIDs of all responses received so far.

Filter - Now, we filter users from requests, by excluding responded users. As these are simple arrays, filter action would do, From is 'Select requests', Left of filter is 'Select responses' and to the right is item()

Now we have list of user ids who haven't responded yet. But we need email ids of these users. Below logic is to fetch email ids.

Apply to each - loop through each user from Filter action output array

Get Record - to get user details using the user id

Append to string - we extract user email from above action and append it to the variable created to hold pending approvals

Post your own adaptive card - we send the adaptive card available through create an approval on top to use email id.

This would send the adaptive card to all pending participants through the teams and they can respond directly as in email.

And, as we also have all email ids in the string variable, we use it to send reminder email with Approval link.

All the actions put together, the flow would be as below.