Google Sheets | Documentation

The Google Sheets component is a data component that allows users to connect to and interact with Google Sheets spreadsheets to read and write data. It can carry out the following tasks:

Create Spreadsheet
Delete Spreadsheet
Add Sheet
Delete Sheet
Create Spreadsheet Column
Delete Spreadsheet Column
List Rows
Lookup Rows
Get Row
Get Multiple Rows
Insert Row
Insert Multiple Rows
Update Row
Update Multiple Rows
Delete Row
Delete Multiple Rows

#Release Stage

Alpha

#Configuration

The component definition and tasks are defined in the definition.yaml and tasks.yaml files respectively.

#Setup

In order to communicate with Google, the following connection details need to be provided. You may specify them directly in a pipeline recipe as key-value pairs within the component's setup block, or you can create a Connection from the Integration Settings page and reference the whole setup as setup: ${connection.<my-connection-id>}.

Field	Field ID	Type	Note
Refresh Token	`refresh-token`	string	Refresh token for the Google Sheets API. For more information about how to create tokens, please refer to the Google Sheets API documentation and OAuth 2.0 documentation.

#Supported Tasks

#Create Spreadsheet

Create a new Google Sheets spreadsheet with multiple sheets.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_CREATE_SPREADSHEET`
Title	`title`	string	Title of the new spreadsheet.
Sheets (required)	`sheets`	array[object]	Configuration for sheets to create.

Input Objects in Create Spreadsheet

Sheets

Configuration for sheets to create.

Field	Field ID	Type	Note
Headers	`headers`	array	Column headers for the sheet.
Sheet Name	`name`	string	Name of the sheet.

Output	Field ID	Type	Description
Spreadsheet ID	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.

#Delete Spreadsheet

Delete a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DELETE_SPREADSHEET`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.

#Add Sheet

Add a new sheet to an existing Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_ADD_SHEET`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Headers	`headers`	array[string]	Column headers for the sheet.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.

#Delete Sheet

Remove a sheet from a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DELETE_SHEET`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.

#Create Spreadsheet Column

Add a new column to a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_CREATE_SPREADSHEET_COLUMN`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Column Name (required)	`column-name`	string	Name of the column.

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.

#Delete Spreadsheet Column

Delete a column from a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DELETE_SPREADSHEET_COLUMN`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Column Name (required)	`column-name`	string	Name of the column.

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.

#List Rows

List all rows in a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_LIST_ROWS`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Start Row	`start-row`	number	The starting row number to retrieve (1-based index).
End Row	`end-row`	number	The ending row number to retrieve (1-based index).
Sheet Name (required)	`sheet-name`	string	Name of the sheet.

Output	Field ID	Type	Description
Rows Data	`rows`	array[object]	Multiple rows data with row numbers and data.

Output Objects in List Rows

Rows Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Lookup Rows

Find multiple rows based on column value in a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_LOOKUP_ROWS`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Column Name (required)	`column-name`	string	Name of the column.
Search Value (required)	`value`	string	Value to search for in the specified column.

Output	Field ID	Type	Description
Rows Data	`rows`	array[object]	Multiple rows data with row numbers and data.

Output Objects in Lookup Rows

Rows Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Get Row

Get a single row from a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_GET_ROW`
Row Number (required)	`row-number`	number	The row number to retrieve (1-based index).
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.

Output	Field ID	Type	Description
Row Data	`row`	object	Row data with row number and data.

Output Objects in Get Row

Row Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Get Multiple Rows

Get multiple rows from a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_GET_MULTIPLE_ROWS`
Row Numbers (required)	`row-numbers`	array[integer]	The row numbers to retrieve (1-based indices).
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.

Output	Field ID	Type	Description
Rows Data	`rows`	array[object]	Multiple rows data with row numbers and data.

Output Objects in Get Multiple Rows

Rows Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Insert Row

Insert a single row into a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_INSERT_ROW`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

Output	Field ID	Type	Description
Row Data	`row`	object	Row data with row number and data.

Output Objects in Insert Row

Row Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Insert Multiple Rows

Insert multiple rows into a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_INSERT_MULTIPLE_ROWS`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Row Values	`row-values`	array[json]	Array of row data in JSON format where keys are column names and values are the corresponding cell values
Sheet Name (required)	`sheet-name`	string	Name of the sheet.

Output	Field ID	Type	Description
Rows Data	`rows`	array[object]	Multiple rows data with row numbers and data.

Output Objects in Insert Multiple Rows

Rows Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Update Row

Update a row in a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_UPDATE_ROW`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Row Data (required)	`row`	object	Row data with row number and data.

Input Objects in Update Row

Row Data

Row data with row number and data.

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

Output	Field ID	Type	Description
Row Data	`row`	object	Row data with row number and data.

Output Objects in Update Row

Row Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Update Multiple Rows

Update multiple rows in a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_UPDATE_MULTIPLE_ROWS`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Rows Data (required)	`rows`	array[object]	Multiple rows data with row numbers and data.

Input Objects in Update Multiple Rows

Rows Data

Multiple rows data with row numbers and data.

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

Output	Field ID	Type	Description
Rows Data	`rows`	array[object]	Multiple rows data with row numbers and data.

Output Objects in Update Multiple Rows

Rows Data

Field	Field ID	Type	Note
Row Number	`row-number`	number	Row number to update (1-based index)
Row Data	`row-value`	json	Row data in JSON format where keys are column names and values are the corresponding cell values

#Delete Row

Delete a row from a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DELETE_ROW`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Row Number (required)	`row-number`	number	Row number to update (1-based index).

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.

#Delete Multiple Rows

Delete multiple rows from a Google Sheets spreadsheet.

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DELETE_MULTIPLE_ROWS`
Spreadsheet ID (required)	`shared-link`	string	Shared link of the spreadsheet. You can get the shared link by clicking 'Share' button and selecting 'Copy link'.
Sheet Name (required)	`sheet-name`	string	Name of the sheet.
Row Numbers (required)	`row-numbers`	array[integer]	Row numbers to update (1-based indices).

Output	Field ID	Type	Description
Success	`success`	boolean	Result of the operation.