Lesson 2. Google Sheets as a Database#
Goal: learn how to organize data in Google Sheets for agent integration.
Why Google Sheets Is a Good Starting Point#
- familiar interface (like Excel)
- free up to 10 million cells per document
- API for reading and writing
- easy integration with Zapier, Make, n8n
- collaborative editing
How a Table for an Agent Should Be Structured#
The agent treats the sheet like a database, so:
- Row 1 is column headers (field names)
- Each following row is one record (customer, order, task)
- Columns have a clear format (text, number, date, yes/no)
- No empty rows or columns in the middle of the table
- No merged cells or extra formatting
Example: "Customers" Table#
| ID | Name | Phone | Status | Registration Date | |
|---|---|---|---|---|---|
| 1 | Ivan Petrov | ivan@example.com | +79001234567 | Active | 2026-01-15 |
| 2 | Maria Sidorova | maria@example.com | +79007654321 | Lead | 2026-01-20 |
| 3 | Alexey Kovalev | alex@example.com | +79009876543 | Inactive | 2025-12-10 |
What matters:
- ID — unique record number (so you don't mix up two Ivan Petrovs)
- Data format — phone always in one format, date in YYYY-MM-DD
- Status — only three values (not "active," "Active," "active")
- No empty rows between records
How to Connect Google Sheets to an Agent#
Step 1. Prepare the sheet
- fill row 1 with headers
- ensure there are no empty rows or columns
- check data formats (dates, numbers, text)
Step 2. Set sharing
- click "Share" → "Anyone with the link" (or grant access to a service account)
- if using API — create a service account in Google Cloud Console (for advanced setups)
Step 3. Connect via integration
- in Zapier / Make / n8n select the "Google Sheets" module
- authorize (grant access to the sheet)
- choose document and sheet
- configure action: "Read rows," "Add row," "Update row"
Step 4. Test
- run a test — confirm data is read and written correctly
- verify new rows are appended to the end, not inserted randomly
Common Google Sheets Mistakes#
| Mistake | Why it's a problem | How to fix |
|---|---|---|
| Empty rows inside the table | Agent can't tell where data ends | Remove all empty rows between records |
| Merged cells | API can't read the structure | Unmerge cells, use one cell per field |
| Mixed formats in one column | "+7 900 123-45-67" and "89001234567" are different | Use a single format |
| No headers | Agent doesn't know what each column means | Add a first row with field names |
| Formulas in cells | API reads the formula, not the result | Copy values, not formulas |
Practical Example: Lead Qualification Bot#
Task: a customer writes to a Telegram bot; the bot checks if the customer exists in a Google Sheets base.
"Customers" table:
| Name | Status | Last Contact Date | |
|---|---|---|---|
| ivan@example.com | Ivan | Active | 2026-01-30 |
| maria@example.com | Maria | Lead | 2026-01-25 |
Agent logic:
- Customer enters email in the bot
- Agent queries Google Sheets: "Find row where Email = [entered email]"
- If found → agent replies: "Hello, [Name]! Your status: [Status]"
- If not found → agent replies: "You're a new customer. Shall we register you?"
- If customer agrees → agent adds a new row to the table
Tools: Zapier (for the chatbot) + Google Sheets (for data) + integration via API or Zapier.