Automate Smartsheet Integration Tasks from PowerShell

The CData ADO.NET Provider for Smartsheet is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Smartsheet.

Getting Started

---

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Smartsheet; this tutorial shows how to use the Provider to create, retrieve, update, and delete Smartsheet data.

Once you have acquired the necessary connection properties, accessing Smartsheet data in PowerShell can be enabled in three steps.

Smartsheet uses the OAuth authentication standard. To authenticate using OAuth, register an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties.

However, for testing purposes you can instead use the Personal Access Token you get when you create an application; set this to the OAuthAccessToken connection property.

1. Load the provider's assembly:

   
   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Smartsheet\lib\System.Data.CData.Smartsheet.dll")
       

2. Connect to Smartsheet:

    
   $conn= New-Object System.Data.CData.Smartsheet.SmartsheetConnection("OAuthClientId=MyOauthClientId;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;")
   $conn.Open()
   

3. Instantiate the SmartsheetDataAdapter, execute an SQL query, and output the results:

   
   $sql="SELECT TaskName, Progress from Sheet_Event_Plan_Budget"
   
   $da= New-Object System.Data.CData.Smartsheet.SmartsheetDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.taskname $_.progress
   }
     

Update Smartsheet Data

$cmd =  New-Object System.Data.CData.Smartsheet.SmartsheetCommand("UPDATE Sheet_Event_Plan_Budget SET Assigned='Ana Trujilo' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Smartsheet.SmartsheetParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Smartsheet Data

$cmd =  New-Object System.Data.CData.Smartsheet.SmartsheetCommand("INSERT INTO Sheet_Event_Plan_Budget (Assigned) VALUES (@myAssigned)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Smartsheet.SmartsheetParameter("@myAssigned","Ana Trujilo")))
$cmd.ExecuteNonQuery()

Delete Smartsheet Data

$cmd =  New-Object System.Data.CData.Smartsheet.SmartsheetCommand("DELETE FROM Sheet_Event_Plan_Budget WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Smartsheet.SmartsheetParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()