Automate Coda Integration Tasks from PowerShell

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

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Coda; this tutorial shows how to use the Provider to retrieve Coda data.

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

Start by setting the Profile connection property to the location of the Coda Profile on disk (e.g. C:\profiles\Coda.apip). Next, set the ProfileSettings connection property to the connection string for Coda (see below).

Coda API Profile Settings

Obtain your API token from your Coda account settings under the API Settings section.

1. Load the provider's assembly:

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

2. Connect to Coda:

    
   $conn= New-Object System.Data.CData.API.APIConnection("Profile=C:\profiles\Coda.apip;ProfileSettings='APIKey=your_api_token';")
   $conn.Open()
   

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

   $sql="SELECT CanShare, CanShareWithWorkspace from AclMetadata"
   
   $da= New-Object System.Data.CData.API.APIDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.canshare $_.cansharewithworkspace
   }