I Put =GEMINI() in 420 Cells. The Calls Kept Coming on Days I Never Opened the Sheet

Thirty seconds is not six minutes

These two limits get conflated constantly, so let us separate them. The documentation is unambiguous: a custom function call must return within 30 seconds. If it does not, the cell displays #ERROR! and the cell note reads Exceeded maximum execution time (line 0).

A normal script started from a menu or a trigger gets six minutes. Same Apps Script, but custom functions run on one-twelfth of the budget. Those 37 error rows were not slow prompts or long inputs. They were simply the 37 rows that happened to cross 30 seconds during a busy stretch.

That 30 seconds also decides the ceiling for the next section. On my setup, a round trip to gemini-flash-latest for a short summary took roughly 1.7 to 2.1 seconds per row. Call it 2.0 to be safe, and 30 seconds holds about 15 rows. Discount to 70 percent for real-world variance and the practical cap is around 10.

Trimming the prompt, capping output tokens, dropping to a lighter model — I tried all of it, and all of it helps. Add rows and you meet the same wall again. Since the ceiling will not move, I have come to prefer moving the design out from under it rather than getting cleverer beneath it.

Take a range, return a 2D array

Before rebuilding anything, there is an order-of-magnitude win the documentation hands you for free. A custom function can accept a range as a two-dimensional array and return a two-dimensional array that overflows into adjacent cells. Instead of 420 formulas in 420 cells, you write one formula in one cell that returns 420 rows.

/**
 * Takes a range of review bodies and returns a single column of summaries.
 * Calls the server once, not once per cell.
 *
 * @param {Array<Array<string>>} range The range of review bodies.
 * @return {Array<Array<string>>} One column of summary text.
 * @customfunction
 */
function GEMINI_SUMMARY_RANGE(range) {
  const rows = (Array.isArray(range) ? range : [[range]])
    .map((row) => String(row[0] || '').trim());
 
  // Claim the 30-second budget up front. Mark the rest rather than dying.
  const deadline = Date.now() + 25 * 1000;
  const out = [];
 
  for (const text of rows) {
    if (!text) { out.push(['']); continue; }
    if (Date.now() > deadline) { out.push(['#BUDGET']); continue; }
    out.push([summarizeOne_(text)]);
  }
  return out;
}
 
function summarizeOne_(text) {
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/'
    + 'gemini-flash-latest:generateContent?key=' + key;
  const res = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({
      contents: [{ parts: [{ text: 'Summarize this review in one sentence.\n\n' + text }] }],
      generationConfig: { maxOutputTokens: 120 },
    }),
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) return '#GEMINI_ERROR';
  return JSON.parse(res.getContentText()).candidates[0].content.parts[0].text.trim();
}

With =GEMINI_SUMMARY_RANGE(C2:C11), the sheet makes one call to the Apps Script server. The visible layout barely changes and the call count drops by two orders of magnitude.

The deadline line is where the shape shows its limit, though. Thirty seconds caps how many rows the range can hold — around 10 to 12 at my measured 2.0 seconds per row. Push past that and the tail fills with #BUDGET. You could split 420 rows across 42 formulas of 10 rows each, but that is just 42 stacked 30-second budgets, and the recalculation amplification is untouched.

This shape belongs in exploration. While you are still deciding prompt wording and output granularity across a few dozen rows, seeing results land in the sheet as you type is genuinely worth something. I use it right up until the prompt settles. Then I move it out.

Three things a custom function cannot see

Separate from row counts, the custom function execution model carries three constraints that only bite in production. They sit quietly in a documentation table, invisible while everything works.

It cannot write back. The Spreadsheet service is read-only inside a custom function: get*() methods work, set*() methods do not, and SpreadsheetApp.openById() and openByUrl() are unavailable. On top of that, a custom function cannot affect any cell other than the ones it returns a value to. So "mark this row done in another column" or "log failures to a ledger sheet" — the bookkeeping any batch does by default — is structurally impossible.

Failures collapse into values. All you can return is something displayable, which is why my code returns the string #GEMINI_ERROR. Rate limit? Key restriction? Malformed schema? The cell does not distinguish. console.log puts it in the execution log, but anyone looking at the sheet learns only that something broke.

The key has nowhere safe to live. Custom functions never prompt for authorization, which is why they cannot reach services that touch personal data in the first place. Properties works, but getUserProperties() returns only the spreadsheet owner's properties, and editors cannot set user properties inside a custom function. More to the point, a container-bound script shares its container's access list. Sharing the sheet with edit access means sharing the ability to edit the script. An API key in script properties is three lines of code away from anyone on that list. Handing out a homemade =GEMINI() on a shared sheet is close to handing out the key.

Make the cell a request, not a call

The migration fits in one sentence: change what the cell holds from a call into a state. Column D holds a value, not a formula. Column E records when it was produced and by which model. The only thing that calls Gemini is a batch you started from a menu. Recalculation amplification goes to zero, because values do not recalculate no matter who inserts a column.

const SHEET_NAME = 'reviews';
const COL = { text: 3, summary: 4, status: 5, model: 6, at: 7 };
 
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Gemini')
    .addItem('Summarize pending rows', 'runSummaryBatch')
    .addToUi();
}
 
/**
 * Summarizes only unprocessed rows and writes results back as values.
 * If it hits the 6-minute wall, everything written so far stands.
 * Run it again and it resumes.
 */
function runSummaryBatch() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const last = sheet.getLastRow();
  if (last < 2) return;
 
  const values = sheet.getRange(2, 1, last - 1, COL.at).getValues();
  const deadline = Date.now() + 5 * 60 * 1000; // step down before the 6-minute limit
  let done = 0;
 
  for (let i = 0; i < values.length; i++) {
    if (Date.now() > deadline) break;
 
    const row = i + 2;
    const text = String(values[i][COL.text - 1] || '').trim();
    const status = String(values[i][COL.status - 1] || '').trim();
 
    if (!text) continue;
    if (status === 'ok') continue; // idempotency lives here and nowhere else
 
    const result = summarizeWithRetry_(text);
 
    // Write each row as it completes. A crash never costs completed work.
    sheet.getRange(row, COL.summary, 1, 4).setValues([[
      result.ok ? result.text : '',
      result.ok ? 'ok' : 'error:' + result.reason,
      'gemini-flash-latest',
      new Date(),
    ]]);
    done++;
  }
 
  SpreadsheetApp.getActive().toast(done + ' rows processed', 'Gemini', 5);
}
 
function summarizeWithRetry_(text) {
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/'
    + 'gemini-flash-latest:generateContent?key=' + key;
  const payload = JSON.stringify({
    contents: [{ parts: [{ text: 'Summarize this review in one sentence.\n\n' + text }] }],
    generationConfig: { maxOutputTokens: 120 },
  });
 
  for (let attempt = 0; attempt < 4; attempt++) {
    const res = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      payload: payload,
      muteHttpExceptions: true,
    });
    const code = res.getResponseCode();
 
    if (code === 200) {
      const parts = JSON.parse(res.getContentText()).candidates?.[0]?.content?.parts;
      if (!parts || !parts[0]?.text) return { ok: false, reason: 'empty' };
      return { ok: true, text: parts[0].text.trim() };
    }
    // Only 429 and 5xx are worth waiting on. Resending a 4xx changes nothing.
    if (code !== 429 && code < 500) return { ok: false, reason: 'http' + code };
    Utilities.sleep(Math.pow(2, attempt) * 1000);
  }
  return { ok: false, reason: 'retry_exhausted' };
}

One status column changes the character of the whole thing. Stop at six minutes and the written rows are already final. Press the menu item again and it picks up. Failed rows carry error:http429 so you can filter for them later. Getting 420 rows through unattended — handing the baton to yourself with a trigger to cross the 6-minute wall — is a separate implementation, and I wrote it up in processing thousands of spreadsheet rows with Gemini from Apps Script. This piece is the step before that one: dropping the formula shape in the first place.

After the move, my weekly call count went from 1,180 to 420. Most of what disappeared was happening while I was doing nothing at all.

Where a custom function still earns its place

I do not think everything should be a batch. The real value of a custom function is that the answer lands where you are looking. Here is the granularity I actually use.

That last row is not a throwaway. For work like localizing store descriptions, the built-in path is faster and safer than anything I would write. I keep the app metadata side on the Fill with Gemini workflow for multilingual store listings at Dolice, and reserve custom code for what does not fit there. Being able to build it and being better off building it are different questions.

Start by counting one sheet

You do not need an architecture argument to make this call. Pick one sheet that currently has =GEMINI()-style formulas in it, add the console.log line above, and use it normally for a week. Then put two numbers side by side: executions in the Apps Script dashboard, and formulas in the sheet. The wider that gap, the clearer the case for writing values back.

I ran the formula shape for about six months. What had been growing was not only the cost — it was the amount of time I spent not knowing what was happening.