How to create HTML table for Get Items in Power Automate?

Summary

A question from user is that how to send multiple pending items from a list in an HTML table format.

The desired results in email is the following with ID, Title and Clickable link item.

The email from the Power Automate Flow.

Step by Step Solution

To make the flow simple I will just focus on how to get the above HTML table after getting items from the list let’s make a Manually triggered flow.

Step # 1 Create a manually triggered flow get all items from the FAQ list.

In your case you may have some more filter to get the items from the list, but for simplicity I am getting all items from an FAQ list.

Get items from an FAQ list

Step # 2 Use Create HTML table action to create table.

Map ID, Title and Link columns. See below
ID is mapped => item()?['ID']
Title is mapped to => item()?['Title']
Link is mapped to => concat('<a href="',item()?['{Link}'],'">Click Here</a>') 

# note the link will generate string like
<a href="https://blah blah blah">Click Here</a>

Step # 3 Now is the hard part, the expression to replace encoded html tags.

This is a required step, the output of the Create HTML Table does not put the HTML element as tags instead it put as encoded strings.

Compose to replace &lt, &gt;, &qout; &amp; with < > ” & respectively. Also add table border and padding.
# if you simply want to copy the expression use the following, but see below 
# for the explanation of replace.

replace(replace(replace(replace(replace(body('Create_HTML_table'), '&lt;', '<'), '&gt;', '>'),'&quot;','"'),'&amp;','&'),'<table>','<table border="1" padding="5">')

# Note the following is the breakdown of the above expression, 
# there are five replace expressions for '&lt;', '&lt;', '&quot;', '&amp;' and '<table>'.

replace(
  replace(
    replace(
      replace(
        replace(
           body('Create_HTML_table'), 
           '&lt;''<'
        ), 
        '&gt;' '>'
      ),
      '&quot;',
      '"'
    ),
    '&amp;',
    '&'
  ),
  '<table>',
  '<table border="1" padding="5">'
)

Step 4 Finally use the output of the Compose to send and email.

Send an email using the output of the compose.

Result

The Compose output will have properly formatted table which can be sent in an email body.

My inspiration is from the following awesome article.

Formatting HTML Tables in Flow | April Dunnam – SharePoint Siren

Posted in Uncategorized | Leave a comment

How to develop SPFX using Docker Container?

Summary

Wouldn’t it be nice if you don’t have to install required SPFX tools to do the development?

Yes, answer is to use Docker. The Docker provides a container with required software pre installed. To use the container you should have an image.

Think of Docker Image as a Class and Docker Container is like objects. The images are template to run the container(s).

Step by Step Solution

The prerequisite is that you will need to install the Docker desktop on your host machine. To install Docker desktop go here.

Step # 1 Run the docker to launch container.

# use the following command in the PowerShell command prompt.
docker run -it --rm --name spfx-dev -v ${PWD}:/usr/app/spfx -p 5432:5432 -p 4321:4321 -p 35729:35729 dannyjessee/spfx
-it–interactive Keep STDIN open even if not attached
–rmAutomatically remove the container when it exits
–nameAssign a name to the container
-vBind mount a volume
-pPublish a container’s port(s) to the host
Map host ports 5432, 4321 & 35729 to the container
dannyjessee/spfxThe image name
Docker run command options

Step # 2 Check the version of the installed software on the container.

npm ls -g --depth=0

Step # 3 Create SPFX using Yo

Create the SPFX Webpart

Step # 4 Some changes are required in the created solution to work with Docker.

Changes for SharePoint Framework v1.11 project to support running the local workbench from within a Docker container. The following files needs the change.

config\serve.json, add below line 3:

"hostname": "0.0.0.0"

config\write-manifest.json, add below line 4:

"debugBasePath": "https://localhost:4321"

node_modules\@microsoft\sp-build-web\lib\SPWebBuildRig.js, replace lines 96-98 with:

if (!spBuildCoreTasks.writeManifests.taskConfig.debugBasePath) {
     spBuildCoreTasks.writeManifests.mergeConfig({
         debugBasePath: ${serve.taskConfig.https ? 'https' : 'http'}://${serve.taskConfig.hostname}:${serve.taskConfig.port}/
     });
}

Step # 5 Build and debug

run gulp serve

Posted in Docker | Leave a comment

How to export Data Loss Prevention (DLP) rule package for tenant?

Summary

I looked at the custom DLP config file in XML. This will allow to create Custom DLP rule. But I wanted to see if I can export all existing out of the box rules file in an XML. This will allow to study and create a custom new DLP Policy.

Step by step solution

Step # 1 Install and import the ExchangeOnlineManagement module in PowerShell

PS C:\DLPRules> Install-Module ExchangeOnlineManagement
PS C:\DLPRules> Import-Module ExchangeOnlineManagement

Step # 2 Connect to the portal with global admin user using Connect-IPPSSession

PS C:\DLPRules> Connect-IPPSSession
WARNING: Your connection has been redirected to the following URI:                                                      
"https://gcc02b.ps.compliance.protection.outlook.com/Powershell-LiveId?BasicAuthToOAuthConversion=true;PSVersion=5.1.19 041.610"                                                                                                                PS C:\DLPRules> Get-DlpSensitiveInformationTypeRulePackage                                                              

Step # 3 List the Rule pack of the tenant

PS C:\DLPRules> Get-DlpSensitiveInformationTypeRulePackage
 Invariant Name         Localized Name         Publisher             Encrypted 
 --------------         --------------         ---------             --------- 
 Microsoft Rule Package Microsoft Rule Package Microsoft Corporation False     

Step # 4 Using the above localized name “Microsoft Rule Pacage” get package info

PS C:\DLPRules> $rulepak = Get-DlpSensitiveInformationTypeRulePackage -Identity "Microsoft Rule Package"

Step # 5 Now export the package info to an XML file

PS C:\DLPRules> Set-Content -Path "C:\DLPRules\ExportedRulePackage.xml" -Encoding Byte -Value $rulepak.SerializedClassificationRuleCollection

The file should be created now like the following.

ExportedRulePackage.xml

PS C:\DLPRules> dir
 Directory: C:\DLPRules
 Mode                 LastWriteTime         Length Name
 ----                 -------------         ------ ----
 -a----         12/7/2020   2:23 PM        2041460 ExportedRulePackage.xml
 PS C:\DLPRules>
Posted in DLP | Leave a comment

How to send approval requests to Distributed List (DL) Group in Power Automate

Problem

The Microsoft Flow Approvals connector does not support sending approval requests to groups today or distributed groups.

To demonstrate the issue I have a sample distributed list (DL) group ‘SomeO365DLGroup’.

The DL group has the following members.

Members of the DL group

Created a sample manually triggered flow to send and approval with the DL ‘someo365DLGroup@GOV019539.onmicrosoft.com’. I got the following error.

{
  "error": {
    "code": "InvalidApprovalCreateRequestAssignedToNoValidUsers",
    "message": "Required field 'assignedTo' contained no valid users in 
the organization"
  }
}
Error in the flow

Step by Step Solution

There is an alternative approach, first to get list of members email from the DL group. We will use the “Office 365 Groups” connector with “List group members” action. The action requires to use the group ID.

Step # 1 : Using Azure AD portal get the group id of the distributed list.

To get group id of the distributed list

Step # 2 : Create a Manually trigger with a EmailArray variable as Array datatype.

Step # 3 : In the manually triggered Power Automate make a call to the ‘List group members’

‘List group members’ action.
The output of the above action is.
[
  {
    "@odata.type": "#microsoft.graph.user",
    "id": "17d7b3e9-7e7c-46e5-b16f-2baef2e6cd46",
    "businessPhones": [
      "+1 732 555 0102"
    ],
    "displayName": "Jordan Miller",
    "givenName": "Jordan",
    "jobTitle": "Auditor",
    "mail": "JordanM@GOV019539.OnMicrosoft.com",
    "officeLocation": "19/3123",
    "surname": "Miller",
    "userPrincipalName": "JordanM@GOV019539.OnMicrosoft.com"
  },
   ... other objects are removed brevity
]

Step # 4 : Loop through the output of the above action to get the mail of the each member. Add the mail address in the EmailArray variable.

Loop through the output to get mail address

Step # 5: Finally use the EmailArray variable to create a string with semicolon delimited string.

Use join expression to get the “Email addresses, separated by a semicolon (;)”

Step # 6: Use the output of the above step as the Assigned To field.

Use the output of the Compose to the “Assigned to” field.

Result

The alternate approach to get the list of emails to use in the Assigned to field resolved the issue of the distributed list group mentioned.

Posted in Power Automate | Leave a comment

SharePoint HTTP connector: Send a no-reply email in Power Automate Flow

Problem

There are many blog posts for sending no-reply email from Power Automate.

The question from the customer is how to create the JSON object body ‘dynamically’ to put in the “SharePoint HTTP connector”?

The following is the sample list with Subject, Body and TO (with multi-person) fields data. How to read this information and create the JSON body for the no reply email?

Sample list for sending and email using “SharePoint HTTP connector”
Note: This is the format of the JSON object you need to pass
Please note the To field is a JSON array.

{
  "properties": {
    "Subject": "This is a new request from test system.",
    "To": [
      "BobK@GOV019539.OnMicrosoft.com",
      "DebraB@GOV019539.OnMicrosoft.com",
      "JordanM@GOV019539.OnMicrosoft.com"
    ],
    "Body": "**removed for brevity***"
  }
}
Note: In the above 'To' is a JSON array.

Step by Step Solution

This solution will required some basic understanding of the JSON object and JSON array.

We will make use of the setProperty expression to set the property of the JSON object.

IMPORTANT: The ‘Set Variable’ action does not allow self reference, in other words you can not do Index = Index + 1. See here for the details. The alternative approach is to use the Compose action to manipulate the variable value and then assign the output of the Compose in the Set Variable.

Step # 1 Define a manual trigger, two variables and a Get Item action.

JSON Object and JSON Array variables are defined.

Step # 2 Set the ‘Subject’ property to the ‘PropertiesObjectVar’ variable

Set the Subject Property using Compose action and use the Set Variable action
Note: First time 'PropertiesObjectVar' variable is an empty object.

setProperty(
          variables('PropertiesObjectVar'),
          'Subject',
           outputs('Get_item')?['body/Subject']
)

Step # 3 Set the ‘To’ property to the ‘PropertiesObjectVar’ variable

Now there is a trick here, we need the JSON Array object. In the Apply Each action first get all values in the Array variable ‘ToFieldArrayVar’.

Apply Each action to extract Email and append to Array variable ‘ToFieldArrayVar’

Using the output of the array variable set this to the JSON object ‘PropertiesObjectVar’

Set the To property of the JSON Object
Note: The 'PropertiesObjectVar' variable is not with 'Subject'.
After the set Property it will have 'To' property

setProperty(
          variables('PropertiesObjectVar'),
          'To',
           variables('ToFieldArrayVar')
)

Note: The array variable is assigned to 'To' property.

Step #4 Set the ‘Body’ property to the ‘PropertiesObjectVar’ variable

Set the Body property for the JSON Object
Note: The 'PropertiesObjectVar' variable will have 'Subject' and 'To', the following call with add 'Body'

setProperty(
          variables('PropertiesObjectVar'),
          'Body',
           outputs('Get_item')?['body/Body']
)

Step #5 Finally construct the Final Object as required for the no reply email.

A Compose action to create a final JSON object.
Note: setProperty trick is start with empty JSON object and add 'properties' property to it with a above constructed variable.

setProperty(
       json('{  }'), 
       'properties', 
        variables('PropertiesObjectVar')
)

Step #5 Now use Send HTTP to send an email.

Note: Copy the following in the Uri section

_api/SP.Utilities.Utility.SendEmail

Note: copy the following in the Headers section
{
   "Content-Type": "application/json;odata=nometadata",
   "Accept": "application/json;odata=nometadata"
}

Result

Using the setProperty, Compose and some tricks this posts may help you with constructing the JSON Object dynamically.

FYI the list of properties for the EmailProperties.

Posted in Power Automate | Leave a comment

How to delete Recoverable Items folder of the mailbox?

Problem

This was a customer requirement, to delete a “Purge” folder using MS Graph API. There was no mention of purge folder when I checked mailFolder resource type documentation. The closest I found was ‘recoverableitemsdeletions’, I did not even know what ‘purge’ folder meant. So I took some help from a friend Boris Lokhvitsky (an Exchange SME).

Step By Step Solution

To protect from accidental or malicious deletion and to facilitate discovery efforts commonly undertaken before or during litigation or investigations, Exchange Server and Exchange Online use the Recoverable Items folder.

Each user mailbox is divided into two subtrees: 

  1. The IPM (interpersonal messaging) subtree
  2. The non-IPM subtree

The IPM tree is what user can see in Outlook, but the non-IPM can not be seen by the users.

The following few steps are needed to make the mailbox for a litigation hold. The EXO will create the non-IPM folders like ‘Purge’ folder and more. After that you will need to create Azure AD app with MS Graph permission “Mailbox.ReadWrite” to use in the PowerShell script.

Step # 1 Install and import the Exchange PowerShell module

# In PowerShell command prompt install and import the module.
 Install-module ExchangeOnlineManagement

 Import-module ExchangeOnlineManagement 

Step # 2 Connect to Exchange Online

 $userCred = Get-Credential

 Connect-ExchangeOnlie -Crendential $userCred -ShoProgress $true 

Step # 3 Set litigation hold on a mailbox

In this step run the following command for a specific mailbox to set a litigation hold.

Set-Mailbox admin@CRMbc755713.onmicrosoft.com 
            -LitigationHoldEnabled $true `
            -LitigationHoldDuration 2555 

Step # 4 Create an Azure AD app with “Mailbox.ReadWrite” MS Graph permission with admin consented. Please make a note of the Client ID and Client Secret to use in the next steps in PowerShell.

“Mailbox.ReadWrite” MS Graph permission with admin consented

Step # 5 Get a token from Azure AD

The following call with return an Access Token.

# make a call to Azure AD with Azure AD App in a body 
POST https://login.microsoftonline.com/{TenantID}/ouath2/v2.0/token 
# body
 grant_type=client_credentials
 &client_id={appid}
 &client_secret={appSecret}
 &scope=‘Mail.ReadWrite’

Step # 5 MS Graph GET call using Bearer

 $headers = @{}
 $headers.Add("Authorization", $("{0} {1}" -f 
 $responseAuth.token_type, $responseAuth.access_token))

 https://graph.microsoft.com/v1.0/users/{USER_UPN}/mailFolders   

The above script steps are located here for you to use.

Results

The purge folder deletion is a tricky step. I hope that it is clarified with this blog post. If there is any comments or questions please post them here so I can improve this blog post.

Refer the following article for an additional details.

Clean up or delete items from the Recoverable Items folder

Posted in Exchange Online | Leave a comment

Part Two – Using Power Automate show the most recent comment from SharePoint text fields instead of “View Items”

Problem

As my previous blog post on the same topic, what if there is an existing list which has the multi text ‘Comments’ field.

How to take the last comment and copy in the ‘MostRecentComments’ field for all items in the existing list?

Step by Step Solution

Step # 1 – Create a Manually Trigger flow.

Step # 2 – First let’s get all items IDs (plus required fields) from the list using ‘Send an HTTP request to SharePoint’.

*** Uri ****
_api/web/lists/GetByTitle('TestMirrorMultiText')/items?$select=ID

*** Headers ***
{
   "Accept": "application/json;odata=nometadata"
}

***** The output of the action will be an Object with an Array ***
{
  "value": [
    {
      "Id": 5,
      "ID": 5
    },
    {
      "Id": 6,
      "ID": 6
    }
  ]
}

Step # 3 Iterate over each array item and get the Comments field with “versions“.

Note: Apply each of the “value” property of the above step and it is an array.

**** Uri *** Note: we just need multi text Comments field and it's versions.

_api/web/lists/GetByTitle('TestMirrorMultiText')/items(@{items('Apply_to_each')?['ID']})/versions?$select=Comments

*** Input Headers ***
{
   "Accept": "application/json;odata=nometadata"
}

*** Output for each ID will be like following ***
Note: There will be nulls, we need to eliminate nulls in the next step.
{
  "value": [
    {
      "Comments": null
    },
    {
      "Comments": "This is a test2"
    },
    {
      "Comments": "Lorem Ipsum ...."
    }
  ]
}

Step # 4 Within the ‘Apply Each’ loop filter the null from the Comments array.

*** From ***
Note: we need to get the Value array
outputs('Send_an_HTTP_request_to_SharePoint_2')?['body']?['Value']

*** In the condition ***
Note: items is a special function to access each item.
item()?['Comments']
** The output of the Filter will be with Comments with *no* null values 

Step # 5 Final step in the ‘Apply Each’ to update the list.

*** The MostRecentComments field will be set with the first value 
of the filtered array ***

body('Filter_array')?[0]?['Comments']

Results

The final result will be have last comment in the ‘MostRecentComments’ field. This blog post is a technique to update your all items in the existing list. Once you have done that you will not have to run this flow. From my previous blog post the item added and modified event will update the ‘MostRecentComments’ correctly.

Posted in Power Automate | Leave a comment

Using Power Automate show the most recent comment from SharePoint text fields instead of “View Items”

Problem

The customer asked how to do the steps described in the following article in Power Automate? The article uses SharePoint Designer and SharePoint Workflow.

SHOW THE MOST RECENT COMMENT FROM SHAREPOINT TEXT FIELDS INSTEAD OF “VIEW ITEMS”

The scenario is the above list contains two columns ‘Comments’ and ‘MostRecentComments’. The ‘Comments’ column has multiple text but shows it as ‘View Entries’. The SharePoint view shows that because the list is created as ‘version’ enabled list. Every text entered in the ‘Comments’ column it is stored as a version including null.

The common technique is to last comments entered in the list is to add another column called ‘MostRecentComments’. Using the flow as an item added or modified copy the *last* comment entered to the MostRecentComments field.

Step By Step Solution

Step #1 Create a new Power Automate Flow with the Trigger as ‘When an item is created or modified’. Add your Site Address and List Name to the Trigger.

Step #2 Next you will add a trigger condition by clicking on ‘…’ and ‘Settings’. In the Trigger Conditions you will add the following line. Please make a note don’t forget ‘@’ before the expression.

@not(equals(triggerOutputs()?['body/Comments'],null))

Step # 3 Now update the ‘MostRecentComents’ with the last comment added by the user.

Results

As you can see the results the flow runs when the new item is created or modified, the flow updates the ‘MostRecentComments’ field with the last comment. The flow will also skip if user do not add anything in the comments as the trigger condition checks for the null value in the comments.

Please write your comments if this helps you.

Posted in Power Automate | Leave a comment

How do I see current Theme tokens applied to the SharePoint site?

Problem

Sometimes you may want to know what types of Theme tokens applied to the current site.

Let’s say you have the following site with the

Step By Step Solution

It is a simple step, just F12 or Go to Developer Tools on your browser an type.

window.__themeState__.theme

You will see all the applied theme’s templates.

Result

Using the console debug window.__themestate__.theme you were able to see all applied themes to the site.

Posted in SharePoint | Leave a comment

How to eliminate defining large set of variables in Power Automate?

Problem

Here is the scenario, let’s say that in the workflow you may need large set of variables to hold the data as a variable, and set variable with new values etc. Your first approach is to create a multiple ‘Initialize variable’ actions and wherever you make the change to the variable you will use ‘Set variable’ action.

The ‘Initialize variable’ ad ‘Set variable’ actions.

Well the problem is you will end up with lot of variables actions and it may get laborious to rename Initialize and Set actions. There is a better ways to do the same using JSON object.

Step by Step Solution

The first steps is you need to define an object with all the variables you need, e.g. the following object with variables like FirstName, LasteName, Age etc.

An object variable ‘WorkflowVarObject’ with all required variables as properties.
--- In this JSON object each variables is with the data type
--- FirstName, LastName etc. are 'String' type
--- Age is a integer type
--- Salary is float type
--- Address is an object
--- Jobs is an array

{
"FirstName": "",
"LastName": "",
"Address": {
"City":"",
"State":"",
"Zip":""
},
"Age": 0,
"Salary": 0.0,
"Jobs":[ "Job1", "Job2" ]
}

Now second step is you want to change the ‘WorkflowVarObject’ variable’s property such as FirstName, LastName etc. You can add the Set variable action with the SetProperty function.

Use SetProperty function to change the ‘FirstName’ property of the ‘WorkflowVarObject’
setProperty(variables('WorkflowVarObject'),'FirstName', 'Pankaj')

But there is a problem with above action, when you run the flow to test you will get the following error.

An error “Self reference is not supported when updating the value of variable ‘WorkflowVarObject’.”

So to workaround this first put the above expression in a compose and then set output value of the compose to set variable.

Add Compose action before setting the master JSON object variable ‘WorkflowVarObject’.

Result

The solution allowed to eliminate the multiple initialize variable actions. It allowed us to overcome an issue of the updating of the self reference.

There is a UserVoice for self reference issue if that is resolved you can eliminate the extra compose action.

Posted in Power Automate | Leave a comment