How to Use Ollama with Google Sheets via Apps Script

Google Sheets has a built-in scripting environment — Apps Script — that can call external APIs using JavaScript. With Ollama running locally and a tunnel to make it accessible, or Ollama deployed on a server, you can call it directly from Apps Script and add AI capabilities to any Google Sheet. Generate product descriptions, classify customer feedback, summarise text, extract structured data — all from within a spreadsheet, with the results populating cells automatically. This guide covers the complete setup and several practical functions you can use immediately.

The Architecture: Apps Script to Ollama

Apps Script runs in Google’s cloud, which means it cannot reach localhost on your machine directly. To call a local Ollama instance from Apps Script, you need either a tunnel (ngrok or Cloudflare Tunnel) or Ollama deployed on a VPS with a public URL. If privacy is paramount and you cannot use a tunnel or cloud server, this integration is not the right choice — consider running a local Python script with the Google Sheets API instead, which runs entirely on your machine. For most users, the ngrok approach during development is the fastest path to a working prototype.

Setting Up the Connection

Start ngrok to expose your local Ollama:

ngrok http 11434

Copy the HTTPS URL ngrok provides (something like https://abc123.ngrok.io). In Google Sheets, go to Extensions → Apps Script. This opens the Apps Script editor where you will write the code that calls Ollama.

Core Apps Script Function

The foundation is a reusable callOllama function that handles the HTTP request to your Ollama endpoint:

const OLLAMA_URL = "https://abc123.ngrok.io";  // your ngrok or VPS URL
const MODEL = "llama3.2";

function callOllama(prompt, systemPrompt) {
  systemPrompt = systemPrompt || "You are a helpful assistant. Be concise and direct.";
  
  const payload = {
    model: MODEL,
    messages: [
      { role: "system", content: systemPrompt },
      { role: "user", content: prompt }
    ],
    stream: false,
    options: { temperature: 0.1, num_predict: 200 }
  };
  
  const options = {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  
  try {
    const response = UrlFetchApp.fetch(
      OLLAMA_URL + "/api/chat",
      options
    );
    const data = JSON.parse(response.getContentText());
    return data.message.content.trim();
  } catch (e) {
    return "Error: " + e.message;
  }
}

Practical Custom Functions

Once callOllama is working, you can build specific functions for common spreadsheet tasks. These become custom formula functions you can call from any cell with =FUNCTIONNAME(A1):

// Classify sentiment of text in a cell
function SENTIMENT(text) {
  if (!text) return "";
  return callOllama(
    "Classify the sentiment of this text. Reply with exactly one word: positive, negative, or neutral.\n\n" + text,
    "You are a sentiment classifier. Reply with only one word: positive, negative, or neutral."
  );
}

// Summarise long text to a short description
function AI_SUMMARISE(text, maxWords) {
  maxWords = maxWords || 20;
  return callOllama(
    "Summarise this in " + maxWords + " words or fewer:\n\n" + text,
    "You are a text summariser. Be extremely concise."
  );
}

// Extract specific data from text
function AI_EXTRACT(text, whatToExtract) {
  return callOllama(
    "Extract " + whatToExtract + " from this text. Return only the extracted value, nothing else:\n\n" + text,
    "You extract specific information from text. Return only the requested information, no explanation."
  );
}

// Generate content from a template
function AI_GENERATE(topic, style) {
  style = style || "professional";
  return callOllama(
    "Write a " + style + " description about: " + topic,
    "You write concise, " + style + " content. Keep responses under 100 words."
  );
}

Use these in sheets as formulas: =SENTIMENT(A2), =AI_SUMMARISE(B3, 15), =AI_EXTRACT(C4, "email address"). Each cell call is one Ollama API request, so large ranges (hundreds of cells) will take proportionally longer.

Batch Processing a Column

Using cell formulas calls Ollama once per cell, which is fine for small ranges but slow for hundreds of rows. A batch function that processes an entire column avoids per-cell overhead and lets you track progress as it runs. Add this to your Apps Script and trigger it via a custom menu:

function processBatchColumn() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const inputCol = 2, outputCol = 3, startRow = 2;
  const lastRow = sheet.getLastRow();
  const inputData = sheet.getRange(startRow, inputCol, lastRow - startRow + 1, 1).getValues();
  const results = [];
  for (let i = 0; i < inputData.length; i++) {
    const text = inputData[i][0];
    if (!text) { results.push([""]); continue; }
    const result = callOllama(
      "Classify as: bug_report, feature_request, general_feedback, or complaint.\n\n" + text,
      "Reply with only one of: bug_report, feature_request, general_feedback, complaint."
    );
    results.push([result]);
    if (i % 10 === 9) sheet.getRange(startRow, outputCol, i+1, 1).setValues(results);
    Utilities.sleep(500);
  }
  sheet.getRange(startRow, outputCol, results.length, 1).setValues(results);
  SpreadsheetApp.getUi().alert("Done! Processed " + results.length + " rows.");
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu("AI Tools")
    .addItem("Process Column B to C", "processBatchColumn")
    .addToUi();
}

After saving this, reload the spreadsheet and an "AI Tools" menu appears in the top bar. Team members sharing the sheet can run batch AI processing without touching code. The Utilities.sleep(500) pause between requests keeps your local Ollama stable under sequential load.

Error Handling and Rate Management

Apps Script has a 6-minute execution limit for free accounts (30 minutes for Workspace accounts) and UrlFetchApp has its own rate limits. For datasets that might hit the execution limit, add checkpointing via PropertiesService to save which row you reached — if the script times out, re-run it and it picks up where it left off. The Utilities.sleep() call between rows matters: without it, sequential Ollama requests can queue faster than the model processes them, causing timeouts. 500ms works well for a 7B model; reduce to 200ms for smaller models or increase to 1000ms if you get frequent timeouts.

Security: Protecting Your Endpoint

A public ngrok URL is a security risk — anyone who discovers it gets unrestricted access to your local models and compute. For development, accept this risk on a short-lived tunnel and tear it down when not in use. For production use in a shared spreadsheet, either deploy Ollama on a VPS with IP allowlisting restricted to Google's Apps Script IP ranges, or add a secret header to your callOllama function that your endpoint validates. The most secure approach for team use: deploy Ollama on a company server accessible only from the corporate network, and have team members use a VPN when running Apps Script functions against it.

Practical Use Cases That Work Well

Several Google Sheets AI workflows produce genuine time savings. Customer feedback classification: a column of support tickets or reviews classified as bug, feature request, general feedback, or complaint in seconds — without reading each one manually. Product description generation: a list of product names and specs in column A, generated marketing descriptions in column B — useful for e-commerce catalogue management. Resume or application screening: a column of applicant descriptions with a classification of qualified/not-qualified against specific criteria. Data normalisation: messy address or company name fields cleaned and standardised by the AI. Translation: a column in English, translated version in the adjacent column using any multilingual model. Each of these works best with temperature=0 for consistency, a specific system prompt defining exactly the output format you expect, and a small num_predict cap to prevent runaway outputs that fill cells with essays rather than concise answers.

The Apps Script vs Python Trade-off

Google Apps Script runs in the cloud and requires either a tunnel or a public Ollama endpoint, which adds complexity. An alternative for users who want the AI results in Google Sheets but prefer to keep everything local: use Python with the Google Sheets API (via the gspread library) and the ollama Python library together on your own machine. The Python script reads rows from the sheet, processes them through a local Ollama model with no network exposure required, and writes results back to the sheet. This is architecturally cleaner for strict privacy requirements — no data travels through Google's Apps Script servers, only the final results go to Sheets via the API. The trade-off is that it requires running the script locally rather than triggering from within the spreadsheet, and your machine needs to be on and running the script for it to work. For batch jobs that run on a schedule, a simple cron job or scheduled Task handles this cleanly.

Model Selection for Spreadsheet Tasks

For Google Sheets AI functions, the fastest model that meets your quality threshold is the right choice, because every formula call is a separate Ollama request and speed directly determines how quickly your spreadsheet populates. For binary or categorical classification — sentiment, category, yes/no, priority level — a 3B model at temperature 0 works excellently. It is fast, deterministic, and accurate on well-defined classification problems where the output space is narrow. For text generation — product descriptions, summaries, expanded paragraphs — 7B produces noticeably better output quality and is worth the slightly slower response time. Keep num_predict low (100-200 tokens) for all spreadsheet functions to ensure concise, cell-appropriate outputs rather than multi-paragraph responses. Setting temperature to 0 for classification and 0.1-0.2 for generation ensures consistent, reproducible results when the same input appears in multiple rows, which matters for downstream analysis and reporting.

Extending to Google Docs and Gmail

The same Apps Script pattern extends to other Google Workspace products. Google Docs: a script that summarises the active document by extracting body text and calling Ollama, then inserts the summary at the top. Gmail: a script that processes emails in a label — extracting action items, classifying urgency, or drafting reply templates — with the AI output saved to a Google Sheet for review. Calendar: a script that takes meeting notes from a Docs file, calls Ollama to extract decisions and action items, and creates Google Tasks for each action item. All of these use the same callOllama function with different trigger points and data sources. Once you have the Ollama connection working in Apps Script, adapting it to any Google Workspace product is straightforward — the API connectivity is the hard part, and everything after that is standard Apps Script data manipulation.

Getting Started: A 15-Minute Setup

The fastest path to a working Google Sheets AI function: start Ollama with your chosen model, run ngrok to expose port 11434, open a Google Sheet, go to Extensions then Apps Script, paste the callOllama function and one of the specific functions like SENTIMENT, save, then back in the sheet type =SENTIMENT("This product is great!") in a cell. If you see "positive" come back, everything is working. From there, apply it to a real column in your data and watch it classify rows automatically. The whole setup from scratch takes about 15 minutes for someone comfortable with developer tools. The integration rewards the investment: once it is working, AI-powered spreadsheet functions become a regular part of your data workflow rather than a one-off project.