📘 Lesson 5.1: Accessing Google Sheets with Agents

🎯 Lesson Objectives

By the end of this lesson, you’ll be able to:

  • Connect a LangChain-powered AI agent to Google Sheets

  • Read, write, and update spreadsheet data using agent commands

  • Enable your AI to respond dynamically using spreadsheet-based knowledge

  • Build real-world use cases like lead tracking, customer records, or support logs

🧰 Tools Required

Tool Purpose
LangChain AI agent framework
gspread Python package to access Google Sheets
Google Service Account Authenticated access
Ollama/OpenAI Language model backend
Streamlit (optional) UI for team access

🛠️ Step-by-Step Implementation

✅ Step 1: Create a Google Sheet

Example: “CustomerLeads” Sheet

Name Email Status AssignedTo
Sarah Green [email protected] New Alice
John Solar [email protected] Interested Bob

Make sure the sheet is shared with your Google service account email (e.g., [email protected]).

✅ Step 2: Setup Google Cloud API Access

  1. Go to Google Cloud Console

  2. Enable:

    • Google Sheets API

    • Google Drive API

  3. Create a Service Account

  4. Generate and download a credentials.json file

  5. Share the target Sheet with the service account’s email

✅ Step 3: Install and Import Required Libraries

pip install gspread oauth2client

import gspread
from oauth2client.service_account import ServiceAccountCredentials

✅ Step 4: Authenticate and Connect

scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
client = gspread.authorize(creds)

# Open your sheet
sheet = client.open("CustomerLeads").sheet1

✅ Step 5: Read, Write, and Update Data

🔍 Read Data

leads = sheet.get_all_records()
print(leads)

➕ Add a New Row

def add_lead(name, email, status="New", assigned=""):
    sheet.append_row([name, email, status, assigned])

🔄 Update an Existing Lead

def update_lead_status(email, new_status):
    data = sheet.get_all_records()
    for idx, row in enumerate(data, start=2):  # starts from row 2
        if row['Email'] == email:
            sheet.update_cell(idx, 3, new_status)
            return f"Updated status for {email} to {new_status}"

🔗 Step 6: Add as LangChain Tools

from langchain.tools import Tool

tools = [
    Tool(name="ReadLeads", func=lambda x: str(sheet.get_all_records()), description="List all leads"),
    Tool(name="AddLead", func=lambda x: add_lead(*x.split(',')), description="Add a lead: name,email,status,assigned"),
    Tool(name="UpdateStatus", func=lambda x: update_lead_status(*x.split(',')), description="Update lead status: email,new_status")
]

✅ Step 7: Initialize the Agent

from langchain.agents import initialize_agent, AgentType
from langchain.llms import Ollama  # or OpenAI

llm = Ollama(model="mistral")

agent = initialize_agent(
    tools=tools,
    llm=llm,
    agent=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
    verbose=True
)

# Example Run
query = "Add a lead: Tom Lane, [email protected], Interested, Alice"
response = agent.run(query)
print(response)

💡 Example Use Cases

Prompt Agent Action
“List all leads marked as ‘New’” Uses ReadLeads, filters for “New”
“Add a lead named Maya for solar panels” Adds Maya with default status
“Update status of [email protected] to ‘Closed’” Calls UpdateStatus()

🎨 Optional: Streamlit Interface

import streamlit as st

st.title("📊 AI Agent + Google Sheets")

query = st.text_input("Ask your AI to interact with your sheet:")
if st.button("Submit"):
    result = agent.run(query)
    st.write("🤖 Response:", result)

🧠 Bonus: Dynamic Prompt Templates

To improve reliability:

from langchain.prompts import PromptTemplate

template = PromptTemplate(
    input_variables=["instruction"],
    template="""
You are a sales assistant that interacts with a Google Sheet of customer leads.
Instruction: {instruction}
Only perform the instruction using the tools available.
"""
)

✅ Summary

Step Achievement
🔌 Connected to Sheets Via gspread and Google APIs
🧠 Created agent tools For reading, writing, updating
🧾 Allowed spreadsheet data-driven AI Contextual responses based on live data
📊 Optional UI with Streamlit For teams to access interactively

 

66
PreviousNext