langchain-sheetbase 0.1.0
LangChain integration for Google Sheets as database with conversational memory and RAG retriever
License: MIT
Requires Python: <3.13,>=3.10
Keywords: langchain, google-sheets, database, memory, rag, retriever, ai, llm


---

## 🔧 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.