Apps Script

Prerequisites
Connecting to CData Connect Cloud

Apps Script

This page outlines the steps to connect Apps Script to the CData Connect Cloud Virtual SQL Server API and display the data in Google Sheets.

Prerequisites

Before you connect, you must first do the following:

Connect a data source to your CData Connect Cloud account. See Connections for more information.
Generate a Personal Access Token (PAT) on the Settings page. Copy this down, as it acts as your password during authentication.

Connecting to CData Connect Cloud

In order to connect from Apps Script to CData Connect Cloud via the SQL Server, you need the following information:

Server—tds.cdata.com
Port—14333
Database name—enter the Connection Name of the CData Connect Cloud data source you want to connect to (for example, Salesforce1).
Username—enter your CData Connect Cloud username. This is displayed in the top-right corner of the CData Connect Cloud interface. For example, test@cdata.com.
Password—enter the PAT you generated on the Settings page.

Log in to Google Sheets (https://sheets.google.com).
Open an existing Google Sheets document or create a new document.
Select Extensions > Apps Script. A blank Apps Script project appears.

Copy and paste the script below into the Apps Script project. This script connects your data source to Virtual SQL Server and inserts the data into your Google Sheets file.

Change the following in the script:

Update user with your CData Connect Cloud username.
Update password with the PAT you generated in the prerequisites.
Update dbName with the name of the data source you created in the prerequisites.
Update results with your database query.

function fetchAndInsertData() {
     var server = 'tds.cdata.com';
     var port = 14333;
     var dbName = 'mydatabase'; //the name of your database connection 
     var user = 'user@mydomain.com'; //your Connect Cloud username 
     var password = 'CONNECT_USER_PAT'; //your Connect Cloud PAT
        
     var url = 'jdbc:sqlserver://' + server + ':' + port + ';databaseName=' + dbName;
        
     var connection = null;
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        
     try {
         connection = Jdbc.getConnection(url, user, password);
         Logger.log('Connection successful');
            
         var stmt = connection.createStatement();

     // Change the executing query accordingly.
         var results = stmt.executeQuery('SELECT TOP 10 * FROM Workers'); //your database query
            
     //Modify the result structure accordingly.

         var row = 1;
         while (results.next()) {
         for (var col = 1; col <= results.getMetaData().getColumnCount(); col++) {
             sheet.getRange(row, col).setValue(results.getString(col));
         }
         row++;
         }
             results.close();
         stmt.close();
     } catch (e) {
         Logger.log('Error: ' + e.message);
     } finally {
         if (connection) {
         connection.close();
         }
     }
}

Name and save your Apps Script project.
Run the script.

Note: The first time you run the script, you need to authorize it to make external connections. Click Review Permissions and follow the prompts to grant the necessary permissions.
The Execution log tracks the progress of the script. When the execution of the script completes, your connection data from CData Connect Cloud is inserted into Google Sheets.