SELECT * FROM Sheets --Where Id='5815807987847055'
SELECT * FROM Contacts --Where Id='abcd1234abc'
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'
Get Sheet Rows by Id(s) (single or multiple) [Read more...]
This example shows how to list multiple Sheet Rows by its Id(s). Supply Comma Separated List of Row IDs (Upto 100 recommended). You can also supply Row Number(s) instead of ID(s). If you supply both Id(s) and Row Number(s) then only common rows are selected.
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'
)
Get sheet rows by row number(s) (single or multiple) [Read more...]
This example shows how to list multiple Sheet Rows by its Row Number(s). Supply Comma Separated List of Row Numbers (Upto 100 recommended). You can also supply Row Id instead of Row Number(s). If you supply both Id(s) and Row Number(s) then only common rows are selected.
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'
)
Create a new sheet row [Read more...]
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) [Read more...]
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) [Read more...]
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 [Read more...]
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 [Read more...]
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)
Export Sheet (as PDF, Excel, CSV) [Read more...]
This example shows how to export Sheet to PDF, CSV or Excel file). If you export to PDF then you can also supply Paper size.
SELECT * FROM export_sheet WITH(
SheetId=1592926391979908,
,TargetFilePath='c:\temp\exported_sheet_as_excel_file.xlsx' --or .pdf, .csv
,Format='application/vnd.ms-excel' --or-- application/pdf --or-- text/csv
--below Paper Size only valid for PDF
--,PaperSize='LETTER' --or LEGAL, WIDE, ARCHD, A4, A3, A2, A1, A0
)
Export Report (as PDF, Excel, CSV) [Read more...]
This example shows how to export Report to PDF, CSV or Excel file). If you export to PDF then you can also supply Paper size.
SELECT * FROM export_report WITH(
ReportId=2972339541069700,
,TargetFilePath='c:\temp\exported_report_as_excel_file.xlsx' --or .pdf, .csv
,Format='application/vnd.ms-excel' --or-- application/pdf --or-- text/csv
--below Paper Size only valid for PDF
--,PaperSize='LETTER' --or LEGAL, WIDE, ARCHD, A4, A3, A2, A1, A0
)
Send Report via Email to Users or Groups (as PDF, Excel) [Read more...]
This example shows how to send Report via Email (PDF or Excel format) to list of emails or groups.
SELECT * FROM send_report_email
WITH(
ReportId='2972339541069700'
, Subject='Test report'
, Message='This email contains report attachment'
, CcMe='true'
, PaperSize='LETTER'
, SendToEmails='bob@mycompany.com~sam@mycompany.com'
--OR (Emails or Group only - one allowed)
--, SendToGroups='10022222001~10333330002'
)
Send Sheet via Email to Users or Groups (as PDF, Excel) [Read more...]
This example shows how to send Sheet via Email (PDF or Excel format) to list of emails or groups.
SELECT * FROM send_sheet_email
WITH(
SheetId='1592926391979908'
, Subject='Test report'
, Message='This email contains report attachment'
, CcMe='true'
, PaperSize='LETTER'
, SendToEmails='bob@mycompany.com~sam@mycompany.com'
--OR (Emails or Group only - one allowed)
--, SendToGroups='10022222001~10333330002'
)