{"id":1391,"date":"2018-02-14T20:10:31","date_gmt":"2018-02-14T20:10:31","guid":{"rendered":"https:\/\/officedevblogs.wpengine.com\/?p=1391"},"modified":"2018-02-14T20:10:31","modified_gmt":"2018-02-14T20:10:31","slug":"kaizala-message-flow-from-excel","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/kaizala-message-flow-from-excel\/","title":{"rendered":"Flow scenario #4: Kaizala message merge from Excel"},"content":{"rendered":"

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.<\/p>\n

Designing the Flow<\/h2>\n

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

      Create the Excel file<\/h3>\n

      For the demo, I have created a table in Excel file that looks like this<\/p>\n

      \"Excel<\/p>\n

      (Note:<\/strong>\u00a0I 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<\/em>)<\/p>\n

      Define a format<\/h3>\n

      I have 3 fields that need to be input in the format, so have chosen the below format:<\/p>\n

      Hello {0} {1}, thank you for expressing interest in {2} program.<\/p><\/blockquote>\n

      (<\/em>Note:<\/em><\/strong>\u00a0I just used {n} type of format \u2013 but you could choose anything you prefer<\/em>)<\/em><\/p>\n

      Write the flow<\/h3>\n

      For running the flow periodically, I have chosen a daily frequency in the recurrence connector.<\/p>\n

      \"Screenshot<\/p>\n

      Defined 4 variables using\u00a0Initialize Variable<\/strong>\u00a0connector to capture:<\/p>\n

        \n
      1. format string (FormatString)<\/li>\n
      2. format string after the title is filled (FormatStringWithTitle)<\/li>\n
      3. format string after title and name are filled (FormatStringWithTitleAndName)<\/li>\n
      4. output message that will have the final message (OutputMessage)<\/li>\n<\/ol>\n

        \"Screenshot<\/p>\n

        Fetch the rows from the Excel file by using the \u201cGet Rows\u201d action in Excel Flow connector. I have placed the file in OneDrive\u00a0(you will have to authenticate to the data source you choose).<\/em><\/p>\n

        \"screenshot<\/p>\n

        Then add an \u201cApply to each\u201d to iterate through the rows and add an if condition to check if the\u00a0Pending\u00a0<\/strong>column of the row has not been updated (empty).<\/p>\n

        \"dialog<\/p>\n

        Update the\u00a0Pending\u00a0<\/strong>field under the\u00a0If yes\u00a0<\/strong>branch to indicate the row has been processed. I have marked the\u00a0Pending\u00a0<\/strong>field to NO here.<\/p>\n

        \"update<\/p>\n

        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.<\/p>\n

        \"updating<\/p>\n

        Notice the replace method used, taking the Title as an example:<\/p>\n

        replace(variables(\u2018FormatString\u2019),'{0}\u2019,item()?[\u2018Title\u2019])<\/p><\/blockquote>\n