Google Sheets

Coherent can work with spreadsheets using the following tools:

ToolAction
GSHEETS_READRead data from a Google Sheet
ROWS_TO_TASKSConvert rows from a Google Sheet into tasks
GSHEETS_WRITEWrite data to a specific row in a Google Sheet

These tools allow Coherent to interact with Google Sheets in various ways, from reading data to creating tasks and updating specific rows.

Column Modifiers

The following keywords can be used in any part of column names, and will modify how coherent handles those columns.

This checks are very flexible. For example all the following column names would be filled with the conversation url: Conversation URL, CONVERSATION URL, CONVERSATION_URL, conversation url (chat history), Chat history (conversation_UrL)


KEYWORDTITLE

Title

Uses the cell's content as the title of the conversation, in combination with the row number.

#Account (Title)...Conversation title
2Acme Corp....1: Acme Corp.
3Example Inc....2: Example Inc.
4Test Company...3: Test Company

KEYWORDIGNORE

Ignore

Makes the column invisible to the agents. By default, all columns are visible except the ones autofilled by Coherent.


KEYWORDCOST_CREDITS

Cost

Fills the cell with the cost of the conversation in credits.


KEYWORDCONVERSATION_URL

Conversation URL

Fills the cell with the conversation url.


KEYWORDMESSAGE_COUNT

Message count

Fills the cell with the number of messages in the conversation.

Special Columns

The following columns or column naming conventions have special behaviors.


COLUMNSOURCE

.* Source

Columns ending with Source are linked to their corresponding main columns (e.g. Foo Source -> Foo). The agent must provide a value for the Source column before filling the Main column.

Any Source column can be filled with the value "linkedin" if the column linkedin is being filled with a url simultaneously. Both case insensitive.

We can generalize this further to all columns, not just "LinkedIn". Please reach out with specific use cases.

Example

#FooFoo SourceBarLinkedInPositionPosition Source
2Johnhttps://example.com/john42https://linkedin.com/in/johnSoftware EngineerLinkedIn ✅ becomes URL
3Alice❌ Required if setting Foo23Software EngineerFoo ❌ Not url

Example

#### GSHEETS_WRITE
{
  "spreadsheet_id": "1iv51h2vsdb321b52j55l1h",
  "sheet": "Sheet1",
  "cells": {
    "1": {
      "Foo": "John",
      "Foo Source": "https://example.com/john",
      "Bar": "42",
      // LinkedIn Alias example
      "LinkedIn": "https://linkedin.com/in/john",
      "Position": "Software Engineer",
      "Position Source": "LinkedIn" // Will become "https://linkedin.com/in/john"
    }
  }
}


COLUMNINSTRUCTIONS

.* (Instructions)

The content between the parenthesees after a column name is used as instructions for coherent to fill that column.

The conversation variable COLUMN_INSTRUCTIONS is created when reading a spreadsheet or creating taks from rows, and is passed down to children conversations.

The following column headings in the spreadsheet:

  • HubSpot ID (ignore) → Invisible
  • Conversation URL → Invisible, automatic hyperlink to the conversation
  • LinkedIn (Of the person, not the company!) → Column + Instructions
  • Position (Most representative position, just one. e.g. Software Engineer) → Column + Instructions + Source Column
  • Position Source → Shown as part of Position row
  • Open For Work ( "TRUE" / "FALSE", based on linkedin profile) → Column + Instructions
  • Comments → Column

Will result in the following COLUMN_INSTRUCTIONS:

ColumnInstructionsSource Column
LinkedInOf the person, not the company!
PositionMost representative position, just one. e.g. Software EngineerPosition Source
Open For Work"TRUE" / "FALSE", based on linkedin profile
Comments
TABLECOLUMN_INSTRUCTIONSConversation

Column Instructions

A transposed table of all the visible columns in the spreadsheet, their instructions, and their Source columns.

Conversation Variable

TABLE
{COLUMN_INSTRUCTIONS}
| Column        | Instructions                                                   | Source Column   |
| ------------- | -------------------------------------------------------------- | --------------- |
| LinkedIn      | Of the person, not the company!                                |                 |
| Position      | Most representative position, just one. e.g. Software Engineer | Position Source |
| Open For Work | "TRUE" / "FALSE", based on linkedin profile                    |                 |


TOOLGSHEETS_READ

Read Sheets

Parameters for the google sheet read request.

Required attributes

  • Name
    spreadsheet_id
    Type
    string
    Description

    Spreadsheet ID, either provided as input or can be extracted from the URL

Optional attributes

  • Name
    sheet
    Type
    string
    Description

    The name of the sheet to read/write. Leave empty to use the first sheet.

  • Name
    rows
    Type
    array[integer]
    Description

    When the user asks for specific rows or rows meeting some condition, instead of using range you can specify the list of rows to read.

  • Name
    range
    Type
    string
    Description

    If no rows are specified, the range of cells to read in A1 notation. For example, 'A1:Z100' for the first 100 rows, or 'head' to read the first and last rows (if no rows are specified).

    A1:ZZ100

Function Call

TOOL
GSHEETS_READ
#### GSHEETS_READ
```json
{
  "range": "A1:Z100",
  "spreadsheet_id": "1iv51h2vsdb321b52j55l1h"
}
```

Response

#### Success
TOOLROWS_TO_TASKS

Convert Rows to Tasks

Parameters for the google sheet read request.

Required attributes

  • Name
    spreadsheet_id
    Type
    string
    Description

    Spreadsheet ID, either provided as input or can be extracted from the URL

  • Name
    team
    Type
    string
    Description

    The team to assign the tasks to, with the @ symbol.

    @SubordinateTeam

Optional attributes

  • Name
    sheet
    Type
    string
    Description

    The name of the sheet to read/write. Leave empty to use the first sheet.

  • Name
    rows
    Type
    array[integer]
    Description

    When the user asks for specific rows or rows meeting some condition, instead of using range you can specify the list of rows to read.

  • Name
    range
    Type
    string
    Description

    If no rows are specified, the range of cells to read in A1 notation. For example, 'A1:Z100' for the first 100 rows, or 'head' to read the first and last rows (if no rows are specified).

    A1:ZZ100
  • Name
    headers
    Type
    array[string]
    Description

    The columns to use for the tasks. If not provided, all the columns will be used.

  • Name
    message_sender
    Type
    string
    Description

    The handle of the agent that will send the message. Should be a member of the team running the tasks.\n\n If not specified, the default agent in the team (PLANNER) will send the message.

    @Manager
  • Name
    message_content
    Type
    string
    Description

    The description of the task. Always end mentioning this two variables in this way: {{ROW}}: {{ROW_PREVIEW}}

    Process row {{ROW}}: \n\n{{ROW_PREVIEW}}
  • Name
    max_autoreply
    Type
    integer
    Description

    The maximum number of replies to generate before stopping and waiting for user input.

    10
  • Name
    sleep_between_tasks
    Type
    integer
    Description

    The number of seconds to sleep between creating each task.

    1

Function Call

TOOL
ROWS_TO_TASKS
#### ROWS_TO_TASKS
```json
{
  "rows": [
    2,
    3,
    7,
    11
  ],
  "spreadsheet_id": "1iv51h2vsdb321b52j55l1h",
  "team": "@SubordinateTeam"
}
```

Response

#### Success


TABLEROW_PREVIEWConversation

Row Preview

Passed down to children conversations as working memory for the row they are working on. Gets updated as mutations are performed on the row.

Conversation Variable

TABLE
{ROW_PREVIEW}
| Row | Name | Age | Email |
| --- | --- | --- | --- |
| 1 | John Doe | 30 | john@example.com |
| 2 | Jane Smith | 28 | jane@example.com |


NUMBERROWConversation

Row

The row number of the row being processed.

Conversation Variable

NUMBER
{ROW}
2


TEXTSPREADSHEET_IDConversation

Spreadsheet Id

The spreadsheet ID.

Conversation Variable

TEXT
{SPREADSHEET_ID}
1iv51h2vsdb321b52j55l1h


TEXTSHEETConversation

Sheet

The sheet name.

Conversation Variable

TEXT
{SHEET}
Sheet1


TEXTOther ColumnsConversation

Other Columns

Any columns from the spreadsheet get passed down as variables, for example Active Users (#, do not include admin) becomes the variable ACTIVE_USERS after stripping the instructions and converting to variable format.

Conversation Variable

TEXT
{Other Columns}
42
TOOLGSHEETS_WRITE

Write Row to Sheet

Required attributes

  • Name
    spreadsheet_id
    Type
    string
    Description

    Spreadsheet ID, either provided as input or can be extracted from the URL

  • Name
    cells
    Type
    object
    Description

    A dictionary where keys are column headers and values are the cell values to write on those columns.

Optional attributes

  • Name
    sheet
    Type
    string
    Description

    The name of the sheet to read/write. Leave empty to use the first sheet.

Function Call

TOOL
GSHEETS_WRITE
#### GSHEETS_WRITE
```json
{
  "cells": {
    "5": {
      "Age": "30",
      "Name": "John Doe"
    }
  },
  "spreadsheet_id": "1iv51h2vsdb321b52j55l1h"
}
```

Response

#### Success

Was this page helpful?