cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Planner to Excel

NOT Excel to Planner!

 

This is my third attempt at just making this message because the insert image for the workflow will not work.  

 

This is my first workflow creation effort so I am a complete noob to this.  Still 30 years as a developer makes that relative (I hope). 

 

This power automate stuff seems really really counter intuitive.  The trigger I used was when a task was created from the Planner set of triggers.  First thing, right off the bat, it makes no sense.  I do not want to choose a group and plan but instead I want the nominal default case of ALL groups, ALL plans to be the way it behaves.  I understand I could copy the flow from one plan and make a flow for each plan, but, isn't this system about making things easier on us?, about automation, and not about copying stuff each time?  Seriously!  It SHOULD take a blank as a default for ALL plans in my security scope and do the 'for each' itself as an assumed part of the trigger.

 

That being said, I did get it to successfully put all the available columns from a Planner task into an excel table.  But the data types and meaning of the data that was available for the excel table row was not ideal (or even really useful).  For example the user ids are the long ridiculous unique identifiers, instead of the user display name which is OBVIOUSLY what is needed.  Likewise the has a description indicator is less than useful when what is needed is the actual description text.  Lastly, I see no way currently to get the comments on the task included.

 

Is there anyone who can help with this as a sample workflow?

 

I do not seem to understand the way in which apply for each is fed from a previous step.  I tried to get a list of teams and from each team get a list of buckets and from each bucket a list of tasks, but the way to make each apply for each input 'make sense' to actions within its scope is entirely unclear (to me).  Can anyone explain that BETTER than the docs do currently or provide a working example along those lines?

 

Thanks for any help you can give.  

 

I would have posted the image of the workflow I made but the image insertion does not let me.

5 REPLIES 5
Highlighted
Regular Visitor

Re: Planner to Excel

Checking if I can add the image in a reply ...PlannertoExcelFlow.PNG

 

OK, totally different image inset interface … lovely.

Highlighted
Regular Visitor

Re: Planner to Excel

No one else is replying to me so what is needed?

 

I would think that when we have the create a task Planner flow trigger activated, we should already have as context a Planner task object.  Then there should be a set of functions on that object that we can apply along with generic function to format the excel row.  

 

1. Is it true in the flow that after the trigger happens I already have my planner task loaded as context?

2. How do I reference the various parts of that planner task?  These should be listed on the dynamic content side of the column inputs for excel.  I do not see them.  Why not?

3. I would expect the available functions for the task to be listed in the possible expressions and I do not see them.  Why not?

 

Highlighted
Community Support
Community Support

Re: Planner to Excel

Hi @series0,

 

1. If the value of the task is blank will cause the flow to fail, you can use the If expression to determine it previously. If it is a blank, append null into the column of excel: 

if(equals('value',0),null,'value')

 

2. The user ID is the unique ID of the Azure Activity Directory. If you want to get user name, you can use the Get user action of Azure AD to get all information about this User:

Annotation 2019-11-18 141336.png

3. If you want to get the task comments, you can use the Get task details action to achieve it:

Annotation 2019-11-18 142249.png

 

4. I do not quite understand your requirements. If you want to get all tasks of all lists of all teams, you can refer to this way:

Get teams' ID (groups' ID) -> Get the list of plans for a Group -> Get the list of tasks for a Plan.

Annotation 2019-11-18 143909.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Regular Visitor

Re: Planner to Excel

Lin, 

 

Thanks!  That does help a lot.  But it kind of avoids a few key issues that I had as questions.

 

1. To me, it makes the most sense that I use the 'When a New Task is Created' Planner pre-made workflow step.  However, it makes NO sense to me that this step requires me to enter a group and plan id.  Any input relating to a task should allow a blank which defaults to ALL possible tasks within my security context.  If it does not do this (and as far as I can tell it can't/doesn't), then when I create a workflow using this trigger I am foolishly limited to a single plan only workflow.  That means I have to tediously copy my workflow for EVERY plan I have.  That is, … ridiculous.  So, all that being said, my question is, 'Is all that I just described the sad truth right now?' , followed by

2. Is there any plan to FIX that terrible situation?  

 

So, when I use the step for 'When a New Task is Created', I should at least have that task 'with me', to reference. 

3. Is that correct, or is this trigger so … oddly not helpful … that it knowingly (defiantly) does not have the task with it that triggered the event in the first place?  If it did that little thing that makes sense it would prevent all kinds of crazy code efforts from me, like going through the entire list of tasks to find the new one (for instance).  And if that task is present (as it should be) then why do I not see the id for it in the dynamic content?  Get Task Details shows me a pulldown with … all the tasks listed in it.  But that is kind of crazy.  I should only have 1 task, the one that triggered the event, already in context.  That should be the only key/id that I use to enter in to the Get Task Details as input.  It should be that easy.  

 

4. When you say, 'If the value of the task is null ...' I know what you mean, and of course I will check for that, but, as in item 1 above, it shouldn't be null in this case nominally.  I mean we are BASED on a trigger when a task WAS created.  So there IS a task. Yeah, I'll still check it, but you get my point.  Further, it is not the 'whole task' I am posting to a column in Excel, but instead many of the tasks internal details on a column by column basis.  Your item 3 there shows that, but, I got that far in my first workflow and saw those values to choose as my Excel column data.  Still, the types of those values were oddly not what was seen on the display for a Planner task nor then of course what anyone would usually want for an Excel spreadsheet (for instance).  I know I may have to cast the data, but is there somewhere a clear data definition showing me what type each one is, and a function ready made to convert it to what is most typically expected?  Sorry if these are super basic questions.  Software should be useful and therefore intuitive.  This does not seem that way to me at all.

 

I am writing this BEFORE I try to use all the answer you gave.  That may seem backwards.  I will certainly now be trying all of the hints you offered and thanks.  Hopefully, as I get more used to it, it will make more sense.  But I'd still love it if you could answer the above questions in some way.  As a voice for MS, I'd love to hear what you have to say.  And thanks so much for your help so far.

 

Regards,

Series0

 

Highlighted
Regular Visitor

Re: Planner to Excel

So, to be extremely clear:

 

1. My flow starts with a Planner task being created.  I am then totally unsure what to do.  I should have the task.  So what goes after this step:

Event.PNG

As a wild guess, if I have my task, I can immediately go on to post the details from it to Excel using something like this:

No Input.PNG

So, I have NO idea why the use of this Excel step would change itself to an 'Apply to Each' as there is no need.  I have only the one task and the one Excel row.  So that strange default behavior of forcing an Apply to Each is not what I expect or understand.

 

Further, if I try to load my columns (I made as many of them in Excel as there were dynamic contents data items from the task shown), the flow FORCES again the first one as the output of the previous task as input, which is … wrong/odd/strange also.  I just need each column to accept their dynamic content.  I do NOT need output from the previous task as that is already shown in dynamic content.  

 

Finally, the dynamic content that I do see bears some resemblance to a Planner task card, but it is not precise.  I want what ostensibly anyone would want at this stage, the exact same detail I see piece for piece on a Planner task card.  I also want the comments, an issue you did not address in the last post.   --- I would expect that given a task 'object' loaded into dynamic content, a set of expressions (methods) would then be available for that 'object' on the expressions menu.  I do not see anything there that is like - GetDisplayUserName.  I do see your call to GetUser for Azure and I will try that.  But that makes not too much sense to me.  I am only guessing then that this is some sort of 'data preparation' set of steps I have to do BEFORE the step that tried to append a new Excel row.  Which makes me ask the question, why is it not possible to simply put all those step/functions/expressions INLINE in the column editbox like 'any normal programming environment'?  It should be possible to do that.  

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (9,240)