Flow scenario #4: Kaizala message merge from Excel

Banner-Kaizala-Message-Merge

In this post, I will explore the scenario where you have an Excel file that has the content that needs to be sent in a particular format to a particular subscriber / user of a Kaizala public (hub and spoke) group.

Designing the Flow

Broad steps in the flow

  1. Create an Excel file with a table that has the contents
  2. Define a format that will be used to format the message
  3. Write a flow that would
    1. Run periodically, querying the Excel file for entries not processed already
    2. Update a column in Excel file to denote row processed
    3. Draft a message using the format string
    4. Send formatted message to the user / subscriber

Create the Excel file

For the demo, I have created a table in Excel file that looks like this

Excel table - before running

(Note: I have kept the mobile numbers same as I wanted to demo different output strings. However, when you provide different numbers, the message would go to the corresponding number)

Define a format

I have 3 fields that need to be input in the format, so have chosen the below format:

Hello {0} {1}, thank you for expressing interest in {2} program.

(Note: I just used {n} type of format – but you could choose anything you prefer)

Write the flow

For running the flow periodically, I have chosen a daily frequency in the recurrence connector.

recurrence-daily

Defined 4 variables using Initialize Variable connector to capture:

  1. format string (FormatString)
  2. format string after the title is filled (FormatStringWithTitle)
  3. format string after title and name are filled (FormatStringWithTitleAndName)
  4. output message that will have the final message (OutputMessage)

variables

Fetch the rows from the Excel file by using the “Get Rows” action in Excel Flow connector. I have placed the file in OneDrive (you will have to authenticate to the data source you choose).

get rows

Then add an “Apply to each” to iterate through the rows and add an if condition to check if the Pending column of the row has not been updated (empty).

apply-to-each

Update the Pending field under the If yes branch to indicate the row has been processed. I have marked the Pending field to NO here.

Update Row

In order to get the message with the format, I have replaced the {0} with title and then used the resultant string to replace {1} with the name and again use the latest string to replace {2} with the program. Since Flow does not allow to assign back to the same variable after doing a replace, used 3 separate variables.

updating variables

Notice the replace method used, taking the Title as an example:

replace(variables(‘FormatString’),'{0}’,item()?[‘Title’])

  • {0} – placeholder used for Title
  • item() – signifies the current Excel row data being processed
  • item()?[‘Title’] would fetch the Title column in the current row

Finally, send the formatted text to the corresponding user.

send-message

Output

Since I had given the same mobile number for the 3 rows, I got all the formatted messages in the same group.

Messages

Excel sheet after it was updated by Flow.

Excel table - after running

Hope that was helpful. In case you have any questions / need any clarifications, feel free to leave a comment. Since the Flow is big, I have shared the flow here. You could download it and Import it on https://flow.microsoft.com.

2 thoughts on “Flow scenario #4: Kaizala message merge from Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s