Prompt Library

Google Sheets Workflows That Run Themselves

20 copy-paste prompts

20 ChatGPT prompts for formulas, QUERY functions, Apps Script automations, data cleaning, dashboards, and the Google-native features that Excel users miss.

Formulas & Functions

5 prompts

Formula Generator

1/20

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.

Generates Google Sheets-native formulas with ARRAYFORMULA and IFERROR wrapping.

💡

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

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.

Builds SQL-like QUERY formulas in Google Sheets with SELECT, WHERE, GROUP BY, and dynamic inputs.

💡

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

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.

Rewrites dragged formulas into single ARRAYFORMULA cells for cleaner, faster sheets.

💡

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

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.

Connects external data sources via IMPORT functions with authorization, refresh logic, and rate-limit guidance.

💡

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

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.

Builds multi-criteria and fuzzy lookups using XLOOKUP/FILTER/QUERY with fallback logic.

💡

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.

Prompts get you started. Tutorials level you up.

A growing library of 300+ hands-on AI tutorials. New tutorials added every week.

Start 7-Day Free Trial

Apps Script & Automation

5 prompts

Apps Script Generator

6/20

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.

Generates Apps Script with permissions, trigger setup, error handling, and safe test mode.

💡

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

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.

Builds custom menus and sidebars that turn Google Sheets into app-like interfaces for end users.

💡

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

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.

Automates targeted emails from sheet data with templates, quotas, retry logic, and a dry-run mode.

💡

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

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.

Builds a scheduled data refresh script with error notifications, archival, and quota-aware fetching.

💡

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

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.

Designs two-way sync between sheets/systems with conflict resolution, unique IDs, deletion detection, and audit logs.

💡

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 prompts

SPLIT / REGEX Data Cleaner

11/20

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.

Cleans messy data using REGEX and SPLIT functions wrapped in ARRAYFORMULA for bulk one-click application.

💡

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

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.

Detects and merges duplicates with flagging, merge strategy, near-match handling, and a review queue for edge cases.

💡

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

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.

Builds a multi-stage data pipeline with validation checkpoints and orchestration options.

💡

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

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.

Processes Google Form submissions with auto-categorization, confirmation emails, routing, and a dashboard.

💡

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

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.

Diagnoses slow Google Sheets and optimizes via formula replacement, splits, or BigQuery Connected Sheets.

💡

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.

Dashboards & Sharing

5 prompts

Interactive Dashboard Builder

16/20

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.

Builds a single-tab interactive dashboard with slicer-like drop-down controls, KPI layout, and mobile-safe design.

💡

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

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.

Sets up Connected Sheets with BigQuery for analyzing billions of rows with Google Sheets UX.

💡

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

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.

Sets up protected ranges, tiered permissions, named versions, edit alerts, and audit processes for shared sheets.

💡

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

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.

Automates Slides creation from sheet data with template-driven placeholders and email delivery.

💡

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

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.

Builds a mail-merge workflow from Sheets to Docs/PDF with placeholder replacement, folder organization, and email delivery.

💡

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.

Frequently Asked Questions

Google Sheets wins for collaboration (real-time editing, no file versioning), cloud-native automation (Apps Script), and integrations with other Google tools. Excel wins for large datasets (1M+ rows), complex VBA automations, and advanced statistical functions. Many power users use Google Sheets for team workflows and Excel for heavy analysis — pick based on the task, not loyalty.
Yes — ChatGPT is excellent at generating Apps Script (it's JavaScript). Provide: your sheet structure, what you want to happen, and any error messages you've hit. Always test in a copy of your sheet first, add error handling (try/catch), and include logging for debugging. Review generated code for security issues before running on production data.
ARRAYFORMULA applies a formula to entire columns without dragging. It keeps sheets clean (one formula per column vs thousands), updates automatically when rows are added, and often performs better. The tradeoff: some functions (SUMIFS, complex lookups) don't vectorize and need alternatives like SUMPRODUCT or QUERY. If you work with growing data, ARRAYFORMULA is essential.
Yes — Google Sheets has full pivot tables (Insert → Pivot Table). They support rows, columns, values, filters, and calculated fields. For most analysis, they're as capable as Excel pivot tables. For SQL-like grouping and aggregation, the QUERY function often beats pivot tables — especially for dashboards where you want the output formatted a specific way.
Use protected ranges (Data → Protect sheets and ranges) to lock formulas and critical cells while leaving input areas editable. For sensitive sheets, share with "view only" and give edit access via a Google Form that writes to the sheet — this prevents direct editing chaos. Name important versions (File → Version history → Name current version) to preserve recovery points.

Prompts are the starting line. Tutorials are the finish.

A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.

Start 7-Day Free TrialExplore AI for Productivity courses

7-day free trial. Cancel anytime.

Browse All Prompt Collections