Automate Zoho Books Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

Zoho Books uses the OAuth authentication standard. To authenticate using OAuth, create an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties. See the OAuth section of the Getting Started guide in the Help documentation for an authentication guide.

PowerShell

1. Install the module:

   Install-Module ZohoBooksCmdlets

2. Connect:

   $zohobooks = Connect-ZohoBooks -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CallbackURL "$CallbackURL" -OrganizationId "$OrganizationId"

3. Search for and retrieve data:

   $customername = "NewTech Industries" $invoices = Select-ZohoBooks -Connection $zohobooks -Table "INVOICES" -Where "CustomerName = `'$CustomerName`'" $invoices

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

   $invoices = Invoke-ZohoBooks -Connection $zohobooks -Query 'SELECT * FROM INVOICES WHERE CustomerName = @CustomerName' -Params @{'@CustomerName'='NewTech Industries'}

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Zoho Books:

   $conn= New-Object System.Data.CData.ZohoBooks.ZohoBooksConnection("OAuthClientId=MyOAuthClientId;OAuthClientSecret=myOAuthClientSecret;CallbackURL=https://localhost:33333;OrganizationId=MyOrganizationId;InitiateOAuth=GETANDREFRESH") $conn.Open()

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

   $sql="SELECT InvoiceId, InvoiceNumber from INVOICES" $da= New-Object System.Data.CData.ZohoBooks.ZohoBooksDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.invoiceid $_.invoicenumber }