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 and write Smartsheet data effortlessly. Integrate, manage, and automate sheets, rows, and reports — almost no coding required.
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
Read and write Smartsheet data effortlessly. Integrate, manage, and automate sheets, rows, and reports — almost no coding required.
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:

Read sheet rows

<p>Gets rows from a specific sheet. Replace <code>[My Sheet Name]</code> with the actual name of your sheet. If the sheet name contains spaces, enclose it in brackets.</p> 

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

Create a sheet row

<p>Creates a new row in a sheet. You can specify values for various column types such as Text/Number, Checkbox, Contact List, Date, Picklist, and Symbol.</p> 

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 create sheet rows using SQL Server data

<p>Bulk creates rows in a sheet by reading data from an external SQL Server database using the <code>SOURCE</code> clause. Column names must match the target sheet's column names.</p> 

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 sheet rows using SQL Server data

<p>Bulk updates rows in a sheet by reading data from an external SQL Server database using the <code>SOURCE</code> clause. The source query must include the <code>Id</code> of the rows to update.</p> 

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 sheet row

<p>Updates an existing row in a sheet by its <code>Id</code>. You can update specific columns.</p> 

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 rows

<p>Deletes one or more rows from a sheet by ID. You can supply a single ID or a comma-separated list of IDs.</p> 

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:

Read sheet rows

<p>Gets rows from a specific sheet. Replace <code>[My Sheet Name]</code> with the actual name of your sheet. If the sheet name contains spaces, enclose it in brackets.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT Id,RowNumber,[Some Column 1],[Some Column 2] FROM [My Sheet Name] --Where Id=''5815807987847044''';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

Create a sheet row

<p>Creates a new row in a sheet. You can specify values for various column types such as Text/Number, Checkbox, Contact List, Date, Picklist, and Symbol.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '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
)			
*/';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

Bulk create sheet rows using SQL Server data

<p>Bulk creates rows in a sheet by reading data from an external SQL Server database using the <code>SOURCE</code> clause. Column names must match the target sheet's column names.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '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)';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

Bulk update sheet rows using SQL Server data

<p>Bulk updates rows in a sheet by reading data from an external SQL Server database using the <code>SOURCE</code> clause. The source query must include the <code>Id</code> of the rows to update.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '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)';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

Update a sheet row

<p>Updates an existing row in a sheet by its <code>Id</code>. You can update specific columns.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '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)';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

Delete sheet rows

<p>Deletes one or more rows from a sheet by ID. You can supply a single ID or a comma-separated list of IDs.</p> 

DECLARE @MyQuery NVARCHAR(MAX) = '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)';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];