| Example |
Query |
| Query from default Spreadsheet |
SELECT * FROM [Sheet1]
|
| Query from User defined Spreadsheet |
SELECT * FROM [Class Data] WITH(SpreadSheetId='1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms')
|
| Query from custom cell range |
SELECT * FROM [Class Data]
WITH(
Range='A4:GR' --cell range you like to query
, ArrayTransEnableCustomColumns='False' --do not treat first row in range as Column Names
, SpreadSheetId='1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' --enter sheet id you like to query. Comment this if you like to use default ID defined in the connection
)
|
| Query from custom cell range |
UPDATE [Sheet1]
SET Col1='data-1', Col2=100, Col3='2020-01-31' --column names are ignored. Values are sent in the same order you supply and writtern to start cell specified by WriteRange
WITH(
, WriteRange='G9' --start writing from here
, SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
|
| Update Multiple Rows in Sheet from CSV file |
UPDATE [Sheet1]
SOURCE(
'ODBC', --driver type ODBC | MSSQL | OLEDB
'Driver={ZappySys CSV Driver};', --connection string for driver
'SELECT * FROM $ WITH (SRC=''c:\data.csv'') ' --sql query for source data
)
WITH(
WriteRange='G9', SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
|
| Update Values Vertically (Column Mode) |
UPDATE [Sheet1]
SET Col1='Jan',Col2='Feb',Col3='Mar'
WITH(
WriteRange='G9',
SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU', --comment this to use default Sheet Id from connection setting
MajorDimension='COLUMNS' --write values vertical rather horizontal
)
|
| Insert Data in Sheet1 |
INSERT INTO "Sheet1"("MyStringCol", "MyIntegerCol", "MyDateCol", "MyDecimalCol") VALUES('AAA',100,'2020-01-01',150.33)
|
| Insert Multiple Rows in Sheet1 from CSV file |
INSERT INTO [Sheet1]
SOURCE(
'ODBC', --driver type ODBC | MSSQL | OLEDB
'Driver={ZappySys CSV Driver};', --connection string for driver
'SELECT * FROM $ WITH (SRC=''c:\temp\dump.txt'') ' --sql query for source data
)
WITH(
Range='G9', SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
|
| Write Values Vertically (Column Mode) |
INSERT INTO [Sheet1](Col1,Col2,Col3)
VALUES('Jan','Feb','Mar')
WITH(
Range='G9', --starting cell to write data
SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU', --comment this to use default Sheet Id from connection setting
MajorDimension='COLUMNS' --write values vertical rather horizontal
)
|
| Execute Action (i.e. Delete Rows / Columns) |
SELECT * FROM batch_update_request
WITH(
Body='{
"requests": [
{
"deleteDimension": {
"range": {
"sheetId": 0,
"dimension": "ROWS",
"startIndex": 10,
"endIndex": 20
}
}
} ,
{
"deleteDimension": {
"range": {
"sheetId": 0,
"dimension": "ROWS",
"startIndex": 50,
"endIndex": 60
}
}
}
]
}'
, TabId='0' -- tab internal id (use UI to get this). 0 means first tab. Or check URL in browser and see at the end of URL #gid=xxxxxxx where xxxxxx is your tab id
, SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU' --comment this to use default Sheet Id from connection setting
)
|
