Automate Microsoft Planner Integration Tasks from PowerShell

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.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Microsoft Planner, 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.

Once you have acquired the necessary connection properties, accessing Microsoft Planner data in PowerShell can be enabled in three 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" -InitiateOAuth "$InitiateOAuth"
   

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-Object 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-Object 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-Object System.Data.CData.MicrosoftPlanner.MicrosoftPlannerParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject