Search Results

  • How to retrieve files from a SharePoint library and send an email attachment (with Power Automate)

    Every flow needs a trigger to start with so here I am using a manual trigger. Next, I am using the action List Folder which returns files contained in a SharePoint folder. The return response is an array of BlobMetadata which contains the below properties. NamePathTypeDescriptionItemId (integer) - The value that can be used to Get or Update file properties in libraries. Id (string) - The unique id of the file or folder. Name (string) - The name of the file or folder. DisplayName (string) - The display name of the file or folder. Path (string) - The path of the file or folder. LastModified (datetime) - The date and time the file or folder was last modified. Size (integer) - The size of the file or folder. MediaType (string) - The media type of the file or folder. IsFolder (boolean) - A boolean value (true, false) to indicate whether or not the blob is a folder. ETag (string) - The etag of the file or folder. 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. Now we need to construct a loop for reading all of the SharePoint library contents. This could be files or folders, so within the loop I am checking whether the content is a folder or not. See image: Now we need to construct the rest of the logic under the Yes condition, and set all of the file's properties to the array variable which we declared earlier. To get the file's content, we use the action Get file content using path. The Path property is available from the above step using the Blobmetadata content. The Append to array variable action will have two properties - ContentBytes and Name. See the setup below. The expression for the property 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 an error when sending the email. So it is important to use the above expression for Contentbytes. Finally, outside the Apply to 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.

  • How to restore a previous version of a Sharepoint document using Power Automate

    A SharePoint 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. Restoring a previous version of a document using Sharepoint First lets see how we would do this in Sharepoint. We would go to the document library, select the file and then choose ‘Version history’. Then we would select the correct version to restore to and select Restore. Easy. Now, how can we do this using only Power Automate? First, we will use the Sharepoint API to grab a list of the versions of the document. The URL https://{{yoursharepointsite}}/_api/Web/GetFileByServerRelativePath(decodedurl=’/edu/Portal/Ideas/tips.txt’)/Versions is the one we need to hit. In this URL, ‘edu’ is my SharePoint site, ‘Portal Ideas’ is the library name and 'tips.txt' is my document. We use an HTTP request to call this URL. And then we will use a Parse JSON action to unpack the results. The easiest way to get the property name that we require is to execute the flow step (Send an Http Request to SharePoint), look at the run history and copy the JSON in to the below step (Parse JSON). We setup a variable Comment which is taken from the VersionLabel property in the JSON. Now, to actually restore the document to a previous version we will use SharePoint API again. This time we are calling this URL: https://{{yoursharepointsite}}/_api/Web/GetFileByServerRelativePath(decodedurl=’/edu/Portal/Ideas/tips.txt’)/Versions/restoreByLabel({{Comment variable from above action}}) Hope this was useful.

  • Filtering Excel Rows with Power Automate

    Below is my Excel sheet ProdDetails. Retrieving all of the rows To manipulate this data in Power Automate, first we will retrieve all of the rows: This action will 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. Filtering the data Next, we will look at filtering the data. To do this we will use the Filter Query property. This returns three rows where matched Id is equal to 101. If the filter value is a string or alphanumeric then you need to put single quotes around it. See below where I am going to filter for Id A500 Supported filter functions are: eq, ne, contains, startswith & endswith To order the data use the 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.

  • How to determine if a string is numeric (in Power Automate)

    There are a few ways that you could do this. Here I will show you two of my 'go-tos'. Determining if a string is numeric - method #1 The first technique that I would like to demonstrate uses the error handling feature of Power Automate. With error handling, you can define any number of steps to run after the failure of an action. To replicate this follow these steps: Step 1: Initialise a variable called ‘IsInteger’ (type boolean). Step 2: Initialise another variable called ‘IntNumber’ (type string). Step 3: Use a Compose action to convert the string value into an integer. Use the expression 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 completed flow: Here are my test results. If the input string is non-numeric: If the input string IS numeric: Determining if a string is numeric - method #2 The second technique I want to show you uses the Try Catch Finally flow action. If you are from a developer background you will be familiar with try, catch, finally statements in your code. If any failure happens within the Try method, the Catch statements will execute. The Finally block is always executed when the execution leaves any part of the Try…Catch statement. In Power Automate, this is modelled like so:

  • Email OneDrive files using Power Automate

    First step is the trigger. Here I am using a manual trigger. The next step is to declare an array variable called FileContents. This is used for storing the filename and file content. Next, we need to read the list of files in a folder from OneDrive. The Power Automate List files in folder step will give you the metadata of files. Next we build a loop for reading each file from OneDrive. We store the filename and content bytes under the array variable. Once the loop is executed successfully, we have all the files and their byte contents in the FileContents array. Finally, we can use the action Send an email for sending the files. Once the Send an email action 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 be attached at one 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’.

  • How to call an API using the Power Automate HTTP action

    In this blog post I will access NASA's Astronomy Picture of the Day (APOD) API using Power Automate. The plan is to retrieve the picture of the day using the API and store it 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. To generate an API Key you can follow the instructions on the website (https://api.nasa.gov/). Once we have the key we can start to build the Flow. Here I am using a scheduled trigger which will run every day. Next, we use the HTTP flow step. Enter the api_key that you received as part of the NASA API registration process. You can also extend the query using the date and hd parameters (though they are optional). The API call will return a JSON response, which we can parse using the Parse JSON Results action. A sample of the JSON schema is as follows: Inside that JSON is an hdurl attribute that contains a URL of another endpoint that we can call. To get the image content I now use another HTTP flow method and pass in the hdurl property. Now I can use a Compose action to grab the filename of the image from the JSON. The expression to use is: 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.

  • Retrieving a row from an Excel table using Power Automate

    The Power Automate 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 a single row. Below is the output. When I use a key that is a string, Power Automate returns an error. If I try using an invalid key value (P23333). This results in a 404 error. Duplicate rows Below I have an excel sheet that contains three rows with Id 101! When I try to retrieve them: Power Automate returns the first matched row. Below is the output: Conclusions: Action step Get a row retrieves a single row from an Excel table. For duplicated row keys, it returns a single first matched row. There is no need to use single quotes for string values. If there is no match Power Automate will fail and return a 404 error. If you want to handle the exception, then you could Configure run after setting ‘has failed’ in your next step.

  • 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.

  • How to retrieve CDS Option Set Label Name?

    This is something often mentioned in Power Automate community about how to retrieve the option set label name? There are different ways you can retrieve this. It's all depends on the connector you using in your Power Automate Step. When you create an Option set it allow you to include drop down lists of fixed values to a user within your app to ensure data consistency. Behind the scene it stores the name and a numeric value. Power Automate actions steps such as Get or List records retrieve the option set numeric values. Here are the ways you could retrieve the option set label name. There are two types of CDS connectors available in Power Automate. (a) Common Data Service (b) Common Data Service (Default Environment) There are differences between these connectors. If you using Common Data Service Connector you can directly get the option set name from List or Get Record. By default dynamic property shows the option set value only. See below in my example Type (Option Set) shows the dynamic property as Type Value which is the numeric value stored under the database. Label names are not shown under the Dynamic content. If you check your runtime history of flow Power Automate step Get or List action, here you can see the label names are retrieved. You can use the expression to retrieve this If you using the List action then you can declare a compose action step, click the expression and type the following: items('Apply_to_each')?['_ajb_type_label'] Using the the above technique you can retrieve the option set label names. The same technique works with D365 connector as well. The above technique won't work with Common Data Service (Default Environment) Connector. It only retrieves the Option Set numeric values. So how do we retrieve the label names for default environment connector? CDS stores the option set details (Label Name & Value) under a system entity called StringMaps. This entity is not accessible via CDS entity interface (PowerApps > Data > Entities) but available via Power Automate action steps. To retrieve the option set label name you need to know couple of things before hand. Entity name where lookup is defined, lookup attribute name and value of the attribute (numeric value) Execute the above list record which will retrieve the label name 'Value'. See below. Thanks for reading my blog.

  • How to check whether a folder exists in SharePoint (using Power Automate)

    I have a SharePoint document library called aj, and inside of it, three folders (Test1, Test2 & Test3). I am going to build a flow to check whether a folder exists in aj. If the folder exists, then I will grab the file contents. My first action in the flow is a manual trigger. The next step is to initialise two variables. One variable is a boolean used for storing whether a folder exists, and the other is a string that stores the folder name and path that we want to find - in this example /aj/Test1. Next, we need to use the SharePoint List Folder action. Here I am using the FolderName variable from the previous step under the file identifier setting as dynamic content. If required, you can also use the SharePoint URL mapping via a variable. For the above set the Configure run after property as follows: Now we add a parallel branch. Here we need to add two steps under the parallel branch. Step #1 - Create a variable called IsFolderExists. We set it to true. Set the Configure run after (Select the three dot of Set variable True) after as follows: Step #2 - Create another variable called IsFolderExists! We set it to false this time. Set the Configure run after as follows: Now we should have two parallel actions as shown: The next step is to check the condition of whether the SharePoint folder is found or not For this we set the Configure run after as follows: For both set variables (True and False) make sure you set all the checkboxes to true except for ‘has timed out’. Once both variables are set then click done. The final step is to get the name of the files from the folder under the Yes condition. And now if we run it..... Here are my run results: If the SharePoint folder exists (Folder name = /aj/Test1) If the folder does not exist Folder does not exist (Folder name = /aj/Test999)

© 2023 by Walkaway. Proudly created with Wix.com

  • Twitter Black Round
This site was designed with the
.com
website builder. Create your website today.
Start Now