Its Oscars time again…well, Covid-19 Oscars anyway. If you love movies like my wife and I do, then you are making your own list of movies to watch.  For us, we try and see all of the Best Picture nominations and as many of the other nominations as possible.  One nice thing about this season is that so many of the pictures are already streaming.  The Academy decided to allow streaming movies to be nominated even if they never were in a theater.

The First Pass

Normally this process means either making up a list on paper, or printing a ballot and crossing off movies as we see them.  This year, I decided to use some tech to make this easier.  I created a list in Microsoft To Do list for the Oscars. It didn’t take too long. I got a printable ballot from the Oscars website, and then I manually entered each movie.  Originally I was putting the nominations into the title, but that was ungainly.  Then I hit upon the idea of adding a step in the To Do task for each nomination and To Do would automatically list the number in the title so it was easy to see.

I then shared the To Do list with my wife and we have been using it to track out Oscar viewing.  We favorited the Best Picture nominees and when we watch one, we complete the task.  This was all done on my phone which the only place that my wife uses To Do.

Sharing with Others

I showed this to one of my friends at work. She asked me to share the list with her.  Unfortunately I had created the list in my personal M365 tenant and you can only share a To Do list with someone inside of your tenant.  We then looked at how I could export this and they could import it.  You can export your personal tasks from Outlook, but this is in M365 so we cannot export it, and even if we could…To Do doesn’t have an import.  So, what is a guy to do (pun intended)?

Power Automate to the Rescue?

Power Automate has some To-Do activities, they put in a hyphen for some reason, so I will use that in this section.  I figured that I could:

1. Create a Flow in Power Automate
2. Build out the To-Do list
3. Share that with others

Attempt 1 – The Initial Flow

Creating the Flow is really easy.  I decided to create an instant flow since I only have to run this once to do the work.  <Insert Instant Flow 1 & 2>

There are some To-Do activities in PowerAutomate so I searched for them.

• Add a to-do (v3) – This is the one that I am going to use
• Create a to-do list (v2) – Good to know we can use this if we want to create a list…which reminds me…
• Delete a to-do (v2) list
• Get a to-do list – useful if you want to manipulate a specific list
• Get a to-do (v3) – if we need to manipulate or make decisions on an item
• Update a to-list
• Update a to-do (v2)
• Delete a to-do (v2)
• List all to-do lists (v2) – Could be useful in a lot of ways
• List to-do’s by folder (v2)

This made me realize that people will likely want to add this to new list for the Oscars so we need to add a new list.  Which also means that we need a name for that list.  I could hard code it, but instead, lets add it as an input to the Flow like so:

Open up the Manually trigger a flow action, <Insert Animated Image> add a text input, and prompt the user to give you a name for their list.

Next we use the Create a To-Do list (v2) to create a list using the name they gave us.

Now, when we add our To-Do item in the next step, we will use the list we created. <Insert Image of Movie Being added>

One thing to note, we use the ID of the List as opposed to the name when we add it.  That means select custom value and then you can get the dynamic content to add the id from the prior action.

Next we add the Title of the movie as the Title. The Due Date is set to Noon on April 25th (the date of the Oscars this year). We cannot add steps via flow, so I chose to add the nominations as a bulleted list in the Body Content.

Now we do that for the other 65 movies and…wait a second, I am all for a little cut and paste, but that is a LOT of cut and paste.  Yes we have the Clipboard in PowerAutomate now, but it would still require a lot of configuration.

Attempt 2 – Is there a better way to do this?

I decided that if I built an Excel workbook, I could create a table and add a row for each movie with the Title, and then a column for each nomination.  Yes, I could add them all in one column, but I figured there would be an easy way to get them to all concatenate in PowerAutomate.

So, I added an action to list the rows in an Excel table.

This allowed me to add the movie’s title to the To-Do Task

But, there are multiple rows in the table and that makes the Add Task Action get placed into an Apply to Each control.

NOTE: You might get a warning from PowerAutomate that you need to set the Concurrency Control on in the setting and turn the degree of parallelism down to 1 to ensure it runs properly.

So, the flow is all ready to run.  After saving it, on the main screen you can click the Run button

This will check your permissions on the first run and then prompt you to give us the name of the Oscars List you want to create.

Once you click the Run flow button it will execute the flow and build your list.  It works just fine and we have the Movie Name and the number of nominations for each one as the task name

The task itself has in the notes the list of nominations as an unordered list, but it didn’t quite work.  There are two things wrong here.  One is that the unordered list isn’t formatted correctly.  This is because we are mixing the HTML code and the strings and Flow doesn’t know which is which.  The solution is to build a single string with the body text.  Which brings us to the second issue.

Sound of Metal has six nominations but the list always will have 10 because that was what we created it to handle.  The bottom 4 are blank.  Is there an easy way to only display the right number?

I reached out to a few friends who are big with PowerAutomate and my co-worker Pontus was able to give me some advice.  There isn’t an easy way to do what I want here (yes, we could change the data, but we are often presented with data in a format that isn’t easy to work with and part of this exercise is to take data that isn’t in a normal form).  So two things need to be done.  First I need to create a variable (a string) for the body of the task that builds out the nominations, and then set that variable for the item with the correct number of nominations.

NOTE: The data pulled from the Excel workbook is a string, so in the Case statement we have to wrap the number in ” or you will get the following error. “An unexpected exception encountered when evaluating branching condition.”  Check out this post that talks about it and where I found this solution

Once we have all the cases built, then we can add that variable to the Add a to-do task

Wrap Up

So, that is it.  We built a PowerAutomate Flow to make this work, and now I will share that with you as well.

Here is a link to the Oscars.xlsx file from my OneDrive.  Download that and put it in your OneDrive or SharePoint where PowerAutomate can get it.

Here is the PowerAutomate Package for you to import into your environment.  After you import this. you will need to edit the Flow to point to the Oscars file where you placed it.

Last note, I only built the switch to handle 1-6 and 10 nominations.  IF we wanted to use this again next year we might need to update things.

Note after the last note: You then have to fiddle a bit with the connections and the location of the file in OneDrive.