Reference

Endpoint Get Sheet Row by ID(s)

Name

get_sheet_rows_by_ids

Description

Get a Sheet by ID(s). [API reference]

Related Tables

[Dynamic Table]

Parameters

Parameter

Required

Options

Name: SheetId

Label: SheetId

YES

Name: Id

Label: Row Id(s) - Comma separated (Blank=All Rows)

Restrict results to records specified by a comma-separated list of IDs. Max IDs per request controlled by BatchSize property (found in Pagination template)

Name: RowNumber

Label: Row Number(s) - Comma separated list (Blank=All Rows)

A comma-separated list of row numbers on which to filter the rows included in the result. Non-existent row numbers are ignored.

Name: RowsModifiedSince

Label: Rows Modified Since

Option	Value
2020-01-30T13:25:32	2020-01-30T13:25:32
2020-01-30	2020-01-30
now	now
today	today
yesterday	yesterday
monthstart	monthstart
monthend	monthend
yearstart	yearstart
yearend	yearend
weekstart	weekstart
weekend	weekend
yearstart-1y	yearstart-1y
yearend-1y	yearend-1y
monthstart-1d	monthstart-1d
monthend+1d	monthend+1d
today-24h	today-24h
now-24h	now-24h
now-1d	now-1d
today+7d	today+7d

Name: Exclude

Label: Exclude

A comma-separated list of optional elements to not include in the response: filteredOutRows - excludes filtered out rows from response payload if a sheet filter is applied; includes total number of filtered rows linkInFromCellDetails - excludes the following attributes from the cell.linkInFromCell object: columnId, rowId, status linksOutToCellsDetails - excludes the following attributes from the cell.linksOutToCells array elements: columnId, rowId, status nonexistentCells - excludes cells that have never contained any data

Option	Value
filteredOutRows	filteredOutRows
linkInFromCellDetails	linkInFromCellDetails
linksOutToCellsDetails	linksOutToCellsDetails
nonexistentCells	nonexistentCells

Name: Include

Label: Include

A comma-separated list of optional elements to include in the response: attachments - includes the metadata for sheet-level and row-level attachments. To include discussion attachments, both attachments and discussions must be present in the include list. columnType -includes columnType attribute in the row's cells indicating the type of the column the cell resides in. crossSheetReferences - includes the cross-sheet references discussions - includes sheet-level and row-level discussions. To include discussion attachments, both attachments and discussions must be present in the include list. filters - includes filteredOut attribute indicating if the row should be displayed or hidden according to the sheet's filters. filterDefinitions - includes type of filter, operators used, and criteria format - includes column, row, cell, and summary fields formatting. ganttConfig - includes Gantt chart details. objectValue - when used in combination with a level query parameter, includes the email addresses for multi-contact data. ownerInfo - includes the owner's email address and the owner's user Id. rowPermalink - includes permalink attribute that represents a direct link to the row in the Smartsheet application. source - adds the Source object indicating which report, sheet Sight (aka dashboard), or template the sheet was created from, if any. writerInfo - includes createdBy and modifiedBy attributes on the row or summary fields, indicating the row or summary field's creator, and last modifier.

Option	Value
attachments	attachments
columnType	columnType
crossSheetReferences	crossSheetReferences
discussions	discussions
filters	filters
filterDefinitions	filterDefinitions
format	format
ganttConfig	ganttConfig
objectValue	objectValue
ownerInfo	ownerInfo
rowPermalink	rowPermalink
source	source
writerInfo	writerInfo

Name: ColumnIds

Label: Column Ids (Default=All Columns)

A comma-separated list of column ids. The response contains only the specified columns in the columns array, and individual rows' cells array only contains cells in the specified columns.

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
Id	`DT_I8`	`bigint`
CreatedAt	`DT_DBTIMESTAMP`	`datetime`
ModifiedAt	`DT_DBTIMESTAMP`	`datetime`
CreatedAtLocalTime	`DT_DBTIMESTAMP`	`datetime`
ModifiedAtLocalTime	`DT_DBTIMESTAMP`	`datetime`
RowNumber	`DT_I4`	`int`		Row number
[$parent.title$]	`DT_WSTR`	`nvarchar(2000)`	2000
PrevRowId	`DT_I8`	`bigint`

If the column you are looking for is missing, consider customizing Smartsheet Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
Id	`DT_I8`	`bigint`

Required columns that you need to supply are bolded.

Examples

SSIS

Use Smartsheet Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Smartsheet

Read and write Smartsheet data effortlessly. Integrate, manage, and automate sheets, rows, and reports — almost no coding required.

Smartsheet

[Dynamic Table]

Optional Parameters
Row Id(s) - Comma separated (Blank=All Rows)
Row Number(s) - Comma separated list (Blank=All Rows)

SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the [Dynamic Table] table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to get sheet row by id:

API Destination - Smartsheet

Read and write Smartsheet data effortlessly. Integrate, manage, and automate sheets, rows, and reports — almost no coding required.

Smartsheet

[Dynamic Table]

Lookup

Optional Parameters
Row Id(s) - Comma separated (Blank=All Rows)
Row Number(s) - Comma separated list (Blank=All Rows)

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Read sheet rows by ID

Gets specific rows from a sheet by their IDs. Supply a comma-separated list of IDs in the <code>Id</code> parameter within the <code>WITH</code> clause.

SELECT * FROM get_sheet_rows_by_ids 
WITH (
  SheetId=3482072521854852
, Id='1312208360476548,6941707894689668,5815807987847044'
--OR-- (If you supply both Id and RowNumber then only Common Rows selected
--, RowNumber='1,2,100'
)

Read sheet rows by row number

Gets specific rows from a sheet by their row numbers. Supply a comma-separated list of row numbers in the <code>RowNumber</code> parameter within the <code>WITH</code> clause.

SELECT * FROM get_sheet_rows_by_ids 
WITH (
  SheetId=3482072521854852
, RowNumber='1,2,100'
--OR-- (If you supply both Id and RowNumber then only Common Rows selected
--, Id='1312208360476548,6941707894689668,5815807987847044'
)

get_sheet_rows_by_ids endpoint belongs to [Dynamic Table] table(s), and can therefore be used via those table(s).

SQL Server

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

Read sheet rows by ID

Gets specific rows from a sheet by their IDs. Supply a comma-separated list of IDs in the <code>Id</code> parameter within the <code>WITH</code> clause.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_sheet_rows_by_ids 
WITH (
  SheetId=3482072521854852
, Id=''1312208360476548,6941707894689668,5815807987847044''
--OR-- (If you supply both Id and RowNumber then only Common Rows selected
--, RowNumber=''1,2,100''
)';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

Read sheet rows by row number

Gets specific rows from a sheet by their row numbers. Supply a comma-separated list of row numbers in the <code>RowNumber</code> parameter within the <code>WITH</code> clause.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_sheet_rows_by_ids 
WITH (
  SheetId=3482072521854852
, RowNumber=''1,2,100''
--OR-- (If you supply both Id and RowNumber then only Common Rows selected
--, Id=''1312208360476548,6941707894689668,5815807987847044''
)';

EXEC (@MyQuery) AT [LS_TO_SMARTSHEET_IN_GATEWAY];

get_sheet_rows_by_ids endpoint belongs to [Dynamic Table] table(s), and can therefore be used via those table(s).