Reference

Table [Dynamic Table]

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.

Method	Supported	Reference EndPoint
SELECT		get_sheet_rows
INSERT		add_sheet_rows
UPDATE		update_sheet_rows
UPSERT
DELETE		delete_sheet_rows
LOOKUP		get_sheet_rows_by_ids

Examples

SSIS

Use Smartsheet Connector in API Source component to read data or in API Destination component to read/write data:

Read from [Dynamic Table] table using API Source

API Source - Smartsheet

Read / write SmartSheet data inside your app, perform many SmartSheet operations such as Read, Insert, Update, Delete, List, Search, Export on objects like Sheets, Rows, Users etc without coding with easy to use high performance API Connector

Smartsheet

[Dynamic Table]

There are no parameters to configure.

SSIS API Source - Read from table or endpoint

Read/write to [Dynamic Table] table using API Destination

API Destination - Smartsheet

Smartsheet

[Dynamic Table]

Select

There are no parameters to configure.

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Get sheet rows

This example shows how to query any Smart Sheet by its name. If you have space in the sheet name or column name then use bracket ( [my name] ) or double quotes ("my name") around it.

SELECT Id,RowNumber,[Some Column 1],[Some Column 2] FROM [My Sheet Name] --Where Id='5815807987847044'

Create a new sheet row

This example shows how to create a new Sheet Row by calling INSERT statement. Optionally specify location where you like to insert new row (default is at bottom).

INSERT INTO [My Sheet]
  ( [Some Text Number Column], [Some Checkbox Column], [Some Contact List Column], [Some Date Column], [Some PickList Column], [Some Multi PickList Column], [Some Symbol Column] )
VALUES 
  ('Updated at <<FUN_NOW>>', true, 'something@abc.com', '2024-12-31','Not Started','Value2,Value4','Green,Yellow')
/*
WITH(
RowLocation='"toTop": true'  --insert at the top
--OR--
--RowLocation='"siblingId": 1231234567'  --insert after some sibling row id
)			
*/

Bulk INSERT to SmartSheet from External System (e.g. ODBC Source / Microsoft SQL Server / OLEDB)

This example shows how to bulk insert into SmartSheet from any system such as Microsoft SQL Server or ODBC Source or OLEDB Source. Column names must match with Target Sheet column names in SQL Column alias.

INSERT INTO [My Sheet]
SOURCE(   'MSSQL'--OR 'ODBC'--OR 'OLEDB'
        , 'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
		--OR for ODBC
		--,'DSN=MyOdbcDsn'
		--OR (DSN less connection)
		--,'Driver={Some ODBC Driver};uid=xxx;pwd=xxx;prop1=xxx;prop2=xxx....'
        , 'SELECT ''2012-12-31'' [Some Date Column],''true'' [Some CheckBox Column],''Good example'' [Some Text Column]
           UNION 
		   SELECT ''2012-13-31'' [Some Date Column],''false'' [Some CheckBox Column],''Bad date example'' [Some Text Column]
           UNION 
		   SELECT ''2012-10-01'' [Some Date Column],''true'' [Some CheckBox Column],''Good example'' [Some Text Column]
        ')
--WITH(AllowPartialSuccess='true',output=1)

Bulk UPDATE to SmartSheet from External System (e.g. ODBC Source / Microsoft SQL Server / OLEDB)

This example shows how to bulk update SmartSheet data from any system such as Microsoft SQL Server or ODBC Source or OLEDB Source. Column names must match with Target Sheet column names in SQL Column alias.

UPDATE [My Sheet] 
SOURCE(   'MSSQL'--OR 'ODBC'--OR 'OLEDB'
        , 'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
		--OR for ODBC
		--,'DSN=MyOdbcDsn'
		--OR (DSN less connection)
		--,'Driver={Some ODBC Driver};uid=xxx;pwd=xxx;prop1=xxx;prop2=xxx....'
        , 'SELECT 4081850437570436 as Id,''2012-12-31'' [Some Date Column],''true'' [Some CheckBox Column],''Good example'' [Some Text Column]
           UNION
		   SELECT 8585450064940932 as Id,''2012-10-01'' [Some Date Column],''true'' [Some CheckBox Column],''Good example'' [Some Text Column]')
--WITH(AllowPartialSuccess='true',output=1)

Update a new sheet row

This example shows how to update specific columns of a Sheet Row by calling UPDATE statement.

UPDATE [My Sheet]
SET [Some Text Number Column]='Updated - <<FUN_NOW>>',
    [Some Checkbox Column] = true,
    [Some Contact List Column] = 'something@abc.com',
    [Some Date Column] = '<<FUN_NOW>>',
    [Some PickList Column] = 'Not Started',
    [Some Multi PickList Column] = 'Value2,Value4',
    [Some Symbol Column] = 'Green,Yellow'
WHERE Id=7522710866202500		
WITH(Output=1)

Delete sheet row(s) - single or multiple

This example shows how to delete single or multiple sheet rows by calling DELETE statement. You can supply a single Row Id or a comma-separated list of row Ids (Upto 100 recommended).

DELETE FROM [My Sheet] WHERE Id=7522710866202500 
--WITH(IgnoreRowsNotFound='true' --or false)		
--Or
--DELETE FROM [My Sheet] WHERE Id='7522710866202500,7522710866202501,7522710866202502'
--WITH(IgnoreRowsNotFound='true' --or false)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway: