Automate Microsoft Planner Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Microsoft Planner ADO.NET Provider

Rapidly create and deploy powerful .NET applications that integrate with Microsoft Planner.



Are you looking for a quick and easy way to access Microsoft Planner data from PowerShell? We show how to use the Cmdlets for Microsoft Planner and the CData ADO.NET Provider for Microsoft Planner to connect to Microsoft Planner data and synchronize, automate, download, and more.

The CData Cmdlets for Microsoft Planner are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Microsoft Planner.

Cmdlets or ADO.NET?

The cmdlets are not only a PowerShell interface to the Microsoft Planner API, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Microsoft Planner data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Microsoft Planner. To access Microsoft Planner data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Microsoft Planner.

After obtaining the needed connection properties, accessing Microsoft Planner data in PowerShell consists of three basic steps.

You can connect without setting any connection properties for your user credentials. Below are the minimum connection properties required to connect.

  • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
  • Tenant (optional): Set this if you wish to authenticate to a different tenant than your default. This is required to work with an organization not on your default Tenant.

When you connect the Driver opens the MS Planner OAuth endpoint in your default browser. Log in and grant permissions to the Driver. The Driver then completes the OAuth process.

  1. Extracts the access token from the callback URL and authenticates requests.
  2. Obtains a new access token when the old one expires.
  3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

PowerShell

  1. Install the module:

    Install-Module MicrosoftPlannerCmdlets
  2. Connect:

    $microsoftplanner = Connect-MicrosoftPlanner -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CallbackURL "$CallbackURL"
  3. Search for and retrieve data:

    $taskid = "BCrvyMoiLEafem-3RxIESmUAHbLK" $tasks = Select-MicrosoftPlanner -Connection $microsoftplanner -Table "Tasks" -Where "TaskId = `'$TaskId`'" $tasks

    You can also use the Invoke-MicrosoftPlanner cmdlet to execute SQL commands:

    $tasks = Invoke-MicrosoftPlanner -Connection $microsoftplanner -Query 'SELECT * FROM Tasks WHERE TaskId = @TaskId' -Params @{'@TaskId'='BCrvyMoiLEafem-3RxIESmUAHbLK'}

ADO.NET

  1. Load the provider's assembly:

    [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Microsoft Planner\lib\System.Data.CData.MicrosoftPlanner.dll")
  2. Connect to Microsoft Planner:

    $conn= New-Object System.Data.CData.MicrosoftPlanner.MicrosoftPlannerConnection("OAuthClientId=MyApplicationId;OAuthClientSecret=MySecretKey;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH") $conn.Open()
  3. Instantiate the MicrosoftPlannerDataAdapter, execute an SQL query, and output the results:

    $sql="SELECT TaskId, startDateTime from Tasks" $da= New-Object System.Data.CData.MicrosoftPlanner.MicrosoftPlannerDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.taskid $_.startdatetime }

Update Microsoft Planner Data

PowerShell

Update-MicrosoftPlanner -Connection $MicrosoftPlanner -Columns @('TaskId','startDateTime') -Values @('MyTaskId', 'MystartDateTime') -Table Tasks -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MicrosoftPlanner.MicrosoftPlannerCommand("UPDATE Tasks SET TaskId='BCrvyMoiLEafem-3RxIESmUAHbLK' WHERE Id = @myId", $conn) $cmd.Parameters.Add(new System.Data.CData.MicrosoftPlanner.MicrosoftPlannerParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Microsoft Planner Data

PowerShell

Add-MicrosoftPlanner -Connection $MicrosoftPlanner -Table Tasks -Columns @("TaskId", "startDateTime") -Values @("MyTaskId", "MystartDateTime")

ADO.NET

$cmd = New-Object System.Data.CData.MicrosoftPlanner.MicrosoftPlannerCommand("INSERT INTO Tasks (TaskId) VALUES (@myTaskId)", $conn) $cmd.Parameters.Add(new System.Data.CData.MicrosoftPlanner.MicrosoftPlannerParameter("@myTaskId","BCrvyMoiLEafem-3RxIESmUAHbLK")) $cmd.ExecuteNonQuery()

Delete Microsoft Planner Data

PowerShell

Remove-MicrosoftPlanner -Connection $MicrosoftPlanner -Table "Tasks" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.MicrosoftPlanner.MicrosoftPlannerCommand("DELETE FROM Tasks WHERE Id=@myId", $conn) $cmd.Parameters.Add(new System.Data.CData.MicrosoftPlanner.MicrosoftPlannerParameter("@myId","001d000000YBRseAAH")) $cmd.ExecuteNonQuery()