Build Your Own AI Formula in Google Sheets

Imagine having a magic wand inside your Google Sheet. One that could instantly summarize 500 rows of feedback, clean up messy mailing addresses, or translate entire columns of product descriptions — all by typing a simple formula.

Google recently officially introduced the =AI() function to Google Sheets, and while it’s a massive step forward, it comes with a catch: it’s typically locked behind a Google AI pro subscription that can cost you $20 or more every single month, whether you use it once or a thousand times.

But what if you only need AI for a handful of tasks?

By creating your own custom function via Apps Script, you can bypass the “subscription trap.” Instead of a flat monthly fee, you can use the Gemini API, which offers a generous free tier and a “pay-as-you-go” model. If you don’t use the function for a month, you pay $0. If you use it for a few heavy projects, you only pay for the exact number of words (tokens) processed.

This is the ultimate way to deviate from the official, restricted feature and build a tool that is more flexible, more powerful, and significantly cheaper for the savvy user.

Why Go Custom?

Official =AI() vs. Your Custom =API()

The Goal: A Universal AI Function

We want to create a function in our spreadsheet that looks like this:

=API("The prompt/instruction", "The data/reference")

It takes two simple ingredients:

1. Prompt: Your command (e.g., “Summarize,” “Extract email,” “Translate to French”).
2. Reference: The data you want the AI to process (a single cell like A1 or a range like A1:C10).

Phase 1: Preparation (Get Your Key)

Before we write code, you need access to the brain: the Gemini API.

1. Go to **Google AI Studio**.
2. Log in and click the “Get API key” button on the left sidebar.
3. Click “Create API key.”
4. Copy this long string of letters and numbers immediately. Treat this key like your credit card. Anyone with this key can use your AI quota. Keep it private.

Phase 2: Installing the Script

Now we will build the bridge between Sheets and Gemini.

1. Open any Google Sheet.
2. Navigate to Extensions > Apps Script.
3. A new window will open. Delete the small starter function (function myFunction() {...}) and paste the following code into the editor.

Important: Replace "YOUR_API_KEY_HERE" with the key you just copied in Phase 1.

/**
 * Uses Gemini AI to process a prompt and reference data.
 *
 * @param {string} prompt The instruction for the AI.
 * @param {string|Array} reference The data to process (cell or range).
 * @return {string} The AI generated response.
 * @customfunction
 */
function API(prompt, reference) {
  const apiKey = "YOUR_API_KEY_HERE"; // <--- Paste your key here
  const url = "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key=" + apiKey;

  // Convert reference to string if it's a range/array
  const data = Array.isArray(reference) ? reference.flat().join(", ") : reference;

  const payload = {
    "contents": [{
      "parts": [{
        "text": prompt + "\n\nReference data: " + data
      }]
    }]
  };

  const options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload)
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const json = JSON.parse(response.getContentText());
    return json.candidates[0].content.parts[0].text;
  } catch (e) {
    return "Error: " + e.toString();
  }
}

4. Click the Save icon (floppy disk) on the toolbar. Name your project (e.g., “Gemini API Link”) if prompted. You are done with the code!

Phase 3: Using It in Your Sheet

Go back to your Google Sheet. It’s time to use your magic wand. In any cell, try these examples (assuming your data is in column A):

With text strings: =API("Summarize this in 3 words", "The quick brown fox jumps over the lazy dog")

With cell references: =API(A1, B1)

With a range of cells: =API("Clean this data", A1:A10)

Important Considerations (The Small Print)

• Read-Only: A Custom Function can only return data to the cell it lives in. It cannot delete your Sheet, change colors, or format other cells.
• Latency: This function is connecting to a Google server. Expect a brief “Loading…” message (1–3 seconds) every time the formula recalculates. It’s not instant like =SUM().
• Privacy: Be mindful of sensitive data. You are sending information from your Sheet to Google’s API for processing. Avoid using it with highly personal or classified company data.

[embed]Yongjin Lee yongjin-l.github.io