Google Spreadsheet Connector

Overview

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

Google Spreadsheet (Google Sheets) is Google's web-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 data workflows, manage spreadsheet records, and interact with spreadsheet data seamlessly.

Authentication

The Google Spreadsheet connector uses OAuth 2.0 authentication with Service Account credentials for secure, server-to-server communication without requiring user interaction.

Prerequisites

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

1. Administrator access to your Google Cloud Console
2. Access to Google Cloud Console API & Services
3. Ability to create Service Account credentials in Google Cloud
4. Access to your Leena AI workspace with connector management permissions
5. Administrator/owner permissions for your GSuite Workspace

Setting up Google Spreadsheet Connector

Step 1: Access Google Cloud Console

1. Navigate to Google Cloud Console - Go to https://console.cloud.google.com/
2. Click on APIs & Service

Step 2: Enable Google Sheets API

1. Navigate to APIs & Services:
   • In the left sidebar, click APIs & Services
   • Select Library
2. Enable Required API:
   • Search for "Google Sheets API"
   • Click Google Sheets API
   • Click Enable

Step 3: Create Service Account

1. Navigate to Credentials:

   • Go to APIs & Services > Credentials
   • Click + CREATE CREDENTIALS
   • Select Service Account

2. Configure Service Account Details:

   • Enter Service account name (e.g., "leena-ai-sheets-service")
   • The Service account ID will be auto-generated
   • Click Done

Step 4: Generate Service Account Key (JSON)

1. Access Service Account:

   • After Step 3, in the Service Account section, you can see the new service account created
   • Click on that service account

2. Create JSON Key:

   • Click on the KEYS section
   • Select ADD KEYS button
   • Then click CREATE NEW KEY
   • Select type JSON
   • A JSON file will be downloaded automatically

3. Save Key File:

   • The downloaded file contains a JSON object with all required authentication details
   • Store this file securely as it contains sensitive credentials

Step 5: Access the Integrations Page under Leena AI

1. You should have accurate permissions to enable a Google Spreadsheet connector
2. Navigate to Settings section and further to Integrations section to explore the marketplace
3. You can find Google Spreadsheet connector under the marketplace
4. Click on the particular tile for Google Spreadsheet to create a new connection
5. You can view all the previously created connectors for Google Spreadsheet. You can view the configurations of the existing connectors or create a new connector altogether
6. Further configurations for creating a new connector for Google Spreadsheet would render

Step 6: Configure Authentication in Leena AI

The supported authentication type for this application is OAuth 2.0 (Service Account). You'll need to add the details from the downloaded JSON key file:

JSON Key File Structure

Sample JSON file for reference:

{
  "type": "service_account",
  "project_id": "PROJECT NAME",
  "private_key_id": "PROJECT KEY ID",
  "private_key": "PRIVATE KEY",
  "client_email": "Client EMAIL",
  "client_id": "client id",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "Client X509 Certificate URL",
  "universe_domain": "googleapis.com"
}

Step 7: Submit and Save Connection

1. Click on Submit to run the basic validations and save a connection
2. The connector will be configured and ready to use

Access Permissions

Required Scopes

The service account requires the following scopes for full functionality:

Spreadsheet Access

• The service account must have appropriate permissions (Editor or Owner) on the spreadsheets it needs to access
• Share spreadsheets with the service account email address
• Permissions can be granted at the individual spreadsheet level or through shared drives

Quotas and Limitations

API Quotas

• Read requests per day: 500,000,000
• Write requests per day: 500,000,000
• Requests per 100 seconds per user: 100

Spreadsheet Limitations

• Maximum cells per spreadsheet: 10,000,000 cells
• Maximum columns per sheet: 18,278 columns
• Maximum rows per sheet: Based on cell limit
• Maximum worksheets per spreadsheet: 200 sheets

Create New Spreadsheet

Generates a new Google Spreadsheet to store data in a separate file, making it easy to maintain different datasets across projects or workflows. This action is valuable for automating data organization, as it allows the bot to create spreadsheets for unique tasks, such as new holiday calendars, tracking separate campaigns, or logging support tickets for specific issues.

Required Parameters

Response

Upon successful creation, the action returns:

• Spreadsheet ID
• Spreadsheet URL
• Creation timestamp
• Default worksheet name
• Location information

Common Use Cases

1. Data Organization: Create separate spreadsheets for different projects or departments
2. Calendar Management: Generate new holiday calendars for each year
3. Campaign Tracking: Set up tracking sheets for marketing campaigns
4. Support Logging: Create dedicated sheets for specific support issues
5. Report Generation: Automate creation of monthly or quarterly report sheets
6. Project Planning: Generate project-specific tracking spreadsheets

Add New Row

Adds a new row to an existing Google Spreadsheet, allowing data to be recorded sequentially. This action is essential for scenarios where the bot is capturing information, such as user feedback, survey responses, or transaction records, and needs to store them in a centralized location for easy access and analysis by relevant teams.

Required Parameters

Response

Upon successful row addition, the action returns:

• Row number of the newly added row
• Confirmation of successful insertion
• Updated spreadsheet data
• Timestamp of the operation

Common Use Cases

1. User Feedback Collection: Capture and store user feedback sequentially
2. Survey Responses: Record survey responses as they come in
3. Transaction Logging: Log transaction records in chronological order
4. Task Tracking: Add new tasks or assignments to tracking sheets
5. Attendance Records: Record daily attendance or check-ins
6. Inventory Updates: Log new inventory items or stock entries
7. Lead Capture: Store new lead information from web forms

Fetch a Row

Retrieves a specific row of data based on set criteria, which allows the bot to access detailed information from the spreadsheet as needed. This action is valuable in scenarios where the bot must reference historical data, such as fetching a user's previous purchase details, project updates, or specific records for ongoing customer support interactions.

Required Parameters

Optional Parameters

Response

The action returns the row data including:

• All column values from the specified row
• Row number
• Column headers (if available)
• Data types for each cell
• Last modified timestamp

Common Use Cases

1. Historical Data Reference: Retrieve a user's previous purchase details
2. Project Updates: Fetch specific project records or status updates
3. Customer Support: Access historical customer interaction records
4. Record Verification: Validate existing data before updates
5. Report Generation: Pull specific data points for reports
6. Data Analysis: Extract rows matching specific criteria for analysis

Read Column

Reads all data within a specified column, providing an overview of specific entries, such as a list of usernames, task statuses, or dates. This action is beneficial for generating summaries or reports, such as identifying all pending tasks in a project, compiling a list of attendees, or aggregating data points for further processing or decision-making.

Required Parameters

Response

The action returns:

• All values in the specified column
• Column header (if available)
• Number of entries
• Data types for each cell
• Empty cell indicators

Common Use Cases

1. Summary Generation: Fetch all data from a specific column for summaries or reports
2. Task Identification: Identify all pending tasks in a project by reading status column
3. Attendee Lists: Compile a list of attendees or participants
4. Data Aggregation: Gather data points for further processing or analysis
5. Status Tracking: Monitor progress by reading status or completion columns
6. Name Lists: Extract lists of usernames, employee names, or client names
7. Date Compilation: Collect all dates for scheduling or timeline analysis

Update a Row

Modifies data within a specific row, allowing the bot to keep records up-to-date based on recent interactions or changes. This is especially useful for updating customer information, tracking progress on assigned tasks, or modifying inventory levels. It ensures that information remains accurate and current, which is crucial for maintaining data integrity across dynamic workflows.

Required Parameters

Response

Upon successful update, the action returns:

• Confirmation of successful update
• Updated row data
• Row number
• Timestamp of modification
• Previous values (if applicable)

Common Use Cases

1. Customer Information Updates: Update customer details based on recent interactions
2. Task Progress Tracking: Modify task status or completion percentage
3. Inventory Management: Update inventory levels after sales or restocking
4. Record Maintenance: Keep records accurate and current
5. Status Updates: Change project or task statuses
6. Data Correction: Fix incorrect or outdated information
7. Dynamic Workflows: Update data based on automated workflow triggers