> For the complete documentation index, see [llms.txt](https://docs.kognitos.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.kognitos.com/legacy/legacy-experience/books/reference/googlesheets.md).
# Google Sheets
{% hint style="info" %}
The following documentation is for **Google Sheets v2.10.1** *(BDK)*.
{% endhint %}
## 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**](#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`](#google-sheet-table-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | the range to clear. | `google sheet range reference` | Yes | (no default) |
**Examples**
Clear a range
```generic
...
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`](#google-sheet-reference) | the sheet to copy. | `google sheet reference` | Yes | (no default) |
| [`file`](#google-drive-file-reference) | 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`](#google-sheet-reference) | A reference to the copied sheet. | `google sheet reference` |
**Examples**
Copy a sheet
```generic
...
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`](#google-drive-file-reference) | 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`](#google-sheet-reference) | A reference to the created sheet. | `google sheet reference` |
**Examples**
Create a new sheet
```generic
...
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`](#google-sheet-reference) | 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`](#google-sheet-table-reference) | A reference to the created table. | `google sheet table reference` |
**Examples**
Create a new table in a sheet of a spreadsheet
```generic
...
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`](#google-sheet-reference) | 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`](#google-sheet-range-reference) | A reference to the worksheet range. | `google sheet range reference` |
**Examples**
Create a range in a sheet
```generic
...
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`](#google-drive-folder-reference) | The folder to create the Google Sheets spreadsheet in. | `google drive folder reference` | No | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ---------------------------------------- | ------------------------------------- | ----------------------------- |
| [`folder`](#google-drive-file-reference) | the new spreadsheet's file reference. | `google drive file reference` |
**Examples**
Create a Google Sheets spreadsheet called "Hello World" in a google drive folder
```generic
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`](#google-sheet-reference) | the sheet to delete. | `google sheet reference` | Yes | (no default) |
**Examples**
Delete a sheet
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
use the above as the PO Range
get the column count in the PO Range
```
### to get the (file's sheets)
Get the sheets from a Google Sheets file.
**Input Concepts**
| Concept | Description | Type | Required | Default Value |
| -------------------------------------- | ------------------------------------------------------------ | ----------------------------- | -------- | ------------- |
| [`file`](#google-drive-file-reference) | Google Sheets spreadsheet from which to retrieve the sheets. | `google drive file reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ------------------------------------------ | ------------------------------------------- | ------------------------ |
| [`file's sheets`](#google-sheet-reference) | The list of the retrieved sheet references. | `google sheet reference` |
**Examples**
Retrieve the sheets of a spreadsheet inside a drive folder
```generic
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
```generic
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`](#google-sheet-range-reference) | the range to get the following row for. | `google sheet range reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ------------------------------------------------------ | ---------------------------------------------------------------- | ------------------------------ |
| [`following row range`](#google-sheet-range-reference) | A reference to the range covering the row below the given range. | `google sheet range reference` |
**Examples**
Get the row that follows a range
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | the range the row belongs to. | `google sheet range reference` | Yes | (no default) |
| [`row`](#google-sheet-row-reference) | the row whose cells will be retrieved. | `google sheet row reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| --------------------------------------------- | ------------------------------------ | ----------------------------- |
| [`row's cells`](#google-sheet-cell-reference) | A list of the row's cell references. | `google sheet cell reference` |
**Examples**
Get the cells of the first row of a range
```generic
...
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)
Get the tables from a Google Sheets sheet.
**Input Concepts**
| Concept | Description | Type | Required | Default Value |
| ---------------------------------- | ----------------------------------------------------- | ------------------------ | -------- | ------------- |
| [`sheet`](#google-sheet-reference) | spreadsheet's sheet from which to retrieve the table. | `google sheet reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ------------------------------------------------- | ------------------------------------------- | ------------------------------ |
| [`sheet's tables`](#google-sheet-table-reference) | The list of the retrieved table references. | `google sheet table reference` |
**Examples**
Retrieve the tables in a sheet of a spreadsheet
```generic
...
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
```generic
...
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`](#google-sheet-reference) | 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`](#google-sheet-range-reference) | A reference to the used range of the sheet. | `google sheet range reference` |
**Examples**
Get the used range of a sheet
```generic
...
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`](#google-sheet-range-reference) | the range from which to retrieve the rows. | `google sheet range reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ------------------------------------------------------- | --------------------------------------- | ---------------------------- |
| [`worksheet range's rows`](#google-sheet-row-reference) | A list of the retrieved row references. | `google sheet row reference` |
**Examples**
Get the rows of a range
```generic
...
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`](#google-sheet-table-reference) | 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`](#google-sheet-column-reference) | A reference of the newly created column | `google sheet column reference` |
**Examples**
Insert a new column into a table
```generic
...
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`](#google-sheet-table-reference) | 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`](#google-sheet-row-reference) | A list of the retrieved row references. | `google sheet row reference` |
**Examples**
Insert a new row into a table
```generic
...
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`](#google-sheet-table-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
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`](#google-sheet-reference) | 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`](#google-sheet-reference) | A reference to the renamed sheet. | `google sheet reference` |
**Examples**
Rename a sheet
```generic
...
use the above as the sheet
rename the sheet to "Archived"
```
### to retrieve the (columns) from the table
Get the columns from a Google Sheets table.
**Input Concepts**
| Concept | Description | Type | Required | Default Value |
| ---------------------------------------- | ----------------------------------------- | ------------------------------ | -------- | ------------- |
| [`table`](#google-sheet-table-reference) | table from which to retrieve the columns. | `google sheet table reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ------------------------------------------- | ------------------------------------------ | ------------------------------- |
| [`columns`](#google-sheet-column-reference) | A list of the retrieved column references. | `google sheet column reference` |
**Examples**
Retrieve the columns of a table
```generic
...
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
```generic
...
get the PO Table's column whose name is "Amount"
```
### to retrieve the (rows) from the table
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`](#google-sheet-table-reference) | table from which to retrieve the rows information. | `google sheet table reference` | Yes | (no default) |
**Output Concepts**
| Concept | Description | Type |
| ------------------------------------- | --------------------------------------- | ---------------------------- |
| [`rows`](#google-sheet-row-reference) | A list of the retrieved row references. | `google sheet row reference` |
**Examples**
Retrieve the rows of a table
```generic
...
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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-cell-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
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`](#google-sheet-table-reference) | 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
```generic
...
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`](#google-sheet-range-reference) | 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
```generic
...
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`](#list-of-text) | 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`](#list-of-text) | 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`](#list-of-any) | Values in that row | `list of list of any` |
| [`table`](#google-sheet-table-reference) | Table the row belongs to, if the row came from a table | `optional[google sheet table reference]` |
| [`range_reference`](#google-sheet-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]` |
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://docs.kognitos.com/legacy/legacy-experience/books/reference/googlesheets.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.