Skip to main content

Kaizala: Developer Blog

Excel Kaizala

Flow scenario #4: Kaizala message merge from Excel

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.

Screenshot of recurrence

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)

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

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

dialog box of condition pending

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 dialog box

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 dialog box

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 text message to a group dialog box

Output

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

screenshot of 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.

filter-icon