Excel to Planner Using Power Automate - Create Task , Create Checklist, Update Tasks and Checklist
Vložit
- čas přidán 6. 09. 2024
- Hello,
In this video, I will show you how to create Planner Tasks from Excel using Power Automate automatically
You will learn:
1) How to create Tasks from Excel
2) How to create a Checklist from Excel
3) How to update Tasks and Checklists
Thanks for watching.
Reference material:
docs.microsoft...
Dude, your videos are valuable to some small businesses out there from over the world, keep up the good work by sharing your knowledge! Wish you and your family all the very best!
Thank You!
very good. Flow is amazing. ...My first response is that I would use Excel once or twice just to get the tasks etc set up. For me that is about all I need.
For a greater purpose...
Most people however will check the tasks off as they complete them inside Planner or Teams.
In the event you want to produce reports/graphs on progress inside Excel, this should in theory flow back to Excel to keep the Excel sheet up to date right? ... otherwise any changes you make in the original excel might be overwriting a completed task incorrectly. How would get around that issue? Thanks for the vid....amazing and saved me about a day of work
Thank You very much, finally find what I need. I'm new in Power Automate, could you please share the flow package export?. Just to lean from a working flow.
Thank you very much for the video. Can you help me with something? I need to update tasks that I have already created to add checklists to them. However, I don't want them to be recreated; I just want the checklists to be added to the existing tasks. Many thanks for your help.
Is there a way to make the Bucket more dynamic? As it is, it will only update one Bucket at a time.
Good suggestion, I will try to find more dynamic way..stay tuned.
Awesome video, Deepak! Been trying to figure this out off and on for the last couple of months!
You're explanation is very good!
My biggest fear is the inadvertent or malicious deletion of a plan. Being there is no way to recover a deleted plan I have been working on a flow to backup all data to excel. Now that I know how to restore it I am less worried. Thanks for that!!
Very happy to know that my video helped you. Thanks for watching 👍🏻
Thank You very much, I've implemented this flow step by step. I works but not che checklist part. I adds ok the checklist Items for the first task that have checklist but the second adds there own checklist but the first remains, and go on keep growing the checklist array. I suspect that a clear array is missing in the loop, I works fine for you in the video, so I double check everything but I couldn't find the error. Any help or advice please?. Thanks again.
Me too. In an earlier comment a guest mentions this, ‘reset array’, not sure how to do this
RE: Checklist ID column - does that mean that we just assign the id to the checklist in ascending numerical order? The first time we see your checklist there is a repeat '2' in ID but the last time we see it, that checklist item is now '4'. So the first time the ids are '1,2,3,2' and the second time they are '1,2,3,4'. Thanks!
Thank for a very helpful video. ?What command do I use for sorting tasks in different pre-defined Buckets when I import them in Planner?
Thank you for the amazing video! Just to ask, in your opinion can Automate be used for more dynamic tasks? For example, my team get a lot of “reactive” tasks (as customer queries come through) with a mix of “predictable queries” (audit, reports). We currently input all tasks init Planner manually, and I am trying to determine how automate this can become. Any help would be amazing!
This is on some other level
Thank You.
Great video, thank you. I can't seem to find a way to update the priority. The only place priority is mentioned is on the creation stage. Please can you help?
Hi Deepak. I was searching for the update task with task ID functionality. It is a great video. thank you so much
Glad it was helpful!
Hi. I understand you made some cuts to save time but for the ones like me who are beginners, some actions are missing. Also, the filtering thing, you type something that is not what is then shown on screen (' ') appear from nowhere. My code always crashes at this step and I can't figure out why because I can't get the difference between what you do and what I do... Please don't skip steps...
Hello Deepak. This is an awesome video I am using it to build my exact same application, I got everything right except the "Update Task Details" I am desperate now, I have tried a number of times to see where my problem is, I have researched other blogs etc. the error I get is "not found" error 404, when I run it. I have deleted it and put it back up and nothing seems to work. Can you please guide as to what I may be doing wrong? Thank you so much in advance.
Hello !
Great tutorial !!!!
I have a question about this link between Excel and Planner
If my colleagues modify the tasks on Planner (date, bukets or anything else) how do I update Excel?
Can we also have all the actions in a single Excel cells ?
Hi Deepak
That was great video, how we update the task labels using power automate using excel.
Appreciate if you could make a video on it.
Thank you
There is a preview action "Update a task (V2)", that will allow you to update task labels.
Hi Deepak, great video! I've managed to build my flow following your video, very much appreciated. I do have a question however, in my Excel table I have a column where I have listed a link to attachments for certain tasks, but not all. Are you able to show how to update the task details with attachments please?
Thank you . Check this video : Create Planner Tasks from SharePoint list with attachment|Power Automate #powerautomate #sharepoint
czcams.com/video/GCtXprXyO40/video.html
Thank you for your reply@@DeepakShrivastava , unfortunately though this isn't what I'm looking for. I'd like to update the task details with attachments using a link from the Excel table that I have created using your flow for this video?
@@user-td7de4lt1u I have this implemented within Deepak's tutorial for my own needs. Just like the checklist tab with its checklist table, I created an attachments tab with a attachments table in it, with the same setup as the checklist items (but no ID column, instead columns for Attachment Name | Attachment Type | Attachment URL). Then in Power Automate, everything that he did for checklist items, you do it the same way for attachments. It works.
Deepak, you should tell that before the Append array loop, you need to reset the Array variable to [] (set the array variable with the value of an empty ([]) array varriable)
Thank You.
This is where I’m getting stuck. Good spot! Is there instructions on how to reset the array? Otherwise the second and other taskname has all checklist items, not the filtered list
@@alexjans3797 I have the same question and tried my best to find the answer and luckily I found it. All you need to do is add a "set variable" before the "append to array variable". In the "set variable", type null in the value field, and it should work. Tried mine and it is working.
This was so helpful - thank you very much!
Thanks for watching 👍🏻
Thank for making this video Deepak. Can you also create a video where we can assign the bucket name based on value in excel.
Its in my list , will create soon
Hi Deepak, firstly, thank you for taking the time to create this tutorial. I'm experiencing a Syntax Error with the Checklist Item creation. Might you be able to provide some assistance? If so, what is your best contact?
Thank you for watching. I would suggest you ask your question at powerusers.microsoft.com/t5/General-Power-Automate/bd-p/MPAForum , msg me here once you ask I will try to help you there 👍🏻
I'm using the "Recurrence" trigger that runs twice a day instead of "Manually trigger a flow". My issue is that it is creating a task for ALL rows every time it runs, instead of just creating a task for the new rows that were added since the last run. How can I fix this?
Same here.
Is there any video that shows moving tasks into different buckets automatically while uploading the excel tasks ?
Terrific! I have a colleague who shares specific rows in her spreadsheet with me about tasks. This should work well to have her updates automatically show up in planner for me. If we have multiple buckets, will i just need to add more steps to update the excel sheet whenever I move a task from one button to the next? what would that look like?
I'm really hoping for two-way sync, because she may update the spreadsheet as well
Thanks for the comments. Yes you can definitely do two way sync. it all based on the Task Id.
Thanks for sharing the video. After trying to setup a similar flow it failes. I do not get the TaskId back into the excel. Could you please advise.
Hi Deepak - you'll notice (this is a common error) your due dates in planner are actually off by one day when compared to your Excel sheet. Not sure if you have a simple hack for this.
Yes I know this issue , it’s not only with power automate but with SPFx too. I have tried some hack previously will check and share.
Not sure if this will resolve the issue but I saw another YT video where they changed the UTCnow variable to a specific time zone. Might be worth giving a try! Cheers 👍
If the value is a date w/o time, create an Add To Time step [add 1 day] before the Create Task. If the value is a date & time, create a Convert Time Zone step instead [source time zone to UTC 0:00].
Actually, your date and time are UTC format (Greenwich) and depend where you live, it may be off by few hours. I solved this with formatDatetime and addHours ex.: formatDateTime(addHours(outputs('DueDate'),-4),'yyyy-MM-dd HH:mm'). Hope this help !
What is a simple process to use Power Automate to create the buckets and then put the tasks under the correct bucket? I'm not having any luck.
Lot ok thanks for this video ! Great but I have a question, on your video I don't see the creation of de "Set Variable" just before "list row in a table 2"
Sorry for my English language
I like the explanation you provide and the dedication. I tried adding the priority and couldn't add it as a form. Can you help me?
Thanks for watching. are you getting any error?
In your Excel sheet for column "Priority" enter 1 for urgent, 7 for medium and 10 for low
Great video! Can you update the notes seriously and add attachments from a SharePoint list into planner? If so, how can I do this?
Yes , you can do that , I will create a a video on this soon. stay tuned 👍
Fantastic .. great bro. Looking extracting data for planner to excel in sheet. It will be for helpful.
Thanks for watching, it’s in my list I will share soon.
So what if add a new task will the flow run from first to last and create all the tasks again?
Then its going to duplicated right?
I have the same question.
Hi Shrivastava!...The flow that you build (Excel to Planner Using Power Automate - Create Task , Create Checklist, Update Tasks and Checklist) it is fantastic. I am trying to build my own and I got into a problem. My excel file I mimic what you did; however when selecting Table "select a table from the drop-down" my Power Automate is only showing Table1 and not Table2. I am using Excel business, everything the same. Any recommendations to get over the issue. Thanks for the great Demo
Power Automate dropdown will show the tables that you have in your Excel. if you only have inserted one table than "Table 1" is correct. you should select that table only.
Thank you! My IT-department told me this wouldn't be possible... This would work with a Sharepoint list too, right?
Yes . To update the Task you should maintain the task id in one of the SharePoint list column ( similar to as I am doing in excel)
Hi Deepak. Thank you for this video, I think its the only one out there that shows you how to keep updating from excel. I've been trying a test file with 6 tasks to be imported to planner from excel. All of them import into planner with no issue. However the update row function doesn't always work to input the task ID into the excel file. For my test file only 4 out of 6 get updated. If I rerun the flow, it just keeps adding the 2 with blank ID cells as new tasks and does not update the excel with the task ID. any idea why this is happening? thanks!
Hi
First thanks for this great video !
Im facing an issue with the checklist update as the function used is actually adding values to the Checklist variable ("Append to Array Variable"). That means that when you reach the task 2 you add elements of checklist of task 2 to the variable which already contains elements of checklist for task 1. Then task 1 is correct but task 2 has the checklist elements of task 1 + task 2
How did you manage this issue ? We should reinit the variable between process of each task
Thanks
I'm having the same issue did you manage to fix it?
@@FromTheBikeShed : yes I did, by adding the set variable checklistitems to null at the end of the process. Make sure that this task executes even if previous tasks fail so that you make sure that this variable is always cleaned up
I hope you were to fix the issue. Thanks for watching.
@@jcex I'm having the same issue but I can´t understand what you did to fix it, could you explain with more details?
I watched the whole video and I fixed the model, thanks!
Hi, you have a video where we can assign the bucket name based on value in excel.
Hi Deepak, this is exactly the video I was looking for! however I was stuck at the append to array variable section where it doesn't show dynamic content when i try to click on it, also tried going through the expression function but when i go on to recode it, it creates another 'apply to 3' on top of my 'append to array variable' and my codes become "Apply_to_3" instead of "Apply_to_2", would you happen to know why it turns out like that?
you should see it after clicking on view more..
hey i'm having a trouble on reverse i need the planner to export to the document library in sharepoint, the only problem i'm encountering is that the "LABELS" it gives me wrong output
Hi, Deepak, thanks for this amazing video. I'm trying to create checklists on my tasks and when I run the flow, the first task is created with the checklist correctly, but the second one is created with its checklist and also the checklist of the first task. Do you have any insights of what would I been doing wrong? I noticed that your checklist ID has repeated numbers (1,2,3 3,2). If I try to put the ID like you, the flow fails and says that I can't have duplicated IDs.
Hi Karina, Thanks for watching. Make sure you are resting the checklist variable before setting it with new checklists items. I have mentioned in the video to reset the variable to avoid duplicates.
@@DeepakShrivastava which part exactly have you mentioned about the reset? Thanks Deepak.
@@DeepakShrivastava I have same issue, could you tell me more because i do not find this part in this video.
I was having this same issue. I added a "set vairiable" action at the end of the the "apply to each" loop. Then chose "Checklistitems" as the "name" and set the value to be [].
Hi Deepak thanks for u video! my name is Mario from Chile, sorry for my bad english :( i have a cuestion, how can i make the field "Bucket Id" dynamic? i explain, i try to start flow, i put the number for "Bucket" on a variable, and after in "Bucket Id" field take this value for complete the task. But this field dont read the variable, just read when i select an item in the dropdown list.
Hi Deepak, can you teach how to update excel from task and bucket of planner using power automate?
I would definitely add this to my list for future video, thanks for suggestion.
hello Deepak, how to show checklists on task as Show on Card option through flow?
Big thanks if it is possible.
Hi Deepak great video.! What about reoccurring tasks for example every quarter, can we use the same tools ( flow planner excel) to create a reminder?
Hi Maria, Thanks for watching. Yes you can create a "scheduled flow" for this purpose.
Is this a onetime flow or will this process continue to populate planner as I add rows to my excel?
How about importing Jira tasks to MS Planner?
I have not tried it . I think you will require to use api/connector to do this.
Hi @Deepak Shrivastava Your video is truly amazing! I spent the whole night learning from this. It was really confusing for me but I somehow managed to make it work! It really felt amazing when the test succeeded the first time.
I have a question about buck list, it seems I have to manually set the Bucket Id in the "Create a task" step in order to have the tasks set in the right place. Even if I put custom value >> dynamic content >> list rows present in a table 'Bucket' , it doesn't seem to work, any ideas what I'm missing?
Thanks for the comments.
To pass the bucket name dynamically, when you are creating tasks, you have to use the Bucket id, not the bucket name. If you use List buckets action, you will see Id associated with various buckets under each plan. to make it fully dynamic. You may need to do some conditional logic to get the correct Id for a given bucket.
If you need more specific help with screen shots and sample feel free to ask your question at Microsoft Power Automate forum powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity
(you can tag me too if you want @deepak_s22)
Hi Deepak, Thank you for this tutorial. I scoured the internet and your tutorial was the closest to what I need: Create a task plan (this part of my flow works - I customized it with label color as well). I'm struggling with part II: Update Plan Details. I simply have checklist items to add to CERTAIN tasks (not all have a checklist) that I created. If I follow your tutorial to add (import) my checklist to the tasks via Power Automation - the error I am getting on the actual generated planners are: 1. I have 6 checklist items and all 6 are marking as completed and being added to ALL my tasks when it should only be appending to certain tasks. I put 'false' in the 'completed' column in the Excel and yet the checklist is crossed off in the task. So there's two issues. Can you please help me? I've been trying to figure this part out for a couple of days already but I'm not a developer and this is my 1st time using Power Automate Excel Flow. I can email you screenshots of everything. Thank you
UPDATE: I figured out how to fix the checklist items from being marked as complete to unchecked: I used this expression instead: if(equals(items('Apply_to_each')?['Completed'],'FALSE'),true,false) and the items are no longer marked as complete. However - I still have the issue of the checklist showing up on ALL my tasks on my planner. I need the checklist to show up on certain tasks on my planner. Thank you
hi, did you can?
Is it possible to label tasks by the coloured labels through this way?
Great video and instructions Deepak, what do i have to do in order to new planner tasks in excel ? I also would like to manage user from excel in order to not modify the PA every time a user quit our org... can u show this on another video ?
Thanks for watching. Yes I will create another video on planner.
Hi Deepak, I have followed all the instructions as you shown to update the checklist items. Reading checklist items from the table using filter query is working just for once, and after that it is creating task but not updating the task with the checklist.
The checklist table output is having empty at the values[ ] . Please assist me to resolve this.
I need to check your flow in detail. I will publish the flow I used in this video by the end of this year. Stay tuned to the community tab for further updates.
Thanks you a lot ! I have juste one problem : when I run power automate, it recreate a task and my condition didn't works. What did you use as condition to know if you need to update or create?
Thanks for watching. I am checking if "Task Id" is empty or not and based on that create/update
Thank you so much Deepak 🤗
My pleasure 😊, Thanks for watching.
I want to import tasks to several buckets. The bucket sames would be in the spreadsheet. Is this possible somehow?
If you want to import task from multiple bucket, you can first query all the buckets and loop through each bucket.
hi, amazing video! directly put it into use :) now, i am struggling for setting up the same checklist items for several hundreds of tasks. any chance you could support on that end? thank you very much in advance!
Thanks for watching. are you facing any issue?
Hello Deepak, thanks for the amazing video!
I got I little lost when we try to update this existent planner into the same flow. I am not able to enter the conditional prior to the create a task action (18:13min). What can I do to get more assistance?
I would suggest you can ask your question at the Power Automate forum powerusers.microsoft.com/t5/Microsoft-Power-Automate/ct-p/MPACommunity and share more details. Tag me (@deepak_s22), and I will try to help you there.
I have a flow for creating and updating an items from excel to share point list but the column formatted as a Number in SharePoint not accepting the Blank values. Can you please help me to fix this issue.
Deepak this is a good presentation and you talk through things really well. Only one thing I don't understand.
How is it that your flow is not creating duplicates every time you run the flow? i.e.
9:00 you run the flow and create the tasks
12:30 you add in the capability to add checklists and re run the flow. But the flow isn't checking at the create stage whether the task already exists so how is it that new tasks haven't been created (duplication). The part of the flow updating existing tasks doesn't happen until after tickets have already been created in the first part of the flow. Did you delete the original tasks?
Thank You! Yes for demo i may have cleaned the planner before running it. In production you should have a check if task already exist or not.
Hey Deepak, when passing my expression function i have false listed in my table; however, i keep getting a true and cross outs. Can you publish the actual expression here that we can copy and paste?
Where you setting the variable "ChecklistItem" in "isChecked" field use following expression:
if(equals(items('Apply_to_each_2')?['Completed'], 'False'), false, true)
Hi,
I have a spreadsheet for a B.O.M and i would like to be able to make one line in the spreadsheet assigned to a worker in the task in planner. please help.
If data in spreadsheet is in a Table you can use Power Automate flow Excel actions to update a row. If it is not in Table than you can use Office script, Check out this video I created -czcams.com/video/VeqzlWNQjjE/video.html
Hi Deepak please publish some videos on excel automation using Power automate like consolidation from different exce files into one , adds/appends new row data from outlook 's excel/csv attachment to existing excel file etc.
Hi Preetam, Yes i will , thanks for the suggestions.
Help please
i have repeated process a few times i cant add checklist items but do get excel to be updared with planner id
it may be the expression it seems ok but i am not sure where i have gone wrong here is the expression if(equals(items('Apply_to_each_2')?['Completed'],'false'),false,true)
could the error be Apply_to_each_2'
Ideally i would also like to state the progress of the checklist item aswell (true or false)
Actually, copy and paste doesn't do it. You have to go to 'Expression' and type every functions (you can select them as you type) and you will succeed. Hope this help !
Hi all. My Filter Query does not support TaskName eq @{outputs('Create_a_task')?['body/title']}. It cant find the taskname enry. Any suggestions
Its not easy to help without having more details , but your filter query should be like TaskName eq 'outputs('Create_a_task')?['body/title']'
What is the purpose of the id column for checklist items in the excel document? Does Power Automate need that? if so when?
I figured it out. Its nothing special according to the excel document itself, but its something that Planner looks for when you dynamically want to implement check list items.
Awesome 👏
Hi, Deepak, what format did you set the 'DueDate' field in your Excel file? The flow doesn't seem to recognize the date field even the DateFormat is set to ISO 8601. The error is " body/startDateTime is required to be of type 'String/date-tie'. The runtime value '""' to be converted doesn't have the expected format 'String/date-time'. " Many thanks!
Hi, I´m having the same problem. Were you able to solve? how?
Is there any way I can contact you to ask you a couple questions about this for my work? I would really appreciate it
Please contact me on Twitter(@deepak_s22) or by my Facebook page facebook.com/Deepak-Shrivastava-108032534706197
can u show a flow for different bucket name.
Hello Deepak, thank you for your tutorial. I'm experiencing an issue with the checklist step, is applying the checklist items to every task I created (Even though every task has a different name) and also is marked them as completed, please can you advise? I've follow your instructions.
Hi Cedric, I am not sure if i can assist you fully here but check action where i am appending to "ChecklistItems" variable and make sure "isChecked" has expression like this " if(equals(items('Apply_to_each_checklist_items')?['Completed'], 'False'), false, true) (Note the "False")
@@DeepakShrivastava I am running into the same problem. Checklist items are populating every task and marking as false. More specifically Task 1 will have one checklist item, Task 2 will have 2 checklist items that includes checklist items from the previous task. I've re-created your video tutorial 3 times with the same result. Each task has a unique name and each checklist item task name are exactly the same as the tasks. Please help, this is driving me nuts.
Hi Gary, Check what you are getting in step "Get checklist items" for each task. It should have the correct information. If it does not, it means the Filter query in this step is not valid. If this step is right, make sure you are clearing the "Checklistitems" variable.
Here I am limited to help you because we can't share the screenshots. Would you mind asking your question at the Power Automate forum - powerusers.microsoft.com/t5/Power-Apps-Community/ct-p/PowerApps1 ? Tag me, and I will for sure help you there.
@@DeepakShrivastava thanks for your response. FYI, your tutorial is so detailed that i was thrown off when you did not outline the set variable action with name 'Checklistitems' and value 'null' after 'Get Checklist Items' before the 14min mark. Yet it shows up at 18:13 of your video. Your tutorial was super useful, thanks again for posting.
Awesome, I am relieved now. Was wondering why it was not working for you👍🏻
Would it be possible to get a copy of your working files. I'm trying to make it work since 2 evenings and I don't know why it doesn't work
Sure, i will share the export , stat tuned to community tab for updates
@@DeepakShrivastava thank you so much!
Hi Deepak,
Thanks for the informative video. I had an issue when creating the checklist for the tasks. I named the ID's 1,2 3...in that sequence. When the task is created, the first task has the first checklist, the 2nd one has the 2nd checklist and the 1st checklist as well. Could you suggest how that can be avoided?
Where you setting the variable "ChecklistItem" in "isChecked" field use following expression:
if(equals(items('Apply_to_each_2')?['Completed'], 'False'), false, true)
@@DeepakShrivastava Hi Deepak, Thanks it worked. I am facing another issue. The date format is not being picked by power automate even after I apply the ISO8601 option in the flow. Is there some other way to make it work?
Hello Deepak - This is a great video and is one piece I'm looking for. Do you know any good sources for creating the plan with Power Automate?
Thanks for watching. To create a plan, you have to use Graph API. Check out my video on how to use graph API here- czcams.com/video/yGYmgY3OgXk/video.html
I am getting the error message "The template validation failed: 'The repetition action(s) 'Apply_to_each_checklist_items' referenced by 'inputs' in action 'An_Arrayvariable_anfügen' are not defined in the template.'.".
i can't get the problem fixed. i need help!!! please!!!
"The template validation failed: 'The repetition action(s) 'Apply_to_each_2' referenced by 'inputs' in action 'An_Arrayvariable_anfügen' are not defined in the template.'.".
How can i do the same for buckets ?? i mean create many task on different buckets
You should use the "List buckets" action to get all available buckets and pass the bucket ID while creating tasks.
Hi Deepak, great video, can I get this excel file template?
Yes, sure. I will update it to my github and share the link.
Hi, I am trying to follow the steps and the first part (creating the tasks) works fine but when I try to add the checklist items I keep getting this error:
Syntax error at position 25 in 'TaskName eq [name of first task in my table]'.
inner exception: Syntax error at position 25 in 'TaskName eq [name of first task in my table]'.
clientRequestId: 64d80a0a-211e-4a19-b689-16736aa0f70e
My filter query for "List rows present in a table 2" looks exactly the same as in the video. When I copy it is "TaskName eq @{outputs('Create_a_task')?['body/title']}"
Any ideas what I am doing wrong?
I had the same problem, you only need to add apostrophe before and after variable. ex.: TaskName eq '[name of first task in my table]'
How do I make Power Automate enable "Show checklist on task"? I want to see the details, not just 0/2 checklist items done.
I doubt if this is possible as of now.
is there anyway to take labels?
Can I add more fields to the task?
As of now you can not add more fields to Planner task.
How can we add labels to our tasks?
I have to check , but in my knowledge it is not possible at this point of time.
Can you please check.. I am also having the same concern
Cool video, but I cant really see the practical use for this...