Reference

Endpoint Execute Command(s)

Name

batch_update_request

Description

Perform various operations available in online editor such as copy, paste, format, merge. Add multiple JSON command by adding comma after first command (e.g. requests : [ {..cmd1..} , {..cmd2..}, {..cmd3..} ] ) Refer to example requests on help link. [API reference]

Parameters

Parameter Required Options
Name: Body

Label: Request Body

 YES
Name: TabId

Label: TabId

Numeric ID of SpreadSheet Tab for which you like to delete rows/columns. For first tab its 0. You can find this ID from Sheet URL in browser. Get Number after edit#gid= (thats your Tab ID) 		YES
Name: SpreadSheetId

Label: SpreadSheetId

Enter unique spreadsheet Id. To obtain sheet Id. Open spreadsheet in browser and check URL. You will example value like 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms that is your sheet id.
Option Value
Enter Your Sheet Id
Example Sheet Id 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
spreadsheetId DT_WSTR nvarchar(100) 100
If the column you are looking for is missing, consider customizing Google Sheets Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

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

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
Execute Command(s)
Required Parameters
Request Body Fill-in the parameter...
TabId Fill-in the parameter...
SSIS API Source - Read from table or endpoint

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
Execute Command(s)
Required Parameters
Request Body Fill-in the parameter...
TabId Fill-in the parameter...
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Execute Action (i.e. Delete Rows / Columns)

This example shows how to execute various commands for sheet (i.e. copy, paste, formatting, delete, merge etc). In this example we are executing delete comamnds (i.e. deleteDimension commands). Notice we called same command twice becuase we want to delete two ranges (index 10-20 and 50-60). You can execute any valid command available by Google Sheets API. Here are some good examples of formatting commands https://developers.google.com/sheets/api/samples/formatting 

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
)

SQL Server

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

Execute Action (i.e. Delete Rows / Columns)

This example shows how to execute various commands for sheet (i.e. copy, paste, formatting, delete, merge etc). In this example we are executing delete comamnds (i.e. deleteDimension commands). Notice we called same command twice becuase we want to delete two ranges (index 10-20 and 50-60). You can execute any valid command available by Google Sheets API. Here are some good examples of formatting commands https://developers.google.com/sheets/api/samples/formatting 

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

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SHEETS_IN_GATEWAY];