Putting Gemini and Google Workspace to Work — Docs, Sheets, Drive, and an Apps Script That Actually Runs

In Sheets, the question shapes the answer

The worst possible prompt against a Sheets selection is "analyse this data." What comes back is a paragraph that gently observes an average and a direction of travel.

There is only one useful fix: decide the shape of the answer before you ask. Against the monthly AdMob figures, the phrasing I settled on runs roughly like this.

"Revenue moved month over month. Determine whether that movement is explained by (a) impression volume, (b) eCPM, or (c) both, then list the two largest contributors in order. Quote the figures from the columns."

Once you supply the axis of decomposition, the reply stops being commentary and becomes a verdict. A verdict is something you can check. Commentary is not.

The other clause that pulls real weight is the demand for quotation. Adding "quote the figures from the columns" noticeably cuts the rate at which unsourced numbers slip in — and when one does slip in, walking back to the cited column exposes it immediately.

Cross-folder summaries belong in Apps Script

You can gather information across Drive folders from the side panel, but if it is a monthly job, automate it. Anything that depends on a human starting it will be skipped in a busy month.

The script below reads the text-bearing files in a folder, sends them to the Gemini API in a single call, and creates a summary Doc. Store the API key in Script Properties and it runs as written.

const MODEL = 'gemini-3-flash';
const ENDPOINT =
  'https://generativelanguage.googleapis.com/v1beta/models/' +
  MODEL + ':generateContent';
 
function summarizeFolder(folderId, title) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('GEMINI_API_KEY');
  if (!key) throw new Error('GEMINI_API_KEY is not set');
 
  const docs = collectText(folderId);
  if (docs.length === 0) throw new Error('No matching files in folder');
 
  const brief = [
    'The following files belong to a single project.',
    'Merge duplicated statements and organise under three headings:',
    '1. Decided  2. Open questions  3. Next actions',
    'End every item with the source file name in parentheses.',
  ].join('\n');
 
  const body = {
    contents: [{
      role: 'user',
      parts: [{ text: brief + '\n\n' + docs.join('\n\n---\n\n') }],
    }],
    generationConfig: { temperature: 0.2 },
  };
 
  const res = UrlFetchApp.fetch(ENDPOINT, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-goog-api-key': key },
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
  });
 
  if (res.getResponseCode() !== 200) {
    throw new Error('API error: ' + res.getContentText().slice(0, 300));
  }
 
  const parts = JSON.parse(res.getContentText())
    .candidates[0].content.parts;
  const text = parts.map(function (p) { return p.text || ''; }).join('');
 
  const doc = DocumentApp.create(title + ' summary ' +
    Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd'));
  doc.getBody().appendParagraph(text);
  return doc.getUrl();
}
 
function collectText(folderId) {
  const out = [];
  const files = DriveApp.getFolderById(folderId).getFiles();
  while (files.hasNext()) {
    const f = files.next();
    const mime = f.getMimeType();
    if (mime === MimeType.GOOGLE_DOCS) {
      out.push('# ' + f.getName() + '\n' +
        DocumentApp.openById(f.getId()).getBody().getText());
    } else if (mime === MimeType.PLAIN_TEXT) {
      out.push('# ' + f.getName() + '\n' + f.getBlob().getDataAsString());
    }
  }
  return out;
}

Three details in there are deliberate.

muteHttpExceptions: true lets the script inspect the status code itself. Without it, Apps Script throws before you can read the response body, and you end up re-running a call without ever seeing what the API objected to. This was the first thing that caught me out: I mistook a 400 for a 429, added backoff that could never help, and lost half a day narrowing it down.

The parts array is joined rather than indexed. Responses are not guaranteed to arrive as a single part, and parts[0].text will silently truncate the day that changes.

And the brief insists on source file names. A summary gets harder to verify precisely as it gets easier to read. Leaving the provenance inline means you can chase down the one line that looks wrong instead of re-reading everything.

One more thing that matters in real use: Apps Script executions are time-limited, so handing a large folder to this function straight will get you cut off mid-run. The fix is unglamorous — chunk what collectText() returns at around twenty files and run the folder in several passes. It finishes sooner than trying to do it all at once.

Checks before generated text touches a decision

If a summary is going to inform a real decision, it needs a floor of verification. Mine adds no extra API calls and comes in two passes.

The first pass reconciles numbers. Pull every figure out of the answer with a regular expression and confirm it exists in the source. One unmatched figure sends the whole summary to manual review.

function unsourcedNumbers(answer, sourceText) {
  const nums = (answer.match(/\d[\d,]*(\.\d+)?/g) || [])
    .map(function (s) { return s.replace(/,/g, ''); })
    .filter(function (s) { return s.length >= 2; });
  const src = sourceText.replace(/,/g, '');
  const unique = Array.from(new Set(nums));
  return unique.filter(function (n) { return src.indexOf(n) === -1; });
}

The second pass checks structure: are all three headings present, and does every item carry its parenthetical source? In my experience, when that scaffolding breaks the substance has usually broken with it.

Both are a handful of lines, and the payoff was larger than I expected — the number of times I took a monthly summary purely on trust dropped sharply. The aim is not a complete quality judgment. It is stopping the obviously broken output. At the scale one person operates at, that trade feels right.

Three rules I settled on running this solo

Running several apps and blogs alone means the convenient features are exactly the ones that need rules, or they fall over.

First, the model ID lives in one constant in the Apps Script and nowhere else. Workspace will swap its internal models on its own schedule, but anything you wrote yourself is yours to keep current — and scattered IDs turn every swap into a search party.

Second, the API key goes in Script Properties, never in the code. Apps Script is easy to share, and whatever is in the source gets shared with it.

Third, every generated Doc carries its generation date in the title. Six months later, a summary with no date attached was worth about as much as no summary at all. For anything you may want to read chronologically later — App Store and Google Play review histories are the obvious case — that small habit pays for itself.

Where not to push it

Even with tight integration there are edges. Pulling live values from an external API is not something Workspace finishes on its own. Reproducing deeply nested conditional logic was never stable for me. And pointing this at a very large sheet in one go makes runtime unpredictable.

None of that is really "cannot." It is "another tool will be faster." Forcing everything into one environment adds steps rather than removing them.

One thing to try next

Pick a single Drive folder you find yourself revisiting every month and run summarizeFolder() against its ID once. Read the Doc it produces, then pull up two or three of the files named in parentheses and see whether the claims hold. If they do, that folder is safe to put on a trigger. If they do not, the fix starts with rewriting the axis of decomposition in the brief.

Whether something gets automated comes down to one question for me: can I verify what it produces? Not whether it saves time. I hope some of this proves useful in your own setup.