Apps Script
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—接続したいCData Connect Cloud データソースのConnection Name を入力します。例:Salesforce1
-
Username—CData Connect Cloud のユーザー名を入力します。ユーザー名は、CData Connect Cloud の画面の右上に表示されています。例:test@cdata.co.jp
-
Password—Settings ページで生成したPAT を入力します。
-
Log into 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.