> ## Documentation Index
> Fetch the complete documentation index at: https://docs.autocalls.ai/llms.txt
> Use this file to discover all available pages before exploring further.
# Mid-Call Order Lookup from Google Sheets
> Learn how to look up order data from Google Sheets during a live call using mid-call tools and the automation platform
This tutorial shows how to create a mid-call tool that connects to the automation platform to look up order information from a Google Sheet in real-time during calls. When a customer asks about their order, the AI assistant searches for their phone number in your spreadsheet and retrieves the order details to provide accurate information.
## What You'll Learn
* Creating an automation flow with a webhook trigger and Google Sheets lookup
* Setting up a mid-call tool that connects to the automation platform
* Handling phone number format differences between the tool and Google Sheets
* Looking up order data by customer phone number during a live call
* Using the AI Prompt Editor to configure your assistant's behavior
## Prerequisites
* Google account with Google Sheets access
* AI assistant configured for calls
* Basic understanding of [custom mid-call tools](/ai-assistants/custom-tools)
* Access to the [automation platform](/automation-platform/introduction)
## How It Works
Before we start, here's the big picture of what we're building:
```
Customer asks about order → AI triggers mid-call tool with customer_phone
→ Automation strips "+" from phone number → Looks up row in Google Sheets
→ Returns order details → AI tells the customer their order status
```
The key concept is using the automation platform as a **middleware** between your mid-call tool and Google Sheets. The mid-call tool calls the automation webhook synchronously (with `/sync`), the automation searches your spreadsheet, and returns the data back to the AI in real-time.
## Step 1: Prepare Your Google Sheet
First, set up a Google Sheet with your order data. The AI will search this sheet by phone number during calls.
1. **Create a new Google Sheet**
2. **Set up your columns** — for example:
| customer\_phone | customer\_name | order\_number | order\_status | product |
| --------------- | -------------- | ------------- | ------------- | ------------------- |
| 1234567890 | John Smith | ORD-1001 | Shipped | Smart Watch |
| 0987654321 | Jane Doe | ORD-1002 | Processing | Wireless Headphones |
3. **Add a test row** with your own phone number so you can test later
4. **Rename the spreadsheet** to something descriptive (e.g., "Orders AI")
**Phone number format**: Google Sheets doesn't allow a `+` sign in front of numbers. Store phone numbers **without** the `+` prefix (e.g., `1234567890` instead of `+1234567890`). We'll handle stripping the `+` in the automation flow.
## Step 2: Create the Automation Flow
Now we'll build the automation that receives a phone number from the mid-call tool, searches Google Sheets, and returns the order data.
1. Navigate to the **Automation Platform** by clicking "Automate platform" in the sidebar
2. Click **New Flow** and select **From Scratch**
3. Search for **Webhook** and select **Catch Webhook** as your trigger
4. A live URL will be generated — **copy it**
Don't close this tab. We'll come back to test the trigger after setting up the mid-call tool.
## Step 3: Create the Mid-Call Tool
Open a new tab and navigate to **Mid Call Tools** to create the tool that will call our automation webhook.
1. Click **New Mid Call Tool**
2. Fill in the tool details:
* **Name**: `get_order_status`
* **Description**: `Look up order information using the customer_phone variable from the call. Use this tool when a customer asks about their order status, delivery, or purchase history.`
* **Endpoint**: Paste the webhook URL from Step 2
* **Timeout**: `30` seconds
* **Method**: POST
### Headers
The default `Content-Type: application/json` header is already added. No additional headers are needed for the automation platform webhook.
### Tool Parameters
3. Click **Add parameter** and configure:
* **Name**: `customer_phone`
* **Type**: `String`
* **Description**: `The customer_phone variable from the call`
4. Click **Create** to save the tool
## Step 4: Test the Tool and Send Data to the Automation
After saving, we need to send test data so the automation can capture the webhook payload structure.
1. **Save** the tool
2. Go back to the **automation tab** and click **Load Data** to start listening
3. Go back to the **mid-call tool** and click **Test Tool**
4. Return to the automation tab — you should see the test data arrive with the `customer_phone` field in the body
Now add `/sync` to the end of the webhook URL in the mid-call tool's **Endpoint** field and **save**. With `/sync`, the tool will send the data and wait to receive the response back from the automation.
The `/sync` at the end of the webhook URL is critical. Without it, the mid-call tool won't wait for the automation to finish and return data. With `/sync`, the tool waits for the automation response before continuing the conversation.
## Step 5: Add Split Text Step
Phone numbers from the mid-call tool come with a `+` prefix (e.g., `+1234567890`), but Google Sheets stores them without it. We need to strip the `+` before searching.
1. Click **+** to add a step after the webhook trigger
2. Search for **Text** and select **Split Text**
3. Configure:
* **Text**: Select from Catch Webhook → Body → `customer_phone`
* **Delimiter**: `+`
This splits the phone number by the `+` character. The second part (index 1) will contain the number without the prefix.
## Step 6: Add Google Sheets Lookup
Now we'll add the Google Sheets step that searches for the order by phone number.
1. Click **+** to add another step
2. Search for **Google Sheets** and select **Find Row**
3. Create or select a **Google Sheets connection**
4. Select your **spreadsheet** (e.g., "Orders AI") and **sheet name**
5. Configure the lookup:
* **Column name**: `customer_phone`
* **Search value**: Select the result from the Split Text step (index 1 — the number without `+`)
* **Starting row**: `1`
* **Number of rows**: `1`
For testing, you can temporarily hardcode your own phone number (without the `+`) in the search value field. Once you see the row returned successfully, replace it with the dynamic value from the Split Text step.
## Step 7: Return the Data
The final step is to return the order data back to the mid-call tool so the AI can use it.
1. Click **+** to add another step
2. Search for **Return Response** (under Webhook actions)
3. Set the response body type to **JSON** and configure:
```json theme={null}
{
"customer_name": "{{step_1[0]['values']['B']}}",
"order_number": "{{step_1[0]['values']['C']}}",
"order_status": "{{step_1[0]['values']['D']}}",
"product": "{{step_1[0]['values']['E']}}"
}
```
4. **Map each field** by selecting the values from the Find Row step data (Find Row → Row 0 → Values)
5. **Test this step** to verify you receive the correct data
You can return as many fields as you need. The AI will receive all this data and can use any of it during the conversation.
## Step 8: Publish the Automation
1. Verify all steps are correctly configured
2. Click **Publish** to make the flow live
## Step 9: Assign the Tool to Your Assistant
Now we need to connect the mid-call tool to your AI assistant so it can use it during calls.
1. Navigate to your AI assistant → **Prompts and Tools** tab
2. In the **Custom Tools** section, select `get_order_status` from the dropdown
3. **Save** the assistant
## Step 10: Configure the AI Prompt
Open the **AI Prompt Editor** to instruct the AI on when and how to use the tool. Click **Launch AI Prompt Editor** and describe what you need — for example, tell it to build a prompt that uses the `get_order_status` tool when a customer asks about their order.
The AI Prompt Editor will generate the appropriate system prompt rules. Review them, click **Accept**, then **Save**.
## Step 11: Test with a Real Call
1. Click **Speak to Assistant** to start a live voice test
2. When connected, ask about your order status
3. The assistant should call the `get_order_status` tool, look up your phone number in the Google Sheet, and tell you the order details
If the lookup doesn't return data, check that the phone number in your Google Sheet matches the number without the `+` prefix. Also verify the Split Text step is correctly stripping the `+`.
## Best Practices
### Google Sheets Data
* **Store phone numbers without `+`** — Google Sheets doesn't support `+` as a number prefix, the automation handles stripping it
* **Avoid duplicate phone numbers** — the lookup returns the first match
* **Keep the sheet organized** — remove empty rows between data entries
### Tool Configuration
* **Set a reasonable timeout** (30 seconds) to account for automation processing time
* **Write a clear tool description** so the AI knows exactly when to use it
* **Include fallback instructions** in your prompt for when no data is found
### System Prompt Tips
* **Be specific** about what data fields the AI will receive
* **Provide examples** of how to use the data naturally in conversation
* **Handle edge cases** — empty fields, no match found, etc.
## Troubleshooting
### Common Issues
**Tool returns empty data:**
* Verify the phone number format matches between the Split Text output and Google Sheet
* Check Google Sheets connection authorization
* Test the automation manually with a known phone number
**Tool timeout:**
* Increase the timeout value in the mid-call tool settings
* Check that the automation is published and active
* Verify the `/sync` suffix is on the webhook URL
**AI doesn't use the tool:**
* Review the tool description — make it clear when to use it
* Check the system prompt has explicit instructions to use the tool
* Verify the tool is saved and associated with the correct assistant
**Automation not triggering:**
* Confirm the webhook URL is correct (including `/sync`)
* Check that the automation is published
* Review automation runs for error messages
**Phone number not matching:**
* Ensure the Split Text step is correctly stripping the `+` prefix
* Check that phone numbers in the Google Sheet are stored without `+`
* Verify the Split Text delimiter is set to `+`
## Next Steps
Once your order lookup is working:
* **Add more data columns** to your sheet (tracking number, shipping address, estimated delivery)
* **Combine with post-call automation** to update the sheet after each call
* **Create multiple lookup tools** for different data sources
* **Add a "write" tool** to let the AI update order notes during the call
* **Connect to a CRM or e-commerce platform** instead of Google Sheets for larger datasets