Unanswered

Update Excel sheet based on changes to Planner

(0) Share

Report

Posted on by alexneedleman

Hello all! I've been bashing my head against a wall trying to figure out how to properly set up this flow so I figured I'd give the help forums a try.

Essentially, what I did is set up a workflow for my office as follows: whenever someone fills out a request form on Microsoft Forms, the first flow is triggered. That flow adds a new task to our Planner, and adds that task's details to an Excel online sheet that we share with the requesters so they may track the status of their request. It looks like this:

The second flow I built, which is the one giving me issues, builds upon this. I set it to trigger every 5 minutes, and my goal for it was to have it update this spreadsheet based on changes that may occur Planner-side (changing from In Progress to Complete, assigned to someone, etc). However, I often have issues with it. Before, the issues that would arise would be the flow would fail because of a 404 error whenever it couldn't find a row containing a specific ID. Now.. my flow has just been running, non-stop.

Besides those couple of successes, the flows have just kept running non-stop. Since I built this flow on a template I found online, I wanted to check it's integrity with the community to see if there are any glaring issues with it. Here is what I have for it:

I would appreciate any and all help, thank you!

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

Ed Gonzales 4,531 Most Valuable Professional on at

Like (1)

Report
@alexneedleman
Hey there. So there's a bit happening here, right? Lots of loops and things, so let's start to break things down a bit to see if we can't simplify them:
You start by getting a list of teams, presumably to list all of the plans for each group, but then you have a plan ID 'hardcoded' into the List Tasks step. Is that by design or were you just testing something?
From there, you've got a couple of nested loops that may also be giving you trouble. I wonder if we can compare the statuses between the Planner tasks and the spreadsheet, and then proceed with the update only if they're different. Would something like that work for your use-case?
Let's start there, and see how we can piece together something that might work.
-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Was this reply helpful? Yes No
alexneedleman 45 on at

Like (0)

Report

@edgonzales Thank you for the reply!

The template I imported had the List Teams module already in, but since I had created more than one planner for the Team that this relates to, I wanted to make sure it was using the right planner. That could very well be causing issues.

As for your second point, absolutely! All I really need is for changes in the Planner (assignees, progress, etc) to be reflected on the spreadsheet in Excel online. If there is a simpler way to do that, I would very much like to hear it.

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

Will you only be syncing one Planner and one spreadsheet?

Was this reply helpful? Yes No
alexneedleman 45 on at

Like (0)

Report

@edgonzales

Correct, only one spreadsheet off one planner.

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

Oops, forgot to ask...and the only thing we're updating is the status, ya?

Was this reply helpful? Yes No
alexneedleman 45 on at

Like (0)

Report

If possible I would also like to keep the person it was assigned to updated once it's been assigned to someone as well as their contact email like in the screenshot. For the status I've been using the task percentage complete dynamic value that's built-in to Power Automate and then created an IF statement next on the column next to it to change it to "Pending" for 0, "In Progress" for 50, "Completed" for 100.

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

So, I'm able to get it working without the assigned to part. Since that is a 'sub-array' (Assignees are a list, within a list of tasks) that would take more time. Would that much help?

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report

One thing that I'm finding problematic is that some Planner Task ID's may start with a hyphen...which makes Excel freak out a little.

Was this reply helpful? Yes No
alexneedleman 45 on at

Like (0)

Report

Yes! If anything I mostly need to know just how to set up the flow so that it compares the Excel sheet with Planner since I tried using some if statements but couldn't get it to work. So any starting point would be appreciated! Also, on the topic of Planner being problematic with the IDs, I've actually been asked by my boss to look into adapting this exact same kind of workflow using Lists instead of Planner, since it apparently has much better functionality than Planner does.
It has stuff built in for assigning stuff to people and overall just fits our needs way better. The modules for it are kind of tricky to find since they're not listed as Lists but as part of Sharepoint stuff:

Was this reply helpful? Yes No
Ed Gonzales 4,531 Most Valuable Professional on at

Like (0)

Report
So, the hyphen thing may be across all MS products...not sure. Just keep an eye out for it since they don't seem to exclude it from being the first character. Here are the broad steps for the solution, I might detail this out in a blog more, but let me know which bits you want me to elaborate on:

Recurrence trigger so your flow fires as often as you'd like.
Planner - List Tasks associated with the plan you want.
Select action - We're going to use this to compare "apples to apples"...where we get the output from the above task, but only get a field we'll call "KeyCheckID" and the value will use Concat() to combine the task ID, a unique character (I used '%'), and the percentComplete.
Excel - List Rows...now we're going to do the same thing with our list in Excel
Select action - again, using the output from our Excel action, getting a single 'column' calling it KeyCheckID, but this time, concatenating the TaskID from your Excel table, the %, and then the percentage also from Excel.

At this point, you'll have two arrays from each of the Select statements...with a bunch of rows that will contain data that looks like this: IYozkzh7p0CPPsy8q0g83pcADk0W%100

These are our apples. We're going to use Pieter Veenstra's blog, "Compare two arrays, tables or lists a lot faster in Power Automate" to compare those two lists.

What his solution does is go through the items in the first list to see if they exist in the second list. Anywhere that it doesn't exist, it means there wasn't a match for that ID and Complete combination...so on the "no" path of the condition, you would strip out the ID using the split() function at the unique character ('%'), and use that to update that one row in your spreadsheet.

Here's a video for the split function, just in case https://www.youtube.com/watch?v=BqpzvYzdPj8

Take a look at that and let me know where things get cloudy. I didn't want to get overly-detailed since that might be overwhelming. Take it slow, you'll get this.
-Ed

If you liked this reply, please give it a thumbs up! If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.

Was this reply helpful? Yes No