Bulk Updating Dates on Planner Tasks from Excel

[JK682]

Hello!
I am using planner to manage multiple projects. Frequently I find that multiple deadlines will change and it is too messy to update each task. Currently I have a power automate flow to pull data from an excel doc in order to create the tasks. This flow doesn't recognize changes and will create duplicates if it is re run.  So, whenever I have substantial changes to a plan, in order to avoid duplication, I have to delete all tasks in the bucket and re run my flow! It's a pain and I am losing any historical data when I delete everything.

Ideally I want to add something to my flow that would compare the data, recognize data that doesn't match and replace the values.

To add additional clarity I am only changing the START date and END date fields.
Any suggestions are welcome, below is how my current flow is set up for each of my buckets.

Current Set Up

[MarvinBangert]

Hey @JK682 

the easiest would be, when you create a new Planner task, in the output you will also find an ID (the id of the task). Write this ID back into the excel file, so when you run the flow, first check if the ID column within the excel is empty or not. If it is empty, create a new task, if it is filled, use the "Get a task" action, use the ID and update the task.

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[JK682]

Sort of. 
do you have an example of what this would look like in power automate?
would there be a way that adding the task id into excel could be power automated?

[MarvinBangert]

Hey @JK682 

please have a look on this:

- "List rows present in a table": First you get all the items from your Excel table.

- Add an parallel branch (by clicking on the arrow between two actions and select (parallel branch).

- Use the "Filter array" action to filter your action by checking, if the TaskID field within your excel file for each field is empty or not:

ID not empty:

From: @outputs('List_rows_present_in_a_table')?['body/value']
Condition: length(item()?['TaskID']) is greater than 0

 ID empty:

From: @outputs('List_rows_present_in_a_table')?['body/value']
Condition: length(item()?['TaskID']) is equal to 0

(Please notice to add the length() as an expression) into your field.

It will return you a filtered array whenever a condition fits, using "length" we count the characters within the field "TaskID".

- Apply to each item within the Filter array body (one line for each, you can see within the function the "not / is" to just put it into the correct apply to each:

Select an output from previous steps: @{body('Filter_array_-_ID_not_empty')}

Select an output from previous steps: @{body('Filter_array_-_ID_is_empty')}

- On one side we use the "Update a Task" and on the other side we use the "Create a task" and afterwards "Update a row" (in Excel) to write back the TaskID into the Excel-File for another run.

In both you don't use the dynamic value from your Excel-file, the return from the "Filter array" does not show up. Here is an example about the structure:

Format:
@{items('Apply_to_each_2')?['<Your Excel column name>']}

Example
@{items('Apply_to_each_2')?['StartDate']}

Also when you import a date formatted Excel column into Power Automate, it will not show in a correct format, that you can use to import it into a Planner task, you need to format it. Please have a look on this page on how to format this (you can just add this expression within your "Update a Task" or "Create a Task":

Handling Excel Dates in Microsoft Power Automate (shanebart.com)

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel