This page provides a guide to Establishing a Connection to Google Sheets in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Google Sheets

Establishing a Connection shows how to authenticate to Google Sheets and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Google Sheets through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Google Sheets by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Google Sheets

The following sections focus on authentication as a User, and authentication as a Service.

Authenticating as a User (OAuth)

The following subsections describe how to authenticate to Google Sheets from a User account (AuthScheme OAuth) via three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping. Authenticated via either embedded OAuth or custom OAuth.
• Web: access to data via a shared website. Authenticated via custom OAuth only.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard. Authenticated via embedded OAuth or custom OAuth.

For information about how to create a custom OAuth application, and why you might want to create one even for auth flows that have embedded OAuth credentials, see Creating a Custom OAuth Application. For a complete list of connection string properties available in Google Sheets, see Connection.

When the access token expires, the Cloud refreshes it automatically.

Automatic refresh of the OAuth access token:

To have the Cloud automatically refresh the OAuth access token, do the following:

1. The first time you connect to data, set the following connection parameters:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client Id in your application settings.
   • OAuthClientSecret: The client secret in your application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the Cloud to save the OAuth values, which persist across connections.
2. On subsequent data connections, set the following:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAUth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set the following connection properties:
   • OAuthClientId: The Client Id in your application settings.
   • OAuthClientSecret: The Client Secret in your application settings.

3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Creating a Custom OAuth Application

• set AuthScheme to OAuth,
• get and set the OAuthAccessToken, and
• set the necessary configuration parameters.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Google Sheets".)

However, a custom OAuth connection is required for Users who connect via the Web, and for connecting on behalf of users via a Service Account. Custom OAuth applications are also useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure

1. Navigate to the Google Cloud Console.
2. At the left navigation pane, select Library. The console opens the Library page.
3. Use the Search service to find Google Sheets API. Select "Google Sheets API" from the search results.
4. Use the Search service to find Google Drive API. Select "Google Drive API" from the search results.
5. On the Google Sheets API page, click ENABLE to enable both the Google Sheets and Google Drive APIs.

User Accounts

1. Create a new project or select an existing project.
2. At the left navigation pane, select Credentials.
3. If the selected project does not have a consent screen, click CONFIGURE CONSENT SCREEN. If you are not using a Google Workspace account, you are restricted to creating an External-type Consent Screen, which requires specifying a support email and developer contact email. Additional information is optional.
4. On the Credentials page, select Create Credentials > OAuth Client ID.
5. In the Application Type menu, select Web application.
6. Specify a name for your OAuth custom web application.
7. Under Authorized redirect URIs, click ADD URI and enter a redirect URI.
8. Click Enter.
9. Click CREATE.

When the application is complete, the Cloud Console returns you to the Credentials page. A window opens that displays your client Id and client secret.

Although the client secret is accessible from from the Google Cloud Console, we recommend you write down the client secret. You need both the client secret and client Id to specify the OAuthClientId and OAuthClientSecret.

Service Accounts

When using AuthScheme=OAuthJWT, you must create a Service account.

At the Google Cloud Console:

• To complete the service account flow, generate a private key in the Google Cloud Console. In the service account flow, the driver exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The driver grants the same permissions to the Service Account.

• Now create a new Service Account:
  
  1. Create a new project or select an existing project.
  2. At the left navigation pane, select Credentials.
  3. Navigate to Create Credentials > Service account. The Cloud Console displays the Create Service Account page.
  4. Enter the Service account name, the Service account ID, and, optionally, a description.
  5. Click DONE. The Cloud Console returns you to the Credentials page.
  6. In the Service Accounts area, select the service accout you just created.
  7. Click the KEYs tab, then click ADD KEY > Create new key.
  8. Select any supported Key type, such as OAuthJWTCert or OAuthJWTCertType.
  9. Click CREATE.

The key is automatically downloaded to your local device, and any additional information specific to the key is displayed.

Required Scopes and Endpoint Domains for Google Sheets

These permissions are defined by access scopes, which determine what data your application can access and what actions it can perform.

This topic provides information about the required access scopes and endpoint domains for the Google Sheets Cloud.

Understanding Scopes

Scopes are a way to limit an application's access to a user's data. They define the specific actions that an application can perform on behalf of the user.

For example, a read-only scope might allow an application to view data, while a full access scope might allow it to modify data.

Required Scopes for Google Sheets

Understanding Endpoint Domains

Endpoint domains are the specific URLs that the application needs to communicate with in order to authenticate, retrieve records, and perform other essential operations.

Allowlisting these domains ensures that the network traffic between your application and the API is not blocked by firewalls or security settings.

Note: Most users do not need to make any special configurations. Allowlisting is typically only necessary for environments with strict security measures, such as restricted outbound network traffic.

Required Endpoint Domains for Google Sheets

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Spreadsheets as Tables

The Cloud models spreadsheets and ranges as relational tables.

Tables

Tables shows various configuration options to reflect your spreadsheets' organization in the tables; for example, you will find guides for working with headers and querying ranges as tables.

Columns provides more information on column discovery.

Stored Procedures

The Cloud enables you to represent a top-left-oriented spreadsheet or a user-specified range as a database table. You can control how tables are listed by setting the Header property.

Top-Left Oriented Tables

• Top-left-oriented tables are represented with the name of the worksheet.
• The default format requires that the table is top-left-oriented and that the first row of data in the worksheet contains the column names. This means that the default value of true for the Header connection string property is required.
• Headers should not contain special characters.
• By default the Cloud will return all rows until the first empty row. Note: an empty row between data will prevent further data from being returned.

Due to a limitation of Google's Spreadsheet API, all column headers must be non empty.

User-Specified Range

Note: Range notation is only available in a SELECT or UPDATE statement. Ranges are not supported for DELETE and INSERT commands.

You can specify column names or generate column names automatically by setting the Header property. This property affects how you use columns in commands.

Header=True (Default)

• Columns are determined by the first row of the Google spreadsheet. If no values are provided for the first row of the spreadsheet, the Cloud will create unique, alphabetized column names that are available only within the scope of that request.
• The Cloud also adds an Id column for each row that corresponds to the unique URI of the row on the Google servers. This is used during update and delete operations.

Header=False

• Columns will be dynamically assigned based on either the specified range or the size of the worksheet. The autogenerated column names are alphabetical.
• The Id column for each row will represent the row number from the top of the sheet. For example, if you specify a range A3:E6, rows 3, 4, 5, and 6 will be returned.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Google Sheets Views

Query the folders contained in a user's Google Drive.

Select

The Cloud will use the Google Sheets API to process WHERE clause conditions built with the server side supported columns and operators. The rest of the filter is executed client side within the Cloud.

The columns and operators that support server side filtering are:

• Name supports the 'CONTAINS,=,!=' operators.
• Description supports the 'CONTAINS' operator.
• ModifiedTime supports the '<=,<,=,!=,>,>=' operators.
• OwnerEmail supports the 'IN' operator.
• Starred supports the '=,!=' operators.
• Trashed supports the '=,!=' operators.
• ParentIds supports the 'IN' operator.
• DriveId supports the '=' operator. It is used to get all the folders from the specified Drive.

  Note: You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

All the columns that support server side filtering can be paired with the AND and OR logical operators. For example, the following queries are processed server side:

SELECT * FROM Folders WHERE Name = 'example folder'

SELECT * FROM Folders WHERE OwnerEmail IN ('[email protected]', '[email protected]') AND ModifiedTime >= '2020-04-01T05:30:00'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Returns a list of a user's sheets and their relevant information.

Columns

Returns a list of a user's spreadsheets and their relevant information.

Columns

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Google Sheets.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Google Sheets, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Google Sheets Stored Procedures

Add a BigQuery dataSource to the specified spreadsheet. A Custom OAuth app with the scope bigquery.readonly is required to run this stored procedure.

Input

Result Set Columns

Add a DataSource Table to a sheet. A Custom OAuth app with the scope bigquery.readonly is required to run this stored procedure.

Input

Result Set Columns

Add a worksheet to an existing Google spreadsheet.

Input

Result Set Columns

Copies a sheet from a spreadsheet to another spreadsheet.

Input

Result Set Columns

Creates a spreadsheet in the user's Google Drive.

Input

Result Set Columns

Deletes a worksheet in an existing Google spreadsheet.

Input

Result Set Columns

Deletes a spreadsheet.

Input

Result Set Columns

Format cells in a specific range

Input

Result Set Columns

Updates properties of the sheet for the specified SpreadsheetId and SheetId.

Input

Result Set Columns

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Google Sheets:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Spreadsheet1_Sheet1 table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Spreadsheet1_Sheet1' 

Columns

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the DownloadDocument stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'DownloadDocument' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'DownloadDocument' AND IncludeResultColumns='True'

Columns

Pseudo-Columns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Spreadsheet1_Sheet1 table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Spreadsheet1_Sheet1' 
          

Columns

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Columns

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

Connection

OAuth

JWT OAuth

SSL

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The type of authentication to use when connecting to Google Sheets.

Possible Values

Data Type

string

Default Value

"OAuth"

Remarks

• Token: Set this to perform Token Based Authentication via the APIKey property.
• OAuth: Set this to perform OAuth authentication using a standard user account.
• OAuthJWT: Set this to perform OAuth authentication using an OAuth service account.
• GCPInstanceAccount: Set this to get Access Token from Google Cloud Platform instance.

If your client application does not use OAuth 2.0, then it must include an API key when it calls an API that's enabled within a Google Cloud Platform project.

Data Type

string

Default Value

""

Remarks

If your client application does not use OAuth 2.0, then it must include an API key when it calls an API that's enabled within a Google Cloud Platform project.

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

A comma-separated list of the names of the spreadsheets to be viewed.

Data Type

string

Default Value

""

Remarks

A comma-separated list of the names of the spreadsheets to be viewed. Query the Spreadsheet view to retrieve this data.

Note: Make sure to provide the exact spreadsheet name, including the leading and/or trailing spaces. Also, you should not add extra spaces before and after the comma separator. If any of the spreadsheet names includes a comma, escape it by using a backslash '\'.

A comma-separated list of the Ids of the spreadsheets to be viewed. To use spreadsheet name, set the Spreadsheet property.

Data Type

string

Default Value

""

Remarks

A comma-separated list of the Ids of the spreadsheets to be viewed. Query the Spreadsheet view to retrieve this data.

A comma separated list of the folders' names from which to retrieve spreadsheets in the format FolderName='name1,name2'.

Data Type

string

Default Value

""

Remarks

A comma separated list of the folders' names from which to retrieve spreadsheets in the format FolderName='name1,name2'.

A comma separated list of the folders' ids from which to retrieve spreadsheets in the format FolderId='id1,id2,id3'.

Data Type

string

Default Value

""

Remarks

A comma separated list of the folders' ids from which to retrieve spreadsheets in the format FolderId='id1,id2,id3'.

Indicates whether or not the trashed files will be listed.

Data Type

bool

Default Value

false

Remarks

If true, the driver will list the files/spreadsheets that have been trashed.

Indicates whether or not the hidden sheets will be listed.

Data Type

bool

Default Value

false

Remarks

If true, the driver will skip the hidden sheets.

Determines whether or not to retrieve Drive items.

Data Type

bool

Default Value

false

Remarks

If you set this property to 'true', you can query from any Drive spreadsheets.

Indicates whether or not to use Ids as Spreadsheet and Sheet name.

Data Type

bool

Default Value

false

Remarks

Indicates whether or not to use Ids as Spreadsheet and Sheet name. To select in a sheet instead of SpreadsheetName_SheetName use: SpreadsheetId_SheetId. Ex: SELECT * FROM 11696gdF5QUL1EnYikYiUeMTHRqA1111KbdYDoINqI_1151117664

A drive's names or ids from which to retrieve spreadsheets in the format TeamDrive='Shared drive 2, Shared drive 3', or TeamDrive='0BKwyFj1j9FOsUk9EVO, 0ANMIP9RIe1LQUk9PVA'.

Data Type

string

Default Value

""

Remarks

A drive's names or ids from which to retrieve spreadsheets in the format TeamDrive='Shared drive 2, Shared drive 3', or TeamDrive='0BKwyFj1j9FOsUk9EVO, 0ANMIP9RIe1LQUk9PVA'.

Boolean determining if the exposed sheets are limited to only the sheets shared to the user's domain or not.

Data Type

bool

Default Value

false

Remarks

If true, the driver will retrieve and expose only the sheets shared to the user's domain, excluding the sheets owned by the user. If false, the driver will retrieve both, the files owned by and shared to the user.

Used in case FolderId/FolderName properties are defined. If set to True this makes the driver return all the Spreadsheets inside nested folders, else the driver will return only the files directly to that foder. By default this is set to false.

Data Type

bool

Default Value

false

Remarks

Used in case FolderId/FolderName properties are defined. If set to True this makes the driver return all the Spreadsheets inside nested folders, else the driver will return only the files directly to that foder. By default this is set to false.

When enabled, fields where the provider detects error values will be returned as NULL. If disabled, the provider throws an error if an error value is detected in any field.

Data Type

bool

Default Value

true

Remarks

If this property is set to true, the Cloud returns fields containing value errors as NULL.

When this property is set to false, the Cloud throws an error if a value error is discovered.

The Cloud checks for the following error values:

• #NULL!
• #N/A
• #DIV/0!
• #VALUE!
• #REF!
• #NAME?
• #NUM!
• #ERROR!

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.

Data Type

string

Default Value

""

Remarks

This property is required in two cases:

• When using a custom OAuth application, such as in web-based authentication flows, service-based authentication, or certificate-based flows that require application registration.
• If the driver does not provide embedded OAuth credentials.

(When the driver provides embedded OAuth credentials, this value may already be provided by the Cloud and thus not require manual entry.)

OAuthClientId is generally used alongside other OAuth-related properties such as OAuthClientSecret and OAuthSettingsLocation when configuring an authenticated connection.

OAuthClientId is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can usually find this value in your identity provider’s application registration settings. Look for a field labeled Client ID, Application ID, or Consumer Key.

While the client ID is not considered a confidential value like a client secret, it is still part of your application's identity and should be handled carefully. Avoid exposing it in public repositories or shared configuration files.

For more information on how this property is used when configuring a connection, see Establishing a Connection.

Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).

Data Type

string

Default Value

""

Remarks

This property (sometimes called the application secret or consumer secret) is required when using a custom OAuth application in any flow that requires secure client authentication, such as web-based OAuth, service-based connections, or certificate-based authorization flows. It is not required when using an embedded OAuth application.

The client secret is used during the token exchange step of the OAuth flow, when the driver requests an access token from the authorization server. If this value is missing or incorrect, authentication fails with either an invalid_client or an unauthorized_client error.

OAuthClientSecret is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can obtain this value from your identity provider when registering the OAuth application.

Notes:

• This value should be stored securely and never exposed in public repositories, scripts, or unsecured environments.
• Client secrets may also expire after a set period. Be sure to monitor expiration dates and rotate secrets as needed to maintain uninterrupted access.

For more information on how this property is used when configuring a connection, see Establishing a Connection

Specifies a space-delimited list of service account emails for delegated requests.

Data Type

string

Default Value

""

Remarks

The service account emails must be specified in a space-delimited list.

Each service account must be granted the roles/iam.serviceAccountTokenCreator role on its next service account in the chain.

The last service account in the chain must be granted the roles/iam.serviceAccountTokenCreator role on the requesting service account. The requesting service account is the one specified in the RequestingServiceAccount property.

Note that for delegated requests, the requesting service account must have the permission iam.serviceAccounts.getAccessToken, which can also be granted through the serviceAccountTokenCreator role.

Specifies a service account email to make a delegated request.

Data Type

string

Default Value

""

Remarks

The service account email of the account for which the credentials are requested in a delegated request. With the list of delegated service accounts in DelegatedServiceAccounts, this property is used to make a delegated request.

You must have the IAM permission iam.serviceAccounts.getAccessToken on this service account.

Specifies the scope of the authenticating user's access to the application, to ensure they get appropriate access to data. If a custom OAuth application is needed, this is generally specified at the time the application is created.

Data Type

string

Default Value

""

Remarks

Scopes are set to define what kind of access the authenticating user will have; for example, read, read and write, restricted access to sensitive information. System administrators can use scopes to selectively enable access by functionality or security clearance.

When InitiateOAuth is set to GETANDREFRESH, you must use this property if you want to change which scopes are requested.

When InitiateOAuth is set to either REFRESH or OFF, you can change which scopes are requested using either this property or the Scope input.

This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.

Supplies the name of the client certificate's JWT Certificate store.

Data Type

string

Default Value

""

Remarks

The OAuthJWTCertType field specifies the type of the certificate store specified in OAuthJWTCert. If the store is password-protected, use OAuthJWTCertPassword to supply the password..

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, the CData Cloud initiates a search for a certificate. For further information, see OAuthJWTCertSubject.

Designations of certificate stores are platform-dependent.

Notes

• The most common User and Machine certificate stores in Windows include:
  • MY: A certificate store holding personal certificates with their associated private keys.
  • CA: Certifying authority certificates.
  • ROOT: Root certificates.
  • SPC: Software publisher certificates.
• In Java, the certificate store normally is a file containing certificates and optional private keys.
• When the certificate store type is PFXFile, this property must be set to the name of the file.
• When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

Identifies the type of key store containing the JWT Certificate.

Possible Values

Data Type

string

Default Value

"GOOGLEJSONBLOB"

Remarks

Provides the password for the OAuth JWT certificate used to access a password-protected certificate store. If the certificate store does not require a password, leave this property blank.

Data Type

string

Default Value

""

Remarks

This property specifies the password needed to open a password-protected certificate store. To determine if a password is necessary, refer to the documentation or configuration for your specific certificate store.

This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys are not encrypted.

Identifies the subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.

Data Type

string

Default Value

"*"

Remarks

The value of this property is used to locate a matching certificate in the store. The search process works as follows:

• If an exact match for the subject is found, the corresponding certificate is selected.
• If no exact match is found, the store is searched for certificates whose subjects contain the property value.
• If no match is found, no certificate is selected.

You can set the value to '*' to automatically select the first certificate in the store. The certificate subject is a comma-separated list of distinguished name fields and values. For example: CN=www.server.com, OU=test, C=US, [email protected].

Common fields include:

If a field value contains a comma, enclose it in quotes. For example: "O=ACME, Inc.".

The issuer of the Java Web Token.

Data Type

string

Default Value

""

Remarks

The issuer of the Java Web Token. Enter the value of the service account email address.

This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys contain a copy of the issuer account.

The user subject for which the application is requesting delegated access.

Data Type

string

Default Value

""

Remarks

The user subject for which the application is requesting delegated access. Enter the email address of the user for which the application is requesting delegated access.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If you are using a TLS/SSL connection, use this property to specify the TLS/SSL certificate to be accepted from the server. If you specify a value for this property, all other certificates that are not trusted by the machine are rejected.

This property can take the following forms:

Note: It is possible to use '*' to signify that all certificates should be accepted, but due to security concerns this is not recommended.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Data Type

string

Default Value

"1"

Remarks

This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.

The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.

When combined with the LogModules property, Verbosity can refine logging to specific categories of information.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Determines how to determine the data types of columns.

Possible Values

Data Type

string

Default Value

"RowScan"

Remarks

Indicates whether or not the first row should be used as a column header.

Data Type

bool

Default Value

true

Remarks

If true, the first row will be used as a column header. Otherwise, the pseudo column names (A, B, C, etc.) will be used.

The Header property is used in conjunction with the Orientation property. When Header is set to false and Orientation is set to Columns, column names are reported as R1, R2, R3, etc.

Set this property to control the name of the primary key.

Data Type

string

Default Value

""

Remarks

Determines the name of the primary key column which holds the row number. The default value of the primary key is Id.

Set this property if there is a column named ID in the table you are quering, or if you prefer to change the name of the primary key.

Define the tables within the Google Spreadsheet.

Data Type

string

Default Value

""

Remarks

This property is used to define the ranges within a sheet that will appear as tables. The value is a comma-separated list of name-value pairs in the form [Table Name]=[Spreadsheet Name]_[Sheet Name]![Range] or [Table Name]=[Spreadsheet Name]_[Sheet Name]![Range]. Table Name is the name of the table you want to use for the data and will be used when issuing queries. Sheet Name is the name of the sheet within the Google Spreadsheet and Range is the range of cells that contain the data for the table.

Here is an example DefineTables value: DefineTables="Table1=Spreadsheet1_Sheet1!A1:N25,Table2=Spreadsheet1_Sheet2!C3:M53,Table4=xIsPcLs2-bF3AavQcSLCfzs3kGc_Sheet4!C20:N60".

Indicates whether the data in the sheet is laid out horizontally or vertically.

Possible Values

Data Type

string

Default Value

"Vertical"

Remarks

By default, the Cloud models vertically oriented spreadsheet data -- rows arranged vertically below a header row.

Set this to "Horizontal" if the rows are arranged left to right. The first column contains the column names and subsequent columns become rows.

Controls whether the driver includes an auto-generated primary key column in the table definition.

Data Type

bool

Default Value

true

Remarks

When set to True (default), the driver adds a primary key column (id) to each table. The column name can be customized using the PrimaryKeyIdentifier property. When set to False, the driver omits the auto-generated primary key column entirely and returns only the columns defined in the source data.

Disabling this option will impact performance for UPDATE and DELETE operations, as the driver must search all matching rows instead of directly relying on the primary key column.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

If set to true, the driver will automatically expand the dimensions in case the updated/insert/deleted value is outside the range of the sheet.

Data Type

bool

Default Value

false

Remarks

If set to true, the driver will automatically expand the dimensions in case the updated/insert/deleted value is outside the range of the sheet.

Configuration properties to provide when using Workload Identity Federation via AWS.

Data Type

string

Default Value

""

Remarks

The properties are formatted as a semicolon-separated list of Key=Value properties, where the value is optionally quoted. For example, this setting authenticates in AWS using a user's root keys:

AWSWorkloadIdentityConfig="AuthScheme=AwsRootKeys;AccessKey='AKIAABCDEF123456';SecretKey=...;Region=us-east-1"

Configuration properties to provide when using Workload Identity Federation via Azure.

Data Type

string

Default Value

""

Remarks

The properties are formatted as a semicolon-separated list of Key=Value properties, where the value is optionally quoted. For example, this setting authenticates in Azure using client credentials:

AzureWorkloadIdentityConfig="AuthScheme=AzureServicePrincipal;AzureTenant=directory (tenant) id;OAuthClientID=application (client) id;OAuthClientSecret=client secret;AzureResource=application id uri;"

Determines how dates, times, and durations should be represented in the output. This is ignored if the ValueRenderOption is FormattedValue. The default datetime render option is SerialNumber.

Possible Values

Data Type

string

Default Value

"SerialNumber"

Remarks

Determines how existing data is changed when new data is input.

Possible Values

Data Type

string

Default Value

"Overwrite"

Remarks

INSERT INTO Spreadsheet1_Sheet1(Id,Name, Amount) VALUES (2,'Test', 10)

Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

The default value for this property, -1, means that no row limit is enforced unless the query explicitly includes a LIMIT clause. (When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting.)

Setting MaxRows to a whole number greater than 0 ensures that queries do not return excessively large result sets by default.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Indicates whether to read empty cells as null or as empty.

Possible Values

Data Type

string

Default Value

"ReadAsNull"

Remarks

NullValueMode controls how empty Google Sheets cells are modelled. An empty cell is a cell that has not been set and thus contains a null string. If NullValueMode is set to ReadAsNull, NULL is reported for an empty cell; if NullValueMode is set to ReadAsEmpty, an empty string is reported for an empty cell.

Specifies the maximum number of records per page the provider returns when requesting data from Google Sheets.

Data Type

int

Default Value

1000

Remarks

When processing a query, instead of requesting all of the queried data at once from Google Sheets, the Cloud can request the queried data in pieces called pages.

This connection property determines the maximum number of results that the Cloud requests per page.

Note: Setting large page sizes may improve overall query execution time, but doing so causes the Cloud to use more memory when executing queries and risks triggering a timeout.

Boolean determining if percentage columns should be considered as decimal.

Data Type

bool

Default Value

false

Remarks

Boolean determining if percentage columns should be considered as decimal.

Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the Cloud exposes as table columns.

To specify individual pseudocolumns, use the following format:

Table1=Column1;Table1=Column2;Table2=Column3

To include all pseudocolumns for all tables use:

*=*

The maximum number of rows to scan to look for the columns available in a table.

Data Type

int

Default Value

50

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

Indicates whether or not the empty rows should be pushed.

Data Type

bool

Default Value

false

Remarks

If true, the empty rows will be pushed at the output.

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

Data Type

int

Default Value

60

Remarks

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.

Timeout is set to 60 seconds by default. To disable timeouts, set this property to 0.

Disabling the timeout allows operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server.

Note: Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

Specifies whether or not simple names should be used for tables and columns.

Data Type

bool

Default Value

false

Remarks

Google Sheets tables can include special characters in their names that are typically not allowed in standard databases. This property makes the Cloud easier to use with traditional database tools.

Setting UseSimpleNames to True simplifies the names of the columns that are returned. It enforces a naming scheme where only alphanumeric characters and underscores are valid for displayed column names.

Notes:

• Any non-alphanumeric characters are converted to underscores.
• If the column or table names exceed 128 characters in length they are truncated to 128 characters to comply with SQL Server standards.

Determines how inserted values should be treated.

Possible Values

Data Type

string

Default Value

"UserEntered"

Remarks

Determines how values should be rendered in the output.

Possible Values

Data Type

string

Default Value

"FormattedValue"

Remarks

The ID of your Workload Identity Federation pool.

Data Type

string

Default Value

""

Remarks

The ID of your Workload Identity Federation pool.

The ID of the Google Cloud project that hosts your Workload Identity Federation pool.

Data Type

string

Default Value

""

Remarks

The ID of the Google Cloud project that hosts your Workload Identity Federation pool.

The ID of your Workload Identity Federation pool provider.

Data Type

string

Default Value

""

Remarks

The ID of your Workload Identity Federation pool provider.

LZMA from 7Zip LZMA SDK

LZMA SDK is placed in the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original LZMA SDK code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

LZMA2 from XZ SDK

Version 1.9 and older are in the public domain.

Xamarin.Forms

Xamarin SDK

The MIT License (MIT)

Copyright (c) .NET Foundation Contributors

All rights reserved.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

NSIS 3.10

Copyright (C) 1999-2025 Contributors THE ACCOMPANYING PROGRAM IS PROVIDED UNDER THE TERMS OF THIS COMMON PUBLIC LICENSE ("AGREEMENT"). ANY USE, REPRODUCTION OR DISTRIBUTION OF THE PROGRAM CONSTITUTES RECIPIENT'S ACCEPTANCE OF THIS AGREEMENT.

1. DEFINITIONS

"Contribution" means:

a) in the case of the initial Contributor, the initial code and documentation distributed under this Agreement, and b) in the case of each subsequent Contributor:

i) changes to the Program, and

ii) additions to the Program;

where such changes and/or additions to the Program originate from and are distributed by that particular Contributor. A Contribution 'originates' from a Contributor if it was added to the Program by such Contributor itself or anyone acting on such Contributor's behalf. Contributions do not include additions to the Program which: (i) are separate modules of software distributed in conjunction with the Program under their own license agreement, and (ii) are not derivative works of the Program.

"Contributor" means any person or entity that distributes the Program.

"Licensed Patents " mean patent claims licensable by a Contributor which are necessarily infringed by the use or sale of its Contribution alone or when combined with the Program.

"Program" means the Contributions distributed in accordance with this Agreement.

"Recipient" means anyone who receives the Program under this Agreement, including all Contributors.

2. GRANT OF RIGHTS

a) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free copyright license to reproduce, prepare derivative works of, publicly display, publicly perform, distribute and sublicense the Contribution of such Contributor, if any, and such derivative works, in source code and object code form.

b) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free patent license under Licensed Patents to make, use, sell, offer to sell, import and otherwise transfer the Contribution of such Contributor, if any, in source code and object code form. This patent license shall apply to the combination of the Contribution and the Program if, at the time the Contribution is added by the Contributor, such addition of the Contribution causes such combination to be covered by the Licensed Patents. The patent license shall not apply to any other combinations which include the Contribution. No hardware per se is licensed hereunder.

c) Recipient understands that although each Contributor grants the licenses to its Contributions set forth herein, no assurances are provided by any Contributor that the Program does not infringe the patent or other intellectual property rights of any other entity. Each Contributor disclaims any liability to Recipient for claims brought by any other entity based on infringement of intellectual property rights or otherwise. As a condition to exercising the rights and licenses granted hereunder, each Recipient hereby assumes sole responsibility to secure any other intellectual property rights needed, if any. For example, if a third party patent license is required to allow Recipient to distribute the Program, it is Recipient's responsibility to acquire that license before distributing the Program.

d) Each Contributor represents that to its knowledge it has sufficient copyright rights in its Contribution, if any, to grant the copyright license set forth in this Agreement.

3. REQUIREMENTS

A Contributor may choose to distribute the Program in object code form under its own license agreement, provided that:

a) it complies with the terms and conditions of this Agreement; and

b) its license agreement:

i) effectively disclaims on behalf of all Contributors all warranties and conditions, express and implied, including warranties or conditions of title and non-infringement, and implied warranties or conditions of merchantability and fitness for a particular purpose;

ii) effectively excludes on behalf of all Contributors all liability for damages, including direct, indirect, special, incidental and consequential damages, such as lost profits;

iii) states that any provisions which differ from this Agreement are offered by that Contributor alone and not by any other party; and

iv) states that source code for the Program is available from such Contributor, and informs licensees how to obtain it in a reasonable manner on or through a medium customarily used for software exchange.

When the Program is made available in source code form:

a) it must be made available under this Agreement; and

b) a copy of this Agreement must be included with each copy of the Program.

Contributors may not remove or alter any copyright notices contained within the Program.

Each Contributor must identify itself as the originator of its Contribution, if any, in a manner that reasonably allows subsequent Recipients to identify the originator of the Contribution.

4. COMMERCIAL DISTRIBUTION

Commercial distributors of software may accept certain responsibilities with respect to end users, business partners and the like. While this license is intended to facilitate the commercial use of the Program, the Contributor who includes the Program in a commercial product offering should do so in a manner which does not create potential liability for other Contributors. Therefore, if a Contributor includes the Program in a commercial product offering, such Contributor ("Commercial Contributor") hereby agrees to defend and indemnify every other Contributor ("Indemnified Contributor") against any losses, damages and costs (collectively "Losses") arising from claims, lawsuits and other legal actions brought by a third party against the Indemnified Contributor to the extent caused by the acts or omissions of such Commercial Contributor in connection with its distribution of the Program in a commercial product offering. The obligations in this section do not apply to any claims or Losses relating to any actual or alleged intellectual property infringement. In order to qualify, an Indemnified Contributor must: a) promptly notify the Commercial Contributor in writing of such claim, and b) allow the Commercial Contributor to control, and cooperate with the Commercial Contributor in, the defense and any related settlement negotiations. The Indemnified Contributor may participate in any such claim at its own expense.

For example, a Contributor might include the Program in a commercial product offering, Product X. That Contributor is then a Commercial Contributor. If that Commercial Contributor then makes performance claims, or offers warranties related to Product X, those performance claims and warranties are such Commercial Contributor's responsibility alone. Under this section, the Commercial Contributor would have to defend claims against the other Contributors related to those performance claims and warranties, and if a court requires any other Contributor to pay any damages as a result, the Commercial Contributor must pay those damages.

5. NO WARRANTY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, THE PROGRAM IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER EXPRESS OR IMPLIED INCLUDING, WITHOUT LIMITATION, ANY WARRANTIES OR CONDITIONS OF TITLE, NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Each Recipient is solely responsible for determining the appropriateness of using and distributing the Program and assumes all risks associated with its exercise of rights under this Agreement, including but not limited to the risks and costs of program errors, compliance with applicable laws, damage to or loss of data, programs or equipment, and unavailability or interruption of operations.

6. DISCLAIMER OF LIABILITY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, NEITHER RECIPIENT NOR ANY CONTRIBUTORS SHALL HAVE ANY LIABILITY FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING WITHOUT LIMITATION LOST PROFITS), HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OR DISTRIBUTION OF THE PROGRAM OR THE EXERCISE OF ANY RIGHTS GRANTED HEREUNDER, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

7. GENERAL

If any provision of this Agreement is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this Agreement, and without further action by the parties hereto, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable.

If Recipient institutes patent litigation against a Contributor with respect to a patent applicable to software (including a cross-claim or counterclaim in a lawsuit), then any patent licenses granted by that Contributor to such Recipient under this Agreement shall terminate as of the date such litigation is filed. In addition, if Recipient institutes patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Program itself (excluding combinations of the Program with other software or hardware) infringes such Recipient's patent(s), then such Recipient's rights granted under Section 2(b) shall terminate as of the date such litigation is filed.

All Recipient's rights under this Agreement shall terminate if it fails to comply with any of the material terms or conditions of this Agreement and does not cure such failure in a reasonable period of time after becoming aware of such noncompliance. If all Recipient's rights under this Agreement terminate, Recipient agrees to cease use and distribution of the Program as soon as reasonably practicable. However, Recipient's obligations under this Agreement and any licenses granted by Recipient relating to the Program shall continue and survive.

Everyone is permitted to copy and distribute copies of this Agreement, but in order to avoid inconsistency the Agreement is copyrighted and may only be modified in the following manner. The Agreement Steward reserves the right to publish new versions (including revisions) of this Agreement from time to time. No one other than the Agreement Steward has the right to modify this Agreement. IBM is the initial Agreement Steward. IBM may assign the responsibility to serve as the Agreement Steward to a suitable separate entity. Each new version of the Agreement will be given a distinguishing version number. The Program (including Contributions) may always be distributed subject to the version of the Agreement under which it was received. In addition, after a new version of the Agreement is published, Contributor may elect to distribute the Program (including its Contributions) under the new version. Except as expressly stated in Sections 2(a) and 2(b) above, Recipient receives no rights or licenses to the intellectual property of any Contributor under this Agreement, whether expressly, by implication, estoppel or otherwise. All rights in the Program not expressly granted under this Agreement are reserved.

This Agreement is governed by the laws of the State of New York and the intellectual property laws of the United States of America. No party to this Agreement will bring a legal action under this Agreement more than one year after the cause of action arose. Each party waives its rights to a jury trial in any resulting litigation.