Excel to Planner Using Power Automate - Create Task , Create Checklist, Update Tasks and Checklist

[Music] [Music] hi everyone i'm deepak srivastav and welcome to my channel and thanks for subscribing to my channel and if you have not yet subscribed go ahead and click subscribe button and also the bell icon for the notification so today's video is all about automating planner task creation using power automate but before that uh let's take a quick uh walk through on the planner itself so if you go to task.office.org this is how you start logging into your planner site and once you log in you can see your different plan that you have created if you have not accessed this site before then you may see this empty but if you are using it you can see all of your different plan as i'm seeing here i have four plan right now under each plan you can see all the different tasks that are created and also the different bucket so just to give you a quick background of bucket so bucket is nothing but a grouping of different tasks in your given plan if you wanted to group certain kind of task together you create a bucket by default when you create a plan you always got a to do bucket created but you can always come here click on add new bucket and create a new bucket and under that bucket you can create different tasks so for an example i am having these three tasks right now here each task can have multiple metadata or property for it for an example you can see here saying pro progress priority start date due date notes checklist item attachment comments as of now these are the only field that you can have for a given task so if you are creating a task these are the field that you can use to perform your workflow or any any other reason we are creating a task for checklist is nothing but more granular work item for this given task so if you are creating a task but that task required multiple checklist item to be completed when you can actually mark that as completed you can define that as a checklist item it's very useful because this will reduce the total number of tasks that you may end up creating if you don't have the checklist item okay so this was just a quick run-through of the planner right now if i wanted to create a task under this plan i can go one by one and add each task at a time also if you see if i click on add task i can only provide information like task name due date and assigning and once my task created after that i can click on that and provide some other information like for example checklist item or maybe if i need to change the progress or priority or the other notes comments right it's good but if i want to create let's say 50 tasks all together then i need to do 50 times i need to create one by one so why not use power automate to automate that class creation process and this is what we will learn today okay and before we go to power automate let me show you the excel file that we are going to use as the source for our task i'm using excel you can use sharepoint also you just need to create a list with the same column and similar information okay so what i did i created this excel file uh this is the my first tab task under that i have task name plan name bucket name priority notes assigned to due date percentage completed and task id right now if you notice these are the very similar columns or fields that we have in planner i also set up some sample tasks so as you can see here there are nine tasks that i have created plan name if i go to my planner and if i go to the planner tutorial so this is the plan that i have created i pre-created this so we can use it and i also created the bucket called community work that work that's what i'm passing here this is my plan name this is my bucket name and then i'm just setting the priority assigned to due date and percentage complete so we are ready to go to flow now so what i'm going to do i'm going to start creating a flow for this demonstration i'm creating instant flow because we need to run it multiple time but you can also use the same approach for automated or scheduled flow there is no difference so my flow is created now the first thing that we need we need to get all the tasks that we have created in our excel click on new action and search for list rows present in a table okay so this action will give me all the rows in a given excel file in a given location location as i have told i'm using sharepoint you can also use onedrive document library is my documents file name this is the name of my file and the table name okay now if you don't know how the excel and power automate work i have another video go ahead and check it out so we got this information click save click on show advanced option under date time format select iso 8601 this is the new update in excel action and what this is going to allow us it will allow us to read the state field as it is instead of that full integer number so now we have connected to excel the next step that we are going to do is for the planner so before we select the action type planner there click on the planner these are the different actions are available right now in power automate for the planner so as you can see here there are create tasks add assignee to task create bucket delete tasks get tasks there are so many right update tasks update task details we are going to use uh not everything but most useful to create an update but once you get used to it you can use other actions as well the first action that we are going to use is a create task pick the preview one because it has this priority field added to it if you pick just the create task it will not have the priority as of the time when i'm creating this video okay group id now group id is nothing but your location of the planner so where you have created that planner so i created the planner under my this environment or this group then the plan id so i know this is the plan id uh planner tutorial then we have title so now title is going to be the task name okay so i'll select the task name as soon as i do that power automate is smart enough so it will going to add that loop because i'm doing the list represent in the table and it can be more than one rows okay so that's good okay in the bucket we know we are creating in the community work so that is our work it name and if you go here you can see in the planner that's the bucket that i'm using start date uh because i don't have the start date column in my excel i'm going to use the utc now and utc now will give me the time as of right now due date we know we have the due date column here assigned to we also know we are passing assigned to then you can also define these different categories then priority so if you notice as soon as i i clicked on priority i lost my list represent in table actions right so what you can do click somewhere else like for example i clicked on assign to click on priority select that priority field ctrl x go to the priority column control v so this way you can easily copy paste the priority information here okay so we are done let's save and run okay so flow is running as you can see here and if i go to my planner and if i refresh as you can see here my task got created and i should go back flow is completed successfully and if i come to my community work bucket i can see all of these different tasks that were i was having in my excel got created i can go to each task i can see all the information here right and if you notice the priority is also set it up correctly now if you see this well icon this is urgent explanation is important and this is based on the number that we have passed and also it is also assigned to the correct people right so creating task from excel is very simple and straightforward now if you notice the information that we have provided is populated correctly there is no issue with that now let's say i also wanted to create a checklist item for these tasks and i i want this to happen automatically from the planner so how you can do that so go back to excel i created another tab called checklist and in the checklist i created these four column id checklist title completed yes no and the task name so let me add like two three checklist item here so that we can use in our flow okay so i created these four checklist items two for the first task that is this one and a couple of them for create block material okay now let's go back to the flow so we don't need to make any changes to our existing action because we still need to create the task now let's add another action where we will get those checklist item from an excel for this given task and create them okay so yes first thing that we need to do we need to do the list represent in table because we need to get the checklist item okay location is my sharepoint site document library excel file and this time i'm going to select the table 2 because if i go back to my excel this is the table 2. so my checklist is under table 2 but we don't need everything from that table instead we need to filter so what we are going to filter with task name we are going to say task name equals to title of this create task so we are going to filter the data that we are getting from the checklist item for that given task okay now the next action that will help us to create those checklist item right so yes we need to go to the planner and we will look for action called update task details okay select that and as you can see here first thing that this action need is the task id and we know the task id because we are just creating the task on the top so go to the enter custom value and then from create task action so look for the create task because that's the name of our action and then id okay so this way you can get the id of the newly created task then this action has two field one is alias so this is for the file attachment so if you want to associate a file with the url to this task you can do that the second is the checklist as you can see here the information that we need to provide is the checklist id checklist title checklist is checked or not if i go back to my excel these are the three column i have the last column i have because we need to filter okay so now let me show you how this information look like so i'm just putting some sample information right now for the testing if i click on this small icon you can say it's an array so what we need to pass we can pass the array with multiple values here so what we can create we can create an array based on the item that we are getting and pass it here so for that let me first initialize a variable type it's an array because that's what we need right and then when you are just after the list represent in a table add an action append to array variable okay so that's what we're gonna do select the name of the array that we are going to append so this is one and then if you notice this this is the one item of this array so i'm going to copy this paste it here and then what i'm going to do id is the second or the checklist item that we are getting id right again as soon as you select the follow will going to add a loop no don't worry about it the title we know we are getting the checklist title and then this one use an expression here so what we are saying if completed value that we are passing from the excel is false then we are going to pass the false else true so this would be a small very basic expression here just past the true or false because this is true or false field and once you get that variable created what we need to do we need to come to this checklist section here you can remove whatever is there and select our variable one more thing i would add here so once you done with updating or creating the checklist item if you look at the excel file and if you look at the task i have this task id so what happened when you create a task in planner the planner create the task and it also create a unique id for that given task we can use that unique id to update this excel again so that in the future if you need to update any information in bulk so for an example if i want to update the priority of all these different tasks altogether i can actually use power automate based on that id and update the priority right so i'm going to add one small action here at the end again that is excel because we are updating our row so click on update row location my sharepoint site location document library file table this is a table 1 because we are updating the task key column is my task name and the key value from the create task title and the only information that i'm updating is the task id and in this i'm passing the from the create task action id okay just add and you need on this action only if you are planning to update the planner task from excel if you have if you don't have that requirement you can avoid this so let me save this i also clean my planner so it is empty right now and let's run this okay so as you can see the flow is running and if i go to my planner i think i should start seeing those different tasks here okay now let's check if flow completed successfully as it is and if i go back to my planner task you can see all these planner tasks created as we need it and if i look at these two planner tasks in which we have actually provided the checklist item you can see here it's saying zero out of two zero out of two and if i go to the detail you can see those two checklist item also got created and because we are passing that the checklist item is not completed it's still saying as not completed right this is how you can add more checklist item as needed for a given task now let's talk about updating this is only applicable as i said if you are trying to bulk update the planner task from the excel okay so how you can do that now if you remember during our flow update we added this task id column and we also added this id update action so now as you can see here all of my tasks now having the task id that is created in the planner so now if i want to take this excel and create the update i can do easily because now i know the id of the each task and based on that i can update information so how you can do that let's go back to the flow and also let me rename these actions so that we can easily understand them now what we can do because we know we have a column in our excel called task id we can check if task id column is empty then it's a new task if it is not empty then we need to update the task okay so i'm going to add a condition here so that's going to be my first action after apply to each task and what i'm checking this condition i'm checking if task id is empty and if it is empty then i'm going to create the task if it is not empty then i'm going to use another planner action update task okay and what this need it needs the task id and we know because the task id is not empty so i'm going to pick the custom value and then task id now as you can see you will get all these columns you can select the information if you wanted to update all the information or you can just select the information that you wanted to update so i'm going to pick the percentage complete and as i said earlier if you don't see once you click click on percent is complete and if you don't see the right column and you don't want to do write an expression what you can do you can select anywhere else pick the column that you want and then select that ctrl x and just paste where you want that to be used okay rest information you don't need to make any changes so if i want to update the checklist item also this action that we have previously written will still work get checklist item all of this information is work the only thing you need to make sure because here we are passing the title from the create task but when we are doing update task you need to pass the task name from here so how you can manage that so let me show you create a variable and we will call it task name and then what we're going to do in our condition we will update that variable so set variable task name and because we are under create task i'm going to use the title and in the update task side i'm going to do the same thing set variable task name and here we will use the update task detail title okay and then in this filter query where we are filtering the checklist item we will just use this variable okay now similarly if you look at the update task detail in the task id we are again same passing uh the create task id so we need to create another variable here so this is my task id and very similarly i'm going to add an action here and set variable task id and this is going to be the task id of this create task okay and in the update task section set variable select the name of the task id and this time it is the task id that we are getting from the get task okay and then only thing we will do we will in the update task detail instead of this id column here we will use our variable okay and this update row this we don't need here we will move it here and the reason i'm moving here because we only need to update our excel when we are creating the task when we are updating the task we don't need to so this flow is ready now we are going to now update the existing task that we already have so we already have the task let me update the percentage here okay and in the checklist also i'm going to say this is true this is completed this is also true okay so let's go back to the flow and save it and run it and let's see if it works okay so flow completed successfully and now let's see if how the flow runs so yeah our condition of updating is true because we had id for all of our tasks so it's going into the update task and as you can see here it's updating the percentage complete checklist item and all let's go back to the planner refresh it okay so as you can see here the percentage complete has in effect if i go to each task and if you see one i set it up hundred percent so that is completed so as you can see here now let's see this task because in this we have the checklist item one checklist item also got completed because we set true so this is the bulk operation that we have just did using this flow that's it so let me know if this tutorial helped you thank you very much thanks for watching and also don't forget to subscribe thank you and keep learning

Channel: Deepak Shrivastava

Views: 7,578

Rating: 4.9633026 out of 5

Keywords: microsoft planner, power automate, power automate tutorial, microsoft power automate, planner tasks, microsoft planner tutorial, power automate excel, microsoft 365 tutorial, excel to planner, planner from excel, planner tasks from excel, excel to planner tasks, use excel for planner, planner, power automate planner, office planner, planner how to use, automate planner, automate microsoft planner, deepak shrivastava, update planner tasks from excel, planner checklist excel

Id: 5lGkclFRJp4

Length: 22min 41sec (1361 seconds)

Published: Tue Apr 27 2021