# Apps Script + Gemini Lab DiscoveryVIP · October 2026 The browser lab uses fixed local fixtures, not live Gemini output. Code.gs is a real Apps Script starter and requires your own API access. ## 1. Connect the pieces Apps Script runs your automation on Google’s servers. Gemini generates content through an API. UrlFetchApp sends the HTTP request; SpreadsheetApp reads and writes the spreadsheet. A Gemini subscription in a consumer app is not the same thing as API access. Try: Draw this flow: sheet → prompt → request → response → review → sheet. Check: Which service sends the request? Answer: UrlFetchApp. Use UrlFetchApp.fetch(url, options) for HTTP requests. ## 2. Choose bound or standalone For a spreadsheet menu, open a test spreadsheet and choose Extensions → Apps Script. That creates a bound project. A standalone project at script.google.com can open a spreadsheet by ID. Use explicit file IDs in background jobs; an active spreadsheet is not a reliable background dependency. Try: Create a disposable spreadsheet with input in A2. Leave B2 empty. Check: What is best for a scheduled standalone job? Answer: Use openById with a configured ID. Explicit IDs make the data source unambiguous. ## 3. Configure the project In Apps Script Project Settings, add Script Properties GEMINI_API_KEY and GEMINI_MODEL. Obtain API access through Google AI Studio and select an available model that supports generateContent. Model availability, pricing and quotas change; verify them before running. This guide deliberately does not hard-code a model version. Try: Set both properties in your own project, never in this browser lab. Check: Where does the sample read configuration? Answer: Script Properties. PropertiesService.getScriptProperties() retrieves project settings. ## 4. Understand property access Script Properties keep configuration out of source code, but they are not a vault isolated from project editors. Editors can write code to read them. Limit project sharing, restrict keys where supported and rotate exposed credentials. User Properties are user-scoped and require a different setup flow. Try: List who can edit your bound project before adding a key. Check: Who should be trusted with project edit access? Answer: Only people trusted with its configuration. Project editors can access script configuration. ## 5. Read a cell deliberately getRange("A2").getDisplayValue() gives displayed text. getValues() gives a two-dimensional array of typed values for a range. Validate empty or oversized input before sending. Read only the approved cells; sending a whole sheet can disclose unrelated information. Try: Compare a formatted date’s display value with its underlying value. Check: Which reads a matrix of values? Answer: getValues(). A range can contain multiple rows and columns. ## 6. Write a useful prompt Separate the task, output format and source data. State that source text is untrusted material, not instructions. Ask the model to preserve uncertainty and avoid inventing facts. This separation helps, but cannot guarantee resistance to prompt injection. Human review still matters. Try: Use the prompt workbench to add a task, format and source text. Check: How should source text be treated? Answer: As data to analyze. External content should not control the automation. ## 7. Build the payload generateContent accepts contents containing parts. A simple text input is {contents:[{parts:[{text:prompt}]}]}. JSON.stringify serializes a JavaScript object into the request body. A raw object passed as payload can be treated as form data instead of JSON. Try: Inspect the lab request and locate contents[0].parts[0].text. Check: What serializes the body? Answer: JSON.stringify. Stringify converts the object to JSON text. ## 8. Send the request UrlFetchApp.fetch uses method: "post", contentType: "application/json", an x-goog-api-key header and the JSON payload. muteHttpExceptions: true allows inspection of HTTP error responses. Transport failures can still throw exceptions. Apps Script requests require authorization for external access. Try: Inspect the generated Code.gs request options. Check: What does muteHttpExceptions enable? Answer: Inspect HTTP error responses. It changes handling of HTTP failures, not every possible exception. ## 9. Read the response Read getResponseCode() before treating the body as a result. Parse getContentText() with JSON.parse. Text may appear in multiple candidate parts. Do not assume candidates[0].content.parts[0].text always exists. Blocked or incomplete generations need a separate outcome. Try: Switch the simulator to blocked and truncated responses. Check: What should be checked before using a result? Answer: HTTP status and response structure. Success transport and usable output are different checks. ## 10. Review before writing Write generated text as a draft to a dedicated output cell. Preserve the source and avoid overwriting an existing result silently. Sheets can interpret a leading equals sign as a formula; the starter escapes it. Do not let model output become executable code or an automatic email instruction. Try: Run the fixture and explicitly approve it before the simulated write. Check: What is the safest default? Answer: Review a draft first. A review boundary prevents a text-generation mistake becoming an action. ## 11. Add a menu A bound onOpen function can create a menu with SpreadsheetApp.getUi().createMenu().addItem().addToUi(). Put the Gemini call in the menu handler, which the user runs and authorizes. A simple onOpen trigger cannot use services that require authorization. Try: Download the starter, reload your test sheet, then run the menu item. Check: Where should the API call go? Answer: In the authorized menu handler. Use onOpen to build the menu, not to make authorized external calls. ## 12. Handle errors by category 400 suggests an invalid request; 403 often indicates permissions or key restrictions; 404 can indicate an unavailable model or route; 429 indicates a limit or exhausted quota; 503 suggests temporary unavailability. These are diagnostic clues, not proof of one exact cause. Try: Try every failure fixture and compare its coaching message. Check: Should you retry every 400 unchanged? Answer: No, inspect the request first. Invalid requests need correction, not repeated traffic. ## 13. Retry within a budget Retry only failures appropriate to retry, with bounded attempts and increasing delay plus jitter. Check Retry-After when present. A retry can consume time and paid usage; a transport failure may occur after the provider processed a request. The starter performs one attempt so you can inspect errors deliberately. Try: Explain why 429 and 400 need different handling. Check: What is a useful retry boundary? Answer: A small attempt and time budget. Backoff should remain bounded by runtime and usage limits. ## 14. Process rows in batches Read a small range once, skip completed rows, process a limited number and write results with explicit status. Batch sheet reads and writes reduce service overhead, but each model request still has its own cost and latency. Keep a resume cursor and do not assume generation calls are free or unlimited. Try: Plan columns for Input, Draft, Status and Updated time. Check: What should a resume workflow do? Answer: Skip completed work. A status column or cursor reduces accidental repeats. ## 15. Prevent overlapping jobs A script lock can reduce overlapping executions. Acquire it with a timeout and release in finally. Persist progress only after the intended step succeeds. Locks alone do not guarantee exactly-once delivery across remote API calls and spreadsheet writes. Try: Describe what happens if generation succeeds but the sheet write fails. Check: Does a lock guarantee exactly-once API usage? Answer: No. Remote side effects and local writes can fail between steps. ## 16. Move from demo to useful automation Start with a manual, one-cell summarizer. Then adapt the task to a reply draft or action-item extraction. Test known examples, blank input, hostile instructions, missing credentials, blocked output and failures. Add scheduled triggers only after the manual path is reliable. A successful HTTP response does not establish factual accuracy. Try: Use the launch checklist, then test the downloaded starter with non-sensitive data. Check: What establishes output quality? Answer: Checks against known examples and human review. Evaluate the content separately from transport success. ## References https://ai.google.dev/api https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app https://developers.google.com/apps-script/guides/properties https://developers.google.com/apps-script/guides/triggers