Data Dip Using Google Sheets

Why This Guide Exists

Data dipping is one of the most powerful tools available in a contact center — it lets you pull live, contextual customer information into your call flows so agents and IVR systems can respond intelligently. Traditionally, this means standing up a database, building middleware, exposing secure APIs, and maintaining infrastructure that requires a dedicated IT team.

But here's the secret: you don't need any of that to get started. If your customer data already lives in a Google Sheet, you have everything you need to build a fully functional data dip endpoint — for free. This guide will walk you through doing exactly that, with an AI assistant doing most of the heavy lifting.

How It All Fits Together

Caller dials in → Xima collects a value (e.g., phone number or an account number)
                ↓
        Xima sends value to Google Apps Script (your API)
                ↓
        Script searches your Google Sheet for a match
                ↓
        Script returns matching data back to Xima
                ↓
   Agent sees details / IVR personalizes / Data saved to record

Part 1: Generate Your Apps Script with AI

Rather than writing the script from scratch, you'll use an AI assistant (Claude or Gemini both work well) to generate a customized script tailored to your sheet. The prompt below walks the AI through asking you every question it needs to build the right script for your setup.

How to Use the Prompt

1. Open your preferred AI tool (Claude or Gemini recommended).
2. Copy the entire prompt from the code block below.
3. Paste it into the AI chat as your first message.
4. Answer the AI's questions one at a time as it walks you through configuration.
5. When finished, the AI will provide your customized script and deployment instructions.

The Prompt to Copy

Copy everything inside the code block below and paste it into your AI tool as your first message:

`````
I'd like to create a Google Sheets Apps Script that will serve as a data dip endpoint for Xima CCaaS.

Please ask me the following questions one at a time:

Q1: Generate a new random 32-character bearer token for authentication. Provide it to me and suggest I save it for future reference, as I'll need it again when configuring Xima.

Q2: Ask me what queryKey I'd like to use. A queryKey is what parameter Xima will use to search the sheet. The most commonly used keys are a phone number, or a value the caller has entered when prompted (such as a PIN, AccountNumber, OrderNumber, etc.).

Ask what the queryKey will be named (it must match a column header in my sheet exactly), and ask whether the value is a phone number.

If I confirm the queryKey is a phone number, automatically include phone number normalization logic in the script — do not ask whether to include it. Just let me know that you've added normalization that strips special characters (parentheses, dashes, dots, spaces, plus signs) and removes a leading "1" country code from both the incoming value and the values in the sheet, so mismatches in formatting won't cause lookup failures in either direction.

Q3: Ask what I'd like to happen if the queryKey value doesn't find a record in the sheet. Suggest returning the queryKey back with a value of "Not Found" as a clean fallback.

Q4: Ask what matching values I'd like returned from the sheet when a record is found. These should match column names from my sheet exactly. Give me examples of commonly returned values (such as Name, AccountStatus, Balance, DueDate, LastContact, etc.) and then ask me to list the column names from my actual sheet that I want returned.

Q5: For each returned value, repeat the value back to me and ask (you can combine the questions):
- Would I like this value to display to agents? (If yes, set "displayToAgent" to true)
- Would I like this value to save to the database/call record? (If yes, set "saveToDatabase" to true)

Repeat my choices back with emoji indicators — ✅ for yes and ❌ for no.

Then, before moving to the next value, ask if I'd like the value name returned as it appears in the sheet, or if I'd like to rename it for the response sent back to Xima. If I request a rename, adjust the script accordingly and confirm back to me what the original column name was and what it will now display as in the returned results.

Q6: After all values are configured, ask if there are any additional requests or custom logic for the script before you generate the final version.

---

SCRIPT FORMAT REQUIREMENTS

The generated script MUST follow the exact structure, formatting, comments, and conventions of the reference template below. Use this as your template — substitute only:
  - {BEARER_TOKEN} → the 32-character token from Q1
  - {QUERY_KEY} → the queryKey name from Q2
  - The normalization block → include the phone-number normalization helper and apply it to both the incoming queryValue and the sheet value if Q2 confirmed a phone number; if not a phone number, omit the helper function and compare values directly using ===
  - The "Not Found" fallback parameters → match the structure I chose in Q3
  - The matched-record "parameters" block → one entry per returned column from Q4, using the renamed response key (if I renamed it) as the JSON key and the original sheet column name inside headers.indexOf(), with displayToAgent and saveToDatabase set based on my Q5 answers

Do not add unrequested features, alter the response JSON structure, or change error message wording.

Reference template:

```javascript
// Define the Bearer Token for Authentication
const BEARER_TOKEN = '{BEARER_TOKEN}';

// Main function to handle POST requests
function doPost(e) {
  // Step 1: Authentication (using URL parameter for token)
  const token = e.parameter.token; // Retrieve token from URL parameter
  if (token !== BEARER_TOKEN) {
    return ContentService.createTextOutput(JSON.stringify({ error: "Unauthorized access" })).setMimeType(ContentService.MimeType.JSON);
  }

  // Step 2: Parse JSON body to get queryKey
  let requestBody;
  try {
    requestBody = JSON.parse(e.postData.contents);
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({ error: "Invalid JSON format" })).setMimeType(ContentService.MimeType.JSON);
  }

  const queryKey = "{QUERY_KEY}";
  let queryValue = requestBody[queryKey];

  if (!queryValue) {
    return ContentService.createTextOutput(JSON.stringify({ error: `Missing ${queryKey} in request body` })).setMimeType(ContentService.MimeType.JSON);
  }

  // Normalize the queryValue by removing the leading '1' if it exists
  queryValue = queryValue.startsWith("1") && queryValue.length === 11 ? queryValue.slice(1) : queryValue;

  // Step 3: Access the Google Sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Replace with your sheet name
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const queryIndex = headers.indexOf(queryKey);

  // Check if queryKey column exists
  if (queryIndex === -1) {
    return ContentService.createTextOutput(JSON.stringify({ error: `${queryKey} column not found` })).setMimeType(ContentService.MimeType.JSON);
  }

  // Step 4: Find matching row and collect data
  let result = null;
  for (let i = 1; i < data.length; i++) {
    // Normalize the phone numbers in the sheet by removing the leading '1' if present
    let sheetPhoneNum = data[i][queryIndex].toString();
    sheetPhoneNum = sheetPhoneNum.startsWith("1") && sheetPhoneNum.length === 11 ? sheetPhoneNum.slice(1) : sheetPhoneNum;

    if (sheetPhoneNum == queryValue) {
      result = {
        "parameters": {
          "Name": {
            "value": data[i][headers.indexOf("name")],
            "displayToAgent": true,
            "saveToDatabase": true
          },
          "PIN": {
            "value": data[i][headers.indexOf("PIN")],
            "displayToAgent": true,
            "saveToDatabase": true
          },
          "Company": {
            "value": data[i][headers.indexOf("company")],
            "displayToAgent": true,
            "saveToDatabase": true
          }
        }
      };
      break;
    }
  }

  // Step 5: Return response in JSON format
  if (result) {
    return ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON);
  } else {
    return ContentService.createTextOutput(JSON.stringify({
      parameters: {
        name: {
          value: "Not Found",
          displayToAgent: true,
          saveToDatabase: true
        },
        PIN: {
          value: "Not Found",
          displayToAgent: true,
          saveToDatabase: true
        },
        company: {
          value: "Not Found",
          displayToAgent: true,
          saveToDatabase: true
        }
      }
    })).setMimeType(ContentService.MimeType.JSON);
  }
}
```

Note: the reference template above uses the simpler inline phone normalization shown in Step 4. If Q2 confirmed a phone number, you may upgrade the normalization to a dedicated helper function that strips ALL non-digit characters (parentheses, dashes, dots, spaces, plus signs) in addition to the leading "1", and apply that helper to both the incoming queryValue and the sheet value. If Q2 did NOT confirm a phone number, remove the normalization lines entirely and compare values directly.

---

DEPLOYMENT AND XIMA CONFIGURATION

Once I've answered all questions, provide the complete updated script along with step-by-step instructions on how to apply it as a new Apps Script in a Google Sheet and deploy it as a web app.

Next, ask me these questions one by one:

1. "Did you grab the Web App URL? It should look something like https://script.google.com/macros/s/[long-string]/exec — please share that with me."

Once I share the URL, combine it with "?token=" and the bearer token to create the full "API URL" and provide it to me.

Then instruct me to do the following in Xima CCaaS:
- Open Xima CCaaS and build a new Data Dip Profile
- Select "+ Add New Profile"
- Name the Profile something recognizable

Remind me that although the token is technically already in the URL (a requirement for Google Apps Script), the "API Token" field in Xima is still required, so I should put the bearer token there as well. Remind me of the token value.

Next, instruct me to:
- Open the Request Parameter menu using the "Edit" button
- Click "+ Add Request Parameter"
- Place the queryKey in the "Parameter Key" field (remind me of the queryKey value)
- Select the "Parameter Type" — Standard, Session, or Static — based on where the queryKey value will come from in my call flow
- Apply changes, add any optional announcements or tones, and save
- Perform a test call

Ask me if the test worked, since I can verify by checking Cradle to Grave or CCAC.

If the test failed, encourage me to test with Postman using these steps:
1. Create a new request in Postman
2. Set the request type to POST
3. Use the full API URL (remind me of it, including the token)
4. In the request Body, select "raw" and use this format, replacing the queryKey with my actual key:
{
    "[queryKey]": "<example value>"
}
5. Send the request and review the response

If the Postman test also fails, ask me to share the returned body so we can troubleshoot the error together.
`````

Part 2: Apply the Script to Your Google Sheet

Once the AI has generated your customized script, here's how to deploy it:

1. Open your Google Sheet — the one containing the customer data you want to dip into.

2. From the top menu, click Extensions → Apps Script.

3. In the Apps Script editor, delete any default code in the Code.gs file.

4. Paste the script the AI provided into the editor.

5. Click the 💾 Save icon (or press Ctrl/Cmd + S) and give the project a recognizable name.

6. Click Deploy → New deployment.

7. Click the ⚙️ gear icon next to "Select type" → choose Web app.

8. Configure the deployment:

   • Description: Anything recognizable (e.g., "Xima Data Dip v1")
   • Execute as: Me
   • Who has access: Anyone

9. Click Deploy.

10. Authorize the script when prompted. You may see a warning screen — click Advanced → Go to [project name] (unsafe). This warning appears because the script is unpublished/personal, not because anything is actually unsafe.

11. After deployment completes, copy the Web App URL that appears. It will look like:

    https://script.google.com/macros/s/AKfycb.../exec

⚠️

Important: Your sheet column headers must exactly match the queryKey and returned value names you configured (case-sensitive). Also confirm the sheet tab name in the script matches your actual tab (default is Sheet1).

Part 3: Configure the Data Dip Profile in Xima CCaaS

1. Open Xima CCaaS and navigate to Data Dip Profiles.
2. Click "+ Add New Profile".
3. Give the profile a recognizable name that distinguishes it from any other profiles (e.g., "Customer Lookup by Phone").

Set the API URL

Combine your Web App URL with ?token= and your bearer token. The result is your full API URL, and looks like this:

https://script.google.com/macros/s/AKfycb.../exec?token=YourBearerTokenHere

Paste this combined URL into the API URL field.

Set the API Token

The token is already embedded in the URL above (this is required because Google Apps Script handles authentication via URL parameter rather than a header). However, Xima's API Token field is still required — paste your bearer token there as well.

Add the Request Parameter

1. Find the Request Parameter section and click the Edit button to open it.
2. Click "+ Add Request Parameter".
3. In the Parameter Key field, enter your queryKey exactly as configured in the script (case-sensitive).
4. Select the Parameter Type:
   • Standard — typically used for built-in call data like the caller's ANI (phone number)
   • Session — used for values collected during the call (such as IVR input)
   • Static — used for hardcoded values, often helpful for testing
5. Apply your changes.

Finish Up

• Add any announcements or tones you'd like to play during the data dip (optional).
• Save the profile.
• Place a test call to verify the data dip fires correctly.

You can confirm results by reviewing the call in Cradle to Grave or CCAC.

Part 4: Troubleshooting with Postman

If the test call doesn't return the expected data, Postman is a great way to test the endpoint in isolation and see exactly what's happening.

1. Open Postman and create a New Request.

2. Set the request method to POST.

3. In the URL field, paste your full API URL (the one including ?token=YourBearerTokenHere).

4. Click the Body tab and select raw.

5. Enter the following JSON, replacing [queryKey] with your actual queryKey name and <example value> with a real value from your sheet:

   JSON

   {
       "[queryKey]": "<example value>"
   }

   For example, if your queryKey is PhoneNum:

   JSON

   {
       "PhoneNum": "5551234567"
   }

6. Click Send and review the response.

Common Issues

If you're still stuck, copy the response body Postman returns and bring it back to your AI assistant — paste it in and ask for help troubleshooting. The error message almost always points directly to the fix.

You're Done

Once your test call returns the expected data, your Google Sheets data dip is live. You can now:

• Build call flows that route or personalize based on the returned values
• Add additional Data Dip Profiles for different lookup types (one for phone number lookups, one for account number lookups, etc.)
• Expand your sheet with more columns and update the script to return them
• Share the sheet with team members who need to maintain the customer data

For any updates to the script later — adding columns, changing the queryKey, renaming returned values — just paste the prompt into your AI assistant again and walk through the questions with your new requirements.