Search Multiple Google Sheet Tabs with Different Columns

This guide shows how to upgrade the standard single-sheet API from How To Use AI To Search Google Sheet For Data into a multi-sheet, multi-column version, so your AI agent can search across different Google Sheet tabs, each with completely different column structures.

How It Works

Instead of hardcoding one sheet and one set of columns, we use a SHEET_CONFIG object at the top of the script. Each entry maps a sheet tab name to its searchable columns. The script loops through all configured sheets and returns matching results tagged with their source sheet.

The Only Section You Need to Edit

At the top of the script, find SHEET_CONFIG and update it to match your actual sheet tab names and column headers:

const SHEET_CONFIG = {

'Data' : ['id', 'name', 'price', 'description', 'stock_quantity', 'permalink'],

'Table' : ['property_id', 'location', 'price', 'bedroom', 'bathroom', 'size_sqft', 'agent_name'],

'Sheet3': ['client_id', 'client_name', 'email', 'phone', 'status'],

// Add more sheets below as needed

};

Step 1: Set Your API Secret

Before deploying, you need to create a secret key so only your AI agent can access the API.

1. In the Apps Script editor, click the Project Settings (gear icon) on the left sidebar
2. Scroll down to Script Properties and click Add script property
3. Fill in Property: API_SECRET and Value: a strong random string, letters and numbers only, no symbols (e.g. aB3kR9mX2wQ7nL5p)
4. Click Save script properties

Use a password generator and copy the result. No spaces, no symbols, alphabet and numbers only.

Step 2: Deploy as Web App

1. Click Deploy (top right), then New deployment
2. Click the gear icon next to "Select type" and choose Web app
3. Fill in a Description (e.g. Multi-Sheet Search API), set Execute as: Me, and Who has access: Anyone
4. Click Deploy
5. Google will ask you to Authorize access, follow the prompts
6. After success, copy the Web App URL, you'll need it for the Custom Tool

Full Script

Paste this entire script into Extensions → Apps Script, replacing any existing code:

Quick Reference: How to Edit SHEET_CONFIG

• Add a new sheet: add a line like 'MyNewSheet': ['col1', 'col2', 'col3'],
• Remove a sheet: delete its line
• Rename a sheet tab: update the name in the config to match the new tab name, e.g. change 'OldName': ['col1', 'col2'], to 'NewName': ['col1', 'col2'],

Custom Tool Configuration

Configure the custom tool exactly as in the single-sheet guide, with this request URL:

https://<YOUR_WEB_APP_URL>?secret=<YOUR_API_SECRET>&search=

Optional extra dynamic parameter (lets the AI search a specific sheet only):

• Name: sheet
• Type: String
• Location: Query
• Required: No

Suggested System Prompt

Adjust the sheet names and column descriptions to match your actual data:

> You have access to a "search_sheet" tool that searches across multiple data sources:

> Data: Product catalog (id, name, price, description, stock, link)

> Table: Property listings (location, price, bedrooms, bathrooms, size, agent)

> Sheet3: Client records (name, email, phone, status)

> Always use this tool to retrieve accurate, live data before answering. Each result will include a "_source_sheet" field to indicate which sheet it came from.

Key Differences vs. the Single-Sheet Version

• Number of sheets: single-sheet supports 1, multi-sheet is unlimited
• Column structure: single-sheet uses the same columns for all data, multi-sheet allows different columns per sheet
• Result tagging: multi-sheet adds a _source_sheet field to every result
• Optional sheet filter: multi-sheet lets the AI pass ?sheet=SheetName to search one tab only
• Config location: multi-sheet keeps all configuration in the SHEET_CONFIG object at the top of the script