Create Pivot Table
sheets_create_pivot_tableCreate a pivot table from spreadsheet data, specifying row/column groupings, value aggregations, filters, and date or numeric bucketing options.
Instructions
Create a pivot table from spreadsheet data with full Google Sheets UI feature support.
Args:
spreadsheet_id (string): The ID of the Google Spreadsheet
source_range (string): A1 notation range (e.g., 'Sheet1!A1:E100', 'Sales!A:F')
destination_sheet_id (number, optional): Sheet ID for pivot (default: creates new sheet)
destination_sheet_name (string): Name for new sheet (default: 'Pivot Table')
rows/columns (array): Groupings (at least one row OR column required)
source_column: Column letter ('A') or index (0)
label: Custom display name
show_totals: Show subtotals (default: true)
sort_order: 'ASCENDING' or 'DESCENDING'
sort_by_value: { value_index: 0 } - Sort by aggregated value instead of alphabetically
group_rule: Bucketing options (pick one):
{ date_time: { type: 'MONTH' } } - Group dates (YEAR, QUARTER, MONTH, DAY_OF_WEEK, etc.)
{ histogram: { interval: 100, start: 0, end: 1000 } } - Numeric buckets
{ manual: { groups: [{ group_name: 'West', items: ['CA', 'WA', 'OR'] }] } }
group_limit: Max groups to display
values (array, required): Aggregations
source_column: Column to aggregate (or use formula)
formula: Custom formula like '=Revenue/Quantity' (use with summarize_function: 'CUSTOM')
summarize_function: SUM, COUNT, COUNTA, COUNTUNIQUE, AVERAGE, MAX, MIN, MEDIAN, PRODUCT, STDEV, STDEVP, VAR, VARP, CUSTOM
name: Display name
calculated_display_type: 'PERCENT_OF_ROW_TOTAL', 'PERCENT_OF_COLUMN_TOTAL', 'PERCENT_OF_GRAND_TOTAL'
filters (array, optional): Filter source data
source_column: Column to filter
visible_values: ['Active', 'Pending'] - Show only these values
condition: { type: 'NUMBER_GREATER', values: [100] } - Filter by condition
value_layout: 'HORIZONTAL' or 'VERTICAL' (default: 'HORIZONTAL')
Examples:
Date grouped: rows=[{source_column: "A", group_rule: {date_time: {type: "MONTH"}}}], values=[{source_column: "E", summarize_function: "SUM"}]
Sorted by value: rows=[{source_column: "A", sort_by_value: {value_index: 0}, sort_order: "DESCENDING"}], values=[{source_column: "E", summarize_function: "SUM"}]
Filtered: filters=[{source_column: "B", visible_values: ["Active"]}], rows=[{source_column: "A"}], values=[{source_column: "E", summarize_function: "SUM"}]
Percentage: values=[{source_column: "E", summarize_function: "SUM", calculated_display_type: "PERCENT_OF_GRAND_TOTAL"}]
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||