Update excel spreadsheet when a Planner task changes

(0) Share

Report

Posted on by stuartemh

I have an existing excel spreadsheet for tracking the progress of activities in a project (simplified version below):

The table is a data source for a power bi dashboard.

I have been asked if this table can be adjusted via the Planner app.

I have copied the tasks to a new plan in Planner, and exported the plan to excel, so I can copy the task IDs to my existing table, so:

I can set a task's Status to complete with a pre-built flow:

But the triggers available are very limited and there isn't a trigger for when a task starts/becomes "In progress".

Is there a way to create a custom trigger "When a task starts...", so I can change the status from "Not Started", to "In progress"?

Then a more advanced version would be "When a task is completed, set next task to in progress".

Any help would be greatly appreciated.

Thanks

Stuart

I have the same question (0)

All responses (3)

Answers (0)

v-bofeng-msft on at

Like (0)

Report

Re: Update excel spreadsheet when a Planner task changes

Hi @stuartemh :

Do you want to create a custom trigger that triggered when a task starts?If so,I'm afraid that is not available.

In addition,I have an alternative:

You could Build a scheduled cloud flow,and check the tasks status every day.When it is found that the current status of a task is inconsistent with the status in Excel, update the status of the corresponding record in the Excel table.

Best Regards,

Bof

Was this reply helpful? Yes No
stuartemh 21 on at

Like (0)

Report

Re: Update excel spreadsheet when a Planner task changes

Hi @v-bofeng-msft,
Thanks for the suggestion. I've been working on a flow that runs every 5 minutes and overwrites the progress status in the excel table, with what the corresponding planner task progress is set to. I thought this might be a bit easier than using if statements. It also sends the task information in an email at the end.
I'm modifying an existing flow template "Create a daily summary of Planner Tasks by Bucket", as this seems to be working ok at the moment. I'm at a point where I can set task progress in Planner, and when the flow runs the progress statuses in the excel table are updated to match.
My next challenge is adjusting the flow so we can have multiple buckets in Planner, each controlling their own table in excel.
This is my current flow:

Any guidance on how I might do this would be greatly appreciated, thanks.
Stuart

Was this reply helpful? Yes No
takolota1 4,944 Moderator on at

Like (0)

Report

Re: Update excel spreadsheet when a Planner task changes

For anyone with similar use-cases, it may be easier to try to use a template like this instead of constantly syncing things across tools…

Free for any licensed Office365 organizations, this SharePoint & Teams template makes one source of truth for a project log, kanbans, GANTTs, workload trackers, & update notifications:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Project-Tracker-SharePoint-and-Teams/td-p/1788102

Was this reply helpful? Yes No