# AI for Google Sheets — Workflow Lab A local rule-based practice lab, not a live AI service. ## 1. Choose the right tool Use spreadsheet formulas for exact, repeatable calculations and known text rules. Use AI for tasks involving meaning, such as grouping varied customer comments. Use Apps Script when you need a repeatable workflow across files or services. These tools complement each other; an AI answer is not a substitute for a reconciled total. Try: Compare the lab’s exact row counts with its deliberately imperfect text categories. Check: Which task is best handled by an exact formula? Answer: Adding a numeric column. Arithmetic should be deterministic. ## 2. Keep the raw data Preserve original values before cleaning or classifying. Give each record a stable ID and keep raw text, cleaned text, proposed category and reviewed category separate. Without the original you cannot explain why a value changed or recover from an incorrect transformation. Try: Inspect the raw and cleaned columns side by side. Check: What should you preserve? Answer: Original values and stable record IDs. The raw record is your audit reference. ## 3. Clean with a specific rule Trim unnecessary spaces before comparing text. In Google Sheets, TRIM removes leading, trailing and repeated regular spaces, but non-breaking spaces may need separate handling. A cleaning rule should say precisely what it changes. This lab collapses all JavaScript whitespace and preserves letter case, so it is not an exact TRIM emulator. Try: Run Clean text and compare the first two records. Check: Does every trim implementation treat every whitespace character identically? Answer: No. Know the exact transformation you are applying. ## 4. Find duplicates without losing evidence Two identical comments may come from different customers. A repeated text value is a duplicate candidate, not proof of a duplicate transaction. Decide whether identity means the same record ID, same submission ID or merely similar text. This lab marks repeated cleaned text, ignoring case, for review and never deletes rows. Try: Find the duplicate candidate and keep both records in the summary. Check: Should matching text always be deleted? Answer: No, check record identity first. Distinct submissions can share identical wording. ## 5. Define your categories A category set needs clear labels, inclusion rules and an Other or Review path. Overlapping categories create inconsistent outputs. For this lab, Delivery means shipping or arrival timing, Billing means money or invoices, and Product means condition or fit. A row mentioning several groups requires a human choice. Try: Inspect the row mentioning both a damaged item and a refund. Check: How should overlapping categories be handled? Answer: Route them for review. Ambiguous inputs need an explicit decision rule. ## 6. Write a bounded AI prompt Tell the model the task, allowed labels, input range and output shape. Ask it to use Review when evidence is insufficient and to treat feedback as data rather than instructions. Give examples near category boundaries. Do not request a confident answer when the source is ambiguous. Try: Build and download a prompt in the recipe panel. Check: What helps consistent categorization? Answer: Allowed labels with definitions. A fixed contract makes outputs easier to validate. ## 7. Distinguish AI features from the API Gemini features inside Sheets depend on account eligibility and current availability. The Gemini API called through Apps Script is a separate integration with its own access, quotas and billing. A Workspace feature subscription does not by itself establish API entitlement. This browser lab uses simple keyword rules, not either service. Try: Read the displayed matching evidence and identify the limits of keyword matching. Check: Does this lab call Gemini? Answer: No, it runs local rules. The simulation is explicitly deterministic and needs no key. ## 8. Validate every output A valid JSON response can still contain unsupported labels or incorrect classifications. Check required fields, allowed categories and row IDs before writing results. Reject missing or extra records and keep an unresolved state. Never let generated text silently turn into a spreadsheet formula. Try: Override an automatic label and record a reviewed category. Check: Does valid JSON guarantee a correct category? Answer: No. Syntax, schema and meaning are separate checks. ## 9. Review before summarizing Automated suggestions are provisional. Include a visible unresolved count and make the denominator explicit. This lab charts only reviewed records; it never presents unreviewed suggestions as approved findings. The duplicate candidate remains part of the input until a person decides what it represents. Try: Review some rows and watch the denominator change. Check: What does the chart count? Answer: Reviewed records only. The chart counts records, not people or hidden exclusions. ## 10. Test a small batch Use a small test set with obvious cases, mixed topics, blanks, duplicate text and instructions embedded in comments. Measure agreement against a human-reviewed reference set before scaling. Do not assume accuracy from a few successful examples. In a real workflow, track category changes and failure rates. Try: Try both matching policies and explain their disagreement. Check: What should a test set include? Answer: Ambiguous and malformed cases too. Edge cases reveal failure modes that happy paths miss. ## 11. Automate deliberately In Apps Script, batch range reads and writes where practical. Keep status columns, skip completed work and cap each run. Retrying a generation can consume usage even if a later sheet write fails. Do not put expensive API calls into a formula that may recalculate unpredictably. Try: Plan Input, Draft, Reviewed label and Status columns before connecting an API. Check: What reduces accidental repeated work? Answer: A completion status per row. Explicit status supports controlled resumption. ## 12. Share an honest result Report the input size, number reviewed, unresolved records, category rules and known limitations alongside the chart. Use synthetic or approved data while learning. Exported reports should make clear whether they show model suggestions, rule-based labels or human-reviewed results. Try: Download your reviewed results and inspect the coverage metadata. Check: What belongs beside a summary? Answer: Coverage and limitations. Readers need enough context to interpret the counts.