# Google Sheets

{% hint style="info" %}
The following documentation is for **Google Sheets v2.0.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 <kbd>**Install**</kbd> or <kbd>**Add Connection**</kbd> 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 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 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 get the (file's sheets)

![FILTER - CAPABLE](https://img.shields.io/static/v1?label=FILTER\&message=CAPABLE\&color=blue)

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 (sheet's tables)

![FILTER - CAPABLE](https://img.shields.io/static/v1?label=FILTER\&message=CAPABLE\&color=blue)

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 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 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
```

### to retrieve the (rows) from the table

Get the rows from a Google Sheets table.

**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
```

### 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
```

## 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 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 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 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.

| Field Name               | Description              | Type                  |
| ------------------------ | ------------------------ | --------------------- |
| `index`                  | Index of the row         | `number`              |
| [`values`](#list-of-any) | Values in that row       | `list of list of any` |
| `table`                  | Table the row belongs to | `json`                |