A data dip traditionally requires a database, middleware, and an exposed API endpoint — infrastructure that demands dedicated IT resources. If customer data already lives in a Google Sheet, that sheet can serve as a fully functional data dip endpoint at no cost. This guide walks through generating the required Apps Script using an AI assistant, deploying it as a web app, and connecting it to CCaaS.

How It Fits Together

flowchart TD
    A[Caller dials in] --> B["CCaaS collects a value (e.g., phone number or account number)"]
    B --> C["CCaaS sends the value to Google Apps Script (the API endpoint)"]
    C --> D["Script searches the Google Sheet for a matching record"]
    D --> E["Script returns matching data back to CCaaS"]
    E --> F["Agent sees details / IVR personalizes / data saved to call record"]

Part 1: Generate the Apps Script with AI

Rather than writing the script from scratch, an AI assistant generates a customized script based on the sheet's structure. The prompt below walks the AI through every required configuration question.

How to Use the Prompt

1. Open an AI tool (Claude or Gemini recommended)
2. Copy the entire prompt from the code block below
3. Paste it into the AI chat as the first message
4. Answer each question as the AI walks through configuration
5. The AI will provide a customized script and deployment instructions when finished

The Prompt

Copy everything inside the code block and paste it into the AI tool as the 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;
  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");
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const queryIndex = headers.indexOf(queryKey);

  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++) \{
    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
  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);
  }
\}
```
`````

Part 2: Deploy the Script

Once the AI provides the customized script:

1. Open the Google Sheet containing the customer data
2. From the top menu, click Extensions → Apps Script
3. Delete any default code in the Code.gs file
4. Paste the AI-generated script into the editor
5. Click Save (or 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:
   • Execute as: Me
   • Who has access: Anyone
9. Click Deploy
10. Authorize the script when prompted — if a warning screen appears, click Advanced → Go to [project name] (unsafe). This warning appears because the script is unpublished, not because it is unsafe.
11. Copy the Web App URL after deployment completes — it will look like:

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

Part 3: Configure the Data Dip Profile in CCaaS

1. Navigate to Routing Configuration > Data Dip Profiles
2. Click Add New Profile and give the profile a recognizable name

API URL

Combine the Web App URL with ?token= and the bearer token to form the full API URL:

https://script.google.com/macros/s/AKfycb.../exec?token={bearer_token}

Paste this into the API URL field.

API Token

The token is embedded in the URL above (required by Google Apps Script, which handles authentication via URL parameter rather than a header). Paste the bearer token into the API Token field as well — CCaaS requires a value in this field.

Request Parameter

1. Locate the Request Parameters section and click Edit
2. Click Add Request Parameter
3. Enter the queryKey in the Parameter Key field — must match exactly as configured in the script (case-sensitive)
4. Select the Parameter Type:
   • Standard — built-in call data such as the caller's ANI (phone number)
   • Session — a value collected during the IVR flow (such as caller input)
   • Static — a hardcoded value; useful for testing
5. Click Apply

Add any optional announcements or processing tones, then click Save and place a test call. Results can be verified in Cradle-to-Grave or CCAC.

Part 4: Troubleshooting with Postman

If the test call does not return expected data, Postman can be used to test the endpoint in isolation.

1. Open Postman and create a new POST request
2. Paste the full API URL (including ?token=...) into the URL field
3. Click the Body tab, select raw, and enter:
   JSON

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

   For example, if the queryKey is PhoneNum:
   JSON

   {
       "PhoneNum": "5551234567"
   }

4. Click Send and review the response

Common Errors

If the Postman test also fails, copy the response body and share it with an AI assistant for targeted troubleshooting.

Wrapping Up

Once a test call returns the expected data, the Google Sheets data dip is live. From here:

• Call flows can route or personalize based on the returned values
• Additional Data Dip Profiles can be added for different lookup types — for example, one for phone number lookups and another for account number lookups
• The sheet can be expanded with more columns, with the script updated to return them
• The sheet can be shared with team members who maintain the customer data

For future script updates — adding columns, changing the queryKey, renaming returned values — the same prompt can be reused with an AI assistant, walking through the questions again with the new requirements.