Flow scenario #4: Kaizala message merge from Excel

Kaizala Developer Platform team

February 14th, 2018 0

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

Create an Excel file with a table that has the contents
Define a format that will be used to format the message
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

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

Defined 4 variables using Initialize Variable connector to capture:

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

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

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

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.

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.

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.

Output

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

Excel sheet after it was updated by Flow.

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.