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 and write Google Sheets data effortlessly. Query, update, and manage spreadsheets, ranges, and cells — almost no coding required.

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 a row into a sheet

<p>Inserts a single row into the <code>My sheet</code> tab in the default spreadsheet. <code>Column1</code>–<code>Column4</code> represent your actual sheet columns.</p>

INSERT INTO "My sheet"(Column1, Column2, Column3, Column4)
VALUES('AAA', 100, '2020-01-01', 150.33)

Insert multiple rows into a sheet from a CSV file

<p>Bulk inserts multiple rows into a sheet using CSV data as the source. Reads rows from a CSV file using an ODBC connection and writes them into the specified range in the sheet.</p>

INSERT INTO [My sheet]
SOURCE(
  'ODBC',
  'Driver={ZappySys CSV Driver};',
  'SELECT * FROM $ WITH (SRC=''c:\data\orders.csv'')'
)
WITH(
  Range='G9',
  SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU'
)

Write values vertically (column mode)

<p>Writes inserted values vertically starting at a specific cell. Uses <code>MajorDimension='COLUMNS'</code> so each value in the row becomes a column of values.</p>

INSERT INTO [My sheet](Column1, Column2, Column3)
VALUES('Jan','Feb','Mar')
WITH(
  Range='G9',
  SpreadSheetId='1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU',
  MajorDimension='COLUMNS'
)

SQL Server

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

Insert a row into a sheet

<p>Inserts a single row into the <code>My sheet</code> tab in the default spreadsheet. <code>Column1</code>–<code>Column4</code> represent your actual sheet columns.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO "My sheet"(Column1, Column2, Column3, Column4)
VALUES(''AAA'', 100, ''2020-01-01'', 150.33)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Insert multiple rows into a sheet from a CSV file

<p>Bulk inserts multiple rows into a sheet using CSV data as the source. Reads rows from a CSV file using an ODBC connection and writes them into the specified range in the sheet.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO [My sheet]
SOURCE(
  ''ODBC'',
  ''Driver={ZappySys CSV Driver};'',
  ''SELECT * FROM $ WITH (SRC=''''c:\data\orders.csv'''')''
)
WITH(
  Range=''G9'',
  SpreadSheetId=''1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU''
)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];

Write values vertically (column mode)

<p>Writes inserted values vertically starting at a specific cell. Uses <code>MajorDimension='COLUMNS'</code> so each value in the row becomes a column of values.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO [My sheet](Column1, Column2, Column3)
VALUES(''Jan'',''Feb'',''Mar'')
WITH(
  Range=''G9'',
  SpreadSheetId=''1az2H8ZYk7BvjddVTqPR-LfDjX9IRpIpjCDpFPe9EzkU'',
  MajorDimension=''COLUMNS''
)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];