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 read_sheet_data
INSERT insert_sheet_data
UPDATE update_sheet_data
UPSERT
DELETE
LOOKUP read_sheet_data

Examples

SSIS

Use Google Sheets 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 - Google Sheets
Read / write Google Sheets data inside your app, perform many Google Sheets operations without coding using easy to use high performance API Connector for Google Sheets
Google Sheets
[Dynamic Table]
Required Parameters
Range Fill-in the parameter...
Optional Parameters
IncludeParentColumns False
ArrayTransformType TransformColumnslessArray
First Row Has Column Names True
ArrayTransCustomColumns Col1,Col2,Col3
SSIS API Source - Read from table or endpoint

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

API Destination - Google Sheets
Read / write Google Sheets data inside your app, perform many Google Sheets operations without coding using easy to use high performance API Connector for Google Sheets
Google Sheets
[Dynamic Table]
Select
Required Parameters
Range Fill-in the parameter...
Optional Parameters
IncludeParentColumns False
ArrayTransformType TransformColumnslessArray
First Row Has Column Names True
ArrayTransCustomColumns Col1,Col2,Col3
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Query from default Spreadsheet

Gets data from Tab name Sheet1 from SpreadSheet Id defined in the connection 

SELECT * FROM [Sheet1]

Query from User defined Spreadsheet

Gets data from Tab name 'Class Data' from user defined SpreadSheet Id 

SELECT * FROM [Class Data] WITH(SpreadSheetId='1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms')

Query from custom cell range

In this example we query Tab name 'Class Data' and we are reading Range 'A4:GR'. 

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

In this example we query Tab name 'Class Data' and we are reading Range 'A4:GR'. 

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

In this example we query CSV file as Source (Using ZapyySys CSV ODBC Driver) and updating Google Sheet in BULK. 

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)

In this example we will write value as columns rather than row. 

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 row to tab name Sheet1 in SheetId defined in connection 

INSERT INTO "Sheet1"("MyStringCol", "MyIntegerCol", "MyDateCol", "MyDecimalCol") VALUES('AAA',100,'2020-01-01',150.33)

Insert Multiple Rows in Sheet1 from CSV file

In this example we query CSV file as Source (Using ZapyySys CSV ODBC Driver) and updating Google Sheet in BULK. 

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)

In this example we will write value as columns rather than row. 

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
)

SQL Server

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

Query from default Spreadsheet

Gets data from Tab name Sheet1 from SpreadSheet Id defined in the connection 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM [Sheet1]';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Query from User defined Spreadsheet

Gets data from Tab name 'Class Data' from user defined SpreadSheet Id 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM [Class Data] WITH(SpreadSheetId=''1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'')';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Query from custom cell range

In this example we query Tab name 'Class Data' and we are reading Range 'A4:GR'. 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Query from custom cell range

In this example we query Tab name 'Class Data' and we are reading Range 'A4:GR'. 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Update Multiple Rows in Sheet from CSV file

In this example we query CSV file as Source (Using ZapyySys CSV ODBC Driver) and updating Google Sheet in BULK. 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Update Values Vertically (Column Mode)

In this example we will write value as columns rather than row. 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Insert Data in Sheet1

Insert row to tab name Sheet1 in SheetId defined in connection 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO "Sheet1"("MyStringCol", "MyIntegerCol", "MyDateCol", "MyDecimalCol") VALUES(''AAA'',100,''2020-01-01'',150.33)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Insert Multiple Rows in Sheet1 from CSV file

In this example we query CSV file as Source (Using ZapyySys CSV ODBC Driver) and updating Google Sheet in BULK. 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Write Values Vertically (Column Mode)

In this example we will write value as columns rather than row. 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];