Draft FAQ answers from a knowledge base

Northwind’s support inbox sees the same questions over and over, and every answer should say the same thing — but written from scratch each time, they drift. One agent quotes a 30-day refund window, another says “about a month”, and a customer notices. The fix is not more discipline; it is a single source of truth that the answers are drafted from.

This script keeps that source of truth in a knowledge-base Doc and a queue of unanswered questions in a Sheet. For each question still missing an answer, it asks Claude to draft a reply using only the facts in the Doc — and to flag anything the Doc does not cover for a human to handle. You get consistent first-draft answers; the agent reviews and sends.

What you’ll need

• A Google Doc holding your knowledge base — policies, facts, common fixes.
• A Google Sheet with a question column and a answer column. Rows with a blank answer are the queue; the script fills them.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The Doc holding the support knowledge base.
const KB_DOC_ID = '1abcKnowledgeBaseId';

// The Sheet queue of questions waiting for answers.
const FAQ_QUEUE_SHEET_ID = '1abcFaqQueueId';

// How much of the KB Doc to send as context. Keeps the prompt within
// a sensible token budget — see "Watch out for".
const KB_CHAR_LIMIT = 8000;

/**
 * Drafts an answer for every queue row that has a question but no
 * answer yet, grounded in the knowledge-base Doc.
 */
function draftFaqAnswers() {
  // 1. Load the knowledge base, trimmed to the context budget.
  const kb = DocumentApp.openById(KB_DOC_ID)
    .getBody()
    .getText()
    .slice(0, KB_CHAR_LIMIT);

  // 2. Read the question queue and map header names to column indexes.
  const sheet = SpreadsheetApp.openById(FAQ_QUEUE_SHEET_ID).getSheets()[0];
  const [h, ...rows] = sheet.getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No questions in the queue — nothing to draft.');
    return;
  }
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. Walk the queue; draft an answer for each unanswered question.
  let drafted = 0;
  rows.forEach((r, i) => {
    // Skip rows already answered, or with no question to answer.
    if (r[col.answer] || !r[col.question]) return;

    // 4. Ground the prompt: answer only from the KB, flag gaps.
    const prompt =
      'Answer this Northwind support question using ONLY facts from ' +
      'the KB. If the KB does not cover it, say "needs human review".' +
      '\n\nKB:\n' + kb + '\n\nQ: ' + r[col.question];

    // 5. Write the draft back into the answer column.
    sheet.getRange(i + 2, col.answer + 1)
      .setValue(callClaude(prompt, 'claude-sonnet-4-6'));
    drafted++;
  });
  Logger.log('Drafted ' + drafted + ' answers.');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. draftFaqAnswers opens the knowledge-base Doc and reads its text, trimmed to KB_CHAR_LIMIT so the prompt does not exceed a sensible token budget.
2. It reads the question queue and builds a col map from header names to column indexes, so the code refers to col.question, not a brittle number.
3. If the queue is empty it logs that and stops — no Doc context wasted.
4. For each row it skips anything already answered or missing a question, then builds a prompt that pins Claude to the KB and tells it to write “needs human review” when the KB has no answer.
5. Claude Sonnet drafts the reply, which is written straight back into the answer column. The i + 2 offset accounts for the header plus the zero-based loop index.

Example run

The KB Doc says “Northwind refunds within 30 days of delivery.” The queue Sheet holds two unanswered rows:

question	answer
How long do I have to request a refund?	(blank)
Do you offer a student discount?	(blank)

After a run, the answer column is filled:

question	answer
How long do I have to request a refund?	You can request a refund within 30 days of delivery.
Do you offer a student discount?	needs human review

The first answer is grounded in the Doc and ready to send. The second is honest about a gap — the KB says nothing about student discounts, so it routes to a person instead of inventing a policy.

Run it

This is an on-demand job — run it whenever the queue has built up:

1. In the Apps Script editor, select draftFaqAnswers and click Run.
2. Approve the authorisation prompt the first time.
3. Open the queue Sheet and review the drafted answers before sending.

To let support staff trigger it from the Sheet, add a custom menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Support tools')
    .addItem('Draft FAQ answers', 'draftFaqAnswers')
    .addToUi();
}

Watch out for

• Drafts need review. The script writes straight into the answer column; treat every answer as a first draft, not an outgoing message.
• The KB is trimmed at KB_CHAR_LIMIT. If your knowledge base is longer than 8000 characters, the tail is dropped — split it into topic-specific Docs, or retrieve only the relevant section before prompting.
• “needs human review” is a signal, not a failure. Filter the answer column for that phrase to find genuine gaps in your knowledge base worth filling.
• Re-running skips filled rows. To redraft an answer, clear its cell first — otherwise the r[col.answer] guard treats it as done.
• Header names must match. The code looks for question and answer exactly; rename a column and the matching cell stays blank with no error.