How to create a buddy bot in Power Automate

Knowledge Level: Advanced

Purpose

In this tutorial, we will setup a buddy bot that will let users matchup for meetings with colleagues, on a random basis.

We will setup a bot to trigger webhooks to Power Automate which will use connections to an Excel Sheet for randomly matching users with each other.

Prerequisite

• A bot
• A Power Automate account with a license
• An Excel Sheet which acts as a staff directory (You need name and email as columns)
• Your favorite beverage or snack

Setting up the bot

To setup the bot, we will need two message parts:

• A welcome message asking the user if they would like to buddy up with someone
• A confirmation message letting the user know that a buddy is being searched, this message part will also contain the webhook to trigger our Power Automate flow.

Setting up Power Automate

We will be using the following modules in our scenario:

• When a HTTP request is received
• Initialize Variable
• Excel (List rows present in a table)
• Prase JSON
• Compose
• Response

Setup Webhook

📘

Please follow this guide to setup a webhook in Power Automate that works with The Bot Platform.

Once the webhook data is received, we should be able to continue building the rest of our Power Automate flow.

Setup 'Initialize Variable' module

In the next step, we will initialize a variable called 'random number'. Choose type 'float' and set the value 1. We need this variable so that in later steps, we can randomly pick a person from the staff list to match against our user as a buddy.

Setup Excel Sheet module

In the next step, add a new module and look for Excel. Pick the 'List rows present in a table module'

In the configuration of this Excel module, select your Excel sheet containing the name and email addresses of your employees. You can use an existing staff directory Spreadsheet for this purpose.

The configuration should look as follows:

In the above screenshot, we have configured a filter query condition to ensure the requester is not matched with themselves! The filter looks at the email column and excludes results matching the same email address.

Setup Parse JSON module

In the next step, we will add an Parse JSON aggregator module. Since the lists rows module from Excel is going to produce possible matches in the form of a list, we need to be able to summarize the list and then be able to only pick one match to return to the user, randomly.

We will configure the module as the following:

• Content: Set it to 'Body' from the 'Excel module'
• Schema: Copy this from a previous test execution, for the exact steps of configuring schema, follow this guide . If your excel sheet only has name and email columns, the schema can look something like this:

{
    "type": "object",
    "properties": {
        "statusCode": {
            "type": "integer"
        },
        "headers": {
            "type": "object",
            "properties": {
                "Cache-Control": {
                    "type": "string"
                },
                "Pragma": {
                    "type": "string"
                },
                "Transfer-Encoding": {
                    "type": "string"
                },
                "Vary": {
                    "type": "string"
                },
                "Strict-Transport-Security": {
                    "type": "string"
                },
                "x-ms-request-id": {
                    "type": "string"
                },
                "OData-Version": {
                    "type": "string"
                },
                "X-Content-Type-Options": {
                    "type": "string"
                },
                "X-Frame-Options": {
                    "type": "string"
                },
                "Timing-Allow-Origin": {
                    "type": "string"
                },
                "x-ms-apihub-cached-response": {
                    "type": "string"
                },
                "x-ms-apihub-obo": {
                    "type": "string"
                },
                "Date": {
                    "type": "string"
                },
                "Content-Type": {
                    "type": "string"
                },
                "Content-Length": {
                    "type": "string"
                },
                "Expires": {
                    "type": "string"
                }
            }
        },
        "body": {
            "type": "object",
            "properties": {
                "@@odata.context": {
                    "type": "string"
                },
                "value": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "@@odata.etag": {
                                "type": "string"
                            },
                            "ItemInternalId": {
                                "type": "string"
                            },
                            "Name": {
                                "type": "string"
                            },
                            "Email": {
                                "type": "string"
                            },
                            "FBID": {
                                "type": "string"
                            },
                            "Location": {
                                "type": "string"
                            }
                        },
                        "required": [
                            "@@odata.etag",
                            "ItemInternalId",
                            "Name",
                            "Email"
                        ]
                    }
                }
            }
        }
    }
}

Setup a 'Compose' module

The previous step gave us staff information in the form of a list. Using this compose step, we will count the total number of items available in the list. This is necessary to be able to properly generate a random number which is within the range of total available of staff from which a random person is being picked.

Add a compose step and add the following expression in the 'Inputs' section:

length(body('Parse_JSON')?['value'])

Setup another Compose module

Now that we have know the total count available, lets proceed and pick a random number from within this range. This random number will allow us to randomly pick an individual from the staff list to match against.

Add another 'Compose' module step and configure the following expression in the 'Inputs' section:

rand(0, sub(outputs('Compose'), 1))

Setup another Compose module to pick an individual

We are almost done with our scenario. After counting the total available staff and generating a random number, we are going to use the random number and find our match. For this purpose, add another 'Compose' module and configure it with the following expression:

body('Parse_JSON')?['value'][outputs('Compose_2')]

Adding this step will take the random number and match that with our original staff list picked up from Excel, and find a relative entry.

Setup webhook response module

In the last step of our tutorial, add a 'Response' module:

This module will allow us to send the randomly picked user information back to the bot user. To compose your response , use the following code in the 'body' of the module:

{
  “recipient”: {
    “id”: “USER_ID”
  },
  “message”: {
    “text”: “Hello world”
  }
}

Replace 'USER_ID' with the 'FBID' variable received in the webhook module as an input. For the text part, feel free to compose it the way you like. We also need to include the name and email of the individual who has been randomly picked, for this purpose use the following code somewhere appropriate in your message:

concat('Name: ', outputs('Compose_3')?['Name'], ' Email: ', outputs('Compose_3')?['Email'])

Here's an example configuration:

This was the last step. Make sure you save the flow and turn it on. If everything was setup correctly, the bot user will receive a response from the bot with a random buddy picked from the provided staff list.

Happy buddy-ing!!