Pivot Tables

{
  "rows": [
    {
      object (PivotGroup)
    }
  ],
  "columns": [
    {
      object (PivotGroup)
    }
  ],
  "criteria": {
    integer: {
      object (PivotFilterCriteria)
    },
    ...
  },
  "filterSpecs": [
    {
      object (PivotFilterSpec)
    }
  ],
  "values": [
    {
      object (PivotValue)
    }
  ],
  "valueLayout": enum (PivotValueLayout),
  "dataExecutionStatus": {
    object (DataExecutionStatus)
  },

  // Union field source_data can be only one of the following:
  "source": {
    object (GridRange)
  },
  "dataSourceId": string
  // End of list of possible types for union field source_data.
}

object (PivotGroup)

Each row grouping in the pivot table.

object (PivotGroup)

Each column grouping in the pivot table.

map (key: integer, value: object ( PivotFilterCriteria))

An optional mapping of filters per source column offset.

The filters are applied before aggregating data into the pivot table. The map's key is the column offset of the source range that you want to filter, and the value is the criteria for that column.

For example, if the source was C10:E15, a key of 0 will have the filter for column C, whereas the key 1 is for column D.

This field is deprecated in favor of filterSpecs.

object (PivotFilterSpec)

The filters applied to the source columns before aggregating data for the pivot table.

Both criteria and filterSpecs are populated in responses. If both fields are specified in an update request, this field takes precedence.

object (PivotValue)

A list of values to include in the pivot table.

enum (PivotValueLayout)

Whether values should be listed horizontally (as columns) or vertically (as rows).

object (DataExecutionStatus)

Output only. The data execution status for data source pivot tables.

object (GridRange)

The range the pivot table is reading data from.

string

The ID of the data source the pivot table is reading data from.

{
  "showTotals": boolean,
  "valueMetadata": [
    {
      object (PivotGroupValueMetadata)
    }
  ],
  "sortOrder": enum (SortOrder),
  "valueBucket": {
    object (PivotGroupSortValueBucket)
  },
  "repeatHeadings": boolean,
  "label": string,
  "groupRule": {
    object (PivotGroupRule)
  },
  "groupLimit": {
    object (PivotGroupLimit)
  },

  // Union field source can be only one of the following:
  "sourceColumnOffset": integer,
  "dataSourceColumnReference": {
    object (DataSourceColumnReference)
  }
  // End of list of possible types for union field source.
}

boolean

True if the pivot table should include the totals for this grouping.

object (PivotGroupValueMetadata)

Metadata about values in the grouping.

enum (SortOrder)

The order the values in this group should be sorted.

object (PivotGroupSortValueBucket)

The bucket of the opposite pivot group to sort by. If not specified, sorting is alphabetical by this group's values.

boolean

True if the headings in this pivot group should be repeated. This is only valid for row groupings and is ignored by columns.

By default, we minimize repetition of headings by not showing higher level headings where they are the same. For example, even though the third row below corresponds to "Q1 Mar", "Q1" is not shown because it is redundant with previous rows. Setting repeatHeadings to true would cause "Q1" to be repeated for "Feb" and "Mar".

+--------------+
| Q1     | Jan |
|        | Feb |
|        | Mar |
+--------+-----+
| Q1 Total     |
+--------------+

string

The labels to use for the row/column groups which can be customized. For example, in the following pivot table, the row label is Region (which could be renamed to State) and the column label is Product (which could be renamed Item). Pivot tables created before December 2017 do not have header labels. If you'd like to add header labels to an existing pivot table, please delete the existing pivot table and then create a new pivot table with same parameters.

+--------------+---------+-------+
| SUM of Units | Product |       |
| Region       | Pen     | Paper |
+--------------+---------+-------+
| New York     |     345 |    98 |
| Oregon       |     234 |   123 |
| Tennessee    |     531 |   415 |
+--------------+---------+-------+
| Grand Total  |    1110 |   636 |
+--------------+---------+-------+

object (PivotGroupRule)

The group rule to apply to this row/column group.

object (PivotGroupLimit)

The count limit on rows or columns to apply to this pivot group.

integer

The column offset of the source range that this grouping is based on.

For example, if the source was C10:E15, a sourceColumnOffset of 0 means this group refers to column C, whereas the offset 1 would refer to column D.

object (DataSourceColumnReference)

The reference to the data source column this grouping is based on.

{
  "value": {
    object (ExtendedValue)
  },
  "collapsed": boolean
}

object (ExtendedValue)

The calculated value the metadata corresponds to. (Note that formulaValue is not valid, because the values will be calculated.)

boolean

True if the data corresponding to the value is collapsed.

{
  "valuesIndex": integer,
  "buckets": [
    {
      object (ExtendedValue)
    }
  ]
}

integer

The offset in the PivotTable.values list which the values in this grouping should be sorted by.

object (ExtendedValue)

Determines the bucket from which values are chosen to sort.

For example, in a pivot table with one row group & two column groups, the row group can list up to two values. The first value corresponds to a value within the first column group, and the second value corresponds to a value in the second column group. If no values are listed, this would indicate that the row should be sorted according to the "Grand Total" over the column groups. If a single value is listed, this would correspond to using the "Total" of that bucket.

{

  // Union field rule can be only one of the following:
  "manualRule": {
    object (ManualRule)
  },
  "histogramRule": {
    object (HistogramRule)
  },
  "dateTimeRule": {
    object (DateTimeRule)
  }
  // End of list of possible types for union field rule.
}

object (ManualRule)

A ManualRule.

object (HistogramRule)

A HistogramRule.

object (DateTimeRule)

A DateTimeRule.

{
  "groups": [
    {
      object (ManualRuleGroup)
    }
  ]
}

object (ManualRuleGroup)

The list of group names and the corresponding items from the source data that map to each group name.

{
  "groupName": {
    object (ExtendedValue)
  },
  "items": [
    {
      object (ExtendedValue)
    }
  ]
}

object (ExtendedValue)

The group name, which must be a string. Each group in a given ManualRule must have a unique group name.

object (ExtendedValue)

The items in the source data that should be placed into this group. Each item may be a string, number, or boolean. Items may appear in at most one group within a given ManualRule. Items that do not appear in any group will appear on their own.

{
  "interval": number,
  "start": number,
  "end": number
}

number

The size of the buckets that are created. Must be positive.

number

The minimum value at which items are placed into buckets of constant size. Values below start are lumped into a single bucket. This field is optional.

number

The maximum value at which items are placed into buckets of constant size. Values above end are lumped into a single bucket. This field is optional.

{
  "type": enum (DateTimeRuleType)
}

enum (DateTimeRuleType)

The type of date-time grouping to apply.

{
  "countLimit": integer,
  "applyOrder": integer
}

integer

The count limit.

integer

The order in which the group limit is applied to the pivot table.

Pivot group limits are applied from lower to higher order number. Order numbers are normalized to consecutive integers from 0.

For write request, to fully customize the applying orders, all pivot group limits should have this field set with an unique number. Otherwise, the order is determined by the index in the PivotTable.rows list and then the PivotTable.columns list.

{
  "visibleValues": [
    string
  ],
  "condition": {
    object (BooleanCondition)
  },
  "visibleByDefault": boolean
}

string

Values that should be included. Values not listed here are excluded.

object (BooleanCondition)

A condition that must be true for values to be shown. ( visibleValues does not override this -- even if a value is listed there, it is still hidden if it does not meet the condition.)

Condition values that refer to ranges in A1-notation are evaluated relative to the pivot table sheet. References are treated absolutely, so are not filled down the pivot table. For example, a condition value of =A1 on "Pivot Table 1" is treated as 'Pivot Table 1'!$A$1.

The source data of the pivot table can be referenced by column header name. For example, if the source data has columns named "Revenue" and "Cost" and a condition is applied to the "Revenue" column with type NUMBER_GREATER and value =Cost, then only columns where "Revenue" > "Cost" are included.

boolean

Whether values are visible by default. If true, the visibleValues are ignored, all values that meet condition (if specified) are shown. If false, values that are both in visibleValues and meet condition are shown.

{
  "filterCriteria": {
    object (PivotFilterCriteria)
  },

  // Union field source can be only one of the following:
  "columnOffsetIndex": integer,
  "dataSourceColumnReference": {
    object (DataSourceColumnReference)
  }
  // End of list of possible types for union field source.
}

object (PivotFilterCriteria)

The criteria for the column.

integer

The zero-based column offset of the source range.

object (DataSourceColumnReference)

The reference to the data source column.

{
  "summarizeFunction": enum (PivotValueSummarizeFunction),
  "name": string,
  "calculatedDisplayType": enum (PivotValueCalculatedDisplayType),

  // Union field value can be only one of the following:
  "sourceColumnOffset": integer,
  "formula": string,
  "dataSourceColumnReference": {
    object (DataSourceColumnReference)
  }
  // End of list of possible types for union field value.
}

enum (PivotValueSummarizeFunction)

A function to summarize the value. If formula is set, the only supported values are SUM and CUSTOM. If sourceColumnOffset is set, then CUSTOM is not supported.

string

A name to use for the value.

enum (PivotValueCalculatedDisplayType)

If specified, indicates that pivot values should be displayed as the result of a calculation with another pivot value. For example, if calculatedDisplayType is specified as PERCENT_OF_GRAND_TOTAL, all the pivot values are displayed as the percentage of the grand total. In the Sheets editor, this is referred to as "Show As" in the value section of a pivot table.

integer

The column offset of the source range that this value reads from.

For example, if the source was C10:E15, a sourceColumnOffset of 0 means this value refers to column C, whereas the offset 1 would refer to column D.

string

A custom formula to calculate the value. The formula must start with an = character.

object (DataSourceColumnReference)

The reference to the data source column that this value reads from.