Processing Thousands of Sheet Rows with Gemini from Apps Script — Beating the 6-Minute Limit with Chunking and Idempotency

The smallest function that applies Gemini to one row

Keep the per-row body as thin as possible. You don't need any library to call Gemini from Apps Script — UrlFetchApp can hit the REST endpoint directly. Since you'll use the result downstream (sorting, aggregation), receiving JSON rather than free text keeps things stable. The trick is to set responseMimeType to application/json and lock the shape with responseSchema.

const MODEL = 'gemini-2.5-flash'; // a fast model; swap in newer Flash models freely
const ENDPOINT = 'https://generativelanguage.googleapis.com/v1beta/models/'
  + MODEL + ':generateContent';
 
function classifyOne_(text) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const payload = {
    contents: [{ parts: [{ text:
      'You are an app-review classifier. Classify the following review.\n' + text }] }],
    generationConfig: {
      responseMimeType: 'application/json',
      responseSchema: {
        type: 'OBJECT',
        properties: {
          sentiment: { type: 'STRING', enum: ['positive', 'neutral', 'negative'] },
          topic: { type: 'STRING' },
          needs_reply: { type: 'BOOLEAN' },
        },
        required: ['sentiment', 'topic', 'needs_reply'],
      },
    },
  };
  const res = UrlFetchApp.fetch(ENDPOINT + '?key=' + apiKey, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true, // don't throw on 429; decide for ourselves
  });
  const code = res.getResponseCode();
  if (code === 429 || code >= 500) {
    throw { retryable: true, code: code }; // caught by the backoff below
  }
  if (code !== 200) {
    throw { retryable: false, code: code, body: res.getContentText().slice(0, 200) };
  }
  const json = JSON.parse(res.getContentText());
  const out = json.candidates[0].content.parts[0].text;
  return JSON.parse(out); // responseSchema guarantees JSON.parse succeeds
}

The muteHttpExceptions: true is deliberate. Without it, UrlFetchApp throws immediately on a 429 or 500, and you lose the chance to inspect the status code and decide "should this be retried?" The API key lives in PropertiesService script properties — never hardcoded in the script or the sheet. For untangling structured-output failures, I keep notes in Fixing validation errors from Gemini API Structured Output.

Watch the time budget and pass the baton before you stop

The heart of it is the main function. It's simple: remember the start time, and check elapsed time after each row. If the remaining time is about to exceed budget, break out of the loop right there and schedule a trigger to "run the rest."

function runBatch() {
  // Prevent overlapping invocations of the same function
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(1000)) return;
 
  const start = Date.now();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CFG.SHEET);
 
  try {
    while (Date.now() - start < CFG.TIME_BUDGET_MS) {
      const batch = nextPendingRows_(sheet, 20); // pull 20 rows at a time
      if (batch.length === 0) {
        cleanupTriggers_();   // nothing pending: clear continuation trigger and finish
        Logger.log('All rows processed');
        return;
      }
      for (const item of batch) {
        if (Date.now() - start >= CFG.TIME_BUDGET_MS) break; // over budget: stop
        processRow_(sheet, item);
      }
    }
    // Budget spent: hand the rest to the next execution
    scheduleContinuation_();
  } finally {
    lock.releaseLock();
  }
}
 
// Schedule a single trigger that calls runBatch 30 seconds later
function scheduleContinuation_() {
  cleanupTriggers_(); // always delete old continuation triggers first (no breeding)
  ScriptApp.newTrigger('runBatch').timeBased()
    .after(CFG.TRIGGER_DELAY_MS).create();
}
 
function cleanupTriggers_() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'runBatch') ScriptApp.deleteTrigger(t);
  });
}

Calling cleanupTriggers_() before creating a continuation trigger prevents triggers from breeding. Forget it, and every execution stacks one more trigger until several runBatch instances start running at once. LockService blocks the overlap itself, but the wasted fires pile up, so I reset to zero at both the entrance (clean before create) and the exit (clean on completion). Setting TIME_BUDGET_MS to 4.5 minutes rather than a flat six follows the same logic: leave a few seconds of headroom for trigger creation and sheet writes.

Isolate 429s and transient errors per row

Run a few thousand rows and you will hit a 429 (rate limit) or a transient 500 somewhere. If that halts the whole job, one row blocks everything. The policy: retry what's retryable within the row, and if it still fails, set error and move to the next row. One row's failure must not drag the rest down.

function processRow_(sheet, item) {
  try {
    const result = withBackoff_(function () { return classifyOne_(item.text); });
    sheet.getRange(item.row, CFG.COL_OUTPUT).setValue(JSON.stringify(result));
    sheet.getRange(item.row, CFG.COL_STATUS).setValue('done');
  } catch (e) {
    // Non-retryable, or retries exhausted: mark error, isolate, move on
    sheet.getRange(item.row, CFG.COL_STATUS).setValue('error');
    sheet.getRange(item.row, CFG.COL_OUTPUT)
      .setValue('ERR ' + (e.code || '') + ' ' + (e.body || e.message || ''));
  }
}
 
// Exponential backoff with jitter; only persist through retryable failures
function withBackoff_(fn) {
  const MAX = 5;
  for (let attempt = 0; attempt < MAX; attempt++) {
    try {
      return fn();
    } catch (e) {
      if (!e.retryable || attempt === MAX - 1) throw e;
      const wait = Math.pow(2, attempt) * 1000 + Math.floor(Math.random() * 500);
      Utilities.sleep(wait); // 1s, 2s, 4s, 8s ... plus a little jitter
    }
  }
}

The small jitter (Math.random()) on the backoff matters: when several rows hit a 429 together, it breaks up the "synchronized retry" where every row waits the same number of seconds and slams into another 429 in lockstep. Rows marked error stay in the sheet, so once you fix the cause you can blank their status and they get picked up again as pending. Failures persisting rather than vanishing is itself the payoff of an idempotent design. For the thinking behind rate limits themselves, see Operational notes on Gemini API rate limits and 429s.

Re-injecting only the failed rows

Rerunning after a fix needs no special machinery. Blank the status cell of the error rows. The next runBatch picks them up as pending. I wire a "reset errors" menu item so I can re-inject with one click after fixing the cause.

function resetErrors() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CFG.SHEET);
  const n = sheet.getLastRow() - CFG.HEADER_ROWS;
  if (n <= 0) return;
  const range = sheet.getRange(CFG.HEADER_ROWS + 1, CFG.COL_STATUS, n, 1);
  const vals = range.getValues();
  for (let i = 0; i < n; i++) {
    if (String(vals[i][0]).trim() === 'error') vals[i][0] = '';
  }
  range.setValues(vals);
}

What I learned actually running it (field notes)

A few impressions from classifying 2,000-plus reviews with gemini-2.5-flash this way. The numbers are rough guides from my environment and will shift with review length, model, and network.

• 1.2–2.5 seconds per row. A 4.5-minute budget moved roughly 120–200 rows per execution, so 2,000 rows splits into about 10–15 executions
• A 30-second trigger gap is safer than something tighter. When I shortened it to 10 seconds, the previous run's sheet writes overlapped the next startup and lock waits grew, so I went back to 30
• Calling setValue per row can become the bottleneck. Past tens of thousands of rows, buffering results into an array and writing once with setValues is faster. But that trades against the "progress survives a crash" property, so up to a few thousand rows I choose the safe side and write per row
• error rows ran 1–2% of the total, and most weren't 429s — they were unexpectedly long or blank input cells. Locking the schema meant those data-side problems surfaced as error instead of corrupting the output, which I was grateful for

This particular workload was reviews pulled from both the App Store and Google Play, but the same shape carries over to, say, importing an AdMob report into a sheet and summarizing it — you just swap the input column, prompt, and schema. For Apps Script patterns in general, reading it alongside Operational notes on Google Workspace × Gemini API automation will widen your trigger-design toolkit.

Where to start

Add a single status column to your sheet and get nextPendingRows_ — "take only the empty rows" — running first. Once idempotency is in, trigger continuation and backoff are things you can add later as peace of mind for when something stalls. I'm still tuning parts of this as I run it, but simply switching to the mindset of "don't try to finish it all in one go" is what let me hand work to a sheet without flinching at the row count.