Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Outlook
    • Planner
    • Power Apps
    • SharePoint
    • Teams
  • Triggers
  • Ready to use solutions
    • Approval Process Template
    • Task Delegation App
    • The Ultimate Power Automate expressions cheat sheet
    • Power Automate HTTP requests to SharePoint cheat sheet
    • Power Automate HTTP requests to Graph API cheat sheet
  • ABOUT ME
  • Get help with your flow
  • POWER PLATFORM SERVICES
Menu

How to import tasks from Excel into Planner with Power Automate

Posted on March 7, 2021October 30, 2022 by Tom

“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 serial number

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, and maybe send a notification with a link to the task.

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.


🚀 Master Power Automate

Join 2,000+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

47 thoughts on “How to import tasks from Excel into Planner with Power Automate”

  1. Mac says:
    April 18, 2021 at 11:08 am

    Tkank you Tom. I was wonder how to add description to the task, and you showed the solution.

    Reply
  2. Mac says:
    April 30, 2021 at 3:26 pm

    I have a problem with function addDays. When I want to choose the date from the Excel, in the Dynamic Content I see only value and body. What am I doing wrong?

    Reply
    1. Tom says:
      May 2, 2021 at 7:54 pm

      Hello Mac,
      take a look on this post: https://tomriha.com/how-to-use-missing-date-dynamic-content-in-power-automate-expression/.

      Reply
  3. Mac says:
    May 4, 2021 at 3:01 pm

    Works well !! Thank you ! Or rather: Děkuju.

    Reply
  4. Mike says:
    May 6, 2021 at 4:20 pm

    This was a big help. One note… I had to change the date in the expression to 12/31/1899 as it was making my actual due dates off by 1 day…

    addDays(’12/31/1899′,int(items(‘Apply_to_each’)?[‘DueDate’]),’yyyy-MM-ddTHH:mm:ss’)

    Reply
  5. Eddy van der Goot says:
    May 16, 2021 at 11:36 pm

    Comment

    Hi Tom, I ran into this error:
    Unable to process template language expressions in action ‘Create_a_task’ inputs at line ‘1’ and column ‘23787’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.

    I think it has to do with the date formatting in my excel in excel it is formated yyyy-mm-dd
    should I format different or is the error caused by something else?

    regards
    Eddy

    Reply
    1. Tom says:
      May 19, 2021 at 11:13 pm

      Hello Eddy,
      the format doesn’t matter, as long as the cell is formatted as a date it’ll return a sequence number. Try to add a ‘Compose’ action right before the ‘Create a task’ action and store the date from Excel into that ‘Compose’ action. That way you’ll see in the flow run history what value Excel provides and that might give you a hint what’s wrong.

      Reply
  6. Sébastien says:
    May 19, 2021 at 5:58 pm

    Hi,

    thank you for your usefull message.
    I have an issue with the bucket id value
    My bucket name is : Collecte des documents / infos
    when i run the flow, i have this error message : Schema validation has failed. Validation for field ‘BucketId’, on entity ‘Task’ has failed: The specified identifier is invalid.

    I think is probably due to invalid caracter on my bucket name. Do you know how to fix this issue ?

    Thank you again

    Reply
    1. Tom says:
      May 20, 2021 at 6:46 pm

      Hello Sébastien,
      the name of the bucket doesn’t matter, Power Automate works with ID, not with the bucket name. If you want to create a task in a specific bucket, you must get the bucket ID first:

      1. ‘List buckets’ to get all the buckets in the plan
      2. ‘Filter array’ to filter from all the buckets only the one where ‘value Name’ is equal to ‘‘
      3. Use the ‘Id’ output from ‘Filter array’ to reference the bucket id to create the task
      Reply
  7. Chris says:
    May 20, 2021 at 8:06 pm

    I am also getting the error on Create Task: “Unable to process template language expressions in action ‘Create_a_task’ inputs at line ‘1’ and column ‘23787’”. Adding the Compose didn’t provide much information. The compose passed.

    Reply
    1. Tom says:
      May 22, 2021 at 8:47 pm

      Hello Chris,
      my guess would be that your date column in Excel contains also time, not only the date. I added new chapter to the post “Excel dates with time” that handles such data.

      Reply
  8. Edinho says:
    May 25, 2021 at 1:52 pm

    Hello Chris
    Is there any way i could fill-in [Group Id] and [Plan Id] directly on the excel sheet, so i could use only one file to feed as many plans as i need?
    In this case, the [Group Id] and [Plan Id] would be the name or any sort of code/link?

    Reply
  9. Marie-Soleil Tessier says:
    May 26, 2021 at 11:04 pm

    Comment hi can you be more specific please it’s not working from my part.

    ___
    Regarding this comment :
    Hello Sébastien,
    the name of the bucket doesn’t matter, Power Automate works with ID, not with the bucket name. If you want to create a task in a specific bucket, you must get the bucket ID first:

    ‘List buckets’ to get all the buckets in the plan‘Filter array’ to filter from all the buckets only the one where ‘value Name’ is equal to ‘‘Use the ‘Id’ output from ‘Filter array’ to reference the bucket id to create the task

    Reply
    1. Tom says:
      May 28, 2021 at 11:14 am

      Hello Marie-Soleil,
      I’ll publish a post on the topic on Sunday.
      update: here it is.

      Reply
  10. Edinho says:
    May 27, 2021 at 10:49 pm

    Sorry, this question was to Sébastien, my mistake!

    Reply
  11. Edinho says:
    May 27, 2021 at 10:51 pm

    Tom, please could you help?

    Is there any way i could fill-in [Group Id] and [Plan Id] directly on the excel sheet, so i could use only one excel file to feed as many plans as i need?
    In this case, the [Group Id] and [Plan Id] would be the name or any sort of code/link?

    Reply
    1. Tom says:
      May 28, 2021 at 11:14 am

      Hello Edinho,
      I’ll publish a post on the topic on Sunday.
      update: here it is.

      Reply
  12. Eddy van der Goot says:
    June 3, 2021 at 10:14 am

    Hi Tom,
    still run into this error

    Unable to process template language expressions in action ‘Create_a_task’ inputs at line ‘1’ and column ‘26308’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.

    this is in the create task in the for each. the data extrextion from the excel look ok

    “@odata.etag”: “”,
    “ItemInternalId”: “ac40236e-0128-481e-89ad-c224fec51717”,
    “Task Name”: “Task10”,
    “Bucket Name”: “To Do”,
    “Progress”: “Not started”,
    “Priority”: “Medium”,
    “Assigned To”: “evandergoot@eu-kyocera-senco.com”,
    “Created By”: “Eddy van der Goot”,
    “Created Date”: “44333”,
    “Start Date”: “44347”,
    “Due Date”: “44347”,
    “Late”: ” “,
    “Completed Date”: “”,
    “Completed By”: “”,
    “Description”: “”,
    “Completed Checklist Items”: “”,
    “Checklist Items”: “”,
    “Labels”: ” ”

    what can be the problem??

    Reply
    1. Tom says:
      June 6, 2021 at 9:25 pm

      Hello Eddy,
      the error message tells you that one of the expressions you use contains also expression int(…) whose parameter is not valid, it’s either empty, a decimal number or something else. I’d check the data in Excel, if there’s any row that has an empty date column or if some date contains also time.

      Reply
  13. Moe says:
    June 14, 2021 at 9:05 pm

    Hi, great article

    What if the spreadsheet has multiple tables, is there a way to allow the user to select the table they want to import tasks from?

    Reply
    1. Tom says:
      June 16, 2021 at 5:32 pm

      Hello Moe,
      the table name is just a string, you can enter any variable/dynamic content with existing table name. For example, if it’s a manual flow, you can put the table name as the input parameter and ask users to type in the name of the table they want to use. Then use their input as the ‘Table’ in the ‘List rows present in a table’ action.

      Reply
  14. ABHIJEET says:
    June 23, 2021 at 6:25 am

    Hi Tom,
    if there are Multiple Notes/Description(2-3 notes) to a Single Task then how to place it in Excel rows and column (One Drive for Business Excel) ? And also how it will get updated Automatically in the Planner once i entered the details in the Excel ?

    Reply
    1. Tom says:
      June 29, 2021 at 9:22 am

      Hello Abhijeet,
      you can have them in 3 columns and just update all of them at once into the task description, just put the dynamic contents one after another like here.
      You could trigger the flow on file update in OneDrive, for each task check if it already exists, similar to this post, and if it does then update the task description.

      Reply
  15. Andi says:
    July 1, 2021 at 10:51 pm

    For those having issues with the “Unable to process template language expressions in action ‘Create_a_task’ inputs at line ‘1’…” I found that it was because my title in Excel had a space in Start Date and Due Date. Try changing Start Date to StartDate without a space in your Excel title. I tested this a few times and that seemed to be the problem and solution for me.

    Reply
  16. Rodrigo says:
    August 27, 2021 at 10:44 pm

    Hi,

    I hope you are fine!

    This post is exactly what I need. I tried to use the expression ”
    if(equals(items(‘Apply_to_each’)?[‘Termination Date’],”),null,items(‘Apply_to_each’)?[‘Termination Date’])” (the date column name in my table is “Termination Date”) in order to check if the date is not “null” but it’s not working.

    When I tried to salve the flow, the message “Error in the flow save: Invalid Template. Not possible to validate the model/template: ‘The repetition actions ‘Apply_to_each’ referenced by ‘inputs’ in the action ‘Create_Sharepoint_Item are not defined in the model/template.’
    I noticed that in the List rows present in a table of your example, the parameter Table was changed from TableByPA to Outputs. I did not understand how I should change this (in my example I kept my Excel table name ‘ResourceDB’ instead of changing to Outputs).

    Thanks in advance

    Reply
    1. Tom says:
      September 1, 2021 at 7:41 pm

      Hello Rodrigo,
      the table name ‘Outputs’ is from another flow as it was added later, you’re correct to select your table name in the ‘List rows present in a table’ action.
      Based on the error message you shared I’d guess that the expression is not used inside ‘Apply to each’. It must be in an ‘Apply to each’ where you process the ‘value’ output from ‘List rows…’.

      Reply
  17. Tousif says:
    September 11, 2021 at 5:41 am

    Hi Tom,

    I hope you are fine!

    Description you added through Update task Details (Power Automate) are shown in Task Notes (Microsoft Planner) but not reflected in email notification.
    How can I get description in email notification?

    Reply
    1. Tom says:
      September 15, 2021 at 6:03 pm

      Hello Tousif,
      if it’s not in the email notification then you probably don’t use the right dynamic content. You should use the same dynamic content that you use to update the task details – if that one works then it must have the right value.

      Reply
  18. Karen says:
    November 1, 2021 at 8:12 pm

    Tom,
    Thanks for this. I know nothing about Power Automate (this was my first foray) and it did exactly what I needed.
    But my StartDate is off by 1 day. I have my date as “2021.11.29” in my Excel file, I use the ISO setting, but my Planner task illustrates it as “11/28/2021”

    Reply
    1. Tom says:
      November 3, 2021 at 5:26 pm

      Hello Karen,
      that might be caused by a time zone difference, try to convert the date to your timezone before you create the task: https://tomriha.com/display-sharepoint-time-in-the-correct-time-zone-with-power-automate/. If you’re in the UTC-x zone it might subtract the hours from the UTC time zone (default time zone) which would move it 1 day back.

      Reply
  19. Karen says:
    November 4, 2021 at 3:57 pm

    Thanks. I will check that out.

    Reply
  20. Karen says:
    November 4, 2021 at 9:07 pm

    Tom, I guess if your Start Date Time entry doesn’t have a hh:mm:ss attached to it, Planner automatically assigns a 00:00:00 value to it (thus dropping the date back a day). 2 solutions exist: (a) add a time value of 1 minute to the original data (e.g., 2021.11.29 00:01:00), or (b) hammer it forward with the “addDays(item()?[‘StartDate’],1)” function.

    Reply
  21. Yunus says:
    December 6, 2021 at 4:43 pm

    I would just like to ask how you can dynamically add bucket names from the excel spreadsheet.
    I dynamically create the bucket id in excell and i want tht anme to be the bucket name but keep getting the error
    Schema validation has failed. Validation for field ‘BucketId’, on entity ‘Task’ has failed: The specified identifier is invalid.
    Any help would be aprrecciated

    Reply
    1. Tom says:
      December 9, 2021 at 9:49 am

      Hello Yunus,
      there’s a dedicated post on importing tasks into buckets. You’ll need to extract the bucket id’s, you can’t use their names: https://tomriha.com/import-planner-tasks-into-various-plans-buckets-with-power-automate/

      Reply
  22. Phil Thompson says:
    May 17, 2022 at 11:53 am

    Hi,
    Could you please help me with the formatting for the dates in UK format please. I am struggling to get the load to work with UK dates. My excel data is in the format dd/mm/yyyy and that is what I want to show in the planner board.

    cannot seem to change the below to work?

    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’))

    Reply
    1. Tom says:
      May 25, 2022 at 4:06 pm

      Hello Phil,
      if you’re importing from Excel then go to the ‘List rows…’ action -> advanced settings -> return date as ISO 8601 date. That way you won’t need any calculations, you can use the date as is.

      Reply
  23. AQ says:
    June 21, 2022 at 2:37 pm

    Hi Tom,

    I keep getting the following error details for my flow:

    Unable to process template language expressions in action ‘Create_a_task’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘float’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’

    This is for the “assigned to” but the excel column just has email addresses. I’m not sure how to fix this or change the data type. Thanks

    Reply
    1. Tom says:
      June 28, 2022 at 4:21 pm

      Hello AQ,
      the error is not related to the ‘assigned to’ field, it’s related to the date. If you followed the article then float(…) is used to convert the date from serial number into a date. I’d check the data for the specific row that’s impossible to import, maybe there’s some problem with the data.

      Reply
  24. Mike H says:
    September 10, 2022 at 5:26 am

    Hi Tom
    Excellent article on how to create planner tasks from excel and resolving the date problem.

    When I have run this works very first time, the only thing is I have found when run second time or subsequent on my version it seems to duplicate the tasks not update / amend if there are changes to existing tasks and add new if there are some. Have I got something wrong or is this a one off import?

    Reply
    1. Tom says:
      September 18, 2022 at 7:10 pm

      Hello Mike,
      the flow is one-off import, it doesn’t update existing tasks. If you want to update existing tasks you’ll have to extend the flow by a check if a task already exists, similarly as described here: https://tomriha.com/how-to-avoid-creation-of-duplicate-planner-task-with-power-automate/

      Reply
  25. Karen P says:
    December 20, 2022 at 12:25 am

    Hi Tom,

    I’m misunderstanding how you are directly converting the date format into ISO format. Do you convert the dates in the Excel spreadsheet into ISO format (yyyy-mm-dd) first as a General value? Then, type “ISO 8601” within the Date Format section of the List of Tables?

    I appreciate any guidance you can provide!

    Reply
    1. Tom says:
      January 9, 2023 at 3:51 pm

      Hello Karen,
      if you take the values in ISO format you can use them as they are. If you take them as a serial number you’ll have to use an expression.
      This article was originally written before Microsoft added the option to get the date in ISO format, that’s probably the only reason why it contains also the calculation.

      Reply
  26. Sam Cogger says:
    October 2, 2023 at 11:56 pm

    Thanks for this Tom. Is there a way you can import tasks with recurring due dates?

    Reply
    1. Tom says:
      November 19, 2023 at 1:23 pm

      Hello Sam,
      I never needed that, I’d probably just create them again with a recurring flow when needed.

      Reply
  27. Kjetil says:
    April 24, 2024 at 1:05 pm

    Hi,
    Thanks for this, very useful. I have created something similar, but I want to be able to set a task as done if e.g. column status is set to “completed”. Is this possible?

    Reply
    1. Tom says:
      May 25, 2024 at 9:43 pm

      Hello Kjetil,
      you can update the task once you create with the ‘Update a task’ action that gives you the possibility to set ‘Task progress’ to Completed.

      Reply
  28. Moses says:
    July 22, 2024 at 4:08 pm

    Hello Tom,

    Thanks for this walkthrough. I’m getting the following error when I run the flow
    “Unable to process template language expressions in action ‘Create_a_task’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘int’ was invoked with a parameter that is not valid. The value cannot be converted to the target type.’.”

    How can I solve this?

    Reply

Leave a Reply Cancel reply

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

🚀 Master Power Automate

Join 2,000+ professionals getting actionable Power Automate tutorials, solutions, cheat sheets & tips every week.

No spam. Unsubscribe anytime.

Working on an Approval process?

Use the Approval Process Template and the Task Delegation App to skip the hard part and deploy a fully functional approval solution on a SharePoint list in minutes! And then the next one, and the next one...

Approval Template Preview ✨ Learn more ✨

Turn ideas into flows with ease!

Grab the complete Power Automate Cheat Sheet Bundle—everything you need to master expressions, SharePoint HTTP calls, and Graph API in Power Automate.

Cheat Sheet Bundle Preview ✨ Get the Cheat Sheets

Didn't find what you were looking for?
Need to adjust a solution to fit your needs?
Or would you just like to get an assistance from somebody with thousands of hours of experience with Power Automate?

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2025 Let's POWER Automate | Theme by SuperbThemes