Resources
Transcript for Video
00:00
Today I wanted to do a video on Teams to show you how to make an Excel spreadsheet that has all of the do and overdue tasks and to flag the overdue tasks across your entire group organization. This is not functionality that comes with Teams out of the box. I’ve seen a lot of people ask about it. There some forum stuff. I pieced through that and worked on it myself to get a more complete solution that works for me. I figured I’d put together a quick video, show you how I did it, and I will provide a link for you. At the end of the video, I’ll tell you where to go get a link for it. The whole purpose of this is let’s just say you have these four or five channels. The way we do our work is we have client channels.
00:51
So John Smith. Mary sue. Whatever. Client one. Client two. Their tasks are done through tasks by Planner inside of Microsoft Team. You can click on their tasks and we have separate planners or plans for each client. So you’ll see that these are different. This one’s, I can’t believe It’s not done. This one’s got an overdue one from last week and then one due tomorrow. You can see that it’s overdue by the red on it. Microsoft Planner Teams does not have a way for, let’s just say the CEO or the boss or whoever to see what’s overdue across the entire organization. They can’t even see what’s ever due for a specific person. They have to click on each client, click on the plan, and then see what’s ever due. You can also do it inside the back end of Planner in the web browser, but it’s not much better.
01:47
You have to click on each plan. Pretty, not good, especially if you have like we do usually about 80 clients at any given time. What we are going to do is let me pull this up without showing you anything you’re not allowed to see. Let’s see that’s safe. We are going to do an Excel spreadsheet and I’m going to pull it up right here. What it’s going to do is it’s going to have the title of the task, it’s going to have the name of the client, it’s going to have the due date, and then it’s going to tell you whether or not it’s ever due or not. We’re going to set this up and I’m going to show you how it works. We’re going to be using Power Automate do this. I have already loaded up my template for this. What you’re going to have to do is you’re going to have to go to Myflows and let me save this real quick.
02:45
Oh, it’s mad at me. Yeah, I know. Okay, so what you’re going to have to do is go to my Flows. You’re going to import a flow because I’ll give you a link for one to import and you’re going to click on Flows, going to click on Edit and then you’re going to be here. Whenever you load up my flow, or if you do it from scratch, I’ll show you the text from scratch as well if you want to try to type it out yourself. I have set to recur once a week and then I am not going to try to explain to you how exactly this works, but essentially what it does is it’s going to pull the name of the client and insert it into each task. Not going to explain to you how this works because it took a lot of trial and error for me to get it right.
03:27
What we’re going to do is we are going to first you’re going to pick your team. So in this case or your group. In this case, I want all the plans from the Adam Playground group because that’s my playground to play with YouTube stuff. We’re going to load up all the plans. In this case, we’re going to have plans for, I believe probably it’s going to be two plans. I think client one, client two and maybe one for tasks. It’s going to go through and it’s going to pull the names of all the plans. For each plan, what this is going to do is it is going to pull the name of each task. This is going through and for each plan it’s going to pull the name of each task. This is essentially, and again not going to try to explain the variables. It’s a gigantic pain.
04:18
What this is doing is this is setting the value of each group or each plan and then setting it to my plan name so that they’re the same. Because then what we’re going to do is we’re going to show each list inside of each plan and that’s the wrong group ID, let me switch it to the playground. What this is going to do is it’s going to add the plan name to each task. Again, this perhaps a little more detail than is necessary, but that is going to allow this row right here for client to fill in correctly. Otherwise it shows the plan ID number, which is useless because it’s just a string of numbers you could put into your browser and pull it up. But that’s sort of piece point. What we’re going to do is we’re going to only get the tasks that are not finished.
05:07
So that’s the tasks. This is value percent completed and it’s less than 100, which means it’s unfinished. For each one of those unfinished tasks, we’re going to add a row to. This is actually almost the right spot. You have to go into your SharePoint back end or your OneDrive, and then set up a Excel document. I have it saved here second, and then YouTube video. Here’s my Excel document I set up for this purpose. I’m going to choose that document and it’s going to load. This is the tables. I have one table, YouTube task table. If you see here, it’s got title, client due, date, past due. I don’t know why that’s right there, it’s weird. It has the title, the client due, date, past due, which you see is going to oh, I see it’s picking that up. It’s going to find those four rows.
06:19
You’re going to import the title of the task into title for the client. You’re going to choose that variable that you set up before. For due date, it’s just going to be due date. So this is the whole thing. Again, I will export this, provide a link. It’s going to be a lot easier. That is showing you the the columns you need to change. The columns you need to change are this one list the plans, pick your correct group. This one, you got to choose the correct group again. This one, which is going to be the location of your Excel document. So this is all we need. So we’re going to hit save. Hopefully nothing gets mad at me. Okay, seems good to go. So I’m going to hit test. Oh, wait, I need to enable it. Give me 1 second while I figure out how to enable it.
07:23
Of course, it’s right here. Turn on. We’re going to test it, edit, test manually, test run, flow. You’re going to see here it’s going to show your flows running. It’s going to have this apply to each. Depending on how much stuff you have, this could take . What I’m going to do is I’m going to pause the video, wait for this to finish. Oh, never mind. It’s done. I guess because there’s not much going on, I want to say it takes about five to ten minutes for my primary organization, the law firm. I’m going to pull open, close this again, and let’s pull it open again. Probably did that. Oh, it hasn’t loaded yet. One of the downsides of OneDrive and SharePoint is it takes a minute to load. Let me give it a minute and then we’ll pull it open. All right, now it’s working.
08:28
We’re going to click on it and then you’re going to see here it is, one, the titles of the tasks, the names of the clients, and then whether or not it’s due or overdue. You’re going to see this first column is going to always say overdue. That’s where we’re putting the formula in. This is the formula to decide whether or not it’s due or overdue. You’re going to see anything without a due date is considered overdue, which is intentional because everything should have a due date. I can’t wait. This isn’t done is overdue because it was due back in January. The one that’s due tomorrow is not due, and the one that was due last week is overdue. You can sort this by A to Z if you want to, and you can see, okay, go away. You can see either not due or overdue.
09:17
I guess. Let’s sort z to a that makes more sense. You can see all the overdue tasks by the name of the client, and then you can go from there. Obviously, with the small data set, it’s not super useful, but the way that this column is working so this formula is pulling off of this date. This date is basically what it’s doing is it’s creating the date in the same format that planner exports it in. It’s the same UTC standardized date format, which that’s why it’s got the Z at the end. You can see that this text now is text is spits out in text form. Now is to pull the time as of when the spreadsheet was last refreshed. This format is the UTC standardized data set time format. What it then does is it says which one is less than the other. If the one in here in this column is less than the past due date or is less than this, then it’s overdue.
10:22
If it’s greater than, it’s not overdue. So I hope that this makes sense. I know this is a lot to take in. Like I said, I’m going to provide to you this spreadsheet. I will make a copy of this. I’m going to put it on my website. You’ll be able to download it there. I will also provide a link to the flow so that you can just import it. If you have any questions, let me know. I know this was perhaps not the cleanest tutorial I’ve ever done. It’s a little more complicated than most of the stuff that I’ve shown, few more steps. I do think with the formulas and the templates, that you should be able to import it pretty easily. Of course, you can do whatever you want with this. You could make it blue, you can make it red, beyond the scope of the video.
11:07
You can make it nice and pretty. I just wanted to keep it nice and simple for today. All right. I hope this was useful. Thanks. Bye.