langchain-sheetbase 0.1.0

LangChain integration for Google Sheets as database with conversational memory and RAG retriever

---

🔧 Google Apps Script - Complete Setup

Why Apps Script Mode?

Apps Script Mode unlocks full CRUD operations (Create, Update, Delete) while still bypassing the Google Cloud API:

• ✅ No Google Cloud project needed
• ✅ No API quotas or billing
• ✅ Deploy once, use forever
• ✅ 2-minute setup (copy-paste script)

Step-by-Step Setup

1. Open Apps Script Editor

1. Open your Google Sheet
2. Click Extensions → Apps Script
3. Delete any default code
4. Copy the script below

2. Copy This Complete Script

/**

SheetBase Apps Script - Full CRUD API

Provides REST API for Google Sheets with:

GET: Search and retrieve records

POST: Create, update, delete records

Author: Charles Lepoittevin (SheetBase)

Version: 2.0.0

License: MIT */

// ═══════════════════════════════════════════════════════════════════ // Configuration // ═══════════════════════════════════════════════════════════════════

const CONFIG = { SHEET_NAME: 'Sheet1', // Change to your sheet tab name MAX_RESULTS: 1000, // Maximum records to return FUZZY_THRESHOLD: 0.2, // Search sensitivity (0.0-1.0) ENABLE_LOGGING: true // Enable detailed logging };

// ═══════════════════════════════════════════════════════════════════ // GET Request Handler - Search & Retrieve // ═══════════════════════════════════════════════════════════════════

/**

Handle GET requests (search and retrieval)

Query parameters:

q: Search keywords (space-separated, OR supported)

user_id: Filter by user ID (for memory operations)

limit: Maximum results (default: 100) */ function doGet(e) { try { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);

if (!sheet) { return jsonResponse({ success: false, error: Sheet "${CONFIG.SHEET_NAME}" not found }, 404); }

const data = sheet.getDataRange().getValues();

if (data.length === 0) { return jsonResponse({ success: true, data: [], count: 0 }); }

const headers = data; let records = data.slice(1).map((row, idx) => { const record = { __row: idx + 2 }; headers.forEach((header, i) => { record[header] = row[i]; }); return record; });

const params = e.parameter || {}; const query = params.q || ''; const userId = params.user_id || ''; const limit = parseInt(params.limit || '100');

if (query) { const keywords = query.toLowerCase().split(' ').filter(k => k.length > 0); records = records.filter(record => { const text = JSON.stringify(record).toLowerCase(); return keywords.some(kw => text.includes(kw)); }); }

if (userId && records.length > 0) { records = records.filter(record => { return record['user_id'] === userId || record['UserID'] === userId; }); }

if (limit > 0 && limit < records.length) { records = records.slice(0, limit); }

logInfo(GET request: Found ${records.length} records (query="${query}", user_id="${userId}"));

return jsonResponse({ success: true, data: records, count: records.length });

} catch (error) { logError('GET request failed', error); return jsonResponse({ success: false, error: error.toString() }, 500); } }

// ═══════════════════════════════════════════════════════════════════ // POST Request Handler - Create, Update, Delete // ═══════════════════════════════════════════════════════════════════

/**

Handle POST requests (CRUD operations)

Request body examples:

Create: {"operation": "create", "values": {"Product": "USB Cable", "Price": "9.99"}}

Update: {"operation": "update", "rowNumber": 5, "updates": {"Price": "29.99"}}

Delete: {"operation": "delete", "rowNumber": 5} */ function doPost(e) { try { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);

if (!sheet) { return jsonResponse({ success: false, error: Sheet "${CONFIG.SHEET_NAME}" not found }, 404); }

const body = JSON.parse(e.postData.contents); const operation = body.operation;

if (!operation) { return jsonResponse({ success: false, error: 'Missing "operation" field in request body' }, 400); }

switch (operation.toLowerCase()) { case 'create': return handleCreate(sheet, body);

case 'update': return handleUpdate(sheet, body);

case 'delete': return handleDelete(sheet, body);

default: return jsonResponse({ success: false, error: Unknown operation: ${operation}. Use "create", "update", or "delete" }, 400); }

} catch (error) { logError('POST request failed', error); return jsonResponse({ success: false, error: error.toString() }, 500); } }

// ═══════════════════════════════════════════════════════════════════ // CRUD Operation Handlers // ═══════════════════════════════════════════════════════════════════

function handleCreate(sheet, body) { if (!body.values || typeof body.values !== 'object') { return jsonResponse({ success: false, error: 'Missing or invalid "values" field' }, 400); }

const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); const newRow = headers.map(header => body.values[header] || '');

sheet.appendRow(newRow); const rowNumber = sheet.getLastRow();

logInfo(CREATE: Added row ${rowNumber} with data: ${JSON.stringify(body.values)});

return jsonResponse({ success: true, message: 'Record created successfully', rowNumber: rowNumber, data: body.values }); }

function handleUpdate(sheet, body) { if (!body.rowNumber || typeof body.rowNumber !== 'number') { return jsonResponse({ success: false, error: 'Missing or invalid "rowNumber" field' }, 400); }

if (!body.updates || typeof body.updates !== 'object') { return jsonResponse({ success: false, error: 'Missing or invalid "updates" field' }, 400); }

const rowNumber = body.rowNumber; const lastRow = sheet.getLastRow();

if (rowNumber < 2 || rowNumber > lastRow) { return jsonResponse({ success: false, error: Invalid row number: ${rowNumber}. Must be between 2 and ${lastRow} }, 400); }

const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

Object.keys(body.updates).forEach(columnName => { const colIndex = headers.indexOf(columnName); if (colIndex !== -1) { sheet.getRange(rowNumber, colIndex + 1).setValue(body.updates[columnName]); } });

logInfo(UPDATE: Modified row ${rowNumber} with updates: ${JSON.stringify(body.updates)});

return jsonResponse({ success: true, message: Row ${rowNumber} updated successfully, rowNumber: rowNumber, updates: body.updates }); }

function handleDelete(sheet, body) { if (!body.rowNumber || typeof body.rowNumber !== 'number') { return jsonResponse({ success: false, error: 'Missing or invalid "rowNumber" field' }, 400); }

const rowNumber = body.rowNumber; const lastRow = sheet.getLastRow();

if (rowNumber < 2 || rowNumber > lastRow) { return jsonResponse({ success: false, error: Invalid row number: ${rowNumber}. Must be between 2 and ${lastRow} }, 400); }

sheet.deleteRow(rowNumber);

logInfo(DELETE: Removed row ${rowNumber});

return jsonResponse({ success: true, message: Row ${rowNumber} deleted successfully, rowNumber: rowNumber }); }

// ═══════════════════════════════════════════════════════════════════ // Utility Functions // ═══════════════════════════════════════════════════════════════════

function jsonResponse(data, statusCode = 200) { return ContentService .createTextOutput(JSON.stringify(data)) .setMimeType(ContentService.MimeType.JSON); }

function logInfo(message) { if (CONFIG.ENABLE_LOGGING) { console.log([SheetBase] ${message}); } }

function logError(message, error) { console.error([SheetBase ERROR] ${message}:, error); }

3. Deploy as Web App

1. Click Deploy → New deployment
2. Click the gear icon ⚙️ → Select Web app
3. Fill in the form:
   • Description: SheetBase API v1
   • Execute as: Me (your Google account)
   • Who has access: Anyone ⚠️ (Important!)
4. Click Deploy
5. Click Authorize access
   • Select your Google account
   • Click Advanced → Go to [Project Name] (unsafe)
   • Click Allow
6. Copy the Web App URL (looks like): https://script.google.com/macros/s/AKfycbxxxxxxxxxxxxx/exec

4. Test Your Deployment

Test GET (search): curl "https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec?q=laptop&limit=5"

Expected response: { "success": true, "data": [ {"__row": 2, "Product": "Gaming Laptop", "Price": "999"}, {"__row": 3, "Product": "Laptop Stand", "Price": "29"} ], "count": 2 }

Test POST (create): curl -X POST "https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec" -H "Content-Type: application/json" -d '{"operation": "create", "values": {"Product": "USB Cable", "Price": "9.99"}}'

5. Use in Python

from langchain_sheetbase import create_sheetbase_tools

tools = create_sheetbase_tools( mode='appsscript', apps_script_url='https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec' )

Now you have full CRUD operations!

Configuration Options

Edit the CONFIG object at the top of the script:

const CONFIG = { SHEET_NAME: 'Sheet1', // Change to your sheet tab name MAX_RESULTS: 1000, // Limit search results FUZZY_THRESHOLD: 0.2, // Search sensitivity (0.0-1.0) ENABLE_LOGGING: true // Set to false in production };

Troubleshooting

Error: "Script function not found: doGet"

• Solution: Make sure you deployed as Web app, not API executable

Error: "Authorization required"

• Solution: Redeploy with "Who has access" set to Anyone

Error: "Sheet not found"

• Solution: Update CONFIG.SHEET_NAME to match your sheet tab name

No data returned

• Solution: Ensure your sheet has a header row (row 1) with column names

Updating the Script

When you modify the script:

1. Save changes (Ctrl+S / Cmd+S)
2. Click Deploy → Manage deployments
3. Click ✏️ Edit icon
4. Change Version to New version
5. Click Deploy

⚠️ The URL stays the same - no need to update your Python code!

Memory Schema (Optional)

If using SheetBase for conversational memory, add these columns to your sheet:

timestamp	user_id	user_request	agent_response
2025-10-27T20:00:00Z	user_123	What laptops?	We have 5 gaming...

The SheetBaseMemory class will automatically use this schema.