Google Sheets Analyze Data
When to Use
- User asks to "analyze this spreadsheet" or "summarize the numbers"
- User wants a "pivot table" or "chart"
- User asks to "highlight" or "format" data
- User wants "totals by category" or any aggregation
- User asks to set up "dropdowns" or "validation"
Tools
Discovery
- GOOGLESHEETS_SEARCH_SPREADSHEETS — Find spreadsheets by name
- GOOGLESHEETS_GET_SPREADSHEET_INFO — Get spreadsheet metadata
- GOOGLESHEETS_GET_SHEET_NAMES — List sheets within a spreadsheet
- GOOGLESHEETS_VALUES_GET — Read cell values and structure
- GOOGLESHEETS_BATCH_GET — Read multiple ranges at once
Analysis
- GOOGLESHEETS_EXECUTE_SQL — Run SQL queries against sheet data
- SELECT, WHERE, GROUP BY, ORDER BY, SUM, AVG, COUNT, etc.
Visualization
- GOOGLESHEETS_CUSTOM_CREATE_PIVOT_TABLE — Summarize data by dimensions
- rows: Grouping columns (e.g., ["Region", "Product"])
- values: Aggregations [{column: "Sales", aggregation: "SUM"}]
- GOOGLESHEETS_CUSTOM_CREATE_CHART — Create visual charts
- Types: BAR, LINE, PIE, COLUMN, AREA, SCATTER, COMBO
Formatting
- GOOGLESHEETS_CUSTOM_ADD_CONDITIONAL_FORMAT — Visual rules
- value_based: >, <, =, contains, between
- color_scale: Gradient across range
- custom_formula: Advanced rules
- GOOGLESHEETS_CUSTOM_SET_DATA_VALIDATION — Input restrictions
- dropdown_list: List of allowed values
- dropdown_range: Values from another range
- number: Min/max constraints
- date: Date restrictions
- custom_formula: Advanced validation
Sharing
- GOOGLESHEETS_CUSTOM_SHARE_SPREADSHEET — Share with users
- recipients: Email list
- role: reader, writer, commenter
Workflow
Step 1: Understand the Data (CRITICAL)
Before any analysis, read the data:
GOOGLESHEETS_SEARCH_SPREADSHEETS(query="sales data")
GOOGLESHEETS_GET_SHEET_NAMES(spreadsheet_id="...")
GOOGLESHEETS_VALUES_GET(
spreadsheet_id="...",
range="Sheet1!A1:Z5"
)
Study the results to understand:
- Column headers and their meaning
- Data types (numbers, dates, text, categories)
- Number of rows (to gauge dataset size)
- Any patterns or anomalies
Step 2: Choose the Right Analysis Tool
| Need |
Tool |
| Simple aggregation (totals, averages) |
EXECUTE_SQL |
| Cross-dimensional summary |
CUSTOM_CREATE_PIVOT_TABLE |
| Visual patterns and trends |
CUSTOM_CREATE_CHART |
| Highlight specific values |
CUSTOM_ADD_CONDITIONAL_FORMAT |
| Restrict input options |
CUSTOM_SET_DATA_VALIDATION |
Step 3: Execute Analysis
SQL Query Example:
GOOGLESHEETS_EXECUTE_SQL(
spreadsheet_id="...",
query="SELECT Category, SUM(Amount), AVG(Amount) FROM Sheet1 GROUP BY Category ORDER BY SUM(Amount) DESC"
)
Pivot Table Example:
GOOGLESHEETS_CUSTOM_CREATE_PIVOT_TABLE(
spreadsheet_id="...",
source_sheet="Sales",
rows=["Region", "Product"],
values=[
{"column": "Revenue", "aggregation": "SUM"},
{"column": "Orders", "aggregation": "COUNT"}
]
)
Chart Example:
GOOGLESHEETS_CUSTOM_CREATE_CHART(
spreadsheet_id="...",
sheet_name="Sales",
chart_type="BAR",
title="Revenue by Region"
)
Step 4: Apply Visual Formatting
Conditional formatting:
GOOGLESHEETS_CUSTOM_ADD_CONDITIONAL_FORMAT(
spreadsheet_id="...",
sheet_name="Sales",
range="D2:D100",
condition="greater_than",
condition_values=["1000"],
background_color="#4CAF50"
)
Data validation (dropdowns):
GOOGLESHEETS_CUSTOM_SET_DATA_VALIDATION(
spreadsheet_id="...",
sheet_name="Tracker",
range="B2:B100",
validation_type="dropdown_list",
values=["High", "Medium", "Low"]
)
Step 5: Present Insights
Don't just return raw data — interpret it:
- "Total revenue is $X, with Region Y contributing 45%"
- "The top 3 categories account for 80% of sales"
- "There's a clear upward trend from Q1 to Q3"
Then offer: "Want me to create a chart to visualize this?"
Step 6: Range Notation Reference
- Basic:
Sheet1!A1:B10
- Entire column:
Sheet1!A:A
- Entire row:
Sheet1!1:1
- Names with spaces:
'My Sheet'!A1:B10
- Always include sheet name in multi-sheet spreadsheets
Important Rules
- Understand data first — Always read structure before analyzing
- Choose right tool — SQL for queries, pivot for summaries, charts for visuals
- Interpret results — Present insights, not just raw numbers
- Offer visualizations — After analysis, suggest charts when appropriate
- Respect data — Don't modify source data unless asked; create new sheets for analysis
- Destructive actions need consent — Deleting sheets, clearing ranges, overwriting data
1---2name: googlesheets-analyze-data3description: Analyze spreadsheet data intelligently - understand structure, choose right analysis tool (SQL/pivot/chart), apply formatting, present insights.4target: google_sheets_agent5metadata:6 source: https://skillmd.com/skills/theexperiencecompany/googlesheets-analyze-data7---89# Google Sheets Analyze Data1011## When to Use12- User asks to "analyze this spreadsheet" or "summarize the numbers"13- User wants a "pivot table" or "chart"14- User asks to "highlight" or "format" data15- User wants "totals by category" or any aggregation16- User asks to set up "dropdowns" or "validation"1718## Tools1920### Discovery21- **GOOGLESHEETS_SEARCH_SPREADSHEETS** — Find spreadsheets by name22- **GOOGLESHEETS_GET_SPREADSHEET_INFO** — Get spreadsheet metadata23- **GOOGLESHEETS_GET_SHEET_NAMES** — List sheets within a spreadsheet24- **GOOGLESHEETS_VALUES_GET** — Read cell values and structure25- **GOOGLESHEETS_BATCH_GET** — Read multiple ranges at once2627### Analysis28- **GOOGLESHEETS_EXECUTE_SQL** — Run SQL queries against sheet data29 - SELECT, WHERE, GROUP BY, ORDER BY, SUM, AVG, COUNT, etc.3031### Visualization32- **GOOGLESHEETS_CUSTOM_CREATE_PIVOT_TABLE** — Summarize data by dimensions33 - rows: Grouping columns (e.g., ["Region", "Product"])34 - values: Aggregations [{column: "Sales", aggregation: "SUM"}]35- **GOOGLESHEETS_CUSTOM_CREATE_CHART** — Create visual charts36 - Types: BAR, LINE, PIE, COLUMN, AREA, SCATTER, COMBO3738### Formatting39- **GOOGLESHEETS_CUSTOM_ADD_CONDITIONAL_FORMAT** — Visual rules40 - value_based: >, <, =, contains, between41 - color_scale: Gradient across range42 - custom_formula: Advanced rules43- **GOOGLESHEETS_CUSTOM_SET_DATA_VALIDATION** — Input restrictions44 - dropdown_list: List of allowed values45 - dropdown_range: Values from another range46 - number: Min/max constraints47 - date: Date restrictions48 - custom_formula: Advanced validation4950### Sharing51- **GOOGLESHEETS_CUSTOM_SHARE_SPREADSHEET** — Share with users52 - recipients: Email list53 - role: reader, writer, commenter5455## Workflow5657### Step 1: Understand the Data (CRITICAL)5859Before any analysis, read the data:6061```62GOOGLESHEETS_SEARCH_SPREADSHEETS(query="sales data")63```64```65GOOGLESHEETS_GET_SHEET_NAMES(spreadsheet_id="...")66```67```68GOOGLESHEETS_VALUES_GET(69 spreadsheet_id="...",70 range="Sheet1!A1:Z5"71)72```7374Study the results to understand:75- Column headers and their meaning76- Data types (numbers, dates, text, categories)77- Number of rows (to gauge dataset size)78- Any patterns or anomalies7980### Step 2: Choose the Right Analysis Tool8182| Need | Tool |83|------|------|84| Simple aggregation (totals, averages) | EXECUTE_SQL |85| Cross-dimensional summary | CUSTOM_CREATE_PIVOT_TABLE |86| Visual patterns and trends | CUSTOM_CREATE_CHART |87| Highlight specific values | CUSTOM_ADD_CONDITIONAL_FORMAT |88| Restrict input options | CUSTOM_SET_DATA_VALIDATION |8990### Step 3: Execute Analysis9192**SQL Query Example:**93```94GOOGLESHEETS_EXECUTE_SQL(95 spreadsheet_id="...",96 query="SELECT Category, SUM(Amount), AVG(Amount) FROM Sheet1 GROUP BY Category ORDER BY SUM(Amount) DESC"97)98```99100**Pivot Table Example:**101```102GOOGLESHEETS_CUSTOM_CREATE_PIVOT_TABLE(103 spreadsheet_id="...",104 source_sheet="Sales",105 rows=["Region", "Product"],106 values=[107 {"column": "Revenue", "aggregation": "SUM"},108 {"column": "Orders", "aggregation": "COUNT"}109 ]110)111```112113**Chart Example:**114```115GOOGLESHEETS_CUSTOM_CREATE_CHART(116 spreadsheet_id="...",117 sheet_name="Sales",118 chart_type="BAR",119 title="Revenue by Region"120)121```122123### Step 4: Apply Visual Formatting124125**Conditional formatting:**126```127GOOGLESHEETS_CUSTOM_ADD_CONDITIONAL_FORMAT(128 spreadsheet_id="...",129 sheet_name="Sales",130 range="D2:D100",131 condition="greater_than",132 condition_values=["1000"],133 background_color="#4CAF50"134)135```136137**Data validation (dropdowns):**138```139GOOGLESHEETS_CUSTOM_SET_DATA_VALIDATION(140 spreadsheet_id="...",141 sheet_name="Tracker",142 range="B2:B100",143 validation_type="dropdown_list",144 values=["High", "Medium", "Low"]145)146```147148### Step 5: Present Insights149150Don't just return raw data — interpret it:151- "Total revenue is $X, with Region Y contributing 45%"152- "The top 3 categories account for 80% of sales"153- "There's a clear upward trend from Q1 to Q3"154155Then offer: "Want me to create a chart to visualize this?"156157### Step 6: Range Notation Reference158159- Basic: `Sheet1!A1:B10`160- Entire column: `Sheet1!A:A`161- Entire row: `Sheet1!1:1`162- Names with spaces: `'My Sheet'!A1:B10`163- Always include sheet name in multi-sheet spreadsheets164165## Important Rules1661. **Understand data first** — Always read structure before analyzing1672. **Choose right tool** — SQL for queries, pivot for summaries, charts for visuals1683. **Interpret results** — Present insights, not just raw numbers1694. **Offer visualizations** — After analysis, suggest charts when appropriate1705. **Respect data** — Don't modify source data unless asked; create new sheets for analysis1716. **Destructive actions need consent** — Deleting sheets, clearing ranges, overwriting data