Google Sheets

Ask questions directly within Google Sheets and get AI-powered answers inserted into your spreadsheet.

Overview

The Chat Aid Google Sheets integration allows you to:

• Select cells containing questions
• Automatically query your Chat Aid knowledge base
• Receive answers inserted in adjacent cells
• Get source citations as clickable hyperlinks

This is perfect for:

• Batch processing multiple questions
• Creating FAQ documents
• Building knowledge base exports
• Documenting Q&A workflows

Setup

1. Get Your API Key

1. Navigate to Settings → Custom APIs
2. Click + New API Key
3. Configure your API key settings
4. Copy your API key

See API Guide - Getting Started for details.

2. Open Google Sheets Apps Script

1. Open a blank Google Sheet (or existing sheet)
2. Click Extensions → Apps Script
3. Delete any default code in the editor

3. Add the Chat Aid Script

Copy and paste the following script into the Apps Script editor:

Google Sheets Chat Aid Script

// ===========================
// CONFIGURATION
// ===========================

// Replace with your Chat Aid API key from Settings → Custom APIs
var CHATAID_API_KEY = "YOUR_API_KEY_HERE";

// Optional: Text to remove from the beginning of responses
// Set to empty string ("") if you don't want to remove any text
var REMOVE_OPENING_TEXT = "";

// ===========================
// SCRIPT (Do not modify below)
// ===========================

// This function sets up the custom menu item when the spreadsheet is opened.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('API Actions')
    .addItem('Ask Chat Aid', 'runForSelectedCells')
    .addToUi();
}

// This function is called when you select "Ask Chat Aid" from the custom menu.
async function runForSelectedCells() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange(); // Get the currently selected range
  var values = range.getValues(); // Get the values of the selected range

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      var inputValue = values[i][j]; // This is the value of the current cell
      if (inputValue) { // Check if the cell is not empty
        const a = range.getRow() + i;
        const b = range.getColumn() + j;
        callHttpPostApi(inputValue, a, b).then((data) => {
          const response = data.data;
          sources = [];
          if (response['sources'] && response['sources']['raw']) {
            sources = response['sources']['raw'];
          }

          // Process the response text
          var responseText = response['response'];
          
          // Remove opening text if configured
          if (REMOVE_OPENING_TEXT && responseText.length > REMOVE_OPENING_TEXT.length) {
            if (responseText.substr(0, REMOVE_OPENING_TEXT.length) == REMOVE_OPENING_TEXT) {
              responseText = responseText.substr(REMOVE_OPENING_TEXT.length, 1).toUpperCase() + 
                           responseText.substr(REMOVE_OPENING_TEXT.length + 1, responseText.length);
            }
          }

          sheet.getRange(data.x, data.y + 1).setValue(responseText); // Write the response next to the input value
          SpreadsheetApp.flush();

          // Write source hyperlinks
          for (var k = 0; k < sources.length; k++) {
            var source = sources[k];
            sheet.getRange(data.x, data.y + k + 2).setFormula('=HYPERLINK("' + source.url + '", "' + source.name + '")');
          }
        }).catch((error) => {
          sheet.getRange(a, b + 1).setValue(error); // Write the error
        });
      }
    }
  }
}

function callHttpPostApi(inputValue, x, y) {
  return new Promise(async (resolve, reject) => {
    var url = "https://api.chataid.com/chat/completions/custom";
    var payload = {
      "prompt": inputValue
    };

    var options = {
      "method": "post",
      "contentType": "application/json",
      "headers": {
        "Authorization": CHATAID_API_KEY
      },
      "payload": JSON.stringify(payload),
      "timeoutSeconds": 600,
      "muteHttpExceptions": true,
    };

    try {
      response = UrlFetchApp.fetch(url, options);
      Logger.log("API Response: ");
      Logger.log(response);
      
      if (response.getResponseCode() == 200) {
        var json = response.getContentText();
        var data = JSON.parse(json);
        
        // Assuming initial request starts the process and provides a polling endpoint
        if (data.pollEndpoint) {
          const response = await pollForResult(data.pollEndpoint);
          resolve({ data: response, x: x, y: y });
        } else {
          resolve({ data: data, x: x, y: y }); // Return data if there is no need to poll
        }
      }
    } catch (error) {
      Logger.log("Error starting the API request: " + error);
      reject({ response: "Error occurred", sources: [] }); // Return error response
    }
  });
}

async function pollForResult(pollEndpoint) {
  var pollOptions = {
    "method": "get",
    "headers": {
      "Authorization": CHATAID_API_KEY
    },
    "timeoutSeconds": 600,
    "muteHttpExceptions": true
  };

  var attempts = 0, maxAttempts = 120;
  while (attempts < maxAttempts) {
    Utilities.sleep(2000);
    try {
      var pollResponse = UrlFetchApp.fetch(pollEndpoint, pollOptions);
      Logger.log("Poll Response: ");
      Logger.log(pollResponse);
      
      var pollJson = pollResponse.getContentText();
      var pollData = JSON.parse(pollJson);
      Logger.log(pollData);
      
      if (pollData.data && pollData.data.response) {
        return pollData.data; // Successful data retrieval
      }
    } catch (error) {
      Logger.log("Polling attempt #" + (attempts + 1) + " failed: " + error);
    }
    attempts++;
  }
  return { response: "Error: Polling completed without success.", sources: [] }; // Return default error response after max attempts
}

4. Configure the Script

At the top of the script, update the configuration:

// Replace with your Chat Aid API key
var CHATAID_API_KEY = "sk_live_abc123...";

// Optional: Remove opening text from responses
var REMOVE_OPENING_TEXT = "At Your Company, ";

Configuration Options:

• CHATAID_API_KEY: Your API key from Settings → Custom APIs
• REMOVE_OPENING_TEXT: Optional text to remove from the start of responses (e.g., company name). Set to "" to disable.

5. Save and Authorize

1. Click the Save icon (💾) or press Ctrl+S / Cmd+S
2. Name your project (e.g., "Chat Aid Integration")
3. Click Run (▶️) to execute onOpen once
4. When prompted, click Review Permissions
5. Select your Google account
6. Click Advanced → Go to [Project Name] (unsafe)
7. Click Allow

First Run

You must run the script once and authorize it before the menu item will appear.

Usage

Asking Questions

1. Enter questions in cells (one per cell):

   | What is our refund policy? |
   | How do I reset my password? |
   | What are the shipping options? |

2. Select the cells containing your questions

3. Click API Actions → Ask Chat Aid from the menu

4. Wait for answers to appear in adjacent cells:

   | What is our refund policy? | Our refund policy allows... | [Source 1] | [Source 2] |
   | How do I reset my password? | To reset your password... | [Source 1] |

Output Format

For each question:

• Column +1: Answer text
• Column +2: First source (clickable hyperlink)
• Column +3: Second source (clickable hyperlink)
• Column +4: Additional sources...

Best Practices

1. Batch Processing: Select multiple cells to process many questions at once
2. Leave Space: Ensure columns to the right are empty for answers and sources
3. Clear Questions: Write clear, specific questions for better answers
4. Monitor Progress: Watch as answers populate (this may take a few seconds per question)
5. Error Handling: If an error occurs, the error message will appear in the answer column

Troubleshooting

Menu Item Not Appearing

Problem: "API Actions" menu doesn't show after adding script

Solutions:

1. Click the Run button (▶️) in Apps Script editor
2. Complete the authorization flow
3. Refresh your Google Sheet
4. Reopen the spreadsheet

Authorization Errors

Problem: Script fails with "Authorization required"

Solutions:

1. Verify your API key is correct in CHATAID_API_KEY
2. Check that your API key is active in Chat Aid Settings
3. Ensure no extra spaces or quotes around the API key
4. Generate a new API key if needed

No Response in Cells

Problem: Nothing happens after clicking "Ask Chat Aid"

Solutions:

1. Check the Apps Script logs: View → Logs
2. Verify the selected cells contain text
3. Ensure columns to the right are empty
4. Check your API key has sufficient question credits
5. Wait longer (complex questions can take 30-60 seconds)

Timeout Errors

Problem: "Error: Polling completed without success"

Solutions:

1. The question may be too complex or data sources are slow
2. Try asking a simpler question first
3. Check your data sources are active and trained
4. Contact support if the issue persists

Limitations

Each question you process may take between 5 and 60 seconds to complete, depending on its complexity. Google Apps Script enforces execution time limits, with each script run allowing up to 6 minutes. To avoid timeouts and ensure successful processing, it is recommended to process only a few questions at a time.

Example Use Cases

Creating an FAQ Document

1. List all common questions in Column A
2. Select all question cells
3. Run API Actions → Ask Chat Aid
4. Export as PDF or share the sheet

Building a Knowledge Base

1. Organize questions by category in different sheets
2. Process each category separately
3. Review and edit answers as needed
4. Use as internal documentation

Customer Support Templates

1. Create templates with common questions
2. Generate fresh answers when data sources update
3. Copy answers into support tickets or emails

Related Documentation

• API Guide - Getting Started: Create and configure API keys
• API Guide - Asking Questions: Complete API documentation
• Custom APIs Settings: Manage API keys and permissions
• Data Sources: Connect data sources for better answers

---

Need Help?

Our support team is here to help you get the most out of Chat Aid.

• 📧 Email us at support@chataid.com
• 📅 Schedule a call with our team
• 📖 Browse our documentation