ChatGPT Prompts for Google Sheets

VLOOKUP with error handling

Write a VLOOKUP formula that:
- Looks up the value in cell A2 in the first column of Sheet2!A:D
- Returns the value from the 3rd column of that range
- Handles cases where no match is found by returning "Not found" instead of #N/A
- Also show me the INDEX-MATCH equivalent that achieves the same result

Multi-condition SUMIFS for business reporting

Write a SUMIFS formula that totals sales amounts from column C where:
- The date in column A is within the current month
- The region in column B matches the value in cell F2
- The status in column D is either "Closed" or "Won"
Explain each argument so I can adapt it for other conditions.

ARRAYFORMULA for auto-calculated columns

I want column E to automatically calculate the profit margin for every row: (Revenue in column C - Cost in column D) / Revenue in column C, formatted as a percentage.
Write an ARRAYFORMULA that:
- Goes in cell E1 and covers all rows automatically as new data is added
- Skips the header row
- Shows a blank instead of an error when column C is empty

Dynamic tiered commission calculator

Write a formula that calculates commission for sales amounts in column B using these tiers:
- 0–$10,000: 5%
- $10,001–$50,000: 8%
- $50,001+: 12%

The commission should apply the correct rate to the full amount (not marginal — flat rate per tier).
Show me both a nested IF version and an IFS version.

Normalise messy text data

I have a column of company names that are inconsistent — some are all caps, some lowercase, some with extra spaces, some with "Ltd", "Limited", "Ltd." inconsistently.
Write formulas (or an Apps Script) to:
1. Trim extra spaces (leading, trailing, and double-spaces)
2. Convert to proper case (first letter of each word capitalised)
3. Standardise "Limited" / "Ltd." / "Ltd" to "Ltd"
Show me the formula approach first, then the Apps Script approach for bulk processing.

Split full names into first and last

Column A contains full names in the format "FirstName LastName". Some entries have middle names too: "FirstName MiddleName LastName".
Write formulas to:
- Column B: Extract first name only
- Column C: Extract last name only (the last word in the string)
- Column D: Flag rows that have a middle name (TRUE/FALSE)
Handle edge cases where someone has only one name.

Remove duplicates and log changes via Apps Script

Write an Apps Script that:
1. Scans column A for duplicate values (keeping the first occurrence)
2. Deletes all duplicate rows
3. Logs the number of rows deleted and the values that were removed to a "Change Log" sheet with timestamp
4. Sends a summary email to [my email] after completion

Add error handling so if something goes wrong, the script logs the error and exits cleanly without partial deletion.

Email validation with formula

Write a formula in column B that validates whether each email address in column A is properly formatted.
Mark each cell as:
- "Valid" — if it matches standard email format (has @ sign, domain, extension)
- "Invalid" — if it's missing @ sign or domain
- "Empty" — if column A is blank

Use REGEXMATCH. Explain the regex pattern so I can modify it if needed.

Monthly KPI dashboard with auto-update

Design a Google Sheets dashboard structure for tracking these monthly KPIs: Total Revenue, New Customers, Churn Rate, Average Order Value, and MoM Growth %.

Include:
- Where to put raw data vs summary
- QUERY or SUMIFS formulas to pull monthly totals automatically
- Conditional formatting rules to flag metrics above/below target
- SPARKLINE formulas to show trends in-cell
- A drop-down to filter by region or product line

Cohort retention analysis

I have customer transaction data with columns: Customer ID, First Purchase Date, Transaction Date, Revenue.
Build a cohort analysis in Google Sheets that:
- Groups customers by the month of their first purchase (cohort)
- Shows retention rate for each cohort in months 0, 1, 2, 3, 6, 12
- Calculates cumulative revenue per cohort
- Uses conditional formatting to heat-map retention (green = high, red = low)
Provide both the data structure and the formulas needed.

Sales pipeline tracker with probability weighting

Create a sales pipeline sheet with:
- Columns: Deal Name, Stage, Deal Value, Close Date, Probability %, Owner
- A weighted pipeline value column (Value × Probability)
- Summary section showing: Total Pipeline, Weighted Pipeline, Count by Stage
- QUERY formula that shows only deals closing in the next 30 days
- Conditional formatting: red for past close dates, yellow for <7 days, green for on track

Budget vs actuals variance tracker

Build a budget vs actuals sheet for [monthly/quarterly] tracking with:
- Input area for budget figures by category
- Input area for actual spend
- Variance column (Actual - Budget) with conditional formatting (red = over budget)
- % variance column
- YTD totals for both budget and actuals
- A SPARKLINE trend chart for each category
- Alert formula that flags any category more than 10% over budget

Automated invoice generator

Write an Apps Script that generates a PDF invoice from Google Sheets data:
- Input sheet has: Client Name, Email, Line Items (description, qty, unit price)
- Script calculates subtotal, tax (at rate in cell B1), and total
- Creates a formatted invoice in a new sheet
- Exports it as PDF to Google Drive in a folder called "Invoices/[Month-Year]"
- Emails the PDF to the client email address
Include a trigger so it runs when a "Generate Invoice" checkbox is ticked in column H.

Slack or email alert when threshold is crossed

Write an Apps Script that monitors a Google Sheet and sends an alert when:
- Inventory in column C drops below the reorder point in column D
- A deal in the pipeline sheet changes stage to "Closed Lost"
- A budget category exceeds 90% of its budget allocation

For each alert, send an email to [email address] with: which item triggered the alert, current value, threshold, and a link to the sheet.
Set up the trigger to run every hour.

Form response auto-assignment and notification

I have a Google Form that collects support ticket submissions. Responses go to a Google Sheet.
Write an Apps Script (triggered on form submit) that:
1. Assigns each new ticket to a team member from a rotation list in Sheet2!A:A
2. Adds "Assigned" status and assignee name to columns G and H
3. Sends an email to the assignee with ticket details and a link to the sheet
4. Sends a confirmation email to the submitter with their ticket number
Include error handling if the rotation list is empty.

Weekly report email with data summary

Write an Apps Script that sends a weekly email report every Monday at 8am containing:
- Total revenue for the past 7 days (from the transactions sheet)
- Number of new customers this week
- Top 3 products by revenue this week
- Any budget categories that exceeded their weekly allocation
Format the email in clean HTML with a table. Send to a distribution list stored in Sheet2!A:A.
Set up the time-based trigger automatically within the script.

QUERY function for dynamic filtering

I have a dataset on Sheet1 with columns: Date, Region, Product, Sales, Returns, Net Sales.
Write QUERY formulas for:
1. Show all rows where Region = the value in cell B1 AND Sales > 1000, sorted by Date descending
2. Summarise total Net Sales and count of transactions by Product for the current month
3. Show top 10 products by Net Sales for a date range specified by cells D1 and D2

Explain the QUERY syntax so I can write my own variations.

IMPORTRANGE with dynamic sheet reference

I want to pull data from multiple Google Sheets (one per month) into a master summary sheet.
Sheet URLs are stored in column A of my "Sources" tab.
Write a solution that:
- Uses IMPORTRANGE to pull the "Summary" tab from each source sheet
- Combines them into one continuous table on the master sheet
- Handles errors gracefully if a source sheet is unavailable
- Includes a column showing which source sheet each row came from

Scenario planning model

Build a scenario planning model in Google Sheets with three scenarios: Conservative, Base Case, Optimistic.
Structure:
- Assumptions section (inputs that change per scenario): growth rate, margin, CAC, churn
- Dropdown in cell B1 to select which scenario to view
- All calculated outputs update automatically based on selection
- Comparison table showing all three scenarios side by side
- Key insight row showing which metrics differ most between scenarios
Use named ranges and indirect references so the model is easy to maintain.

Live API data integration via Apps Script

Write an Apps Script that fetches data from a public REST API (example: exchange rates from https://api.exchangerate-api.com/v4/latest/USD) and writes it to my Google Sheet:
- Fetch the JSON response
- Parse the relevant fields (in this case: currency codes and rates)
- Write them to a sheet called "Exchange Rates" with headers and today's timestamp
- Set a daily trigger to refresh automatically
- Handle API errors with a logged error message rather than crashing

Show me how to adapt this pattern for any REST API that returns JSON.