Tokyo Olympics: Automate Twitter Posts

 

If you follow me on Twitter (@ExcelAllison) you may have noticed I've been playing around with Power Automate and sending out automated Tweets throughout the Olympic games. I've really been enjoying following my two 'home teams' and watching the medal tallies tick up, with near-instant alerts. 

Power Automate

Power Automate is a fantastic tool and really versatile in what it can do. It doesn't require any formal coding, though there is a bit of a learning curve at first and it does help to have some basic understanding of logical functions. However, if you put in a few hours a week to get up to speed with Power Automate, it could save you 2-3 days a month that you'll no longer have to spend on repetitive administrative tasks. I'm not exaggerating - there are studies and the time saved is real!

Objective

The goal of this exercise was to a) demonstrate the capability of Power Automate and b) drive traffic to my Tokyo Olympics 2020 Power BI report and blogs. I wanted to take advantage of the live data streaming throughout the Olympic games and engage with it on some level. I'm not very good at Twitter, but I knew that Power Automate has a Twitter connector so thought I'd give it a go - it's the perfect public forum for testing and showcasing the Power of Power Automate and Power BI together.

Born in the US but living in NZ, I wanted to keep up with both teams throughout the Olympics. NZ tv have been doing a good job of keeping us posted on the Kiwi results, but getting instant updates on the US athletes required a bit more effort. 

Key Components

Okay, so let's lay out the concept from start to finish: 

1. Connect to live dataset using Power BI
2. Create explicit measures for the key metrics we want to track (in this case total medals earned by NZ and total medals earned by US)
3. Publish Power BI dataset
4. Setup automated, scheduled refresh on Power BI dataset
5. Create dashboard and manage alerts for the two key metrics
6. Create flow in Power Automate that is triggered by the alert

We've already completed steps 1-5, so this post is all about step 6. As I started working through this process, I further developed step 6, thinking about what I wanted to accomplish with my flow and what my goals were. I wanted to:

• be alerted when my favorite teams won a new medal
• understand what they had won or what was happening in the Olympics
• share that joy with others

Power Automate has lots of connectors for alerts, notifications and information transfer, but Twitter seemed like the perfect platform for sharing the joy with others and also for figuring out what else was happening with the team.

I created two identical flows - one called 'Congrats Team NZ' and one called 'Congrats Team USA'. To simplify this post, we'll focus on the Congrats Team USA post (I used this one for testing purposes as USA got a medal before NZ and has earned more medals than NZ, so this flow has given me more opportunity to refine and test the process.)

Congrats Team USA

 After a few iterations and learning a bit about Twitter and the Twitter connection, I have developed the 'Congrats Team USA' flow to complete the following tasks: 

1. Start whenever a new medal has been earned by US (as triggered by alert in Power BI dashboard)
2. Search for Tweets from @TeamUSA on Twitter - limit search to 1 latest Tweet
3. Retweet that Tweet (hope here is that it's related to the medal they've just earned)
4. Post new Tweet - apparently this can't be the same Tweet over and over (and I'm sure my followers would get tired of it too) so I developed a list of inspirational quotes to add to this Tweet. In order to post the new Tweet, Power Automate will:
1. Go to the SharePoint list of quotes, find the next in sequence and add that text to the Tweet
2. Update the sequence of all quotes in the SharePoint list to move all items forward one place in line (getting ready for the next run of the flow with a new quote)
3. Post the Tweet on Twitter

Twitter Rules

There are a few restrictions that Twitter and/or Power Automate place on your tweets that might be common causes for failure, so double check you've got all these in place if you want your flow to succeed:

• 280 characters or less: Tweets must be less than 280 characters! If you exceed this limit, your flow will fail.
• Avoid duplicates: You can't post the same tweet twice, nor retweet the same tweet, within a certain time period. Ensure your tweets are unique.
• Volume quota: Keep within the limits of the Twitter API 
• # not @: Mentioning users or any reference to @ character will be removed from your tweet, but # and hyperlinks are ok.

How To

Alright, so how do we setup this Flow?

Step 1: Create SharePoint list of Quotes

From Teams or SharePoint, create a New List. Lists are a super cool app that probably deserve an entire post to themselves, but I'll go through the essentials here. I created a new Blank List. 

By default, this list will have a single column called 'Title' that must be single line of text and is a required field.

Click on the Settings cog at the top of the list and choose 'List Settings'

Scroll down to find the 'Title' column and click on the link to edit the column. Untick the required box and Save. 

Add a new column - multiple lines of text and call it 'Tweet Text'.

Add a new column - number and call it 'Sequence'. 

We'll use both of these new columns in our Flow.

Now click on 'Edit in grid view' and add some inspirational quotes or Tweet messages. Remember the character limit! For the 'Sequence' column, start with 1 and increment by one each row. 

Click 'Exit grid view' to save your changes.

Step 2: Create Flow

Navigate to Power Automate and click 'Create' to start a new flow. 

Click 'Automated cloud flow'

Type 'Congrats Team USA' (or fill in your team name).

Trigger: When a data driven alert is triggered

Search for Power BI in the triggers. Select 'When a data driven alert is triggered'. Click 'Create'.

Search Tweets

Click 'New step' and search for Twitter. Add the 'Search tweets' action. At this stage you will be asked to login to your Twitter account to create the connection.

Enter your desired Search text. In my flow I put 'from:@TeamUSA' as my search text. 

Expand 'Show advanced options' and limit the maximum results to 1.

Retweet

Click the 'New step'. Search for and add 'Retweet'. 

From Dynamic Content, add the Tweet Id from the Search Tweets step. This step will automatically get added to an 'Apply to each'. Even though we limited our search results to 1 Tweet, Power Automate still sees it as a list of Tweets, so will Apply the RETWEET to each search result. 

Click the three dots at the top right of the Retweet step and ensure it's using your signed in Twitter connection.

Variables: Quote and NewSequence

Variables are basically a box to hold information. You can then move and reuse this information, or even add and subtract from it. Variables MUST be initialized in Power Automate before you can use them. In this Flow, I created a String variable for 'Quote' and a Float variable for 'NewSequence'. 

I left value blank for both, we'll assign this later.

Get Items (SharePoint List)

Add a new step for 'Get Items'. This will return the values of the columns in a SharePoint list. 

Select your Site Address and List Name that we created in Step 1 from the drop down.

Apply to each SharePoint List Item

Add a new 'Condition' to the Flow. Choose 'Sequence' column from the Get Items step. This will automatically wrap the Condition inside an Apply to each step. Everything we do in here will now be done to EACH item in the SharePoint list. 

We want to find the next Quote in Sequence - our list starts with Sequence = 1, so that's the quote we want. Ensure your Condition looks like the image above: Sequence is equal to 1.

If yes, add a step for 'Set variable'. Set Quote text Variable to 'Tweet Text' column from the Get Items step. Since we're still inside the Apply to each, this will happen for each item, IF the Sequence is equal to 1. If no, we'll leave that blank.

Now add an action for 'Set variable' below the condition, but still inside the Apply to each. We want to shift ALL the items in the list (not just the one we chose for the quote) so that they move 1 place in line and the next list item ends up with Sequence 1 for the next time this flow runs. 

• Set the 'NewSequence' variable equal to value of 'Sequence' column from the Get Items step.
• Add a new step for Increment Variable. Choose the 'NewSequence' variable and type -1 in the value to increment. 
• Add an new step for Update Item (SharePoint list). Select your Site Address and List Name that we created in Step 1. Put the ID from Get Items into the ID field. Put NewSequence variable in the Sequence field. This moves every item one step forward in sequence/line.

Post Tweet

Below the Apply to Each, add a new step at the very bottom of the flow and search for: Post a Tweet.

Type your Tweet text, be sure to include the Quote Text variable in there somewhere and follow the Twitter character limits!

Save & Test

Once you're happy with your Tweet, save it and test when the Power BI alert triggers. You may need to come back to edit and troubleshoot some small errors. Remember - all testing in Power Automate is live to the data connection. You may want to use a test Twitter account or SharePoint list for testing. When you're ready, you can easily update the connections to the live data sources and Twitter accounts. 

Enjoy!

Create Free Automated Registration Forms with Microsoft 365

I have been doing a few webinars and experimenting with different platforms for registrations, but have found that Microsoft 365 has all the tools I need to seamlessly automate the registration process for attendees and myself. 

Rather than keep this knowledge to myself, I thought I'd share it with you all here. 

Step 1: Use Teams to Create Live Event webinar

Teams Live Events are a great way to reach a wide audience. They enable you to broadcast a variety of content in a webinar style format live. Okay, actually there's a 20-30 second delay, but who's counting?

Create live event

From your Microsoft Teams app, click the Calendar on the far left. 

Once in your calendar, click the down arrow next to New meeting, and select 'Live event'. 

This will open a new window where you can populate all the Live event details. Make sure to select the correct Date, give it a title that you want Attendees to see (this does not update later for attendees if you change it, so get it right now), and provide a description for yourself or other presenters (such as agenda).

Click Next.

Live event permissions

Choose who can view the live event. This is the audience. If you cannot select 'Public', you need to talk to your admin about setting up a Public Live Event Policy. 

How will you produce your live event?

It works really well using Teams to produce your live event - no fancy studio or recording equipment required. 

Tick the boxes to configure your settings. 

Note that audience members cannot speak or present or interact with each other during a live event, so I like to turn on Q&A, as this gives them the opportunity to type questions and interact with the presenter. I also tick the Recording available to attendees as this enables people to watch after the fact if they couldn't join live. You can always turn this off a week or two after the event, and then the link will no longer give them access to the recording. Captions are great for accessibility, so think about your audience.

Click Schedule.

Get attendee link

To share your event with your audience, you'll need to send them the attendee link. 

Simply open the Live Event and click the 'Get attendee link' link to copy the attendee link. Save this somewhere safe, we'll need it later.

Step 2: Use Microsoft Forms to Create Registration Form

There are many ways to create a Form in Microsoft 365, it integrates so well with Office 365 apps such as Teams and Excel. If you're the only one who needs to see registration and attendee information, then you can use any method to create the form, but chances are you might enlist some help at some stage of the process and want to share this form with others. In that case, my favourite way to create a Form is to start in Microsoft Teams and use Forms for Excel. 

Create registration form

Choose the Team where you want the form responses to live. This team should relate to the webinar or topic, and everyone in this team will have Edit access to the registration form. 

Note: This is NOT the team of people you will invite to the webinar, they only need View access. 

Choose the Channel within the Team where you want the Excel file of responses to be stored. Again, this will probably relate to the webinar topic.

Click the Files tab, and select New > Forms for Excel. 

This should open a new browser with Microsoft Forms. 

Format the theme, title, details and questions of the form as you see fit for your webinar. You can ask as many or as few questions as you like.

For this example I recommend you create at least two questions as we will use these in the flow later: 

Add New > Text > Name

Add New > Text > Email

Make both of these questions required. 

Get form ID

In order to use this form in Power Automate, we need the form ID. You can grab this from the URL of the form editing screen. 

Look for the part of the URL that says 

#FormId=

and copy all the letters and numbers after the equal sign. Save this for reference later. 

Step 3: Use Outlook to Create Calendar Reminder

I find it challenging to manage all the webinars I sign up for, especially now that many of them are being offered internationally and I need to calculate time zone differences. If you don't want people to miss your webinar, it's a good idea to give them a calendar invite with the correct time and joining information already populated for them. 

This does add a bit more complexity to the registration setup, but in my opinion it's well worth the time.

Create Calendar Appointment in Outlook

Create a calendar appointment in your own Outlook calendar with all the information you want to share with attendees. Make sure you get the date and time right (include time zone). This is probably a good place to include the attendee link that we saved in Step 1. 

Save .ics file

Save the calendar appointment as a .ics file. You can do this from the File tab in the ribbon when you have the calendar appointment open: Click File > Save As > 

Save this file into SharePoint (I put it in the same Team as the registration form). Choose iCalendar Format for the Save As Type.

(Optional) Get Direct Download Link

In order to make it easy for attendees to open the calendar invite, you should share a link that will automatically start download of the calendar invite. To do this, go to the SharePoint location where you saved the file.

Share this file and grant access so that 'Anyone with the link can view'.

Now click the three dots next to the file, and select 'Details'. This will open a pane on the right hand side. Scroll down to find the 'Path' section and click the icon to copy the filepath: 

You will end up with a link that looks something like this (note this is a sample link I have edited so won't work): 

https://companyname.sharepoint.com/sites/Webinars/Documents/Calendar%20Links/Power%20BI%20webinar%20-%20Survey%20Says.ics

To ensure that it downloads automatically, just add 

?download=1

to the end of the link. It should now look something like this:

https://companyname.sharepoint.com/sites/Webinars/Documents/Calendar%20Links/Power%20BI%20webinar%20-%20Survey%20Says.ics?download=1

You can test it out in a web browser, but save this link somewhere handy so you can use it in your flow.

Step 4: Use Power Automate to Create Flow to send confirmation emails

Open up Power Automate in your Office 365 (or type flow.microsoft.com into the URL of your favourite web browser). 

When you're done, your flow might look something like this: 

I have designed this flow with ease of reuse in mind, so I have created a few variables to enable me to quickly update the flow for my next webinar. Let's start at the beginning though and work through each step: 

Trigger: When a new response is submitted

From Power Automate home screen, click Create and search the templates for 'Forms'. 

The 'Send an email from shared mailbox on Microsoft Form submission' is pretty close to what we're trying to do, so choose that template: 

Now that you're in the form editing screen, we're going to make a few changes:

1. Rename the flow in the top left. I called mine 'Webinar Confirmation Emails' but you may want to be more specific to which webinar. 
2. Fill in the Form Id in both the first and second steps. This is the long code we copied from the URL in step 2.
3. Delete the 'Send an email from a shared mailbox' step. You can use it if you'd like, but I prefer the personal touch of sending the email from me.

Initialize and set variables

Sometimes variables are crucial in the success of running a flow, but in this case I have just added them to make it easier to update the flow for my next webinar. It also makes it easier to show someone else how to maintain the flow, without needing to teach them everything about Power Automate. 

In my flow I have created three simple variables, all of them Type = String. 

• Webinar Name
• Webinar Date (in my local time zone)
• Attendee Time Zone

Note my Attendee Time Zone has a comment - click the three dots at the top of this step and 'Add comment'. This is another way to make it easier to update later; if you're the one updating you don't have to recall what you did, if someone else is helping to update you don't have to teach them the entire flow.

Power Automate (and most things in Microsoft 365) work with UTC time, so we need to convert the time of the webinar so that my confirmation emails go out when I want them to. 

 

Note I have used the dynamic content of my variable, so when I am ready to copy this flow to the next webinar I only need to change the Webinar Date variable and everything will update.

Send Confirmation Email with .ics attachment

Now we need to grab that .ics iCalendar appointment we saved to SharePoint in step 3. There are two ways to share this file, as a link or as an attachment. To share it as an attachment, you need to add a step "Get file content using path".

Browse to the correct Team site and file location in the action.

Then add a step for "Send an email (V2)" for Office 365. Click the three dots and rename this to 'Send confirmation email'. Expand the 'Advanced options' and put the dynamic content of the File Content from the "Get file content using path" step. Note I rename my steps as I go so that they are more meaningful to me.

The trick here is to give the attachment a proper name, including file type. Since it's a .ics file, we need to add .ics to the end of the Attachments name:

Use the dynamic content to fill in the other information in the email. This is where those required Registration Form questions will come into play - it's great to add a personal touch to the confirmation email and address each attendee by name. 

Add links to confirmation email

To add links to the email, just use the action link icon to create the link. If you look closely in my screenshots above, you will see I used the 'compose' action as a step to hold information and called it 'Instructions for Email Links'. I have just discovered the compose box and it's fantastic. You can put anything in here, and here I'm just using it as a holding block for comments and helpful information. In this example, you can copy the attendee link and iCalendar download link for reference: 

Note that this compose box serves no purpose in the flow. You still need to paste these links into the send email action, but it helps store information you plan to use multiple times throughout the flow (dynamic content isn't available in email links yet).

Configure run after to send failure notification email to webinar owner

Since Microsoft Forms free doesn't offer any email validation, we need to build that into our flow. If someone misspells their email or provides a fake email address, the flow will fail when it attempts the 'Send Confirmation Email' step. To make it easier for the webinar owner (they don't want to check the flow details each time that happens), we'll configure run after and send them a custom email.

Add a new step for "Send an email (V2)" for Office 365. Click the three dots and rename this to 'Send invalid email to Webinar Owner'. 

Click the three dots on 'Send invalid email to Webinar Owner' and choose 'Configure run after'. Tick 'has failed' and untick all other boxes, then click Done: 

This will ensure that your webinar owner only gets notified if the confirmation email failed, if it succeeds, then the flow will skip this step and carry on as normal. 

To carry on with the rest of the flow, the subsequent step should have a 'configure run after' that allows it to run when the failure notification is skipped: 

Add subsequent actions

Add any other actions you wish for your flow. In my example, I have one reminder email going out before the webinar. I have used the 'Delay until' action to get this working and 'Subtract from time' to specify how far before the start of the webinar to send this reminder. Note: You must use UTC time for this step. 

Note that Power Automate integrates with hundreds of connectors, not just Microsoft products. For example, you could link it to your Mailchimp account to help with marketing and follow up after the webinar: 

Once you're happy with your flow, save it. 

Test your flow by filling in the registration form. 

Step 5: Share your Registration Form Link

Now you're ready to share live. All you need to do to take registrations is share that Forms link with potential attendees. I'll leave it up to you to decide how best to promote this (email, website, Twitter, LinkedIn, etc).