Overview

The Google Spreadsheet connector enables your AI Colleagues to integrate with your organization's Google Sheets platform, facilitating automated spreadsheet management, data entry, and retrieval workflows.

Google Sheets is Google's cloud-based spreadsheet application that allows users to create, edit, and collaborate on spreadsheets in real-time. The Google Spreadsheet connector allows Leena AI to automate spreadsheet workflows, manage data rows, and interact with sheets seamlessly.

API Details

Leena AI integrates with Google Sheets via REST APIs.

Documentation link: https://developers.google.com/sheets/api/guides/concepts

Setup

The Google Spreadsheet connector uses Service Account authentication for secure server-to-server interactions.

Prerequisites

Before setting up the Google Spreadsheet connector, ensure you have:

Administrator access to your Google Cloud Console
Access to Google Cloud Console API & Services
Ability to create Service Accounts in Google Cloud
Access to your Leena AI workspace with connector management permissions
The Google Sheets that you want to access must be shared with the Service Account email

Get credentials

Here is how to create a Service Account in Google Cloud Console:

Log in to Google Cloud Console (Ensure you're signed in as an admin).
Create or Select a Project:
1. Click on the project dropdown at the top of the page
2. Click New Project or select an existing project
3. Enter a project name and click Create
Enable Google Sheets API:
1. Navigate to APIs & Services > Library
2. Search for "Google Sheets API"
3. Click Enable
Enable Google Drive API:
1. Navigate to APIs & Services > Library
2. Search for "Google Drive API"
3. Click Enable
Create Service Account:
1. Navigate to APIs & Services > Credentials
2. Click + CREATE CREDENTIALS
3. Select Service account
Configure Service Account Details:
1. Service Account Name: Enter a descriptive name (e.g., "leena-sheets-connector")
2. Service Account ID: This will auto-populate based on the name
3. Description: Optional description for the service account
4. Click Create and Continue
Skip Role Assignment (Optional):
1. For basic spreadsheet access, no specific role is required
2. Click Continue, then Done
Generate JSON Key:
1. Click on the Service Account you just created
2. Navigate to the Keys tab
3. Click Add Key > Create New Key
4. Select JSON as the key type
5. Click Create
6. A JSON key file will be downloaded automatically (Keep this file secure)
Note the Service Account Email:
1. The email will look like: your-service-account-name@your-project-id.iam.gserviceaccount.com
2. This email is needed to share spreadsheets with the service account

Share Spreadsheets with Service Account

For the connector to access your spreadsheets:

Open the Google Sheet you want to connect to
Click the Share button in the top right corner
Enter the Service Account email address
Set the permission level to Editor (for read/write access) or Viewer (for read-only access)
Click Send

Add connection

Here is how to add a connection on Leena AI:

Log in to your Leena AI workspace
Navigate to Settings > Integrations
Search for "Google Spreadsheet" and select it from the list to add its new connector
Start configuring the connector
1. Auth Type: Select "Service Account"
2. Service Account JSON: Upload or paste the JSON key file downloaded from Google Cloud Console
Save the connection configuration

Actions

The following actions are supported for the Google Spreadsheet connector:

Add New Row

Appends a new row of data to a selected worksheet in Google Sheets. The Agent can leverage the skill (workflow), which has been designed to add new data to a Google Sheet, once the user provides the required information. Here are some common use cases:

Lead Capture: Add new leads or contacts to a tracking spreadsheet
Order Logging: Record new orders or transactions
Attendance Tracking: Log attendance records
Data Collection: Store form submissions or survey responses
Inventory Updates: Add new inventory items

Input Parameters

Here are the input parameters required to set up this action:

Mandatory

Name	Description
Spreadsheet ID	The unique identifier of the Google Spreadsheet
Sheet ID	The identifier of the specific worksheet within the spreadsheet
Header Row Number	The row number containing column headers (e.g., 0 for first row)

Optional

Name	Description
Append To First Empty Row	When true, appends data to the first empty row; when false, calculates the exact insertion point
Column Values	Dynamic fields generated based on the columns in the header row (e.g., 0, 1, 2 for column indices)

Here is a sample JSON input:

// Basic Add Row

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "appendToFirstEmptyRow": true,
  "0": "John Doe",
  "1": "[email protected]",
  "2": "Engineering",
  "3": "Active"
}

// Add Row with Specific Position

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "appendToFirstEmptyRow": false,
  "0": "Jane Smith",
  "1": "[email protected]",
  "2": "Marketing",
  "3": "Active"
}

Response

Upon successful addition, the action returns:

HTTP status code (200 for success)
API response data confirming the row addition
Updated range information

Fetch Row

Retrieves rows from a Google Sheet based on specified filter criteria. This action can be leveraged by Leena AI Orchestrator/Agent to search for and retrieve specific data from a spreadsheet. Here are some common use cases:

Data Lookup: Find specific records by ID or name
Status Check: Retrieve items with a specific status
Report Generation: Fetch data matching certain criteria
Validation: Check if a record exists in the spreadsheet
Filtered Exports: Get subset of data based on conditions

Input Parameters

Here are the input parameters required to set up this action:

Mandatory

Name	Description
Spreadsheet ID	The unique identifier of the Google Spreadsheet
Sheet ID	The identifier of the specific worksheet within the spreadsheet
Header Row Number	The row number containing column headers

Optional

Name	Description
Filter Type	How multiple filters should be combined, options: AND: All filter conditions must be met OR: Any filter condition must be met
Filters	Array of filter conditions to apply, each filter contains: Column: The column index to filter on Condition: The comparison operator to use Value: The value to compare against

Name

Description

Filter Type

How multiple filters should be combined, options:

AND: All filter conditions must be met
OR: Any filter condition must be met

Filters

Array of filter conditions to apply, each filter contains:

Column: The column index to filter on
Condition: The comparison operator to use
Value: The value to compare against

Filter Conditions

The following filter conditions are available:

Condition	Description
contains	Cell value contains the specified text
notContains	Cell value does not contain the specified text
equalsIgnoreCase	Cell value equals the text (case-insensitive)
eq	Cell value exactly equals the specified value
neq	Cell value does not equal the specified value
in	Cell value is in the specified list of values
nin	Cell value is not in the specified list
lt	Cell value is less than the specified value
gt	Cell value is greater than the specified value
lte	Cell value is less than or equal to
gte	Cell value is greater than or equal to
before	Date value is before the specified date
after	Date value is after the specified date
beforeOrEquals	Date value is before or equal to
afterOrEquals	Date value is after or equal to
null	Cell value is null or empty
nnull	Cell value is not null or empty
isEmpty	Cell is empty
isNotEmpty	Cell is not empty

Here is a sample JSON input:

// Fetch with Single Filter

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "filterType": "AND",
  "filters": [
    {
      "column": "2",
      "condition": "eq",
      "value": "Engineering"
    }
  ]
}

// Fetch with Multiple Filters (AND)

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "filterType": "AND",
  "filters": [
    {
      "column": "2",
      "condition": "eq",
      "value": "Engineering"
    },
    {
      "column": "3",
      "condition": "eq",
      "value": "Active"
    }
  ]
}

// Fetch with Multiple Filters (OR)

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "filterType": "OR",
  "filters": [
    {
      "column": "2",
      "condition": "eq",
      "value": "Engineering"
    },
    {
      "column": "2",
      "condition": "eq",
      "value": "Marketing"
    }
  ]
}

// Fetch with Contains Filter

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "filterType": "AND",
  "filters": [
    {
      "column": "1",
      "condition": "contains",
      "value": "@company.com"
    }
  ]
}

// Fetch Non-Empty Rows

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "filterType": "AND",
  "filters": [
    {
      "column": "0",
      "condition": "isNotEmpty"
    }
  ]
}

Response

The action returns:

HTTP status code (200 for success)
Array of matching rows, where each row is an object with column names as keys (e.g., "A-Name": "John Doe", "B-Email": "[email protected]")
Row Index included in each result

Update Row

Updates an existing row in a Google Sheet. The Agent can leverage the skill (workflow), which has been designed to modify existing data in a Google Sheet, once the user provides the row details to be updated. Here are some common use cases:

Status Updates: Change the status of a task or order
Data Corrections: Fix errors in existing records
Progress Tracking: Update completion percentages or milestones
Contact Updates: Modify customer or employee information
Inventory Management: Update stock quantities

Input Parameters

Here are the input parameters required to set up this action:

Mandatory

Name	Description
Spreadsheet ID	The unique identifier of the Google Spreadsheet
Sheet ID	The identifier of the specific worksheet within the spreadsheet
Header Row Number	The row number containing column headers
Row To Update	The row number of the row to be updated

Optional

Name	Description
Column Values	Dynamic fields generated based on the columns in the header row with the new values to set

Here is a sample JSON input:

// Update Single Column

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "rowToUpdate": "5",
  "3": "Inactive"
}

// Update Multiple Columns

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "rowToUpdate": "5",
  "1": "[email protected]",
  "2": "Product",
  "3": "Active"
}

// Complete Row Update

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetId": "0",
  "headerRowNumber": "0",
  "rowToUpdate": "10",
  "0": "Updated Name",
  "1": "[email protected]",
  "2": "Sales",
  "3": "Active"
}

Response

Upon successful update, the action returns:

HTTP status code (200 for success)
API response data confirming the update
Updated range information

Read Column

Extracts all values from a single specified column in a Google Sheet. This action can be leveraged by Leena AI Orchestrator/Agent to retrieve column data for use in subsequent workflow steps. Here are some common use cases:

Dropdown Population: Get values to populate selection lists
Data Validation: Retrieve existing values to check against
Aggregation: Collect all values in a column for processing
Export: Extract specific column data for reporting
Reference Lists: Get lookup values from reference sheets

Input Parameters

Here are the input parameters required to set up this action:

Mandatory

Name	Description
Spreadsheet ID	The unique identifier of the Google Spreadsheet
Sheet Name	The name of the specific worksheet (tab name)
Header Row Number	The row number containing column headers
Header Name	The name of the column header to read from

Here is a sample JSON input:

// Read Email Column

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Sheet1",
  "headerRowNumber": "1",
  "headerName": "Email"
}

// Read Status Column

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "Employees",
  "headerRowNumber": "1",
  "headerName": "Status"
}

// Read Department Column

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "sheetName": "HR Data",
  "headerRowNumber": "1",
  "headerName": "Department"
}

Response

The action returns:

HTTP status code (200 for success)
Array of all values in the specified column (excluding the header)