Power Automate – Execute a SQL Query Action Step

In this blog we will have a look at Power Automate SQL connector action step ‘Execute a SQL Query’.

There are no dynamic properties available when you use Power Automate SQL Query Action step.

As you can see above there are no dynamic properties available for Execute a SQL Query action step. I also tried to filter the Dynamic Content text using the SQL connector action step name ‘SQL’. So how do we get the value of SQL action step? Wish there was a body dynamic property available.

Next I ran the flow and checked the run history. Below is the JSON result I have from SQL action step

{

“ResultSets”: {
“Table1”: [
{ “name”: 1 }
]
},

“OutputParameters”: {}

}

Here I am after name column value from the address table. So to get the name value we need to use the expression below.

body(‘SQL’)?[‘ResultSets’]?[‘Table1’]?[0]?[‘name’]

Above expression body(‘SQL’) is the name of the previous action step name called ‘SQL’. Then the rest are related to the result sets of JSON output from SQL action step.

Tip: If you can’t find the dynamic property always run the flow and check the run history. Then use the body(‘name of the action step’) expression to retrieve the values. Use underscores for spaces between names. So if my above SQL action step is called ‘Retrieve SQL’ then my body expression will be body(‘Retrieve_SQL’)

Hope it helps.

Thanks

Posted in #MicrosoftFlow, Microsoft, Microsoft Flow, Microsoft Power Automate, No Code, Power Automate, Power Automate SQL Connector, Power Platform, SQL | Leave a comment

Power Automate – How to remove a user sharing permission from a SharePoint Library folder

By default SharePoint list and libraries inherit permissions from parent site. You can break the inheritance and create unique permissions on a particular list or library. Suppose you have a document library with multiple folders or files where you need to share specified files or folders to a particular user. In these scenario you need break the inheritance and use the share option to give permission to that particular user. In this article I will explain how to remove a user sharing permission from a specified SharePoint library folder using Power Automate (https://powerautomate.com).

Background

I have a document library named ‘TestAjDocLib’ and a folder called ‘A1’. Custom permissions are set for this document library. Folder ‘A1’ is shared with a particular user. I want to remove this users shared permission from folder ‘A1’.

Power Automate Build

There are no out of the box SharePoint connectors to do this job using Power Automate. The only option we have is to use the REST API method.

_api/web/GetFolderByServerRelativeUrl(‘TestAjDocLib/A1′)/ListItemAllFields/RoleAssignments/GetByPrincipalId({User_Id’})

First we need to find the user principal id then execute the http DELETE post using the above SharePoint REST API. To find the user principal id here I am using the user email address.

Steps

REST API to get user principal id:

_api/web/siteusers/getbyEmail(‘user@email.com’)

Next we need to use Parse JSON action step to extract the user principal id.

Following is the JSON schema I used above.

{ "type": "object", "properties": { "d": { "type": "object", "properties": { "__metadata": { "type": "object", "properties": { "id": { "type": "string" }, "uri": { "type": "string" }, "type": { "type": "string" } } }, "Alerts": { "type": "object", "properties": { "__deferred": { "type": "object", "properties": { "uri": { "type": "string" } } } } }, "Groups": { "type": "object", "properties": { "__deferred": { "type": "object", "properties": { "uri": { "type": "string" } } } } }, "Id": { "type": "integer" }, "IsHiddenInUI": { "type": "boolean" }, "LoginName": { "type": "string" }, "Title": { "type": "string" }, "PrincipalType": { "type": "integer" }, "Email": { "type": "string" }, "Expiration": { "type": "string" }, "IsEmailAuthenticationGuestUser": { "type": "boolean" }, "IsShareByEmailGuestUser": { "type": "boolean" }, "IsSiteAdmin": { "type": "boolean" }, "UserId": { "type": "object", "properties": { "__metadata": { "type": "object", "properties": { "type": { "type": "string" } } }, "NameId": { "type": "string" }, "NameIdIssuer": { "type": "string" } } }, "UserPrincipalName": { "type": "string" } } } } }

Finally use the below REST API step to remove the user permission from a particular folder.

Using the above method we can remove the users custom permission from a custom folder using Power Automate (aka Microsoft Flow).

Thanks for reading my post.

Posted in #MicrosoftFlow, Microsoft Flow, Microsoft Power Automate, Power Automate, Uncategorized | Tagged , , , , , , , | 2 Comments

Power Automate – How to find a folder exists (Dynamic) in SharePoint?

msflowsmallicon This is something I noticed from community often discussed about SharePoint List Folder can’t have dynamic folder mapping. In this blog I will show you how can we use dynamic folder mappings using SharePoint List Folder action. Action step List Folder file identifier represents the path of a valid SharePoint folder name. If folder not found generally flow will fail and stop the execution. Follow below steps how to determine whether the folder exists or not and continue the flow execution.

I have a SharePoint document library called ‘aj’. Here I have couple of folders called Test1, Test2 & Test3.

The flow we are going to build is pass the dynamic folder mappings, find whether a folder exists in SharePoint or not. If folder exists, then get the file contents.

First step is the trigger and here I am using a manual trigger.

Next step is initialising two variables. One is used for identifying whether a folder exists or not (Boolean) and other is a string where we pass the folder name.

Above I have hardcoded the folder name (SharePoint library name/Folder name).

Next, we need to use the SharePoint List Folder action. Here I am using folder name variable under the file identifier setting as dynamic content. If required, you can also use the SharePoint URL mapping via variable.

Above step set the configure run after as follows:

Once it set click done.

Next step we need to add a parallel branch.

Here we need to add two steps under the parallel branch.

  1. First variable sets the IsFolderExists to true.

Also set the configure run (Select the three dot of Set variable True) after as follows:

  1. Second variable (parallel branch action) is as follows:

Set the configure run after as follows:

Finally, we have two parallel actions like below.

Next step is to check the condition whether SharePoint folder found or not?

For the above step set the configure run after as follows:

Remember to set all true except ‘has timed out’ for both set variable True or False. Select using the mouse for each Set variable (True/False) and select the options separately.

Once both variables are set then click done.

Final step is to get the name of the files from the folder under the Yes condition.

Here are my run results:

  1. A valid SharePoint folder exists Folder name = /aj/Test1

  2. Folder does not exist. Folder name = /aj/Test999

Thank you for reading.

Posted in Uncategorized | 5 Comments

Microsoft Flow – NASA API Integration

 msflowsmallicon In this blog I will explain how to call an API (Application Programming Interface) using Microsoft Flow.

Here I am using NASA API Astronomy picture of the day (APOD). The plan is to retrieve the picture of the day using the API and store in a SharePoint document library.

References:

NASA API Website https://api.nasa.gov/

API Method: https://api.nasa.gov/planetary/apod. Below are the related query parameters for the APOD API Get method.

Above date and hd parameters are optional. To generate an API Key you need to register your name, email and follow the instructions from the website (https://api.nasa.gov/).

Now we can start building the Flow. Here I am using a scheduled trigger which triggers every day.


Next, we need to use the HTTP flow step. Here copy the api_key which you received as part of the registration process. You can also extend the query parameters using date and hd which are optional.


Next we need to use Parse JSON Results for the above HTTP response.


JSON schema is as follows:

{


“type”: “object”,


“properties”: {


“date”: {


“type”: “string”

},


“explanation”: {


“type”: “string”

},


“hdurl”: {


“type”: “string”

},


“media_type”: {


“type”: “string”

},


“service_version”: {


“type”: “string”

},


“title”: {


“type”: “string”

},


“url”: {


“type”: “string”

}

}

}

Next, we need to get the image and store in SharePoint.

To get the image content I am using another HTTP flow method and passing the image hdurl. For the API call we are using the GET method.


Next I am using a compose flow step to get the filename of the image using JSON response url property.


Above expression is as follows:

last(split(body(‘Parse_JSON_Results’)?[‘url’],’/’))

Finally, we have all the necessary attributes to create the file in SharePoint.


Here is the final preview of the flow.



 Thank you for reading.

Posted in #MicrosoftFlow, API, API Integration, Sharepoint | Leave a comment

Microsoft Flow – Retrieve All Files from SharePoint Library Folder & Send Email Attachment

msflowsmallicon In this blog I will explain how to retrieve all files from a specified SharePoint folder and send email as an attachment.

Every flow needs a trigger to start with so here I am using a manual trigger.

Next step I am using flow step SharePoint List Folder which returns files contained in a SharePoint folder.

The return response is array of BlobMetadata which contains the below details.

Name Path Type Description
ItemId ItemId integer The value that can be used to Get or Update file properties in libraries.
Id Id string The unique id of the file or folder.
Name Name string The name of the file or folder.
DisplayName DisplayName string The display name of the file or folder.
Path Path string The path of the file or folder.
LastModified LastModified date-time The date and time the file or folder was last modified.
Size Size integer The size of the file or folder.
MediaType MediaType string The media type of the file or folder.
IsFolder IsFolder boolean A boolean value (true, false) to indicate whether or not the blob is a folder.
ETag ETag string The etag of the file or folder.
FileLocator FileLocator string The filelocator of the file or folder.

Next, we need to collect the file name and contents. For this we need to declare a variable type array called FilesArray.

Next step we need to construct a loop for reading all the SharePoint list folder contents. List folder contents will have files and folders. So within the loop I am checking whether the content is a folder or not. See below image.

Next, we need to construct rest of the logic under the yes condition for getting all the file names & contents to array variable which we declared earlier. To get the file content use flow step Get file content using path. Path property is available from the above step using Blobmetadata content. Append to array will have two properties ContentBytes and Name. See format below.

The expression for file ContentBytes is as follows:

body(‘Get_file_content_using_path’).$content

Note: There is a property called FileContent available from the Dynamic content, but this will result in error for flow step send email. So it’s important to use the above expression for the contentbytes.

Finally, outside the apply each loop use the send email flow action for sending all the files as one attachment.


Below is the final preview of the flow solution which we discussed above.

Thank you for reading.

Posted in #MicrosoftFlow, Microsoft, Microsoft Flow, Office 365, PowerApps, Sharepoint | Leave a comment

Microsoft Flow – Filter Excel List Rows

msflowsmallicon In this blog I will explain about Microsoft Flow Excel list rows filters.

Below is the Excel sheet table ‘ProdDetails’.

Retrieve All

First retrieve all rows from Excel sheet.

This returns all the rows from excel sheet.

The maximum size of an Excel file that is supported by the Excel Online (Business) connector is 25 MB.

The maximum size of an Excel file that is supported by the Excel Online (OneDrive) connector is 5 MB.

Filter

Next, we will look the filters. To filter the query, we need to use the Filter Query property. See below.

This returns three rows where matched Id is equal to 101.

If the filter value is string or alphanumeric then you need to put single quotes around it.

See below where I am going to filter Id A500

This returns a single row where Id is equal to A500. So for non-numeric filters we need to use single quotes around it.

Supported filter functions are: eq, ne, contains, startswith & endswith

Order By

Return the results in ascending or descending order.

Syntax:

[Column name] desc/asc

Eg: Price order by desc. See below.

Top Count

This returns the top/maximum number of records that should be returned.

Skip Count

This is used for number of records that should be ignored. Skip is generally used for pagination which is used for retrieving large amount of data.

Thank you for reading.

Posted in Excel, Microsoft, Microsoft Flow, Uncategorized | 3 Comments

Microsoft Flow – Excel Get Row

msflowsmallicon In this blog post I will explain Microsoft flow action step Excel Get a row.

Flow action Get a row retrieves a single row from an Excel table. Here I am using a valid key value (P2) which exists in my excel sheet.

Flow found single row. Below is the output.

Key value using single quotes.

This resulted in an error 404. See below.

Next, I tried using an invalid key value (P23333).

This also resulted in 404 error.

Below is the excel sheet which contains duplicated Id 101.

I tried to retrieve duplicated Id 101. There are three rows for Id 101.

Flow retrieved the first matched row. Below is the output.

Conclusions:

Flow step Excel – ‘Get a row’ retrieves a single row from table.

For duplicated row filter key values, it returns a single first matched row.

For key value filter no need to use single quotes for string values.

If there is no match flow will fail and returns 404. If you want to handle the exception, then you could use configure run after ‘has failed’ option in your next step.

Thank you for reading.

 

Posted in Excel, Microsoft, Microsoft Flow | 14 Comments

Microsoft Flow – OneDrive Send All Files as Attachment by Email

msflowsmallicon In this blog I will explain how to send all files as attachment by email from a OneDrive folder using Microsoft Flow.

First step is the trigger. Here I am using a manual trigger.

Next action is declaring an array variable called FileContents. This is used for storing filename and content.

Next, we need to read the list of files in a folder from OneDrive. List files in folder step will give the metadata of files.

This step won’t return the file contents. See below step how to get the file content.

Next to form a loop for reading each content and getting the file content. Store the filename and content bytes under the array variable.

Once the loop is executed successfully, we have all the files and contents under FileContents array.

Finally, we can use the flow action Send an email step for sending the files.

Once send an email flow action step is added you can see the below properties as default.

By default, you can attach one filename and content. By clicking Add new item will allow more files to attach one at a time.

Click the above highlighted icon to switch to input entire array so that we can set the FileContent array variable here.

This way we can set all the files and contents under a single property called ‘Attachments’.

Thank you for reading.

Posted in Email, Microsoft Flow, OneDrive | 13 Comments

Microsoft Flow – How to determine a string is numeric?

msflow There are no expression function in Microsoft Flow for checking whether a string is numeric or not.

There are different ways you could resolve this. Here I will show you two techniques.

First technique I am using here is the error handling feature. With error handling, you can define any number of steps to run after the failure of an action.

Following are the detailed steps.

Step 1: Initialise variable called ‘IsInteger’ type as boolean.

Step 2: Initialise another variable (Input number) called ‘IntNumber’ type as string.

Step 3: Compose statement which uses an expression to convert the above string value into integer.

The expression used for the above is int(variables(‘IntNumber’))

Step 4: Set the variable ‘IsInteger’ to false if the above compose step fails.

Also set ‘Configure run after – has failed’ option to true. See below

Step 5: Finally check the If condition whether IsInteger is true or false. Here set configure run after properties ‘is successful’ and ‘is skipped’ to true.

Below is the final flow looks like.

Here are my test results:

  1. Non-numeric test

  2. Numeric test

Second technique is to use the Try Catch Finally flow action. If you are from a developer background you must have used, try catch finally in your code. If any failure happens within the Try method, Catch statements will execute. Finally block is always executed when execution leaves any part of the Try…Catch statement.

Thank you for reading.

Posted in Microsoft Flow | 1 Comment

Microsoft Flow – SharePoint Document – Restore Previous Version

msflow1 This blog explains how to restore a SharePoint document to previous version using Microsoft Flow.

Prerequisite:

Microsoft Flow (https://flow.microsoft.com/en-us/):

Cloud-based service that allows you to create automated workflows between your applications and services.

SharePoint Document Library:

A document library provides a secure place to store files where you and your co-workers can find them easily, work on them together, and access them from any device at any time.

To see the version histories, click the document property ‘Version history’.

To restore the version click the relevant version modified datetime and select restore.

Flow Logic:

First, we need to find the previous version history details of the document.

SharePoint API : https://yoursharepointsite/_api/Web/GetFileByServerRelativePath(decodedurl=’/edu/Portal%20Ideas/tips.txt’)/Versions to restore the document via Flow.

For the above URL, ‘edu’ is my SharePoint document library site and ‘Portal Ideas’ is the folder name. Tips.txt is my document under ‘Portal Ideas’ folder.

Next to do the document restore we need to use SharePoint API. See below.

https://yoursharepointsite/_api/Web/GetFileByServerRelativePath(decodedurl=’/edu/Portal%20Ideas/tips.txt’)/Versions/restoreByLabel(‘yourdocumentpreviousversion’)

Here are the flow steps:

Next step we need to utilise Parse JSON to get the version histories. See below.

The easiest way to get the schema name is execute the flow step (Send an Http Request to SharePoint) to retrieve the versions.

Look the run history and expand the step, copy the results in to the below step (Parse JSON).

Hope this was useful.

Posted in Microsoft Flow, REST, Sharepoint | 1 Comment