Endpoint Insert Sheet Data (Append at the end)

Name

insert_sheet_data

Description

Append data to spreadsheet on desired tab. Data is appended after last non-empty row/cell location. You can supply cell offset to start scan to find non-empty cell. [API reference]

Related Tables

[Dynamic Table] , Sheets

Parameters

Output Columns

Input Columns

Examples

SSIS

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

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 insert sheet data:

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]

Insert

Required Parameters
Start Range	Fill-in the parameter...
Optional Parameters
ArrayTransformType	TransformColumnslessArray
ArrayTransEnableCustomColumns	True
ArrayTransCustomColumns	Col1,Col2,Col3

ODBC application

Use these SQL queries in your ODBC application data source:

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:

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];