SharePoint list as Planner ‘task and checklist template’ in Power Automate

“I have one SharePoint list with all the Planner tasks (which repeat) and a checklist for each, how can I avoid creating new task for each item with Power Automate?”

---

The previous post “How to create a checklist in a Planner task with Power Automate” already explained how to create a checklist in a Planner task. But it included only two simple template examples, a SharePoint list and an Excel table. What if you need more complex template? One SharePoint list with multiple tasks with various checklist items that should be created on a regular basis? Two columns, one with the task name (repeated) and the second one with the checklist items?

You can’t simply loop through all the items as it’d create multiple planner tasks, you must preprocess the data first.

The starting point is the ‘Get items’ action to get all the tasks and checklist items from the list. Don’t use any filter.

Get the unique task names

Once you have the items, you should filter out duplicate task names as you want each task only once. To continue with the example above, you don’t want 3x ‘Task_1’ and 2x ‘Task_2’, you want 1x ‘Task_1’ and 1x ‘Task_2’.

Add the ‘Select’ action to your flow, it’ll allow you to select only the task names from the items in the ‘Get items’ output. Put ‘value’ into the ‘From’ field, switch to ‘text mode’, and enter the task name column into ‘Map’.

The ‘Select’ output will be an array with only the task names, but it’ll still contain duplicates. To remove the duplicates you’ll need the union(…) expression. If you enter 2 arrays into union(…), it’ll return the overlapping values. If you enter the same array (in this case the output from ‘Select’) twice into union(…), it’ll return only unique values from the array.

union(body('Select'),body('Select'))

The output of the ‘Compose’ action above will be an array with values ‘Task_1’ and ‘Task_2’ from the example above.

Create the unique tasks

Now, when you’ve got the unique task names, you can create the tasks in the Planner. Add ‘Apply to each’ with outputs from the ‘Compose’ action as the input. As already mentioned, the output will contain only the unique task names at this moment, no duplicates. Be careful to not use the original ‘value’ output from ‘Get items’.

You’ll see a new available dynamic content inside ‘Apply to each’: ‘Current item’. This dynamic content contains the currently processed value from the input array, in this flow the task name. Following the example, in the 1st loop it’ll contain ‘Task_1’, in the second loop ‘Task_2’.

One use for the ‘Current item’ dynamic content is the task ‘Title’ field.

Get the checklist items for each task

The second use is to get the corresponding checklist items. At this moment you’ve got the array with all tasks/checklist items (output from ‘Get items’), and the current, unique task name. Combining this in the ‘Filter array’ you can filter from all checklist items only the ones for given task. Put the ‘value’ output from ‘Get items’ as the ‘From’ field, and filter only the checklist items where task name column is equal to ‘Current item’ (the currently processed task).

Then you can add another ‘Apply to each’ to process all the checklist items that fit the ‘Filter array’ condition. From this point it’s the same approach as in the original post.

There’re two more things to be careful about though. Firstly, ‘Filter array’ won’t give you dynamic content for the checklist item ID and Title. You can either get it back with ‘Parse JSON’ action, or you can get the values using the expression item()?[‘ColumnInternalName‘]. And secondly, don’t forget to always set the array variable to null expression before you start appending checklist items for the next task.

Full flow diagram

Summary

The Power Automate solution above will allow you to repeatedly create multiple Planner tasks including checklist from a SharePoint list, potentially also with attachments.

The main part here is the work with the array(s). When you ‘Get items’ from a SharePoint list, they’ll be always stored in an array during the whole flow run. You can take some data from the array for further processing, e.g. to get the task names with ‘Select’. But you can always go back to the original full array to get a different part of it, e.g. only the checklist items for selected task in ‘Filter array’.

You should also keep in mind that every time you append data to an array in a loop, you must clear that array before the next loop. You don’t want a single array with all the data, each task in this solution has it’s own checklist.

And if you’re looking for a solution to create just a single task with a checklist, you can ‘lookup’ the desired checklist items instead. This flow will always create all tasks from the ‘template’ list, it doesn’t have any filter on the tasks.

Or you can create tasks from the same list in various groups/plans/buckets.

---

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: