Call SharePoint Rest API with Client ID and Client Secret from Power Automate

Overview:

We recently implemented an automated approval process in SharePoint Online with Power Automate for a construction and engineering company based out of Washington, United States. We came across a scenario where we required to consume SharePoint data in Power Automate with REST API using Client ID & Client Secret. Here in this article, we will discuss step by step process to implement such functionality.

Prerequisite:

We must have SharePoint App registered, please visit this article for step by step process to register the SharePoint App.

Copy Tenant ID from Azure Portal:

1. Open Azure Portal. From Left Navigation click on Azure Active Directory
2. Here, you will get your Tenant ID.
   

Steps to call SharePoint REST APIs with Client ID and Client Secret from Power Automate (MS Flow)

1. Initialize variables for – Client ID, Client Secret, Tenant Name and Tenant ID.
1. Client ID and Client Secret = We can get these from where we registered the App.
2. Tenant Name = Name of the Tenant.
3. Tenant ID = We can get that from Azure Portal (Step 2 from above section).
   
2. Prepare Body Variable.
   

    grant_type=client_credentials&client_id=<<Client ID>>@<<TenantID>>&client_secret=<<Client Secret>>&resource=00000003-0000-0ff1-ce00-000000000000/<<TenantName>>.sharepoint.com@<<TenantID>>   
   

   
3. Now, add “HTTP action”.
1. Method: POST
2. URI: https://accounts.accesscontrol.windows.net/@{variables('tenantID')}/tokens/OAuth/2
3. Headers:  Content-Type: application/x-www-form-urlencoded
4. Body: Pass the variable defined in Step 2.
   
4. Get Access token in Compose Variable.
   Expression: body('Get_Access_Token_from_SharePoint')['access_token']
   
5. Add HTTP Action to get users from SharePoint group.
1. Method: Get
2. URI: https://tenantname.sharepoint.com/sites/sitename/_api/web/sitegroups/getbyname('groupname')/users
3. Headers: 
4. Accept: application/json;odata=verbose
   Authorization: Bearer @{outputs('Compose')}
   
6. Then, let’s Parse our JSON using Select Statement.
   

This is how we can create a flow to find the users from SharePoint Group with client Id and client secret. 

Test Flow

The flow ran successfully.

We got the following access token using first API call.

Then this access token used to call the group users. We got the following users.

Conclusion:

This is how, we can call SharePoint Rest API with Client ID and Client Secret from Power Automate. Isn’t that amazing? 

If you have any questions you can reach out our SharePoint Consulting team here.

[Issue Resolved]: Part URI is not valid per rules defined in the Open Packaging Conventions specification

Introduction

We at Binary Republik recently implemented Intranet Portal on Modern SharePoint for a Real Estate Agency based out of Washington, United States. We also implemented custom webparts with SharePoint Framework (SFPx) as part of the Intranet Portal implementation to meet the business user expectations. While implementing the custom SFPx webparts, we encountered a strange error. Let's see in detail what error we faced in which scenario, what was the root cause of the error and what's the resolution for the same. 

Error/Issue:

• After uploading the ".sppkg" solution of SPFx webpart in the "app catalog", it was giving the error “Invalid SharePoint App package. Error: Part URI is not valid per rules defined in the Open Packaging Conventions specification”.

Scenario:

• We created SPFx webpart solution, the solution was built successfully. Then we executed the below commands to create package:
• gulp bundle --ship
• gulp package-solution –ship
• The package was also created successfully, so we uploaded the same to the "app catalog" and it was giving an error as shown above.

Reason:

• After some research and analysis, we found, this was because of the parent folder of our SPFx webpart solution.
• Here is the path of our solution: D:\Projects\Project Management
• The parent folder name of our solution was "Project Management" (please note, there is a space in the name of the parent folder) and while creating the solution we have selected the option to "Use the current folder" in "Where do you want to place the files?" selection.

• So, when there is space in the name of the parent folder of your solution, it will give the error “Error: Part URI is not valid per rules defined in the Open Packaging Conventions specification”.

Solution:

• To solve this error, remove space from the name of the parent folder of your solution. We renamed the parent folder name to be “ProjectManagement”.
• Then executed below commands:
• gulp clean
• gulp build
• gulp bundle --ship
• gulp package-solution –ship
• And deployed the new package in the "app catalog" and the package was deployed successfully.

Hope this helps!

 

If you have any questions you can reach out our SharePoint Consulting team here.

Update SharePoint Hyperlink fields with URL and Description using Power Automate (MS Flow)

Introduction

We recently implemented an automated approval process in SharePoint Online using Power Automate for the HR department of an Engineering Company based out of Denmark. We came across a scenario wherein it was required to update the value of the Hyperlink field in the SharePoint list with URL & Description from Power Automate.

Problem Statement

We cannot directly update the value of the Hyperlink field with description from using "Update Item" action in Power Automate (MS Flow). So in this article, we will see how to update the value of the Hyperlink field with URL & Description using HTTP Request Action in Power Automate.

Resolution

As an alternate, we can use "Send an HTTP request to SharePoint" action in Power Automate to update the value of the Hyperlink field with URL & Description in SharePoint. Let's see the step by step process to perform the same, for easier understanding we will go with an example SharePoint List with a straightforward scenario.

SharePoint List

We have already created a SharePopint List with two fields Title (Single Line of Text) and Address (Hyperlink).

Steps to Follow

Step 1: Open your flow and add "When an item is created" trigger and provide your "Site Address" and "List Name".

Step 2: Add a new action "Send an HTTP request to SharePoint".

Step 3: Once successfully added, provide all the details as required.

Site Address: Your Site Address

Method: POST

Uri :  _api/web/lists/GetByTitle(‘ListName’)/items([ID]). (Update “ListName” to your list’s name & replace “[ID]” with the ID field from dynamic content).

 

Step 4: Click on “Text Mode” in "Send an HTTP request to SharePoint" action.

Step 5:  Add the below code in the Header Textbox.

1:  {  
2:  "Content-Type": "application/json;odata=verbose",  
3:  "X-HTTP-Method": "MERGE",  
4:  "IF-MATCH": "*"  
5:  }

Step 6:  Add the below code in Body Textbox

1:  {'__metadata': {'type':'SP.Data.RecordsListItem'},  
2:  'Address':{'Description': 'Click Here','Url': 'https://www.msn.com'}  
3:  }  

Step 7: Replace 'SP.Data.RecordsListItem’ with ListItemEntityTypeFullName of the list. We can use "<Site URL>/_api/web/lists/GetByTitle('<List Name>')?select=ListItemEntityTypeFullName" endpoint to know ListItemEntityTypeFullName for a specific SharePoint List.

Step 8: Replace 'Address' with the name of your  hyperlink column name

Step 9: Replace 'Description' text with the label/description text you want for the link

Step 10: Replace https://www.msn.com with your hyperlink.

Output

When a new item is created in the SharePoint List, the Hyperlink Field value gets populated with Description from Power Automate (with values configured in Power Automate).

Hope this helps!

If you have any questions you can reach out our SharePoint Consulting team here.

How to covert out of the box (OOTB) Power Apps Dropdown to Checkboxes?

Overview:

We implemented several Power Apps forms (customized SharePoint Forms) for a consulting firm based out of Alpharetta, Georgia, United States. When we customize our SharePoint form having a choice column, Power Apps represent the choices as a dropdown. For one of the forms we implemented, the requirement was to display checkboxes instead of a dropdown list for a choice column. So, how can we achieve this requirement? Let’s get started!

1. Open your customized SharePoint Power Apps form.
2. We need to add Checkboxes in the same data card as the dropdown. To add a checkbox, go to the insert menu and add a checkbox. Add checkboxes based on the # of choice value from the dropdown.
   
   
   
   
   
3. Now, we need to create one group for all checkboxes. In this group add default property using the below code.
   

    Self.Text in DataCardValue2.SelectedItems.Value  

   
   
   
   
4.  For each Checkbox, we need to execute the below piece of code on both "OnCheck" and "OnUncheck" events. Add the following code to the "OnCheck" and "OnUncheck" events. The code is same for both the events for all the checkboxes.
   

    ClearCollect(ddtocb,  
    If(Checkbox1.Value,Checkbox1.Text),  
    If(Checkbox1_1.Value,Checkbox1_1.Text),  
    If(Checkbox1_2.Value,Checkbox1_2.Text),  
    If(Checkbox1_3.Value,Checkbox1_3.Text),  
    If(Checkbox1_4.Value,Checkbox1_4.Text),  
    If(Checkbox1_5.Value,Checkbox1_5.Text),  
    If(Checkbox1_6.Value,Checkbox1_6.Text),  
    If(Checkbox1_7.Value,Checkbox1_7.Text),  
    If(Checkbox1_8.Value,Checkbox1_8.Text),  
    If(Checkbox1_9.Value,Checkbox1_9.Text),  
    If(Checkbox1_10.Value,Checkbox1_10.Text),  
    If(Checkbox1_11.Value,Checkbox1_11.Text)  
    );  
    ClearCollect(ddtocb,Filter(ddtocb,!IsBlank(Value)))  
   

   
   

   

   
   
5. Now, select dropdown and open the DefaultSelectedItems property. Pass collection created in the above step using the following code snippet.
   

    If( IsEmpty(ddtocb),Parent.Default,ddtocb)  
   

   
   
   
   
6.  Select final Data Card and change Update property using the following code snippet.
   

    If( IsEmpty(ddtocb),ThisItem.'<Field Name>',DataCardValue2.SelectedItems)  

   
   
   
   
7. At last, change the dropdown’s visible property to make it false.
   

Conclusion:

This is how we can convert our dropdowns to checkboxes.

If you have any questions you can reach out to our SharePoint Consulting team here.

Dynamically storing a value in a multi-valued lookup column in a SharePoint List/Library using Power Automate (MS Flow)

Introduction

We implemented the automated approval process in SharePoint Online using Power Automate (MS Flow) for a Postal and Parcel company based out of Melbourne, Victoria, Australia. We came across a scenario where we needed to store/update values in a multi-valued lookup column in the SharePoint list. We are familiar with storing values for lookup columns in a SharePoint list/library using SPFx/JSOM/CSOM, but it's a bit tricky when it comes to updating a field using Power Automate. So, in this blog, we will learn how to dynamically store a value in a multi-valued lookup column in a SharePoint list/library using Power Automate.

Scenario

Here, we will be using two lists as we'll be storing values in a multi-valued lookup column based on the selection of another column and also fetching the values for that selected column from another list. For this, we will be assigning an array value of another list to this lookup column using Power Automate (MS Flow).

Approach

To store a value in a lookup column, we will associate the ID of an item with this lookup column. For easier understanding, we are going with the example lists here, below are the two lists which we will be using:

List Name: City

This is the Primary list, from where we will fetch the values and store them in the secondary list’s lookup column.

Column Name	Column Type	Comments
Title	Single Line of Text	City names will be stored
State	Single Line of Text	State associated with the city will be stored

List Name: Orders

This is the Secondary list, where we will store the multiple values from the Primary list to the lookup column “City”.

Column Name	Column Type	Comments
Title	Single Line of Text	Order details will be stored
State	Choice	All the states of the City list will be there in the choices option
City	Lookup	Multi-Lookup field with Title column of City list

 

Process

When a new item is created in the Orders list, the user will select a State in that item. The flow will be triggered when an item is created and we will find all the cities of that selected state from the City list and will store those cities in the City lookup column of the Orders list.

Follow the below steps to store value in multi-valued lookup column:

Step 1: Create a Flow for "When an item is created" and add an action for Initialize Variable. Provide the variable name "AllCities" and select Array in Type.

Step 2: Add an action for "Get items". We will get all the cities for the selected state using this action.

Step 3: Select site and list In "Get items" action and also add "filter query" to get cities of selected state only.

Step 4: Now add Apply to each action. We need to iterate through each returned items from "Get items" action and append IDs in the array variable.

Step 5: In Apply to each action, select the value of "Get items" action from Dynamic content as shown below.

Step 6: Now in Apply to each, add "Append to array variable" action.

Step 7: In the Name field, select the array variable "AllCities" which we initialized at the top. And in Value pass the JSON for ID as below format and select expression int(items('Apply_on_each')?['ID']) in a value of id.

{

“id”: int(items('Apply_to_each')?['ID'])

}

 

Step 8: Now add a new step for the Update item. Select site address and list name. Now in the Id field, select the ID from Dynamic content as shown below:

Step 9: Here Title is a required field, so we need to select the value of Title field also.

Step 10: Now In a City field, click on Switch to input array icon as shown below:

Step 11: Once we click on it, it will show the text box. Now select the array variable "AllCities" from Dynamic content as shown below:

Step 12: Save your flow and add a new item to the Orders list. While creating an item, we are not selecting the city (it's hidden from the form), we are only selecting the State.

Step 13: Once the flow is executed, we can see all city of selected state populated in the City field.

Conclusion

This is how we can dynamically store value in a multi-valued lookup column using Power Automate (MS Flow). Hope this helps!

If you have any questions you can reach out our SharePoint Consulting team here.

Exploring Microsoft Teams connector in Power Apps – Get Channel Messages

Introduction:

We recently implemented Power Apps - Canvas App that was having the integration with Microsoft Teams for Construction & Engineering company based out of Washington, United States. One of the requirements was to get the MS Teams - Channel Messages and display the same in Power Apps.

In this article, we will explore the Microsoft Teams connector available in Power Apps. The Teams connector with Power Apps opened lots of possibilities to leverage Teams capability within the Power Apps. Let's check how to get messages of a specific channel in MS Teams in PowerApps and what other actions are available using the Teams connector.

Business Scenario:

Here, first we will show all available Teams for the current logged in user. When user clicks on Teams name, this will open all channels for the current Team. Once we click on the channel name, we will be able to see all the messages from that channel on the right side section. That’s the end goal we will achieve at the end of the blog. So, now let’s get started!

Step 1: Add "MicrosoftTeams" connector in Power Apps from DataSource.

Step 2: Insert a Blank Gallery and add the below code in the Items of the Gallery.

 MicrosoftTeams.GetAllTeams().value  

Step 3: Add a label in the Gallery and show the DisplayName of the Items.

 ThisItem.displayName  

 Hence, you can see it will list all the teams that you are part of.

Step 4: Now we require all the channels of the selected Team. For that, insert another gallery and add this code into the Items of that Gallery.

MicrosoftTeams.GetChannelsForGroup(Gallery1.Selected.id).value 

Step 5: Now as we have both the Items (i.e. Team and Channel), we can get the messages of that specific selected channel. Insert a new Gallery and add this code in the Items property of that Gallery.

 SortByColumns(MicrosoftTeams.GetMessagesFromChannel(TeamsGallery.Selected.id,  
 ChannelGallery.Selected.id).value,"createdDateTime",Ascending)  

Step 6: Add a HtmlText in this Gallery and place the below code in the HtmlText

 ThisItem.body.content  

As the message which is returned is in the form of HTML, we need to use the HtmlText element for displaying messages. The final output may look something like shown in the below image:

Here are some of the properties which we can show along with the channel messages:

•         From

•         User

•         ThisItem.from.user.displayName

•         ThisItem.from.user.id

•         Device

•         ThisItem.from.device

•         Attachments

•         ThisItem.attachments

•         Mentions

•         ThisItem.mentions

•         Reactions

•         ThisItem.reactions

•         Dates

•         ThisItem.createdDateTime

•         ThisItem.lastModifiedDateTime

•         Importance

•         ThisItem.importance

As a final outcome, we are able to generate the screen like below.

References:

Please check the following link to get more information about different available methods for Microsoft Teams connector for Power Apps.

https://docs.microsoft.com/en-us/connectors/teams/#actions

Conclusion:

This is how we can use the Microsoft Teams connector for fetching Channel messages in Power Apps. Happy Power Apping!

If you have any questions you can reach out our SharePoint Consulting team here.

How to Run Power Automate with Admin Consent from Power Apps?

Introduction

We recently converted the InfoPath forms to Power Apps forms for an Engineering & Construction company located in Boston, Massachusetts, United States. In one of the forms we converted, the requirement was to populate a dropdown list with the members of a SharePoint Group as values. 

Problem Statement

To populate a dropdown in Power Apps with SharePoint group users, we added Microsoft Power Automate to our Power Apps application with “Send an HTTP request to SharePoint” action. But when a user who is not a part of the group nor has Full Control permission opens the form, the flow would fail. 

Error in Power Apps:

Error in Power Automate (MS Flow): 

Error: Access denied. You do not have permission to perform this action or access this resource.

Root Cause:

Flows started by Power Apps run in the context of the user who is running the Power Apps. So the problem occurs when a user who is not a part of the SharePoint Group nor has Full Control permission opens the form. In this case, the flow would fail.

  

Resolution

So we decided to run the flow in the context of SharePoint Add-ins. When the add-in-only policy is used, SharePoint checks only the permissions of the add-in principal. Authorization checks succeed only if the current add-in has sufficient permissions to perform the action in question, regardless of the permissions of the current user (if any).

The entire procedure covers the following steps:

1. Register SharePoint Add-ins 
2. Create Power Automate flow to get users from SharePoint Group
3. Configure flow in PowerApps

Step-1: Register SharePoint Add-ins

Please check This Article which describes the steps to register App in SharePoint. Client ID and Client Secret availed from this process will be used in Step 2.

 

Step-2: Microsoft Flow to get SharePoint Users

1. Create an instant cloud flow with Power Apps trigger as shown in the below picture.
   
   
   
2. Now, instead of “Send an HTTP request to SharePoint” action we will be using “HTTP” action.
   
   
   In the HTTP action fill the details as per below:
• Method: Post
• Replace the Tenant ID in the URI.
  URI: https://accounts.accesscontrol.windows.net/TenantID/tokens/OAuth/2
• Replace the highlighted content in the body.
  Body: grant_type=client_credentials&client_id=ClientID@TenantID&client_secret=ClientSecret&resource=00000003-0000-0ff1-ce00-000000000000/TenantName.sharepoint.com@TenantID
• Headers:
  Key: Content-Type
  Value: application/x-www-form-urlencoded
  
  
3. Now in the next step, we will use Compose action. Pass the access token that we get from the HTTP action in the inputs.Formula: body(‘HTTP’)[‘access_token’]
   
   
   
4. In the next step, we will again use the HTTP action.
   
   
   
5. In the HTTP 2 action fill the details as per below:
• Method: Get
• Replace the highlighted content in URI. Set the group name to the SharePoint Group of which you want the users.
  URI: https://tenantname.sharepoint.com/sites/sitename/_api/web/sitegroups/getbyname('GroupName')/users
• Headers:
  Key: Accept; Value: application/json;odata=verbose
  Key: Authorization; Value: Bearer and the output of compose action as shown in the above image. 
  
  
6. Now we will use the Select action to get only the specific properties from all the elements of the array and form a new array as shown in the below picture.
• Pass the results of “HTTP 2” action in the From as body('HTTP 2')['d']['results'].
• We need to map the properties to make the array format as required in Power Apps. You can check the properties from the json generated in the HTTP 2 action. Pass the keys as shown in the image and values as item()?['PropertyName'].
  
  
  
  
  
  
  
7. The last step of the Flow is Response action. Here we pass the output of the Select Action.
   
   
   
8. Click on Show advance option and select Generate from sample. Here, we need to insert a sample JSON payload. Add the following JSON structure:
   

    [  
     {  
      "Email": "",  
      "Claims": "",  
      "DisplayName": "",  
      "Department": "",  
      "JobTitle": "",  
      "Picture": ""  
     }  
    ]  
   

Step-3: Configure the Flow in Power Apps

1. Now we will store the response generated by the flow in a collection by passing ClearCollect(CollectionName,'FlowName'.Run()); on the “OnVisible” property of the “FormScreen”.
   
   
   
2. Pass the collection on the “Items” property of the DataCardValue as shown in the below picture.
   
   
   

Conclusion

This is how we can call our Power Automate flow with admin consent from PowerApps.

If you have any questions you can reach out to our SharePoint Consulting team here.