Connecting to Google Sheets via SQL

Pre-requisites

You need the Client ID. To get credentials, please see the subpage.

How to Connect

Run the following statements in the CData Virtuality Studio or the Web UI (remember to enter your credentials):

SQL

CALL SYSADMIN.createConnection (
    name => 'google_spreadsheets_src'
    ,jbossCLITemplateName => 'ws'
    ,connectionOrResourceAdapterProperties => 'ClientId=<ClientId>,AccessTokenEndpoint=https://accounts.google.com/o/oauth2/token,EndPoint=https://sheets.googleapis.com/v4/spreadsheets,redirectUri=<redirectUri>,AccessToken=<AccessToken>,ClientSecret=<ClientSecret>,RefreshToken=<RefreshToken>'
);;

CALL SYSADMIN.createDataSource (
    name => 'google_spreadsheets_src'
    ,translator => 'google_spreadsheets'
    ,modelProperties => 'folders=<folders>,skipInitializeMetadata=false,spreadsheet_names=<spreadsheet_names>,spreadsheet_name_contains=<spreadsheet_name_contains>,spreadsheet_strict_boundaries=true'
    ,translatorProperties => ''
);;