Google Sheets

Procedures and concepts for the Google Sheets integration.

The following documentation is for Google Sheets v2.11.0 (BDK).

Overview

Google Sheets is a powerful cloud-based spreadsheet application with real-time collaboration capabilities. This integration enables automated data entry, spreadsheet management, and collaborative workflow automation. Streamline data analysis and enhance team productivity through automated spreadsheet operations.

Prerequisites

1. Required Books

The following Book(s) need to be added to your agent so it can learn and understand the automation procedures defined within them:

• Google Sheets

How to Add the Book(s)

1. Go to Books → All Books.

2. Search for the name of the book and click on it.

3. Click on Install or Add Connection to add the book to your agent.

4. If adding a connection, you'll be prompted for connectivity details.

Connectivity

This section outlines the available methods for connecting to the Book, along with the required configuration details for each.

Connect using Client Email, Token URI and Private Key

Gets the credentials from the service account keys.

Label	Description	Type
Client Email	The client email of the service account registered in the Google Cloud Console.	text
Token URI	The token URI of the Google Cloud Console.	text
Private Key	The private key of the service account registered in the Google Cloud Console.	sensitive

Procedures

to add some content to a table

Append content to a Google Sheets table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table ref from which to read the content.	google sheet table reference	Yes	(no default)
content	the content to be added into the table reference.	table	Yes	(no default)

Examples

Append the contents of a table into a sheet table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
read the content from the PO Table
get the PO Sheet's table whose name is "PO History Table"
use the above as the PO History Table
add the content to the PO History Table

to clear the worksheet range

Clear the contents of a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range to clear.	google sheet range reference	Yes	(no default)

Examples

Clear a range

...
create a worksheet range in the PO Sheet where
    the start is "A1"
    the end is "C10"
use the above as the PO Range
clear the PO Range

to copy the sheet with a name

Copy a sheet within a Google Sheets file, giving the copy a name.

The copy is created in the same spreadsheet as the source sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	the sheet to copy.	google sheet reference	Yes	(no default)
file	the spreadsheet the copy is created in (the source sheet's file).	google drive file reference	Yes	(no default)
name	the name for the copied sheet.	text	Yes	(no default)

Output Concepts

Concept	Description	Type
answer	A reference to the copied sheet.	google sheet reference

Examples

Copy a sheet

...
use the above as the sheet
copy the sheet with a name where
    the name is "Backup"

to create a (new sheet) in the file

Create a new sheet in a Google Sheets file.

Input Concepts

Concept	Description	Type	Required	Default Value
file	the spreadsheet to create the sheet in.	google drive file reference	Yes	(no default)
name	the name for the new sheet.	text	Yes	(no default)

Output Concepts

Concept	Description	Type
new sheet	A reference to the created sheet.	google sheet reference

Examples

Create a new sheet

...
use the above as the spreadsheet
create a new sheet in the spreadsheet where
    the name is "Q1 Report"

to create a (table) in a sheet

Create a table in a spreadsheet's sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	spreadsheet's sheet where the table will be created.	google sheet reference	Yes	(no default)
headers	List of the headers for the new table.	text	Yes	(no default)

Output Concepts

Concept	Description	Type
table	A reference to the created table.	google sheet table reference

Examples

Create a new table in a sheet of a spreadsheet

...
get the spreadsheet's sheet whose name is "PO Data"
use the above as the PO Sheet
create a table in the Po Sheet where
    the headers are "ID", "Product ID", "Price"

to create a (worksheet range) in a sheet

Create a reference to a range of cells in a sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	spreadsheet's sheet that the range belongs to.	google sheet reference	Yes	(no default)
start	A1 address of the top-left cell of the range (e.g. "A1").	text	Yes	(no default)
end	A1 address of the bottom-right cell of the range (e.g. "B5").	text	Yes	(no default)

Output Concepts

Concept	Description	Type
worksheet range	A reference to the worksheet range.	google sheet range reference

Examples

Create a range in a sheet

...
get the spreadsheet's sheet whose name is "PO Data"
use the above as the PO Sheet
create a worksheet range in the PO Sheet where
    the start is "A1"
    the end is "C10"

to create a google spreadsheet in a (folder)

Create a new Google Sheets spreadsheet.

Input Concepts

Concept	Description	Type	Required	Default Value
name	The name for the new Google Sheets spreadsheet.	text	Yes	(no default)
folder	The folder to create the Google Sheets spreadsheet in.	google drive folder reference	No	(no default)

Output Concepts

Concept	Description	Type
folder	the new spreadsheet's file reference.	google drive file reference

Examples

Create a Google Sheets spreadsheet called "Hello World" in a google drive folder

get the folder at "My Drive Folder"
use the above as the folder
create a google spreadsheet in the folder where
    the name is "Hello World"

to delete the sheet

Delete a sheet from a Google Sheets file.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	the sheet to delete.	google sheet reference	Yes	(no default)

Examples

Delete a sheet

...
use the above as the sheet
delete the sheet

to get the (cell's color)

Get the background (fill) color of a cell as a hex string.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose color will be retrieved.	google sheet cell reference	Yes	(no default)

Output Concepts

Concept	Description	Type
cell's color	The background color of the cell as a hex string (e.g. "#FFFFFF").	text

Examples

Get a cell's color

...
use the above as the cell
get the cell's color

to get the (cell's formula)

Get the formula of a cell.

If the cell does not contain a formula, the literal cell value is returned.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose formula will be retrieved.	google sheet cell reference	Yes	(no default)

Output Concepts

Concept	Description	Type
cell's formula	The formula of the cell.	text

Examples

Get a cell's formula

...
use the above as the cell
get the cell's formula

to get the (cell's number format)

Get the number format pattern of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose number format will be retrieved.	google sheet cell reference	Yes	(no default)

Output Concepts

Concept	Description	Type
cell's number format	The cell's number format pattern (e.g. "0.00"), or "General" if none is set.	text

Examples

Get a cell's number format

...
use the above as the cell
get the cell's number format

to get the (cell's value)

Get the value of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose value will be retrieved.	google sheet cell reference	Yes	(no default)

Output Concepts

Concept	Description	Type
cell's value	The value of the cell.	boolean or number or text

Examples

Get a cell's value

...
use the above as the cell
get the cell's value

to get the (column count) in a worksheet range

Get the number of columns in a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range to count the columns of.	google sheet range reference	Yes	(no default)

Output Concepts

Concept	Description	Type
column count	The number of columns in the range.	number

Examples

Get the column count of a range

...
use the above as the PO Range
get the column count in the PO Range

to get the (file's sheets)

FILTER - CAPABLE

Get the sheets from a Google Sheets file.

Input Concepts

Concept	Description	Type	Required	Default Value
file	Google Sheets spreadsheet from which to retrieve the sheets.	google drive file reference	Yes	(no default)

Output Concepts

Concept	Description	Type
file's sheets	The list of the retrieved sheet references.	google sheet reference

Examples

Retrieve the sheets of a spreadsheet inside a drive folder

get the folder at "My Drive Folder"
use the above as the folder
get the folder's items
get the first item
use the above as the spreadsheet
get the spreadsheet's sheets

Retrieve a sheet of a spreadsheet by name

get the folder at "My Drive Folder"
use the above as the folder
get the folder's items
get the first item
use the above as the spreadsheet
get the spreadsheet's sheet whose name is "PO Data"

to get the (following row range) in a worksheet range

Get the range covering the single row immediately below a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range to get the following row for.	google sheet range reference	Yes	(no default)

Output Concepts

Concept	Description	Type
following row range	A reference to the range covering the row below the given range.	google sheet range reference

Examples

Get the row that follows a range

...
create a worksheet range in the PO Sheet where
    the start is "A1"
    the end is "C10"
use the above as the PO Range
get the following row range in the PO Range

to get the (row count) in a worksheet range

Get the number of rows in a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range to count the rows of.	google sheet range reference	Yes	(no default)

Output Concepts

Concept	Description	Type
row count	The number of rows in the range.	number

Examples

Get the row count of a range

...
use the above as the PO Range
get the row count in the PO Range

to get the (row's cells) from the worksheet range

Get the cells of a row within a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range the row belongs to.	google sheet range reference	Yes	(no default)
row	the row whose cells will be retrieved.	google sheet row reference	Yes	(no default)

Output Concepts

Concept	Description	Type
row's cells	A list of the row's cell references.	google sheet cell reference

Examples

Get the cells of the first row of a range

...
use the above as the PO Range
get the PO Range's rows
get the first row
use the above as the first row
get the first row's cells from the PO Range

to get the (sheet's tables)

FILTER - CAPABLE

Get the tables from a Google Sheets sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	spreadsheet's sheet from which to retrieve the table.	google sheet reference	Yes	(no default)

Output Concepts

Concept	Description	Type
sheet's tables	The list of the retrieved table references.	google sheet table reference

Examples

Retrieve the tables in a sheet of a spreadsheet

...
get the spreadsheet's sheet whose name is "PO Data"
use the above as the PO Sheet
get the PO Sheet's tables

Retrieve the table in a sheet of a spreadsheet by name

...
get the spreadsheet's sheet whose name is "PO Data"
use the above as the PO Sheet
get the PO Sheet's table whose name is "PO Table"

to get the (sheet's used range)

Get the range of cells that contain data in a sheet.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	spreadsheet's sheet from which to retrieve the used range.	google sheet reference	Yes	(no default)

Output Concepts

Concept	Description	Type
sheet's used range	A reference to the used range of the sheet.	google sheet range reference

Examples

Get the used range of a sheet

...
get the spreadsheet's sheet whose name is "PO Data"
use the above as the PO Sheet
get the PO Sheet's used range

to get the (worksheet range's rows)

Get the rows from a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range from which to retrieve the rows.	google sheet range reference	Yes	(no default)

Output Concepts

Concept	Description	Type
worksheet range's rows	A list of the retrieved row references.	google sheet row reference

Examples

Get the rows of a range

...
create a worksheet range in the PO Sheet where
    the start is "A1"
    the end is "C10"
use the above as the PO Range
get the PO Range's rows

to insert a (new column) in the table

Create a new column in a Google Sheets table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table where to insert the new column.	google sheet table reference	Yes	(no default)
index	index in which to insert the column.	number	Yes	(no default)
name	name to the new column	text	Yes	(no default)
values	list of values to add to the new column	boolean or number or text	Yes	(no default)

Output Concepts

Concept	Description	Type
new column	A reference of the newly created column	google sheet column reference

Examples

Insert a new column into a table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
insert a new column in the PO Table
    the index is 3
    the name is "Date"
    the values are "06/13/25", "06/14/25"

to insert a (new row) in the table

Create a new row in a Google Sheets table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table where to insert the new row.	google sheet table reference	Yes	(no default)
index	index in which to insert the row, starting at 0 and relative to the table's header.	number	Yes	(no default)
values	list of values to add with the row	boolean or number or text	Yes	(no default)

Output Concepts

Concept	Description	Type
new row	A list of the retrieved row references.	google sheet row reference

Examples

Insert a new row into a table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
insert a new row in the PO Table
    the index is 3
    the values are 5, "Test Product 5", "1KG", "06/10/25"

to read the (content) from a table

Get the contents of a Google Sheets table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table reference from which to read the content.	google sheet table reference	Yes	(no default)

Output Concepts

Concept	Description	Type
content	A table with the read contents.	table

Examples

Read the contents of a sheet table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
read the content from the PO Table

to read the (content) from a worksheet range

Get the contents of a worksheet range.

The first row of the range is treated as the header row.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range from which to read the content.	google sheet range reference	Yes	(no default)

Output Concepts

Concept	Description	Type
content	A table with the read contents.	table

Examples

Read the contents of a range

...
use the above as the PO Range
read the content from the PO Range

to rename the sheet to a new name

Rename a sheet in a Google Sheets file.

Input Concepts

Concept	Description	Type	Required	Default Value
sheet	the sheet to rename.	google sheet reference	Yes	(no default)
new name	the new name for the sheet.	text	Yes	(no default)

Output Concepts

Concept	Description	Type
answer	A reference to the renamed sheet.	google sheet reference

Examples

Rename a sheet

...
use the above as the sheet
rename the sheet to "Archived"

to retrieve the (columns) from the table

FILTER - CAPABLE

Get the columns from a Google Sheets table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table from which to retrieve the columns.	google sheet table reference	Yes	(no default)

Output Concepts

Concept	Description	Type
columns	A list of the retrieved column references.	google sheet column reference

Examples

Retrieve the columns of a table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
retrieve the columns from the PO Table

Retrieve a column by name

...
get the PO Table's column whose name is "Amount"

to retrieve the (rows) from the table

FILTER - CAPABLE

Get the rows from a Google Sheets table.

When a filter is given, only data rows whose cells satisfy it are returned (referencing columns by their header name); without a filter, every row is returned.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table from which to retrieve the rows information.	google sheet table reference	Yes	(no default)

Output Concepts

Concept	Description	Type
rows	A list of the retrieved row references.	google sheet row reference

Examples

Retrieve the rows of a table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
retrieve the rows from the PO Table

Retrieve the rows where a column meets a condition

...
get the PO Table's rows where the amount is greater than 100

to set the cell's color to a color

Set the background (fill) color of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose color will be set.	google sheet cell reference	Yes	(no default)
color	the fill color as a hex string (e.g. "#FF0000").	text	Yes	(no default)

Examples

Set a cell's color

...
use the above as the cell
set the cell's color to "#FF0000"

to set the cell's content to a value

Set the value of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose value will be set.	google sheet cell reference	Yes	(no default)
value	the value to write into the cell.	boolean or number or text	Yes	(no default)

Examples

Set a cell's value

...
use the above as the cell
set the cell's content to "Hello World"

to set the cell's font color to a color

Set the font (text) color of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose font color will be set.	google sheet cell reference	Yes	(no default)
color	the font color as a hex string (e.g. "#0000FF").	text	Yes	(no default)

Examples

Set a cell's font color

...
use the above as the cell
set the cell's font color to "#0000FF"

to set the cell's formula to a formula value

Set the formula of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose formula will be set.	google sheet cell reference	Yes	(no default)
formula value	the formula to write into the cell (e.g. "=SUM(A1:A10)").	text	Yes	(no default)

Examples

Set a cell's formula

...
use the above as the cell
set the cell's formula to "=SUM(A1:A10)"

to set the cell's number format to a format string

Set the number format of a cell.

Input Concepts

Concept	Description	Type	Required	Default Value
cell	the cell whose number format will be set.	google sheet cell reference	Yes	(no default)
format string	the number format pattern (e.g. "0.00", "$#,##0.00").	text	Yes	(no default)

Examples

Set a cell's number format

...
use the above as the cell
set the cell's number format to "0.00"

to set the range's color to a color

Set the background (fill) color of every cell in a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range whose color will be set.	google sheet range reference	Yes	(no default)
color	the fill color as a hex string (e.g. "#FF0000").	text	Yes	(no default)

Examples

Set a range's color

...
use the above as the PO Range
set the PO Range's color to "#FF0000"

to set the range's font color to a color

Set the font (text) color of every cell in a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range whose font color will be set.	google sheet range reference	Yes	(no default)
color	the font color as a hex string (e.g. "#0000FF").	text	Yes	(no default)

Examples

Set a range's font color

...
use the above as the PO Range
set the PO Range's font color to "#0000FF"

to set the worksheet range's number format to a format string

Set the number format of every cell in a worksheet range.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range whose number format will be set.	google sheet range reference	Yes	(no default)
format string	the number format pattern (e.g. "0.00", "$#,##0.00").	text	Yes	(no default)

Examples

Set a range's number format

...
use the above as the PO Range
set the PO Range's number format to "0.00"

to write the content in a table

Update the contents of a Google Sheets table.

Input Concepts

Concept	Description	Type	Required	Default Value
table	table reference from which to read the content.	google sheet table reference	Yes	(no default)
content	the content to be set into the table reference.	table	Yes	(no default)

Examples

Set the contents of a table into a sheet table

...
get the PO Sheet's table whose name is "PO Table"
use the above as the PO Table
read the content from the PO Table
get the PO Sheet's table whose name is "PO Table Duplicate"
use the above as the PO Table Duplicate
write the content in the PO Table Duplicate

to write the content in a worksheet range

Write content into a worksheet range starting at the range's top-left cell.

Input Concepts

Concept	Description	Type	Required	Default Value
worksheet range	the range to write the content into.	google sheet range reference	Yes	(no default)
content	the content to be written into the range.	table	Yes	(no default)

Examples

Write content into a range

...
use the above as the PO Range
write the content in the PO Range

Concepts

Google sheet table reference

Reference to a table inside a Google Sheets spreadsheet's sheet.

Field Name	Description	Type
id	Id of the table	text
name	Name of the table	text
sheet_reference	Sheet the table belongs to	json

Google sheet reference

Reference to a sheet inside a Google Sheets spreadsheet file.

Field Name	Description	Type
id	Id of the sheet	number
name	Name of the sheet	text
file	spreadsheet file the sheet belongs to	json

Google sheet range reference

Reference to a range of cells inside a Google Sheets spreadsheet's sheet.

Field Name	Description	Type
file	spreadsheet file the range belongs to	json
sheet_id	Id of the sheet the range belongs to	number
sheet_name	Name of the sheet the range belongs to	text
address	A1 notation of the range without the sheet prefix (e.g. "A1:B2")	text
start_row_index	0-based index of the first row in the range	optional[number]
end_row_index	0-based exclusive index of the last row in the range	optional[number]
start_column_index	0-based index of the first column in the range	optional[number]
end_column_index	0-based exclusive index of the last column in the range	optional[number]
row_count	Number of rows in the range	optional[number]
column_count	Number of columns in the range	optional[number]
cell_count	Number of cells in the range	optional[number]

Google drive file reference

Contains all information required to identify a file in Google Drive.

Field Name	Description	Type
id	The id of the file	text
file_name	The name of the file	text
parents	The ids of the parent folders	list of text

Google drive folder reference

Contains all information required to identify a folder in Google Drive.

Field Name	Description	Type
id	The id of the folder	text
folder_name	The name of the folder	text
parents	The ids of the parent folders	list of text

Google sheet column reference

Reference to a column inside a Google Sheets spreadsheet's sheet.

Field Name	Description	Type
index	Index of the column	number
name	Name of the column	text
table	Table the column belongs to	json

Google sheet row reference

Reference to a row inside a Google Sheets spreadsheet's sheet.A row can belong either to a table (table is set) or to a worksheet range (range_reference is set).

Field Name	Description	Type
index	Index of the row	number
values	Values in that row	list of list of any
table	Table the row belongs to, if the row came from a table	optional[google sheet table reference]
range_reference	Range the row belongs to, if the row came from a range	optional[google sheet range reference]

Google sheet cell reference

Reference to a single cell inside a Google Sheets spreadsheet's sheet.A cell is a 1x1 range; row_index and column_index are 0-based (A1 -> row 0, column 0).

Field Name	Description	Type
file	spreadsheet file the cell belongs to	json
sheet_id	Id of the sheet the cell belongs to	number
sheet_name	Name of the sheet the cell belongs to	text
row_index	0-based row index of the cell	number
column_index	0-based column index of the cell	number
address	A1 notation of the cell without the sheet prefix (e.g. "B3"). Derived from row_index/column_index when not provided.	optional[text]
table_id	Id of the table the cell belongs to, when applicable	optional[text]