Estimating a Sheets-Driven Gemini Bill Across the Year-End Price Switch with Apps Script

October is when I start looking at next year's costs. Opening the spreadsheet that drafts replies to app store reviews for me, I realized I had never once seen what that sheet costs, as a number.

It works, of course. A cell formula returns a draft reply, I edit it by hand, and I send it. I had done that for months. But the billing screen never told me which figure belonged to this sheet.

And the intro price for Gemini Flash has a deadline at the end of the year. I didn't want to scramble for an estimate after the increase. I wanted the sheet itself to show me two columns while there was still time.

So here is the Apps Script I wrote, as it is. It appends one row of token counts per call to a ledger tab, keeps a price table with dates, and prints a monthly figure at today's price and at the post-increase price, side by side.

What I'm assuming

I won't repeat the details of the deadline here. That the intro price ends on December 31, 2026, that the standard price starts on January 1, 2027, and that this applies across model generations: I checked those against the primary source in a separate article.

• Writing a Cost Estimate That Spans the Price Switch, in Two Columns from the Start

That one uses Python and the standard library. This one is for people who call Gemini from a spreadsheet and would rather not leave it.

One thing I haven't been able to confirm: in which time zone does the switch happen at midnight? In what I read for that earlier article, no time of day was given. So, as you'll see below, I treat December 31 and January 1 as a band and keep both numbers.

A ledger tab with one row per call

The raw material is the token count of each call. The API response carries usageMetadata: promptTokenCount for input and candidatesTokenCount for output. Models that think before answering also report thoughtsTokenCount, and I add that to the output side.

Create a tab named ledger with these five headers in row one.

The date is a string on purpose. An Apps Script Date follows the script's time zone setting. A one-day shift hardly matters for a monthly total, but it matters a great deal for deciding which side of the price switch a row falls on. Compare strings and that worry goes away.

Wrap your call like this.

function callGemini_(model, prompt, note) {
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/' +
              model + ':generateContent';
  const res = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-goog-api-key': key },
    payload: JSON.stringify({ contents: [{ parts: [{ text: prompt }] }] }),
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) {
    throw new Error('Gemini ' + res.getResponseCode() + ': ' +
                    res.getContentText().slice(0, 200));
  }
  const body = JSON.parse(res.getContentText());
  const u = body.usageMetadata || {};
  const out = (u.candidatesTokenCount || 0) + (u.thoughtsTokenCount || 0);
  const day = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd');
  SpreadsheetApp.getActive().getSheetByName('ledger')
    .appendRow([day, model, u.promptTokenCount || 0, out, note || '']);
  return body.candidates[0].content.parts[0].text;
}

The API key lives in script properties, not in the code (set GEMINI_API_KEY to YOUR_API_KEY, meaning your own key). The ledger holds numbers only, never the prompt text. It's a small line I drew so a customer's message can't end up copied into a tab by accident.

A price table that carries its dates

This is the heart of the estimate. Prices are not baked in as bare numbers; each row says when it applies.

// USD per 1M tokens. from / to are "YYYY-MM-DD"; to = null means no end announced
const PRICES = [
  { model: 'gemini-3.8-flash', from: '2026-09-02', to: '2026-12-31', input: 0.75, output: 3.75 },
  { model: 'gemini-3.8-flash', from: '2027-01-01', to: null,         input: 1.50, output: 7.50 },
  { model: 'gemini-3.7-flash', from: '2026-08-13', to: '2026-12-31', input: 0.75, output: 3.75 },
  { model: 'gemini-3.7-flash', from: '2027-01-01', to: null,         input: 1.50, output: 7.50 },
];
const PER = 1000000;
 
function priceOn_(model, day) {
  for (const r of PRICES) {
    if (r.model === model && r.from <= day && (r.to === null || day <= r.to)) return r;
  }
  throw new Error('No price row: ' + model + ' @ ' + day);
}
 
function costOf_(row, day) {
  const p = priceOn_(row.model, day);
  return (row.inTok * p.input + row.outTok * p.output) / PER;
}

The values are what I confirmed on the official pages at the time of writing. Compare them with the current official price table before you overwrite anything for your own use.

priceOn_ throws when no row matches. I deliberately don't return 0. A forgotten row quietly turning into a plausible, cheap number is the worst failure an estimate can have.

Thirty days, in two columns

Read the ledger, take the daily average of recent rows, stretch it to thirty days, and pass in which day's prices to use.

function projectMonth_(rows, day) {
  const days = new Set(rows.map(r => r.day));
  const perModel = {};
  for (const r of rows) perModel[r.model] = (perModel[r.model] || 0) + costOf_(r, day);
  let total = 0;
  for (const m in perModel) {
    perModel[m] = perModel[m] / days.size * 30;
    total += perModel[m];
  }
  return { perModel: perModel, total: total };
}
 
function estimate() {
  const ss = SpreadsheetApp.getActive();
  const values = ss.getSheetByName('ledger').getDataRange().getValues().slice(1);
  const rows = values.map(v => ({
    day: String(v[0]), model: v[1], inTok: Number(v[2]), outTok: Number(v[3]),
  }));
  const sheet = ss.getSheetByName('estimate') || ss.insertSheet('estimate');
  sheet.clear();
  sheet.appendRow(['basis day', 'model', 'per 30 days (USD)']);
  ['2026-12-31', '2027-01-01'].forEach(function (day) {
    const r = projectMonth_(rows, day);
    for (const m in r.perModel) sheet.appendRow([day, m, Math.round(r.perModel[m] * 100) / 100]);
    sheet.appendRow([day, 'total', Math.round(r.total * 100) / 100]);
  });
}

The two basis days are the band I mentioned. Wherever the boundary actually falls, the real figure lands on one of the two.

Output from my machine

I pulled out only the ledger logic (up to projectMonth_) and ran it in Node.js on seven days of sample rows: an invented ledger mixing days on 3.8 Flash with days on 3.7 Flash.

2026-12-31 gemini-3.8-flash $8.21 | gemini-3.7-flash $6.19 TOTAL $14.39
2027-01-01 gemini-3.8-flash $16.41 | gemini-3.7-flash $12.38 TOTAL $28.79

The amounts mean nothing in themselves; look at the shape. On the January 1 side, both models are exactly double the December 31 side. Input and output prices both double, so with the same ledger the month doubles too. The sheet just makes that visible in numbers.

Staying on an older generation doesn't avoid the doubling. What you can still change is how much you call. Since I started producing the two columns, I think about shortening outputs before I think about switching models, because the output price is five times the input price.

When the price table has a hole

Try asking for a day's price with the 3.6 Flash row missing.

ERR No price row: gemini-3.6-flash @ 2026-10-03

In the sheet, estimate stops there and the same message appears in the execution log. That stop is the point. It's the fastest way to learn that a new model name has appeared in the ledger while the price table is stale.

What to try first

You don't need all of it at once. This is the order I followed.

1. Create the ledger tab and put the five headers in row one.
2. Wrap one existing call with callGemini_, run it for a day, and only check that rows appear.
3. Build the price table with just the models you actually use.
4. Run estimate after a week of data has accumulated.

With only a few days in the ledger, the average skews. Stretching a holiday-heavy week to thirty days will come out cheaper than reality.

Before showing a number, leave one line saying which day's prices and which period's volume it came from. I'd like to keep to that line, whatever the numbers turn out to be.

If you're starting today, I'd begin by creating the ledger tab.