How to import tasks from Excel into Planner with Power Automate

“I’ve got 100s of tasks in an Excel file that I’d like to import into Planner, is that possible with Power Automate?”


If you decide to use Microsoft Planner for a light project management, maybe you don’t want to start from scratch. Maybe you already use an Excel file to track the tasks and you’d like to import them into Planner. While there’re some 3rd party solutions to do that, it’s not necessary to use one. You can build a Power Automate flow for the tasks import from Excel to Planner.

Prepare the Excel file

Before you start with the flow, you must prepare the Excel file with tasks. There’re a few mandatory prerequisites:

  1. All the tasks and their data must be in a table, one row per task.
  2. The Excel file must be stored in SharePoint or OneDrive.

And a few optional prerequisites. Depending on the data you want to import, some columns should have a specific format:

  1. Assigned To user should be user email address.
  2. Start and Due date must be Date only, without time

The flow in this post will import an Excel file as shown below with the 5 columns table.

import tasks from Excel into Planner with Power Automate

Build the Power Automate flow

Since this article is about the initial tasks import, the flow will be started manually. Start from the ‘Instant cloud flow’ with manual trigger.

Power Automate manual trigger

List rows present in the Excel table

The first step in the flow should be to list all the rows in the Excel file. As already mentioned, all the data for each task must be on a single row – if you get all rows, you get all tasks.

Add the ‘List rows present in a table’ action to your flow and select the file location, name and the table with tasks. Use the buttons to select the file and the table, don’t try to type it. While it shows the file name, on the background it needs the file identifier and it’s easier if you select the file from the choices.

In the example below the Excel file is stored in OneDrive, the file is called Book.xlsx and the table TableByPA.

Power Automate select Excel file and table

The output of this action will be an array with all rows = all tasks and their data. Once you have the array with rows (tasks), you can loop through all of them and create a Planner task for each.

Create the Planner task

Power Automate has 2x action called ‘Create a task’, one of them with (Preview) in the name. The only difference is that the (Preview) action can assign a priority to a task, I’d use that one.

Add the ‘Create a task’ action and use the ‘Task name’ from the Excel table as the task ‘Title’, that will add the ‘Apply to each’ action automatically. It’s the loop that’s necessary to process all the rows one by one.

Create Planner tasks from Excel

UPDATE: there was a change in the Excel connector after the original article was published. It’s now much easier to work with dates as you can get them in ISO format directly from the ‘List rows present in a table’ action. The two chapters Format the date and Excel dates with time are not necessary anymore.

But the expressions to check if the columns contain a date are still valid, you can also extend them to move due dates from weekend to Monday.

if(equals(items('Apply_to_each')?['StartDate'],''),null,items('Apply_to_each')?['StartDate'])
if(equals(items('Apply_to_each')?['DueDate'],''),null,items('Apply_to_each')?['DueDate'])

Format the date – only if you get the date as Serial Number

While it’s easy to use the ‘Task name’ and ‘Assigned User Ids’ (if you use email addresses), it’s more complex with dates. Excel date columns don’t give you a date, they give you a number. A number of days since base date of 12/30/1899 (you can read a story behind that date here).

To get a date from the number provided by Excel, you must add the number (of days) to the base date and format it.

addDays('12/30/1899',int(items('Apply_to_each')?['StartDate']),'yyyy-MM-ddTHH:mm:ss')

addDays('12/30/1899',int(items('Apply_to_each')?['DueDate']),'yyyy-MM-ddTHH:mm:ss')

And you should extend it to handle also empty Start or Due date.

if(equals(items('Apply_to_each')?['StartDate'],''),null,addDays('12/30/1899',int(items('Apply_to_each')?['StartDate']),'yyyy-MM-ddTHH:mm:ss'))

if(equals(items('Apply_to_each')?['DueDate'],''),null,addDays('12/30/1899',int(items('Apply_to_each')?['DueDate']),'yyyy-MM-ddTHH:mm:ss'))
Import tasks from Excel into Planner with Power Automate

Note: the Start date and Due date columns are called ‘StartDate’ and ‘DueDate’ in my table, in your Excel table they might have a different name. You should use the name of your columns in the expressions above.

Excel dates with time – only if you get the date as Se

If the dates in Excel contain also time, you must get rid of the time part and keep only the whole number = whole days. That means, convert the date from Excel into float(…) number with decimal spaces. Then formatNumber(…) to remove the decimal part, and convert it back into number with int(…) expression. Only then you can use it in the expression.

if(equals(items('Apply_to_each')?['StartDate'],''),null,addDays('12/30/1899',int(formatNumber(float(items('Apply_to_each')?['StartDate']),'#')),'yyyy-MM-ddTHH:mm:ss'))

if(equals(items('Apply_to_each')?['DueDate'],''),null,addDays('12/30/1899',int(formatNumber(float(items('Apply_to_each')?['DueDate']),'#')),'yyyy-MM-ddTHH:mm:ss'))

Update the Planner task details

The last step is to update the task description. You can’t enter it directly when creating a task, you must add one more action: ‘Update task details’. Use the ‘Id’ output from ‘Create a task’, and update the task with the ‘Description’ from the Excel table.

Update planner task description

Running this flow on the example Excel table above will create 6 tasks in the Planner.

Planner tasks

Summary

As you can see in this post, it’s not complicated to import Excel data with Power Automate. The biggest complication are the date columns and the expressions in them, otherwise it’s very straightforward flow. Get all rows and create a separate task for each of them.

But it’s not necessary to take all rows, you can get only some of them, e.g. skip the empty ones and import the rest. And if you want, you can add also checklists or attachments to the task.

You can even import tasks from a single Excel file into various groups/plans/buckets, or build a flow to send emails for completed tasks from a specific bucket.

You can also add a reporting flow to keep track of the new, closed and open tasks.


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.

Zero spam, unsubscribe anytime.

28 Comments

Add a Comment

Your email address will not be published. Required fields are marked *