Integrate Microsoft Flows with Power Apps

In this article we would go through the steps of Creating a Flow and integrating it with a PowerApps Form.

Though Power Apps doesn't has ability to call any web services / rest apis, it provided easy way to integrate with Flows, thus if we have any complex logic to be executed in the form, we can call a Flow with configured input parameters and the Flow executes the logic and provides response output parameters. 

Creation of  PowerApps Supported Flow

• Create a Flow of type "Instant - from Blank" and in the trigger, select Power Apps,
  
  
   
• Now, for any input parameter that required from PowerApps, click on any action and from Add Dynamic content window, select Ask in PowerApps from Manual section.
  
   
  We refer to as many input parameters we want and all would be in string.
  
•  Build your logic using input values 
• Finally, to respond back with output values, use Respond to a PowerApp or Flow action. This action would provide an ability to add any number of Output values of different types. 
  
  
  
  
• Save the flow.

Creation of PowerApp and integrating the above Flow

• Create an App or through any Sharepoint List, click on customize forms. I used Sharepoint list here
• Select the Screen Control and go to On Visible property, in the function pane, click on Action tab and choose Power Automate. A subscreen pops up with list of available Flows. Click on the Flow we want to call.
  
  
   
• Once we click on it, it adds a Method to call the Flow and it highlights shows the input parameters to fill in.
  
  
   
• Here i set the output of Flow response to a variable and referred it in couple of label texts
  
  
  
  
• Save the PowerApp and publish it. Once we load the form, we can see the responses on screen
  
  
  
  

Few important observations are as below

1. Boolean output is not coming up in the PowerApp. Better we use string type and compare it
2. For date field, PowerApps is considering as a string.
3. On a high note, though we have different data types for output, if we want to sue in powerApps, better we use string type and convert them

Microsoft Flow - Setup Sharepoint Groups and Users

In this article we would go through Creation of a Sharepoint Group, providing access to it and then adding a set of users to it through the Microsoft Flow using Sharepoint Rest APIs.

Creation of a Group:

Rest API to create a group is 

Uri: /_api/Web/SiteGroups

Method: POST

Header: {content-type:'application/json; odata=verbose'}

Body:{

               "__metadata": {

                        "type": "SP.Group"

               },

               "Title": "Demo Group - Contribute",

               "Description": "Created through API in Flows."

           }

We refer the above API call in Flow action: Send an HTTP Request to Sharepoint as in  below

Set Permissions to Sharepoint Group:

Rest API to set Permissions for a group / user is 

Uri: /_api/web/roleassignments/addroleassignment(principalid=<<PrincipalID>>, roledefid=<<PermisisonRoleID>>)

Method: POST

Here, PrincipalID is the ID of group created in above action. we can fetch this in Flow as body('HTTP_-_Create_Group')?['d']?['Id']

And, RoleDefID is the ID of permission role we are looking to assign. In sharepoint, each permisison role has an internal ID. We can find them by accessing Rest API /_api/web/roledefinitions

Here, for Contribute, the RoleID is 1073741827

Add Users to a Sharepoint Group: 

Rest API to add users to a Sharepoint group is

Uri:/_api/web/sitegroups/GetById(<<GroupID>>)/users

Method: POST

Header: {content-type:'application/json; odata=verbose'}

Body: {

                  "__metadata": {

                          "type": "SP.User"

                  },

                  "LoginName": "<<LoginName of User>>"

            }

We already have GroupID from the first action and for user, we need Login Name of it. 

Once we execute it, it creates a group and adds user as in below

If we have multiple user accounts, we can add them to an array and use loop action for this API. Or, use BULK API to merge all calls except the first one as we need GroupID for other service calls. For Bulk API, I have provided demo in my previous post Set Permissions on a Sharepoint list Item

Microsoft Flows: Move Library documents

This article is to go through about Get Files (Properties Only), Move File and Move Folder Sharepoint actions in Microsoft Flows through below implementation.

We want to archive all the files / folders in one library to Central Archive (Separate Site Collection).

Source Library: A Document library with versioning enabled and also has some Meta data columns.

Destination Library: 

• In Destination Library, we need to make sure the versioning is enabled (as we have it in Source) with higher or same limits as in Source Library. 
• It should also include all the meta data columns as in Source Library as Move Actions would fail if schema doesn't match from Source
• Few extra columns like Archived on and SourceLoc to capture archival info.

Below are the steps to achieve the functionality

1. Use Get Files (Properties Only) action to fetch all the files and Folders in the given source library
   Note: We need to make Include Nested Items Property to "No" as we don't want the documents inside folders and when we move folders, it would go along with inside content.
   

   

   
   
2. Apply to Each action to lop through each File / Folder fetched from above action
3. Condition action to check if the current file is a File or Folder. We have IsFolder property to identify it.
   
   
4. Based on above condition, 
• If its folder, use Move Folder action to move complete folder along with content inside it to destination library
  
  
• If Its File, use Move File action to move the corresponding file to destination.
  
  
  
  Note: Both above actions work same as the Move to commands we use in sharepoint
5. Once the Move action is completed, in order to update the Archival meta data in destination, use Update File Properties  action.
   

   
   Here as we either moved File / Folder in above action, we use below if logic to fetch the correct ItemID to update.  
   

   If(empty(body('Move_file'))
           ,body('Move_folder')?['ItemId']
           ,body('Move_file')?['ItemId']
     )

Complete flow would appear as below,

When we run this flow, it moves all files from Main Library in Demo Site to Documents Library in ArchivalCenter.

Microsoft Flows - Send Email with List Item Attachments

In Flows, we don't have direct option to provide attachments references in Email actions. We need to get the content bytes and provide it either in JSON array format or individual file name and content values.

So, in order to send attachments in email,  we first need to get the content to a variable and then use it in the Send an Email action.

Flows has separate Sharepoint inbuilt actions to fetch list item attachments and also documents from a library. 

Below are the steps to send list items attachments in email

Initialize a Variable of type array to hold all the attachments.

Use Get Attachments action to fetch list of attachments in a given list item. This provides attachment name, unique file identifier. 

Use Apply to Each action to go through each attachment fetched from above action.

 

Fetch Attachment content using Get Attachment Content action by providing the unique file identifier

Then, using Append to array Variable action, add the attachment content in expected format. Here, while providing content bytes, we need to make sure to add ['$content'] to the reference as the byte content is in this parameter. body('Get_attachment_content')?['$content']

Note: If the attachment file is .txt, the "Get Attachment Content" action would return content into body, there is no JSON response here. In this case, we can simply use body('Get_attachment_content')

For "ContentBytes" property of attachments array, we can have a generic function as below

if( endsWith(items('Apply_to_each')?['DisplayName'],'.txt'), body('Get_attachment_content'), body('Get_attachment_content')['$content'])

Now, refer the attachments array variable in the Attachments section of Send an Email action.

In the same way, if we want to send any documents from library, we can use available action 

Also, if we want to send the attachments through Approval action, instead of "ContentBytes", we need to use "Content" in the Attachments JSON array.

Happy coding :-)

Microsoft Flows - Send an Email - SPUtility

Before we go into how to implement it, we shall quickly look into the advantages and disadvantages of using this approach to send emails In Flows for Sharepoint.

Advantages:

• Using this approach we can avoid the complex configuration to send  Email to Sharepoint Group 
• we can set any Sharepoint user in From Address, i.e. the Email can be addressed from any existing Sharepoint User. This will be helpful to send emails on behalf of Managers etc.
• Can send HTML formatted email content. This helps us to design custom templates

Disadvantages:

• Cannot attach files in the email
• Can't send emails to external emails or mail boxes, i.e. the emails that doesn't have user accounts
• From Address cannot be configured to any Mailbox or random name

Implementation:

we use SPUtility SendEMail Rest API method to send an email using HTTP Request to Sharepoint Action

As the SPUtility send email is a sharepoint related method, it only accepts Sharepoint entities to send emails and also if we provide a Sharepoint Group name, it would resolve it internally and sends emails to group members.

Below is sample screenshot and the JSON input we used to send email.

This email goes to Sharepoint Group "Demo Sharepoint Group", CCing Sharepoint group "CC Sharepoint Group" along with user "John". And this email is sent on behalf of "Riyana"

Also, we provided an external email id in TO list, but it doesn't send. 

Body:

 {  
       'properties': {  
         '__metadata': {  
           'type': 'SP.Utilities.EmailProperties'  
         },  
         'From': 'riyana@tmsdemo.onmicrosoft.com',  
         'To': {  
           'results': ['Demo Sharepoint Group','nonuseremail@external.com']  
         },  
         'CC': {  
           'results': ['CC Sharepoint Group','john@tmsdemo.onmicrosoft.com']  
         },  
         'Body': '<p>Demo EMail, <br/><br/>Sample email through Flows.</p>',  
         'Subject': 'Sample email through SPUtility'  
       }  
     }  

And the email in Outlook is

Microsoft Flows Handle XML Data

Similar to JSON Parser, there is no direct available actions to handle XML Data in Flows. But we do have 2 functions that can be used in expressions, xpath() and xml(). we will be going through these functions as below.

XML function will take JSON object data or any XML formatted string and converts it into an XML object. And, if we use JSON object, make sure there is a root property (only one) and not an array object.

 xml(json('{"EmployeeName":"Lucky"}'))  
Returns  
 <EmployeeName>Lucky</EmployeeName> 
 
 xml(json('{"Records":{"Employee":[{"Name":"Lucky","Id":"12345"},
                                   {"Name":"Raga","Id":"54321"}]}}'))  
Returns  
 <Records>
  <Employee>
    <Name>Lucky</Name>
    <Id>12345</Id>
 </Employee>
 <Employee>
    <Name>Raga</Name>
    <Id>54321</Id>
 </Employee>
</Records>  

xml('<Employee>Lucky</Employee>')
Returns
<Employee>
   Lucky
</Employee>

 xml(json('{"Name":"Lucky","Id":"12345"}'))  
 This fails as there are 2 parent properties  

XPATH function is used to navigate through XML and fetch the node content. This function has 2 inputs, first one is the XML object and second one is XPATH expression. And, returns array or XML nodes or the values in nodes as per given XPATH

Lets consider below sample XML

 <?xml version="1.0" encoding="utf-8" ?>  
 <Records>  
      <Employee>  
           <Id>1234</Id>  
           <Name>John, Velvet</Name>  
           <Role>Senior Developer</Role>  
           <Salary>15000</Salary>  
      </Employee>  
      <Employee>  
           <Id>3424</Id>  
           <Name>Angel, Josh</Name>  
           <Role>Developer</Role>  
           <Salary>12000</Salary>  
      </Employee>  
      <Employee>  
           <Id>5421</Id>  
           <Name>Tim, Kary</Name>  
           <Role>Tech Lead</Role>  
           <Salary>18000</Salary>  
      </Employee>  
 </Records>  

We can refer to below XPATH expressions to pull data

 xpath(xml(outputs('xmlContent')),'/Records/Employee')  
 Returns array of EMployee Nodes with XML data in them.  
 [  
      '<Employee>  
           <Id>1234</Id>  
           <Name>John, Velvet</Name>  
           <Role>Senior Developer</Role>  
           <Salary>15000</Salary>  
      </Employee>'  
      ,  
      '<Employee>  
           <Id>3424</Id>  
           <Name>Angel, Josh</Name>  
           <Role>Developer</Role>  
           <Salary>12000</Salary>  
      </Employee>'  
      ,  
      '<Employee>  
           <Id>5421</Id>  
           <Name>Tim, Kary</Name>  
           <Role>Tech Lead</Role>  
           <Salary>18000</Salary>  
      </Employee>'  
 ]  

 xpath(xml(outputs('xmlContent')),'//Name')  
 Returns, array of values of all Nodes with "Name",  
 ['<Name>John, Velvet</Name>','<Name>Angel, Josh</Name>','<Name>Tim, Kary</Name>'] 
 
 xpath(xml(outputs('xmlContent')),'/Records/Employee/Name')  
 Returns, array of Names under Employee/Records nodes  
 ['<Name>John, Velvet</Name>','<Name>Angel, Josh</Name>','<Name>Tim, Kary</Name>']  

 xpath(xml(outputs('xmlContent')),'sum(/Records/Employee/Salary)')  
 Returns, sums up the values from the xpath node values.  
 45000  

 xpath(xml('<employee>Lucky</employee>'),'string(.)')  
 Returns, value in the Node.  
 Lucky  

As we get the response in Array, we can use Apply to Each or Join Operations to go through the content.

Note: When we directly look into the action ouputs in flow history, it would be in encoded format. Once you access into any actions, it will be decoded. 

Thanks to Raga for looking into this along with me. 

Microsoft Flows: Send an email to Sharepoint Group

Microsoft Flows doesn't have direct access to Sharepoint Groups. So, we need to use Rest API to fetch group members from a sharepoint group. Also, to send an email, we need Email IDs, thus we extract email ids of all group members from the Rest Response and use them in Email Notification.

Below are the step by step procedure to achieve it .

1. Use Send an HTTP Request to Sharepoint action to call below Rest API to fetch group members
   /_api/web/sitegroups/getbyname('<<GroupName>>')/users?$select=email&$filter=PrincipalType%20eq%201
   
   Here we are selecting only email as we don't need other information. Also, we filtered for PrincipalType to 1 so that we want only Users from the Group. If we want other types, we can update the filter. 
   

   

   
   
2. As the response from HTTP request is in JSON (default), we need to use Parse JSON action to parse the response. 
   
   

   

   
   The content for this action is output of HTTP call and for Schema, we can click on Generate from Sample and  paste the RAW response from HTTP action to it.
   
   
3. Now we have the response with List of Users in Object Collection, we need to extract EMails from it.  We can use Apply to Each action, but we will go with better performance actions - Select and Join as discussed in earlier post
   
   

   

   Select action is to Extract only Emails for the Object collection and convert to simply array of Emails.
   Join action is to join the Emails in array with semi-column delimiter
   
   
4. Now we have all the Email ids in output of Join action and we can use it in Email Notification.
   

   
   

Happy coding :-)

Set Permissions on a Sharepoint list Item using Microsoft Flows

There are no actions yet available to set permissions on a Sharepoint List Item. we need to use Rest APIs through Send an HTTP Request to SharePoint action. In this article, we shall go through all the Rest APIs we are going to use for permissions.

• First, we need to break inheritance of the Item / Sharepoint object, below is the Post call
  breakroleinheritance(copyRoleAssignments=true, clearSubscopes=true)
• CopyRoleAssignments: this will maintain existing permissions. If set to false, clears existing assignments / permissions
• clearSubscopes: this is used for items / objects below current object, i.e., if we are breaking permissions on List, this property controls for list items.
• Next, we need to assign roles, this could to be a user / group.  below is the post call.
  /roleassignments/addroleassignment(principalid=,roleDefId=)
• Principalid: It is the ID of User / SP Group we are going to assign. We can get IDs of user using service /_api/web /siteusers and for group, /_api/web/sitegroups
  

  
  
• roleDefId: Its is the ID of Permission Role we want to assign to. We can get Role IDs, using the service /_api/web/roledefinitions
  

  
  

We can do only one role assignment to one user / group at a time. As there would be multiple Rest API calls, we use Batch Requests to achieve this in single call.

The syntax to batch call is as below,

  --BatchID   
  content-type: multipart/mixed; boundary=ChangesetID   
  Host:    
  Content-Transfer-Encoding: binary  
  --ChangesetID   
  content-type: application/http   
  Content-Transfer-Encoding: binary   
  <<service call>> HTTP/1.1   
  --ChangesetID   
  content-type: application/http   
  Content-Transfer-Encoding: binary   
  <<service call>> HTTP/1.1   
  --ChangesetID--   
  --BatchID--   

Using above format, we can merge all the role assignments to one batch call by using Send an HTTP request to Sharepoint.

Here,  create 2 GUIDs to use for Batch and Changeset, expression is guid()

then, use HTTP request action as in below image

Handling Multi Select Field Values in Microsoft Flows

It's tricky to use Multi Select fields, could be a Choice field / People Picker. It's because the values returned in these field types are JSON Object Arrays, thus if we try to use field directly in an email action or other, it would auto add Apply to Each and extracts individual item as in below image.

And the Sample JSON is as below.

 [  
  {  
   "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
   "Id": 1,  
   "Value": "Agriculture"  
  },  
  {  
   "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
   "Id": 3,  
   "Value": "Political"  
  },  
  {  
   "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",  
   "Id": 5,  
   "Value": "Engineering"  
  }  
 ]  

In order to use the values, we first need to extract Values from above JSON and join the values.
There are couple of ways to implement it as below

Approach 1:

• Initialize Variable of type String to hold the value
• Use Parse JSON Action to parse the JSON object data, i.e. the multi choice field
• Use Apply to Each to go through the Parsed JSON response
• Inside Loop action, add Append to String action and set the Value property of Parsed Object to created variable
• Now the variable will have selected values with semi-column separated and can be used in any action as a string.

If we don't want to use Parse JSON action, we can directly refer the Multi Choice field in Apply to Each. By doing so, we won't be able to select the Value property from JSON object, but can use simple expression to fetch it, item()?['Value']

Approach 2: (Suggested.)

• Use Select action to select Value property from the JSON object array, here we use expression item()?['Value']
• Use Join Action with semi column separator to join the values from above action

Second approach executes much quicker compared to other as we don't use Loop action here.

And, if the field is People Picker, the JSON data sample is as below and instead of 'Value' property in above expressions, we can choose Email / DisplayName as per requirement.

 [  
  {  
   "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",  
   "Claims": "i:0#.f|membership|demo1@sptest.onmicrosoft.com",  
   "DisplayName": "LaxmiNarayana Ruttala",  
   "Email": "demo1@sptest.onmicrosoft.com",  
   "Picture": "https://sptest.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=demo1@sptest.onmicrosoft.com",  
   "Department": null,  
   "JobTitle": null  
  },  
  {  
   "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",  
   "Claims": "i:0#.f|membership|demo2@sptest.onmicrosoft.com",  
   "DisplayName": "Demo User",  
   "Email": "demo2@sptest.onmicrosoft.com",  
   "Picture": "https://sptest.sharepoint.com/_layouts/15/UserPhoto.aspx?Size=L&AccountName=demo2@sptest.onmicrosoft.com",  
   "Department": null,  
   "JobTitle": null  
  }  
 ]  

Validation Rules using Date fields on a Sharepoint List

I have a requirement to restrict users for entering Review date as below

1. Only Thursdays are allowed
2. Need to skip the coming first Thursday
3. Can't be farther from 4 Thursdays from today

I used the available validation settings rule to restrict user and it is as below. For this, created a date field 'CreatedDate' with default value set to today and hidden it in form as the 'Created' column not working in Validation rules in Edit forms.

=IF(WEEKDAY([ReviewDate])=5,IF([ReviewDate]-[CreatedDate]<28,IF([ReviewDate]-[CreatedDate]>=7,TRUE,FALSE),FALSE),FALSE)

In above formula I used Review date and also the created date.

1. WEEKDAY([ReviewDate])=5 - its to make sure the selected date is thursday only, the weekdays start from Sunday with 1
2. [ReviewDate]-[Created]<28 - We are making sure the review date is no more than 28 days so that it doesn't cross 4 Thursdays.
3. [ReviewDate]-[Created]>=7 - This is to restrict the user from selecting first Thursday