Google Sheets Prompt Templates

AI prompt templates for Google Sheets. Create formulas, scripts, and automated workflows.

Overview

Google Sheets has some tricks that Excel doesn't, like QUERY, IMPORTRANGE, and Apps Script automation. These prompts help you tap into those features while also handling the basics. If you're building shared spreadsheets or automating workflows, you'll find templates here that go beyond simple formulas.

Best Practices

1

Mention if your sheet is shared with others, since some functions behave differently

2

Specify whether you want a formula solution or an Apps Script automation

3

Include your data range and structure, including which row has headers

4

Note if you need the solution to update automatically or run on a schedule

5

Ask for QUERY syntax if you're comfortable with SQL-like logic

Prompt Templates

1. QUERY Function Builder

Write a Google Sheets QUERY formula to [GOAL]. 

Data range: [RANGE]
Column structure: [COLUMNS]
Filter conditions: [CONDITIONS]
Sort by: [SORT]
Group by (if needed): [GROUP]

Include explanation of the QUERY syntax.
Write a Google Sheets QUERY formula to get total sales by region for orders over $100. Data range: A1:E500. Column structure: A=Date, B=Region, C=Product, D=Quantity, E=Amount. Filter conditions: Amount > 100. Sort by: Total descending. Group by: Region.
=QUERY(A1:E500, "SELECT B, SUM(E) WHERE E > 100 GROUP BY B ORDER BY SUM(E) DESC LABEL B 'Region', SUM(E) 'Total Sales'")

Breakdown: SELECT B, SUM(E) picks the Region column and sums Amount. WHERE E > 100 filters to orders over $100. GROUP BY B groups by region. ORDER BY SUM(E) DESC sorts highest first. LABEL renames the output columns. Note: Column letters in QUERY match your data range, so A in the query refers to the first column of your range.

When you need to filter, sort, and aggregate data in one formula

  • •QUERY uses column letters based on the range, not the sheet. If your range starts at B, then B becomes column A in QUERY
  • •Use LABEL to give your output columns readable names

2. IMPORTRANGE and Cross-Sheet Data

I need to pull data from another Google Sheet into my current sheet.

Source sheet URL: [URL_OR_DESCRIPTION]
Source range: [RANGE]
What I want to do with the data: [GOAL]
Should it filter or transform the data? [YES/NO - DETAILS]

Note any permission or access concerns.
I need to pull data from another Google Sheet (our sales team's tracker) into my reporting sheet. Source range: 'Sales Data'!A:F. What I want to do: Combine it with my local data and show only closed deals. Should it filter: Yes, only rows where column D (Status) equals 'Closed'.
Step 1 - Basic import:
=IMPORTRANGE("spreadsheet_url", "Sales Data!A:F")

First time you use this, you'll need to click the cell and grant access. Once connected, the data flows automatically.

Step 2 - Filter to closed deals:
=QUERY(IMPORTRANGE("spreadsheet_url", "Sales Data!A:F"), "SELECT * WHERE Col4 = 'Closed'")

Note: In QUERY with IMPORTRANGE, use Col1, Col2, etc. instead of A, B, C. The filter updates automatically as the source sheet changes.

Tip: Put the IMPORTRANGE in a hidden helper sheet if you're doing multiple operations on it. This avoids repeated access prompts and improves performance.

Pulling data from other spreadsheets for consolidated reporting

  • •IMPORTRANGE requires you to grant access once per source sheet
  • •Wrap IMPORTRANGE in QUERY to filter data before it hits your sheet

3. Apps Script Automation

Create a Google Apps Script that [AUTOMATION_GOAL].

Trigger: [WHEN_SHOULD_IT_RUN]
Input data location: [SHEET_AND_RANGE]
Output or action: [WHAT_SHOULD_HAPPEN]
Error handling: [WHAT_IF_SOMETHING_FAILS]

I'm [BEGINNER/INTERMEDIATE/ADVANCED] with Apps Script.
Create a Google Apps Script that sends an email summary every Monday morning with the previous week's sales totals. Trigger: Every Monday at 8am. Input data: 'Weekly Sales' sheet, columns A:D. Output: Email to sales-team@company.com with a formatted summary. Error handling: Log errors to a 'Script Logs' sheet. I'm a beginner with Apps Script.
function sendWeeklySalesSummary() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Weekly Sales');
 var data = sheet.getRange('A2:D' + sheet.getLastRow()).getValues();
 
 var total = 0;
 data.forEach(function(row) {
 total += row[3]; // Column D is index 3
 });
 
 var subject = 'Weekly Sales Summary - ' + new Date().toLocaleDateString();
 var body = 'Total sales last week: $' + total.toFixed(2) + '\n\nRows processed: ' + data.length;
 
 try {
 MailApp.sendEmail('sales-team@company.com', subject, body);
 } catch (e) {
 logError(e);
 }
}

function logError(error) {
 var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Script Logs');
 logSheet.appendRow([new Date(), error.message]);
}

To set up the trigger: Go to Triggers (clock icon) > Add Trigger > Choose sendWeeklySalesSummary > Time-driven > Week timer > Monday > 8-9am.

Automating repetitive tasks like sending reports, cleaning data, or syncing sheets

  • •Test your script manually before setting up time triggers
  • •Apps Script has daily quotas for email and other services. Check limits if you're sending many emails

4. Array Formulas for Dynamic Ranges

Create a Google Sheets formula that automatically expands as new data is added.

Goal: [WHAT_THE_FORMULA_SHOULD_DO]
Data location: [RANGE]
Output location: [WHERE_RESULTS_GO]
Should it handle blank rows? [YES/NO]
Create a formula that calculates running totals that automatically includes new rows. Goal: Show cumulative sum of column C. Data location: A:C (with headers in row 1). Output: Column D. Should handle blank rows: Yes, skip them.
=ARRAYFORMULA(IF(C2:C="", "", SUMIF(ROW(C2:C), "<=" & ROW(C2:C), C2:C)))

Put this in D2. It creates a running total for every row in column C, automatically extending as you add data. The IF wrapper leaves cells blank where there's no data in column C.

Alternative with SCAN (newer Google Sheets):
=ARRAYFORMULA(IF(C2:C="", "", SCAN(0, C2:C, LAMBDA(acc, val, IF(val="", acc, acc+val)))))

SCAN is cleaner for running totals, but the SUMIF version works on older sheets.

When you need formulas that grow with your data without manual updates

  • •ARRAYFORMULA goes in just one cell and fills down automatically
  • •Always wrap array formulas in IF to handle empty cells cleanly

Common Mistakes to Avoid

•

Using Excel syntax in QUERY (like column names instead of letters) when QUERY needs its own SQL-like syntax

•

Forgetting to grant IMPORTRANGE access, then wondering why the formula shows an error

•

Not wrapping ARRAYFORMULA results in IF statements, causing errors or unwanted values in blank rows

Frequently Asked Questions

Google Sheets has some tricks that Excel doesn't, like QUERY, IMPORTRANGE, and Apps Script automation. These prompts help you tap into those features while also handling the basics. If you're building shared spreadsheets or automating workflows, you'll find templates here that go beyond simple formulas.

Related Templates

SQL Query Prompt Templates

AI prompt templates for writing SQL queries. Create SELECT, JOIN, aggregate, and complex queries.

Data Analysis Prompt Templates

AI prompt templates for data analysis. Extract insights, identify patterns, and interpret results.

Data Visualization Prompt Templates

AI prompt templates for data visualization. Create effective charts, dashboards, and visual reports.

Have your own prompt to optimize?

Try Prompt Optimizer More Data & Analytics Prompts