Build a sidebar AI assistant for Sheets

Northwind’s analysts spend a lot of time staring at ranges of numbers and asking the same kinds of questions — what stands out, which row is the outlier, what would a quick summary say. Answering those questions usually means copying cells into a separate AI chat, losing the link back to the data.

This automation keeps the conversation inside the spreadsheet. A sidebar holds a question box; whatever range is selected is sent to Claude along with the question, and the answer appears in the panel. The analyst never leaves the Sheet, and the assistant always sees exactly the data they are looking at.

What you’ll need

• An HTML file named Assistant.html in the Apps Script project for the sidebar UI.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• An onOpen trigger or menu so editors can open the sidebar (added below).

The sidebar (Assistant.html)

<input id="q" placeholder="Ask about the selection">
<button onclick="ask()">Ask</button>
<div id="answer"></div>
<script>
  // Send the question to the server function and show whatever it returns.
  function ask() {
    document.getElementById('answer').textContent = 'Thinking…';
    google.script.run
      .withSuccessHandler((a) => {
        document.getElementById('answer').textContent = a;
      })
      .withFailureHandler((err) => {
        document.getElementById('answer').textContent = 'Error: ' + err.message;
      })
      .answerAboutSelection(document.getElementById('q').value);
  }
</script>

The script

// Claude model and response budget for the assistant.
const ASSISTANT_MODEL = 'claude-sonnet-4-6';
const MAX_TOKENS = 500;

// Cap on how many selected rows are sent, to keep the prompt sensible.
const MAX_ROWS = 50;

/**
 * Opens the assistant sidebar inside the active spreadsheet.
 */
function showAssistant() {
  const ui = HtmlService.createHtmlOutputFromFile('Assistant')
    .setTitle('Ask Claude');
  SpreadsheetApp.getUi().showSidebar(ui);
}

/**
 * Adds an "AI assistant" menu so editors can open the sidebar.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AI assistant')
    .addItem('Open assistant', 'showAssistant')
    .addToUi();
}

/**
 * Sends the current selection plus a question to Claude and returns
 * the answer. Called from the sidebar via google.script.run.
 */
function answerAboutSelection(question) {
  // Guard against an empty question.
  if (!question || !question.trim()) {
    return 'Type a question first.';
  }

  // Read the active range, capped to a sensible number of rows.
  const data = SpreadsheetApp.getActiveRange().getValues().slice(0, MAX_ROWS);
  if (!data.length) {
    return 'Select a range of cells, then ask again.';
  }

  // Give Claude the data and the question together.
  const prompt = 'Northwind data:\n' + JSON.stringify(data) +
    '\n\nQuestion: ' + question;

  return callClaude(prompt);
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt) {
  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: ASSISTANT_MODEL,
      max_tokens: MAX_TOKENS,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. onOpen runs whenever the spreadsheet is opened and adds an “AI assistant” menu, so editors have a one-click way to launch the sidebar.
2. showAssistant loads Assistant.html and shows it as a sidebar titled “Ask Claude”.
3. In the sidebar, clicking Ask calls answerAboutSelection through google.script.run, passing the text from the question box.
4. answerAboutSelection checks the question is not blank, reads the active range with getActiveRange, and caps it at MAX_ROWS so a huge selection does not blow the prompt budget.
5. It builds a prompt that puts the selected data as JSON next to the question, then hands it to callClaude.
6. callClaude reads the API key from Script Properties, posts the prompt to the Anthropic API, and returns the text of the reply.
7. The sidebar’s success handler drops that text into the answer div; the failure handler shows any error instead.

Example run

An analyst selects a range of monthly figures:

Month	Revenue
Jan	42,000
Feb	38,500
Mar	51,200

They type “Which month was the outlier and why?” and click Ask. The sidebar shows “Thinking…”, then a reply such as: “March stands out — at 51,200 it is roughly 25% above the Jan–Feb average of 40,250. February is the weakest month. If this is a quarter, the upward jump in March suggests a strong end to the period.”

Run it

This is an on-demand tool — open it when you need it:

1. Reload the spreadsheet so the AI assistant menu appears (the onOpen trigger creates it).
2. Choose AI assistant → Open assistant.
3. Select a range, type a question, and click Ask.

The first run will prompt for authorisation, including permission to make external requests.

Watch out for

• The whole selection is sent to a third-party API. Do not point the assistant at ranges containing personal or confidential data unless that is acceptable under Northwind’s policy.
• The row cap (MAX_ROWS) protects the prompt budget, but a wide selection with many columns can still be large. Trim the selection or lower the cap if responses get truncated.
• The assistant only sees cell values, not formulas, formatting, or context from other tabs. Phrase questions so they make sense from the numbers alone.
• Each question is a fresh API call with no memory of previous ones — there is no conversation history. Include any needed context in the question itself.
• callClaude uses muteHttpExceptions, so an API error returns a confusing reply rather than throwing. If answers look wrong, log res.getContentText() to see the raw response.