Your Apps Script Gemini Automation Fails Every Month-End — Budgeting Against the UrlFetch Daily Quota

The budget governor: decide how many calls you may spend per day, up front

The idea is simple. Decide up front how many UrlFetch calls you may issue in a day; when you are about to exceed it, stop for today and push the rest to tomorrow. It is close in spirit to a two-stage dollar cap, but what you count here is call count, not money. For the dollar-based design, see pairing Project Spend Caps with an app-side soft limit; if you also want to lock down cost, that is the counterpart.

Make the budget the effective value — the ceiling minus a safety margin and a reservation — not the ceiling itself.

Keep the ledger in Script Properties. The key point is to reset the budget on the timezone boundary, not UTC. Zero out usage the moment the date rolls over in the script's timezone (Asia/Tokyo for me). Leave it on UTC and yesterday's remainder revives at 9 a.m. local time, drifting away from what the daily quota actually measures.

// After: daily budget governor (token-bucket / TZ-boundary reset)
const DAILY_QUOTA    = 20000;  // consumer. Workspace is 100000
const MARGIN         = 0.15;   // safety margin
const RESERVE        = 500;    // reservation
const CALLS_PER_ITEM = 1.08;   // expected retry amplification (tune to measured)
const TZ = 'Asia/Tokyo';
 
function usableBudget() {
  return Math.floor(DAILY_QUOTA * (1 - MARGIN)) - RESERVE;  // 16500
}
 
// today's date key (rolls over on the TZ boundary)
function todayKey() {
  return Utilities.formatDate(new Date(), TZ, 'yyyy-MM-dd');
}
 
// return remaining budget; reset usage to 0 if the date changed
function remainingBudget() {
  const props = PropertiesService.getScriptProperties();
  const ledger = JSON.parse(props.getProperty('urlfetch_ledger') || '{}');
  if (ledger.date !== todayKey()) {
    ledger.date = todayKey();
    ledger.used = 0;
    props.setProperty('urlfetch_ledger', JSON.stringify(ledger));
  }
  return usableBudget() - (ledger.used || 0);
}
 
// record calls actually spent (fold concurrency with LockService)
function chargeBudget(calls) {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const props = PropertiesService.getScriptProperties();
    const ledger = JSON.parse(props.getProperty('urlfetch_ledger') || '{}');
    if (ledger.date !== todayKey()) { ledger.date = todayKey(); ledger.used = 0; }
    ledger.used = (ledger.used || 0) + calls;
    props.setProperty('urlfetch_ledger', JSON.stringify(ledger));
  } finally {
    lock.releaseLock();
  }
}

Read-modify-write on Script Properties loses updates when several triggers run at once, which is why LockService wraps it here. For the Properties contention and size limits themselves, see folding Properties contention into a durable store; if the ledger grows, moving it into that storage layer is the safe path.

A dispatcher that carries the backlog to the next day

Once you can count the budget, change the worker to "advance only within the remaining budget and leave the rest." If you already track pending work with a status column, the carry-over happens for free — rows you did not process today simply stay unprocessed.

// After: process only within remaining budget, defer the rest to tomorrow
function enrichWithinBudget() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('reviews');
  const rows = sheet.getDataRange().getValues();
 
  let remaining = remainingBudget();
  if (remaining <= 0) {
    console.log('Daily UrlFetch budget spent. Carrying over to tomorrow.');
    return;
  }
  // convert remaining budget into an item count (fold in retry amplification)
  let itemsAllowed = Math.floor(remaining / CALLS_PER_ITEM);
 
  for (let i = 1; i < rows.length && itemsAllowed > 0; i++) {
    if (rows[i][STATUS_COL]) continue;
    const { draft, calls } = callGeminiWithRetry(rows[i][TEXT_COL]);
    sheet.getRange(i + 1, STATUS_COL + 1).setValue('done');
    sheet.getRange(i + 1, DRAFT_COL + 1).setValue(draft);
    chargeBudget(calls);          // record calls actually spent
    itemsAllowed -= 1;
  }
}
 
// return the retry count too, so the ledger tracks reality
function callGeminiWithRetry(text) {
  let calls = 0;
  for (let attempt = 0; attempt < 3; attempt++) {
    calls += 1;
    const res = UrlFetchApp.fetch(GEMINI_ENDPOINT, {
      method: 'post', contentType: 'application/json',
      headers: { 'x-goog-api-key': 'YOUR_API_KEY' },
      payload: JSON.stringify({ contents: [{ parts: [{ text: text }] }] }),
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() === 200) {
      const draft = JSON.parse(res.getContentText()).candidates[0].content.parts[0].text;
      return { draft, calls };
    }
    Utilities.sleep(Math.pow(2, attempt) * 1000);  // exponential backoff
  }
  return { draft: '', calls };  // leave failed rows empty for next time
}

The point is that the calls actually added by retries are recorded in the ledger as calls. The estimate factor (CALLS_PER_ITEM) is only used to convert budget into an item count; the record of consumption is measured. That way, even on a day with more retries than expected, the budget draws down correctly and the job stops naturally before the ceiling.

How long does the backlog take to clear — check with a simulation

"Carry over to tomorrow" raises a fear that the backlog will snowball. So I checked whether the carry-over actually converges, with a deterministic simulation. The premise: 3,000 items/day at steady state, plus 40,000 added on day 5 from a post-launch review surge and backfill.

import math
DAILY_QUOTA, MARGIN, RESERVE, CALLS_PER_ITEM = 20000, 0.15, 500, 1.08
UB = int(DAILY_QUOTA * (1 - MARGIN)) - RESERVE          # 16500
CAP = math.floor(UB / CALLS_PER_ITEM)                   # 15277 items/day
 
arrivals = [3000]*14
arrivals[4] += 40000                                    # spike on day 5
 
backlog = 0
for d in range(14):
    pend = backlog + arrivals[d]
    proc = min(pend, CAP)               # process only within budget
    backlog = pend - proc               # defer the rest to tomorrow
    naive = math.ceil(arrivals[d] * CALLS_PER_ITEM)     # naive call count
    status = "OK" if naive <= DAILY_QUOTA else "THROW"
    print(d+1, arrivals[d], proc, backlog, naive, status)

Running it, the effective budget is 16,500 calls/day and daily capacity is 15,277 items. Here are the results.

Two things stand out. First, the naive version attempts 46,440 UrlFetch calls on the day-5 spike, crosses the 20,000 wall, and loses that day's work mid-run (THROW). Second, the governor processes exactly 15,277 items within the ceiling that same day, defers the remaining 27,723 to later days, and clears the backlog to zero just three days after the spike (day 8). As long as the arrival rate (3,000/day) stays below capacity (15,277/day), the carry-over always converges. It does not snowball — it just flattens the peak.

Operations: noticing that it stopped, and the numbers per environment

With the governor in place, the automation stops "dying quietly," but in exchange you get an intentional stop — "spent today's budget, that's it for now." To tell this apart from a failure, log or notify the end-of-day backlog and usage. If the backlog grows for several days in a row, that is a sign the arrival rate has outpaced capacity, and a cue to consider a Workspace account (100,000 calls/day) or batching several items into one call.

The same script has a ceiling that differs fivefold depending on the account type it runs under. If a migration is on the horizon, keep DAILY_QUOTA as a constant you can switch per environment, and you will not scramble later. For the current values of each Apps Script limit, see the Apps Script quotas and limitations documentation; reconciling your own environment's numbers before you go live is time well spent.

Wrapping up

When an Apps Script Gemini automation "only fails on busy days," the cause is often neither Gemini nor rate limiting but the UrlFetch daily call quota. It is easy to miss precisely because you have already solved runtime and trigger counts — a third ceiling behind the first two.

The fix is not to fight the limit but to decide how many calls you may spend per day and defer the rest to tomorrow. Reset the Script Properties budget ledger on the timezone boundary and record consumption from measured calls including retries, and even a spike day flattens out and clears in a few days. To start, add just a remainingBudget() log line to the automation you already run, and measure how many UrlFetch calls a steady day actually uses. Where to draw the margin becomes clear from that measurement.

For me, the month-end error alerts stopped the moment I added this budget ledger. If you run unattended automations the same way, I hope it helps toward a quieter operation. Thank you for reading.