Automate Smartsheet Integration Tasks from PowerShell

Ready to get started?

Download for a free trial:

Download Now

Learn more:

Smartsheet ADO.NET Provider

Easy-to-use Smartsheet client enables .NET-based applications to easily consume Smartsheet Sheets, Contacts, Folders, Groups, Users, etc.



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

The CData Cmdlets for Smartsheet 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 Smartsheet.

Cmdlets or ADO.NET?

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

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

Smartsheet uses the OAuth authentication standard. To authenticate using OAuth, you will need to 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.

PowerShell

  1. Install the module:

    Install-Module SmartsheetCmdlets
  2. Connect:

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

    $assigned = "Ana Trujilo" $sheet_event_plan_budget = Select-Smartsheet -Connection $smartsheet -Table "Sheet_Event_Plan_Budget" -Where "Assigned = `'$Assigned`'" $sheet_event_plan_budget

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

    $sheet_event_plan_budget = Invoke-Smartsheet -Connection $smartsheet -Query 'SELECT * FROM Sheet_Event_Plan_Budget WHERE Assigned = @Assigned' -Params @{'@Assigned'='Ana Trujilo'}

ADO.NET

  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

PowerShell

Update-Smartsheet -Connection $Smartsheet -Columns @('TaskName','Progress') -Values @('MyTaskName', 'MyProgress') -Table Sheet_Event_Plan_Budget -Id "MyId"

ADO.NET

$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 System.Data.CData.Smartsheet.SmartsheetParameter("@myId","10456255-0015501366")) $cmd.ExecuteNonQuery()

Insert Smartsheet Data

PowerShell

Add-Smartsheet -Connection $Smartsheet -Table Sheet_Event_Plan_Budget -Columns @("TaskName", "Progress") -Values @("MyTaskName", "MyProgress")

ADO.NET

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

Delete Smartsheet Data

PowerShell

Remove-Smartsheet -Connection $Smartsheet -Table "Sheet_Event_Plan_Budget" -Id "MyId"

ADO.NET

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