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

Connecting to Smartsheet

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

Accessing Data from CData Cloud Services

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

Connect to Smartsheet 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 Smartsheet

Smartsheet supports connections via the following authentication methods:

• Using the Personal Access Token

Personal Access Token

Use the personal token to test and to access your own data. To obtain the personal token, follow the steps below:

1. Log into Smartsheet.
2. Click Account and select Personal Settings.
3. Click API Access and use the form to generate new access tokens or manage existing access tokens.

Synch Connections

• OAuthClientId: The client Id assigned when you registered your custom OAuth application.
• OAuthClientSecret: The client secret assigned when you registered your custom OAuth application.

Click Connect to Smartsheet to open the OAuth endpoint in your default browser. Log in and grant permissions to the application.

The driver then completes the OAuth process as follows:

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values so that they persist across connections.

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.

The CData Cloud models the Smartsheet data as an easy-to-use SQL database.

Tables

The data model of the Cloud is dynamic. This means that, when you connect using the Cloud, any changes you make in the Smartsheet UI, such as adding a new table, adding new columns, or changing a column's data type, are automatically included in the schema of the Cloud.

However, some tables and views, such as workspaces and folders, are static. These are defined in schema files, which are simple, text-based configuration files. The static tables and views are prefixed with 'Info_'.

Common tables include:

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. They perform tasks beyond standard CRUD operations, including managing sheets and attachments, and retrieving OAuth credentials.

API Restrictions

API limitations and requirements are documented in this section.

You can add hyperlink columns to table schema files generated by the CreateSchema stored procedure.

After generating a schema file from the desired table, add a new column (attr) as follows:

• Set other:columnid to a unique value (any string that isn't already taken by another column).
• Add an other:hyperlink attribute to the column. Set this attribute to the value in the name attribute of the column you want your new hyperlink column to display hyperlinks for.

  These two values must match exactly.

For example, suppose you have this column in your schema file:

<attr name="MyColumnName" xs:type="string" ... other:columnid="5555555555555555"/>

Your new hyperlink column attached to this column should look like this:

<attr name="nameLink" xs:type="string" ... other:columnid="YourUniqueIDHere" other:hyperlink="MyColumnName"/>

The Cloud models the data in Smartsheet as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Smartsheet Tables

Retrieve detailed information about Smartsheet attachments, which can be associated with comments, rows, or sheets, providing a comprehensive view of file dependencies.

Table Specific Information

Select

When getting data from this table the queries might be slow if you have a lot of sheets. To counter this, specifying SheetId is recommended. Also other parameters like DiscussionId, RowId, CommentId can be specified to get Attachments to the related object.

Retrieve all Attachments from all Sheets.

SELECT * FROM Info_Attachments

Retrieve all Attachments related to a Sheet.

SELECT * FROM Info_Attachments WHERE SheetId = '2940085806098308'

Retrieve all Attachments related to a discussion.

SELECT * FROM Info_Attachments WHERE SHEETId = '2940085806098308' AND DiscussionId = '8206230771525508'

Retrieve all Attachments related to a Row.

SELECT * FROM Info_Attachments WHERE SheetId = '2940085806098308' AND RowId = '6773684447799172'

Retrieve all Attachments related to a Comment.

SELECT * FROM Info_Attachments WHERE SheetId = '2940085806098308' AND CommentId = '1322606759569284'

Retrieve details of a specific Attachment.

SELECT * FROM Info_Attachments WHERE Id = '3053958945105796' AND SheetId = '2940085806098308'

Insert

You can attach a file to a specific row in a specific sheet. Fields required for Insert are SheetId, RowId, Name of the file and either FilePath or ContentEncoded.

INSERT INTO Info_Attachments (SheetId, RowId, Name, FilePath) VALUES ('8179839304787844', '4728727909230468', 'TestSheet.xlsx', 'G:\\\\TestSheet.xlsx')

INSERT INTO Info_Attachments (SheetId, RowId, Name, ContentEncoded) VALUES ('8179839304787844', '4728727909230468', 'TestSheet.xlsx', 'dGVzdGluZyBlbmNvZGVkIHRleHQ=')

Columns

Access comments within discussions in Smartsheet, providing insights into collaboration history.

Table Specific Information

Select

This view returns details for Columns of a specified Discussion. The SheetId parameter is always required.

Retrieve all comments from a sheet.

SELECT * FROM Info_Comments WHERE SheetId = '2940085806098308'

Retrieve details of a specified Comment.

SELECT * FROM Info_Comments WHERE SheetId = '2940085806098308' AND Id = '1322606759569284'

Insert

You can insert a comment to a discussion by providing SheetId, DiscussionId and Text.

INSERT INTO Info_Comments (SheetId, DiscussionId, Text) VALUES ('568679927703428', '4661021235275652', 'This is a comment 3')

Columns

Analyze discussion threads, including comments, tied to specific rows or sheets for better communication tracking.

Table Specific Information

Select

This view returns a list of Discussions. The SheetId parameter is always required.

Retrieve all Discussions related to a Sheet.

SELECT * FROM Info_Discussions WHERE SheetId = '2940085806098308'

Retrieve Discussions related to a specific Row of a Sheet.

SELECT * FROM Info_Discussions WHERE SheetId = '2940085806098308' AND RowId = '6773684447799172'

Retrieve details of a specified Comment.

SELECT * FROM Info_Discussions WHERE SheetId = '2940085806098308' AND Id = '8206230771525508'

Insert

You can insert a discussion to a sheet by providing SheetId and FirstDiscussionComment. To add a discussion to a row, provide RowId, SheetId, and FirstDiscussionComment.

INSERT INTO Info_Discussions (SheetId, FirstDiscussionComment) VALUES ('568679927703428', 'This is a comment 1')

INSERT INTO Info_Discussions (RowId, SheetId, FirstDiscussionComment) VALUES ('1889077216995204', '568679927703428', 'This is a comment 2')

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.

Manage and analyze the Sheet_Content_Calendar, designed for scheduling and content tracking.

Columns

Track and analyze historical customer order data using the Sheet_Customer_Order_Tracking_History.

Columns

Plan, budget, and track event-related data with the Sheet_Event_Plan_Budget table.

Columns

Manage and analyze expense reports effectively using the Sheet_Expense_Report table.

Columns

Track and manage projects with fixed deadlines using a Gantt-style layout in this Smartsheet table.

Columns

Track and manage job candidate progress and information with the Sheet_Job_Candidate_Tracker table.

Columns

Monitor sales activity by representative and month with the Sheet_Sales_Activity_Tracking_by_Rep_Month table.

Columns

Simplify sales tracking using the Sheet_Simple_Sales_Pipeline table for streamlined data management.

Columns

Monitor and track team objectives efficiently using the Sheet_Team_Objectives_Tracker.

Columns

Prioritize and manage team tasks with the Sheet_Team_Task_List_by_Priority table.

Columns

Test and analyze web form data integration using the Sheet_WebFormTest table.

Columns

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 - Smartsheet Views

Access the historical changes of specific cells in Smartsheet, enabling detailed audit and tracking capabilities.

Table Specific Information

Select

To get data from this view a SheetId, RowId, and ColumnId are always required.

Retrieve Cell History.

SELECT * FROM Info_CellHistory WHERE SheetId = '2940085806098308' AND RowId = '6773684447799172' AND ColumnId = '7999197812156292'

Columns

Explore individual cell data in Smartsheet rows, offering granular insights into sheet contents.

Table Specific Information

Select

This view returns details for Cells of a specified Row. The SheetId and RowId parameters are always required.

Retrieve Cells.

SELECT * FROM Info_Cells WHERE SheetId = '2940085806098308' AND RowId = '6773684447799172'

Columns

Retrieve metadata and properties of columns in Smartsheet sheets or reports, essential for understanding sheet structures.

Table Specific Information

Select

This view returns details for Columns of a specified Sheet. The SheetId parameter is always required.

Retrieve Columns.

SELECT * FROM Info_Columns WHERE SheetId = '2940085806098308'

Retrieve details of the specified Columns.

SELECT * FROM Info_Columns WHERE SheetId = '2940085806098308' AND Id = '7999197812156292'

Columns

Query Smartsheet user contact details, facilitating management of personal and shared connections.

Table Specific Information

Select

This view returns details for Contacts.

Retrieve Contacts.

SELECT * FROM Info_Contacts

Retrieve details of a specified Contact.

SELECT * FROM Info_Contacts WHERE Id = '1322606759569284'

Columns

Retrieve a user's favorite items in Smartsheet, helping prioritize frequently accessed content.

Table Specific Information

Select

This view returns a list of Favorite objects.

Retrieve all Favorite objects.

SELECT * FROM Info_Favorites

Columns

Explore folder structures in Smartsheet, including user-level folders and workspace organization.

Table Specific Information

Select

Retrieve all Folders.

SELECT * FROM Info_Folders

Retrieve details for a Folder.

SELECT * FROM Info_Folders WHERE Id = '2035256120371076'

Retrieve Folders of a specified Workspace.

SELECT * FROM Info_Folders WHERE WorkspaceId = '2940085806098308'

Columns

Retrieve group information for enhanced user management and role-based permissions in Smartsheet.

Table Specific Information

Select

Retrieve all Groups.

SELECT * FROM Info_Groups

Retrieve details for a Group.

SELECT * FROM Info_Groups WHERE Id = '2035256120371076'

Columns

Access items available on the Smartsheet Home tab, offering a centralized view of user-permissible objects.

Table Specific Information

Select

Retrieve all objects located on the Home tab:

SELECT * FROM Info_Home

Columns

Query Smartsheet reports, providing insights into custom aggregated data across sheets.

Table Specific Information

Select

Retrieve all Reports.

SELECT * FROM Info_Reports

Retrieve Reports located in a specific Workspace.

SELECT * FROM Info_Reports WHERE WorkspaceId = '6773684447799172'

Retrieve Reports located in a specific Folder.

SELECT * FROM Info_Reports WHERE FolderId = '6773684447799172'

Columns

Retrieve detailed row data, including cells, discussions, and attachments, for enhanced row-level analysis.

Table Specific Information

Select

This view returns a list of Sheet Rows. The SheetId parameter is always required.

Retrieve all Rows of a Sheet.

SELECT * FROM Info_Rows WHERE SheetId = '2940085806098308'

Retrieve details of a specified Row.

SELECT * FROM Info_Rows WHERE SheetId = '2940085806098308' AND Id = '8206230771525508'

Columns

Access Smartsheet server information and application constants, useful for API integrations.

Table Specific Information

Select

Retrieve Server Information:

SELECT * FROM Info_ServerInformation

Columns

Retrieve sharing configurations for Smartsheet reports, sheets, and workspaces to manage access controls.

Table Specific Information

Select

This view returns a list of Shares. Either the SheetId or WorkspaceId parameter is always required.

Retrieve all Shares related to a Sheet.

SELECT * FROM Info_Shares WHERE SheetId = '2940085806098308'

Retrieve all Shares related to a Workspace.

SELECT * FROM Info_Shares WHERE WorkspaceId = '1630085806098114'

Retrieve details of a Share related to a specified Sheet.

SELECT * FROM Info_Shares WHERE SheetId = '2940085806098308' AND Id = '1928085806875098'

Retrieve details of a Share related to a specified Workspace.

SELECT * FROM Info_Shares WHERE WorkspaceId = '1630085806098114' AND Id = '2928085806875091'

Columns

Explore detailed metadata and structure of Smartsheet sheets, including their components like columns, rows, and attachments.

Table Specific Information

Select

Retrieve all Sheets.

SELECT * FROM Info_Sheets

Retrieve Sheets located in a specific Folder.

SELECT * FROM Info_Sheets WHERE FolderId = '2928085806875091'

Retrieve Sheets located in a specific Workspace.

SELECT * FROM Info_Sheets WHERE WorkspaceId = '1928085806875098'

Columns

Retrieve Smartsheet template information to streamline the creation of standardized sheets.

Table Specific Information

Select

By default this view returns all Templates created by the user. If the TemplateType filter is set to 'public' , the public templates will be returned.

Retrieve all Templates.

SELECT * FROM Info_Templates

Retrieve all other public Templates.

SELECT * FROM Info_Templates WHERE TemplateType = 'public'

Retrieve Templates located on a specific folder.

SELECT * FROM Info_Templates WHERE FolderId = '2928085806875091'

Retrieve Templates located on a specific Workspace.

SELECT * FROM Info_Templates WHERE WorkspaceId = '1928085806875098'

Columns

Query user data within an organization for administrative and reporting purposes.

Table Specific Information

Select

Retrieve all Users.

SELECT * FROM Info_Users

Retrieve all Users of a specific Group.

SELECT * FROM Info_Users WHERE GroupId = '2928085806875091'

Columns

Access workspace-level details, including sheets and folders, for better organizational insights.

Table Specific Information

Select

Retrieve all Workspaces.

SELECT * FROM Info_Workspaces

Retrieve all details for a specific Workspace.

SELECT * FROM Info_Workspaces WHERE Id = '2928085806875091'

Columns

Analyze sales activities and team objectives using a dedicated Smartsheet report for performance tracking.

Columns

Access detailed task and event data within a Smartsheet report for efficient planning and monitoring.

Columns

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

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

CData Cloud - Smartsheet Stored Procedures

Copy rows from one Smartsheet to another, streamlining data reorganization.

Input

Result Set Columns

Duplicate an existing sheet and relocate it to a specified folder, home directory, or workspace.

Input

Result Set Columns

Create a new sheet in Smartsheet for data organization and tracking.

Note: This procedure makes use of indexed parameters. Indexed parameters facilitate providing multiple instances a single parameter as inputs for the procedure.

Suppose there is an input parameter named Param#. To input multiple instances of an indexed parameter like this, execute:

EXEC ProcedureName Param#1 = "value1", Param#2 = "value2", Param#3 = "value3"

In the table below, indexed parameters are denoted with a '#' character at the end of their names.

Input

Result Set Columns

Remove a specified attachment from Smartsheet to maintain data integrity.

Input

Result Set Columns

Delete a specific sheet from Smartsheet to manage space and relevance.

Input

Result Set Columns

Import CSV or XLSX files into Smartsheet, specifying the target folder or workspace.

Input

Result Set Columns

Move rows between Smartsheets for efficient data organization and management.

Input

Result Set Columns

Relocate a sheet to a new folder, home directory, or workspace for better accessibility.

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 Smartsheet:

• 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 Sheet_Test_Sheet table:

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

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 RefreshOAuthAccessToken stored procedure:

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

Columns

Describes the primary and foreign keys.

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

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

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

Data Type Mappings

The Cloud maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings (when TypeDetectionScheme is set to RowScan; the default behavior).

For more information on Smartsheet data types, refer to Column Types from Smartsheet's API reference.

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

SSL

Logging

Schema

Miscellaneous

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

Specifies the authentication method to use when connecting to Smartsheet.

Possible Values

Data Type

string

Default Value

"PersonalAccessToken"

Remarks

This property specifies whether to use Personal Access Token (PAT) or OAuth for authenticating with Smartsheet. When using PersonalAccessToken, ensure you have a valid token available. For OAuth, configure the necessary OAuth settings, such as OAuthClientId, OAuthClientSecret, and CallbackURL, to enable the provider to perform OAuth-based authentication. Choose the authentication method based on your access requirements and Smartsheet's supported security protocols.

Specifies the Personal Access Token for authenticating with Smartsheet. This token can be generated through the Smartsheet user interface.

Data Type

string

Default Value

""

Remarks

Ensure that the token is valid and matches the credentials required for the connection. If your token expires or becomes invalid, update this property with a new token.

This property is useful for secure and efficient authentication, particularly for automated workflows and integrations with the Smartsheet API.

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

Specifies the hosting region for your Smartsheet account.

Possible Values

Data Type

string

Default Value

"GLOBAL"

Remarks

This property specifies the region where your Smartsheet account is hosted to ensure that the provider connects to the appropriate Smartsheet data center. The available options are:

• GLOBAL: Connects to the default global Smartsheet region.
• EU: Connects to Smartsheet accounts hosted in the European Union.
• GOV: Connects to Smartsheet accounts hosted in the U.S. Government cloud.

This property is useful for aligning your connection with compliance and performance requirements specific to your account's hosting region.

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

Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.

Data Type

string

Default Value

""

Remarks

OAuthClientId is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

Data Type

string

Default Value

""

Remarks

OAuthClientSecret is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

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 using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

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.

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

Specifies whether the first row of the data is treated as column headers or not.

Data Type

bool

Default Value

false

Remarks

This property determines how the provider handles the first row of data when assigning column names. If set to true, the first row is used as column headers, allowing for more descriptive column names. If set to false, the provider assigns generic names, which may be less intuitive but avoids relying on the contents of the data.

When interpreting the first row as headers, certain conditions must be met. Column headers should not contain special characters, as these may cause parsing errors. Additionally, all column headers must be non-empty to ensure proper detection and assignment. While meaningful column names can simplify query writing and improve data readability, users should ensure that the first row in the dataset truly represents headers to avoid misinterpretation.

Specifies whether to hide formatting characters, such as currency symbols and percentage signs, in numeric values. When enabled, numeric columns are converted from varchar to double.

Data Type

bool

Default Value

false

Remarks

This property removes formatting characters from numeric values and converts them into a numerical data type (double). It works only when TypeDetectionScheme is set to RowScan and the first RowScanDepth rows in a column are convertible to double. If these conditions are not met, the column retains its original format as varchar.

This property is useful for users who need clean, numeric data for calculations or analysis. By standardizing numeric values into a consistent data type, it ensures compatibility with analytical tools and simplifies data processing workflows.

Specifies whether the deletion operation should proceed if any specified Row IDs are not found.

Data Type

bool

Default Value

false

Remarks

This property determines how the driver handles cases where specified Row IDs are not found during a deletion operation. If set to false, no rows are deleted, and a "not found" error is returned if any of the Row IDs are missing. If set to true, the operation proceeds, ignoring any missing Row IDs.

This property is useful for ensuring the integrity of delete operations, particularly when precise control over the affected rows is required. It can prevent unintended results caused by missing or incorrect Row IDs.

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

This property sets an upper limit on the number of rows the Cloud returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

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

Specifies the maximum number of results to return from Smartsheet, per page. This setting overrides the default page size set by the datasource, which is optimized for most use cases.

Data Type

int

Default Value

500

Remarks

You may want to adjust the default pagesize to optimize results for a particular object or service endpoint you are querying. Be aware that increasing the page size may improve performance, but it could also result in higher memory consumption per page.

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

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: "*=*"

Specifies the level of compatibility for the returned data, determining the format and functionality provided in query results.

Possible Values

Data Type

string

Default Value

"2"

Remarks

This property specifies the compatibility level of returned data, allowing you to choose between legacy formats or newer, feature-rich data formats such as multi-contact or multi-picklist data. Choose a compatibility level based on your application's needs:

• 0 (Backwards-compatible): Returns data in a legacy text format for maximum compatibility with older systems.
• 1 (Multi-contact data): Returns multi-contact data in a format optimized for standard processing but not specifically for report tables.
• 2 (Report-compatible multi-contact data): Returns multi-contact data in a format specifically designed for report tables.
• 3 (Multi-picklist data): Returns data in a format that supports multi-picklist fields for advanced use cases.

To retrieve data for report tables, the compatibility level must be set to at least 2. Adjust this property based on your application’s requirements to balance compatibility with advanced functionality.

This property is useful for managing how Smartsheet data is processed and consumed. Use it to align your data retrieval with application-specific needs, such as maintaining compatibility with older systems or leveraging advanced data structures for more complex use cases.

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

Data Type

int

Default Value

100

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.

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.

Data Type

int

Default Value

60

Remarks

This property controls the maximum time, in seconds, that the Cloud waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the Cloud cancels the operation and throws an exception.

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.

Setting this property to 0 disables the timeout, allowing 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. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

Specifies the method used to determine the data types of columns, such as by scanning rows, analyzing column formats, or treating all columns as strings.

Possible Values

Data Type

string

Default Value

"RowScan"

Remarks

This property defines how the driver determines column data types during query execution:

• None: All columns are treated as strings. Column names are still scanned if the Header property is set to True.
• RowScan: The driver scans rows to infer data types based on the data content. The number of rows scanned is determined by the RowScanDepth property.
• ColumnFormat: Data types are determined based on the column's format in the dataset.

This property is useful for adapting the driver to different data structures or optimizing performance based on your dataset's characteristics.

If precision is critical, such as when working with mixed data types, use RowScan to ensure accurate type detection. For simpler datasets or when type consistency is not required, None can streamline processing. Use ColumnFormat when the dataset includes predefined formats for columns.

Specifies whether table and view names corresponding to sheets and reports should include the full file path or only the file name.

Data Type

bool

Default Value

false

Remarks

This property controls the naming convention for tables and views exposed by the driver. If set to true, table and view names include the full file path, providing greater context for each dataset. For example, a table representing a sheet might be named SalesSheets_Agents_MonthlyReports_Sheet_JuneSheet. If set to false, only the file name is used in the table and view names. For example, Sheet_JuneSheet.

This property is useful when working with datasets that may contain files with similar names in different directories. Including the full file path helps to distinguish between files and provides additional clarity. However, for simpler datasets or use cases where shorter table names are preferred, setting this property to false can improve readability and simplify queries.

Specifies whether sheet and report IDs, rather than their names, are used as table identifiers.

Data Type

bool

Default Value

false

Remarks

When this property is set to true, the driver uses sheet or report IDs as table identifiers instead of their names. For example, instead of referencing a table as Sheet_SheetName, you would use Sheet_SheetId, such as in the query:

SELECT * FROM Sheet_3759298161102724.

This property is useful in scenarios where sheet and report names are ambiguous, frequently changed, or not unique. Using IDs ensures consistent and reliable table references, even if file names are updated.

Note: The properties UseFullFilePathsAsTableNames and UseIdAsTableName cannot both be enabled at the same time. If both are set to true, an error occurs.

Boolean determining if simple names should be used for tables and columns.

Data Type

bool

Default Value

false

Remarks

Smartsheet tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the Cloud easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.

Specifies whether the driver retrieves cell values from the Value fields, the DisplayValue field, or automatically selects based on availability.

Possible Values

Data Type

string

Default Value

"Auto"

Remarks

This property specifies how cell values are retrieved from the data, based on the selected source field:

• When set to Auto, the driver automatically selects the field that is not empty.
• When set to Value, the driver retrieves data specifically from the Value field.
• When set to DisplayValue, the driver retrieves data from the DisplayValue field.

This property is useful for customizing how cell data is interpreted, particularly when working with data that includes both raw values and formatted display representations.