Google Sheets Workflows That Run Themselves
20 ChatGPT prompts for formulas, QUERY functions, Apps Script automations, data cleaning, dashboards, and the Google-native features that Excel users miss.
In short: This page contains 20 copy-paste ready prompts, organized into 4 categories with a description and pro tip for each. The first 5 prompts are free instantly, no signup needed. Drafted with AI writing assistance and checked against our editorial policy.
Formulas & Functions
5 promptsFormula Generator
1/20✨ What it does
ChatGPT writes a Google Sheets formula for [describe what I want to calculate], with Sheets-versus-Excel notes and IFERROR wrapping. Add [describe columns, headers, sample rows] and [blanks, errors, specific matches], then paste it into your sheet and check a few rows.
Write a Google Sheets formula that [describe what I want to calculate]. My data: [describe columns, headers, sample rows]. Edge cases: [blanks, errors, specific matches]. Deliver: (1) the formula in Google Sheets syntax (note differences from Excel if any), (2) break down each part, (3) handle errors cleanly with IFERROR, (4) suggest ARRAYFORMULA for whole-column application, (5) show expected output for 2-3 inputs.
Pro tip: Google Sheets formulas are subtly different from Excel — arguments often separated by commas vs semicolons depending on locale. ARRAYFORMULA is the Google Sheets superpower — one formula cascades down entire columns without dragging.
QUERY Function Builder
2/20✨ What it does
ChatGPT builds a QUERY formula for [describe what I need] with SELECT, WHERE, GROUP BY, ORDER BY, and LIMIT as needed. Point it at [range], then tweak the clauses until your output matches [filter, group, sort, aggregate].
Write a Google Sheets QUERY function for [describe what I need]. Source data location: [range]. Columns: [describe]. Output: [filter, group, sort, aggregate]. Deliver: (1) the QUERY formula with SELECT, WHERE, GROUP BY, ORDER BY, LIMIT as needed, (2) explain each clause, (3) handle dynamic inputs with concatenation if needed, (4) a non-QUERY alternative with FILTER/SORT/UNIQUE, (5) when QUERY beats pivot tables and vice versa.
Pro tip: QUERY is Google Sheets' secret weapon — essentially SQL inside a cell. It outperforms most formula-based filtering and grouping by 10×. Learn the syntax (SELECT, WHERE, GROUP BY, ORDER BY) and you replace 80% of complex formulas.
ARRAYFORMULA Rewrite
3/20✨ What it does
ChatGPT rewrites [paste formula] as a single ARRAYFORMULA in [e.g., A2], with vectorize gotchas and a speed note versus dragging. Delete the dragged copies, then confirm the one cell fills your whole [describe] range.
Convert this dragged-down formula to ARRAYFORMULA: [paste formula]. Range it applies to: [describe]. Rewrite it as: (1) a single ARRAYFORMULA in cell [e.g., A2], (2) explain how ARRAYFORMULA processes the range, (3) any gotchas (functions that don't vectorize like SUM, some lookups), (4) performance comparison vs dragged formulas, (5) when to use ARRAYFORMULA vs dragging vs LAMBDA-based approach.
Pro tip: ARRAYFORMULA replaces 1,000 dragged cells with 1 cell. It's faster, cleaner, and doesn't break when new rows are added. One sheet can have 99% fewer formulas — maintainability goes through the roof.
IMPORTRANGE & IMPORTDATA
4/20✨ What it does
ChatGPT writes the exact [IMPORTRANGE / IMPORTDATA / IMPORTHTML / IMPORTXML] formula for [URL or sheet reference], plus auth steps, refresh behavior, and error handling. Grant access if it is IMPORTRANGE, then watch one refresh before you rely on it.
Help me connect external data to this sheet via [IMPORTRANGE / IMPORTDATA / IMPORTHTML / IMPORTXML]. Source: [URL or sheet reference]. What I need to import: [describe]. Deliver: (1) the exact formula, (2) authorization steps if IMPORTRANGE, (3) handling of changes (does it refresh automatically?), (4) error handling (permission denied, source moved), (5) refresh triggers and rate limits, (6) an Apps Script alternative for more control.
Pro tip: IMPORTRANGE between sheets is powerful but fragile — sheet renames and permission changes break it silently. For critical workflows, copy the data with Apps Script on a schedule instead. One-click convenience isn't worth silent breakage.
Advanced Lookup Chain
5/20✨ What it does
ChatGPT builds a multi-criteria or fuzzy lookup with XLOOKUP, FILTER, or INDEX/MATCH and a fallback when nothing matches. Spell out the match you need, then test a missing row so you see the fallback fire.
Build an advanced lookup for [describe scenario — e.g., multi-criteria match, lookup with fallback, fuzzy match]. My data structure: [describe]. Deliver: (1) the formula using XLOOKUP, FILTER, or INDEX/MATCH as appropriate, (2) handle missing matches with a specific fallback, (3) case-sensitive / case-insensitive handling, (4) performance notes if data is large, (5) alternative using QUERY if cleaner. Show which approach I should use and why.
Pro tip: VLOOKUP in Google Sheets is obsolete — XLOOKUP handles missing values better, works in any direction, and is easier to read. If you're still using VLOOKUP, switching to XLOOKUP is the single biggest readability upgrade you can make.
Apps Script & Automation
5 promptsApps Script Generator
6/20✨ What it does
ChatGPT writes a complete Apps Script to email rows, sync sheets, or auto-fill on a trigger, with comments and the permissions you must grant. Paste it via Extensions then Apps Script, then authorize it on a copy of [describe sheet structure] first.
Write a Google Apps Script that [describe what I need — e.g., email rows, sync sheets, auto-fill based on trigger]. My setup: [describe sheet structure]. Deliver: (1) complete JavaScript code with comments, (2) how to paste into Apps Script editor (Extensions → Apps Script), (3) required permissions and authorization flow, (4) how to set the trigger (manual button, time-based, onEdit, onFormSubmit), (5) error handling, (6) a test mode that runs on dummy data first.
Pro tip: Apps Script is Google Sheets' version of VBA but running in the cloud. It's how you send emails, create Calendar events, call APIs, and automate cross-app workflows. Most power users unlock Apps Script only once they realize formulas hit a ceiling.
Custom Menu & Sidebar
7/20✨ What it does
ChatGPT builds onOpen() plus each action in [list actions], and the HTML plus showSidebar() code if you want a sidebar. Reload the sheet to see the menu, then click every item on dummy data.
Build a custom menu / sidebar for my Google Sheet. Menu name: [describe]. Menu items needed: [list actions]. Deliver: (1) the onOpen() function creating the custom menu, (2) each menu action as a separate function, (3) if sidebar: the HTML template and showSidebar() code, (4) how users install it (if distributing), (5) error messages that make sense to non-technical users, (6) permission scopes needed.
Pro tip: Custom menus make Google Sheets feel like a real app. Non-technical users can click "Send Report" without knowing there's a script. 20 minutes of menu-building saves hundreds of "how do I run the macro?" questions.
Email Automation Script
8/20✨ What it does
ChatGPT writes an Apps Script that emails from your sheet using MailApp or GmailApp, with a [template description], failed-send handling, and a dry run. Set the trigger to [time-based, onEdit, or manual], then send one test to yourself first.
Write an Apps Script to automate emails from this sheet: [describe use case — notifications, reminders, reports]. Trigger: [time-based, onEdit, or manual]. Email content: [template description]. Deliver: (1) the script using MailApp or GmailApp, (2) a template with variable substitution, (3) handling of failed sends (logging, retry logic), (4) sender identity and quota considerations, (5) how to dry-run to logs before sending for real, (6) a kill-switch for emergency stop.
Pro tip: Apps Script has a 100-email-per-day quota on free accounts (1,500 on Workspace). Always test in dry-run mode first by logging to console — one buggy script can blast your whole contact list with garbage.
Scheduled Data Refresh
9/20✨ What it does
ChatGPT builds a scheduled refresh from [API, another sheet, scraped site, database via connector] into the target sheet, with a time trigger and a failure email. Set it to [hourly, daily, weekly], then watch one run before you leave it unattended.
Build an Apps Script that refreshes / updates data on a schedule. Source of data: [API, another sheet, scraped site, database via connector]. Target sheet: [describe]. Schedule: [hourly, daily, weekly]. Deliver: (1) the fetch/sync logic, (2) the time-driven trigger setup, (3) error notifications (email me if the job fails), (4) archival of old data before overwriting, (5) a test function to run manually before scheduling, (6) quota considerations for your data source.
Pro tip: Scheduled scripts that fail silently are worse than no automation. Always wrap the main logic in try/catch and email yourself on failure. Knowing something broke is more valuable than the automation itself.
Two-Way Sheet Sync
10/20✨ What it does
ChatGPT designs two-way sync between [this sheet] and [another sheet / system], with unique IDs, deletion detection, and [last-write-wins, timestamp-based, manual] conflict rules. Implement both directions, then force a conflict on purpose and check your audit log.
Design a two-way sync between [this sheet] and [another sheet / system]. Fields to sync: [list]. Conflict resolution rules: [last-write-wins, timestamp-based, manual]. Deliver: (1) the sync architecture (which side is source of truth when), (2) Apps Script implementation for both directions, (3) unique ID strategy per row, (4) detection of deleted rows, (5) a sync log for auditing, (6) test scenarios to validate before going live.
Pro tip: Two-way sync is harder than it looks — conflict resolution, deleted rows, and race conditions bite you fast. If one side can be read-only and the other source-of-truth, pick one-way sync. Only go two-way when absolutely necessary.
Data Cleaning & Transformation
5 promptsSPLIT / REGEX Data Cleaner
11/20✨ What it does
ChatGPT writes SPLIT, REGEXEXTRACT, REGEXREPLACE, TRIM, and CLEAN formulas, sequenced so you can wrap them in ARRAYFORMULA. Paste 5 to 10 messy rows, then apply the sequence to the whole column in one cell.
Help me clean this messy data in Google Sheets: [paste sample — 5-10 rows]. Issues: [describe — mixed formats, inconsistent casing, multiple values in one cell, etc.]. Deliver: (1) formulas using SPLIT, REGEXEXTRACT, REGEXREPLACE, TRIM, CLEAN, (2) step-by-step cleaning sequence, (3) how to apply with ARRAYFORMULA to all rows at once, (4) a QUERY to verify cleaning worked, (5) how to make this re-runnable next month. Prioritize formulas over manual work.
Pro tip: REGEXEXTRACT and REGEXREPLACE unlock most cleanup tasks — emails from text, phone numbers from paragraphs, dates from strings. Spending 30 minutes learning regex basics pays back 100×.
Duplicate Detection & Merge
12/20✨ What it does
ChatGPT flags duplicates in [describe sheet/range] with a QUERY or formula, a merge rule, and a review queue for near matches. Choose [exact match / fuzzy match on specific columns], then merge only the rows you have checked by eye.
Find and handle duplicates in [describe sheet/range]. Definition of "duplicate": [exact match / fuzzy match on specific columns]. Deliver: (1) a QUERY or formula to flag duplicates, (2) a merge strategy — keep first, keep last, or merge values, (3) handling of near-duplicates (trailing spaces, casing), (4) output to a separate "clean" sheet without destroying original, (5) a review queue for ambiguous cases. Bonus: an Apps Script for auto-dedupe on edit.
Pro tip: Never delete duplicates directly from the original data — always copy to a "clean" sheet first. Original data preservation saves you when someone says "wait, those weren't duplicates, they were separate transactions."
Data Transformation Pipeline
13/20✨ What it does
ChatGPT builds a Raw to Staging to Clean to Analysis pipeline from [source] to your target shape, with formulas or QUERY and checks between stages. Lay out those four tabs, then stop at any stage that fails a check.
Build a data transformation pipeline from raw [source] to analysis-ready format. Raw structure: [describe]. Target structure: [describe]. Deliver: (1) each transformation step in order, (2) formulas or QUERY for each step, (3) sheet layout (Raw → Staging → Clean → Analysis), (4) validation checks between stages, (5) refresh instructions, (6) an Apps Script to orchestrate if manual updates are error-prone.
Pro tip: Data pipelines in Google Sheets work when you follow the "one sheet per stage" pattern: Raw → Staging → Clean → Analysis. Mixing stages in one sheet makes debugging impossible when numbers look wrong.
Form Response Processor
14/20✨ What it does
ChatGPT adds derived-field formulas plus an onFormSubmit Apps Script to [auto-categorize, send confirmation email, route to team member, tag as priority]. Map [list] to those actions, then submit a fake form response and watch your route fire.
Process Google Form responses automatically. Form fields: [list]. What I need to happen: [auto-categorize, send confirmation email, route to team member, tag as priority]. Deliver: (1) the formulas in the response sheet to derive fields, (2) an onFormSubmit Apps Script trigger, (3) conditional logic per response type, (4) confirmation email template, (5) Slack/email notification for priority responses, (6) a dashboard summarizing responses.
Pro tip: Google Forms + Sheets + Apps Script = free CRM, free helpdesk, free registration system. Before paying for SaaS, see if this combo solves 80% of your need. For small teams, it usually does.
Large Dataset Optimizer
15/20✨ What it does
ChatGPT diagnoses a slow sheet of [rows × columns] with [slow to open, formulas take forever, crashes], then replaces volatile formulas and says when to split or move to BigQuery. Apply the faster versions, then reopen your file and see if it breathes.
My Google Sheet is slow. Size: [rows × columns]. Symptoms: [slow to open, formulas take forever, crashes]. Diagnose and optimize: (1) identify the slowest elements (volatile formulas, large ARRAYFORMULAs, excessive IMPORTRANGE), (2) replace with faster alternatives, (3) when to split into multiple sheets, (4) when to move to BigQuery + Connected Sheets, (5) Apps Script patterns that handle large data better than formulas. Target: make it responsive again.
Pro tip: Google Sheets comfortable max is around 5 million cells — after that, it crawls. If you're over 1 million rows, stop fighting and move to BigQuery + Connected Sheets. You keep the sheet UX with database-level performance.
Like these prompts? There are full tutorials behind them.
Learn the workflows, not just the prompts. Easy-to-follow tutorials inside AI Academy, and the library keeps growing.
Dashboards & Sharing
5 promptsInteractive Dashboard Builder
16/20✨ What it does
ChatGPT builds a one-tab dashboard for [describe purpose] with KPI formulas, drop-down plus FILTER/QUERY controls, and mobile-safe charts. Point it at [describe source], then click every drop-down on your phone before you share it.
Build an interactive dashboard in Google Sheets for [describe purpose]. Data: [describe source]. Audience: [describe]. Deliver: (1) dashboard tab layout — KPIs, charts, filter controls, (2) formulas/QUERY for each KPI, (3) slicer equivalents in Google Sheets (using drop-downs + FILTER/QUERY), (4) chart design recommendations, (5) conditional formatting for visual storytelling, (6) mobile viewing considerations (smaller screens). Single tab, no scrolling.
Pro tip: Google Sheets doesn't have native slicers like Excel, but drop-down lists + FILTER/QUERY approximate the same behavior. Users select a filter, dashboard updates. 90% as good, 100% as free.
Connected Sheets + BigQuery
17/20✨ What it does
ChatGPT walks you through Connected Sheets to BigQuery: table prep, the Data connector, pivots and charts on live data, and refresh timing. Connect the use case you named, then run one pivot on a sample query before you open the firehose.
Help me set up Connected Sheets with BigQuery. Use case: [describe — analyzing large data, real-time business metrics, etc.]. Deliver: (1) BigQuery table/view prep, (2) how to connect from Google Sheets (Insert → Data connector), (3) how to pivot and chart BigQuery data as if it were native, (4) refresh scheduling, (5) cost considerations (BigQuery query pricing), (6) permission model for sharing. Pros/cons vs importing data into Sheets.
Pro tip: Connected Sheets is the best-kept Google Workspace secret. Analyze billions of rows in a pivot table, charts refresh on demand, cost a few cents per query. If your company uses BigQuery, this is non-negotiable.
Shared Sheet Governance
18/20✨ What it does
ChatGPT designs protected ranges, view-only versus edit tiers, named versions, and edit alerts for a sheet shared with [team size] people. Lock your input cells first, then name the current version before the next messy week.
Design governance for a Google Sheet shared with [team size] people. Current issues: [describe — accidental edits, version chaos, permission confusion]. Deliver: (1) protected ranges strategy, (2) edit permissions vs view-only tiers, (3) version history discipline (Named versions via File → Version history → Name current version), (4) notification rules (edit alerts), (5) a "how to use this sheet" tab for collaborators, (6) a quarterly audit process. Make chaos rare.
Pro tip: Shared sheets without governance become graveyards of broken formulas. Lock the formulas, open the input ranges, name the critical versions. 1 hour of setup prevents 100 "who deleted the VLOOKUP?" mysteries.
Sheet-to-Slides Generator
19/20✨ What it does
ChatGPT writes Apps Script that reads the sheet and fills a Slides template using {{variable}} placeholders, plus how to email the deck. Build the template slides, then generate one sample deck and check every placeholder in your file.
Automate Google Slides report generation from this sheet. Data: [describe]. Slides I need: [describe — title, KPI summary, chart breakdown, etc.]. Deliver: (1) Apps Script that reads from sheet + writes to a Slides template, (2) the Slides template structure, (3) placeholder syntax (use {{variable}} in slides), (4) how to insert live charts (linked vs pasted), (5) trigger setup (manual button or scheduled), (6) email delivery of the finished deck.
Pro tip: Weekly executive reports that take hours manually can become one-click button clicks. Template once, automate once, save forever. This is the kind of automation that makes you seem like a wizard at work.
Sheet-to-Doc Mail Merge
20/20✨ What it does
ChatGPT builds a mail merge from Sheets to Docs or PDF for [contracts, invoices, personalized letters], one Doc per row with name, date, and amount swaps. Point it at [Google Doc URL or describe], then generate one file and read it before you run the batch.
Build a mail merge from Google Sheets → Google Docs or PDF. Use case: [contracts, invoices, personalized letters]. Source data: [describe]. Template location: [Google Doc URL or describe]. Deliver: (1) Apps Script that reads rows and generates one Doc per row, (2) placeholder replacement (name, date, amount, etc.), (3) file naming convention per output, (4) Drive folder organization, (5) optional PDF conversion, (6) optional email delivery with the generated Doc attached.
Pro tip: Paid mail-merge tools charge $10-50/month for what Apps Script does for free. Once you learn the pattern (read sheet → clone template → replace placeholders → save/send), you can generate thousands of personalized docs in minutes.
Free tool
Formula Genie
Describe what you need and get the exact Excel or Google Sheets formula.
Frequently Asked Questions
Prompts are the starting line. Tutorials are the finish.
A growing library of hands-on tutorials on ChatGPT, Claude, Midjourney, and many other AI tools. New tutorials are added regularly.
7-day free trial. Cancel anytime.
Related guides