Using Flow to post todays events from SharePoint to Teams

I am going to show you how I built a Microsoft Flow to post upcoming events from a SharePoint events list into Microsoft Teams each morning. In a conversation with a colleague I accidentally said, “I bet you can build a flow to do that”, which I should learn by now is the start of several evenings of detailed work. Flow is a great tool, but I am poor at tempering my ambitions to my skill level. I had recently seen a video from Nishanth in the Teams Product Group about using rich cards which I think is a suitable place to start.

Getting data

In this solution I am pulling data from a SharePoint Event list, the data behind the really nice new events webpart for modern team sites. We are using for department wide events, weekly huddles, business events and the like. As it is a SharePoint list a simple Schedule triggering a SharePoint Get Items action pulls back the lists data. I know there are some awkward limitation with pulling list data, limited to 500 rows and the options to filter based on data fields are not great, but I think it will take a while for us to reach the point it’s a problem, and given how flow develops it might be resolved by then anyway.

So, I now need to filter this data to get the events relevant each day. Dealing with dates from SharePoint lists is always annoyingly fiddly, I see that recently there a new action in Flow to make it easier to do date related stuff, which I will try in the future, but I made a function that works ok for now. Basically I strip the SharePoint date down to something manageable by selecting the first 10 characters then checking that the start is before tomorrow and the end is after now, using:-

@and(less(substring(item()?[‘EventDate’], 0, 10), adddays(utcnow(),1, ‘yyyy-MM-dd’)),greaterOrEquals(substring(item()?[‘EndDate’], 0, 10), utcnow(‘yyyy-MM-dd’)))

Building a rich message card

Posting a rich message card to a Team connector again steps outside the regular action blocks, you assemble a json message then post it to webhook connector using the http post action. The standard post to Teams action just sends text messages, and it is always posted from the user, webhooks make it look more like a proper service message.

As per the video, the Messagecard Playground is the reliable way to define your block of json, then use flow to insert variables. I used the Twitter example as a starting point as I wanted to handle multiple appointments on one day appearing as rows within one message. When you look at the json you can see that there is some header content and then a repeating section for each ‘group’ on row.

In my flow I create an apply to each section to build the rows, then I will combine them back with the header/footer later. Using a compose action I build the row, swapping in data from my list. That bits easy.

Normally to recombine you would just use compose again but use a function to join(actionoutputs()), but I found it hard to then wrap the headers as to flow the json looked invalid, or it would decide the rows should be kept as text. In the end I again built the whole thing as a function using Concatenate() and converting this back to json. It looked like

json(concat(‘{“title”:”’,variables(‘Count’),’ Events for ‘, utcNow(‘D’),’”, “@type”: “MessageCard”,”summary”:”A Summary”, “@context”: “", “themeColor”: “0078D7”, “sections”: [‘,join(actionOutputs(‘EachAppointment’),’ ‘),’]}’))

This took a long time to figure out, if anyone more adept at flow has any better tips for dealing with stuff that looks like json I would love to hear them. Building a function of that length in the expression box is just horrible to do.

So, before the money-shot of the complete flow, some other points I thought about

  • Using a filter on the get-items before the apply to each look like the right thing to do, but I then found it hard to access the named variables in the loop. Using a condition in the loop is ugly, but works and makes it easier to build
  • You’ll notice in my flow some counters, these aren’t really necessary, but I decided that on days where there were no appointment I wanted to not look like I had gone wrong. You could stop processing if there are 0 messages, but I am posting a message just saying so.
  • I create a couple of buttons on the cards to open urls, one for the details page in sharepoint and one if there was a url provided in workspaceurl. The events webpart hijacks the tradition sharepoint events list, workspaceurl is where the link you included ends up, in our case we put links to Skype broadcasts here.
  • You might be wondering if you could do the same from a groups calendar rather than a sharepoint list, I’ve not tried (yet) but you would need to call the graph to get back the appointments. It looks to me that you could more easily pass in a filter on the graph request than you can on a SharePoint list.
  • I’m posting to Teams, but webhook connectors are a standard across Office 365, so you could just as easily post your message against an Outlook group, Yammer Group or your inbox.
  • If you wanted to get a message just before each meeting, try adjusting the schedule to be every 15 minutes, and then build or extend the condition to narrow your time window. If you got this far then that won’t be hard 😊

The result seems to work reliably and does what we want. It does feel a bit like handy-crafts, neither as nice as a coded solution in an azure function, or really as simple as a flow really should be. Let me know if you find this useful!