Sheets

Sheet

A sheet in a spreadsheet.

JSON representation
{
  "properties": {
    object (SheetProperties)
  },
  "data": [
    {
      object (GridData)
    }
  ],
  "merges": [
    {
      object (GridRange)
    }
  ],
  "conditionalFormats": [
    {
      object (ConditionalFormatRule)
    }
  ],
  "filterViews": [
    {
      object (FilterView)
    }
  ],
  "protectedRanges": [
    {
      object (ProtectedRange)
    }
  ],
  "basicFilter": {
    object (BasicFilter)
  },
  "charts": [
    {
      object (EmbeddedChart)
    }
  ],
  "bandedRanges": [
    {
      object (BandedRange)
    }
  ],
  "developerMetadata": [
    {
      object (DeveloperMetadata)
    }
  ],
  "rowGroups": [
    {
      object (DimensionGroup)
    }
  ],
  "columnGroups": [
    {
      object (DimensionGroup)
    }
  ],
  "slicers": [
    {
      object (Slicer)
    }
  ]
}
Fields
properties

object ( SheetProperties )

The properties of the sheet.

data[]

object ( GridData )

Data in the grid, if this is a grid sheet. The number of GridData objects returned is dependent on the number of ranges requested on this sheet. For example, if this is representing Sheet1 , and the spreadsheet was requested with ranges Sheet1!A1:C10 and Sheet1!D15:E20 , then the first GridData will have a startRow / startColumn of 0 , while the second one will have startRow 14 (zero-based row 15), and startColumn 3 (zero-based column D).

merges[]

object ( GridRange )

The ranges that are merged together.

conditionalFormats[]

object ( ConditionalFormatRule )

The conditional format rules in this sheet.

filterViews[]

object ( FilterView )

The filter views in this sheet.

protectedRanges[]

object ( ProtectedRange )

The protected ranges in this sheet.

basicFilter

object ( BasicFilter )

The filter on this sheet, if any.

charts[]

object ( EmbeddedChart )

The specifications of every chart on this sheet.

bandedRanges[]

object ( BandedRange )

The banded (alternating colors) ranges on this sheet.

developerMetadata[]

object ( DeveloperMetadata )

The developer metadata associated with a sheet.

rowGroups[]

object ( DimensionGroup )

All row groups on this sheet, ordered by increasing range start index, then by group depth.

columnGroups[]

object ( DimensionGroup )

All column groups on this sheet, ordered by increasing range start index, then by group depth.

slicers[]

object ( Slicer )

The slicers on this sheet.

SheetProperties

Properties of a sheet.

JSON representation
{
  "sheetId": number,
  "title": string,
  "index": number,
  "sheetType": enum (SheetType),
  "gridProperties": {
    object (GridProperties)
  },
  "hidden": boolean,
  "tabColor": {
    object (Color)
  },
  "rightToLeft": boolean
}
Fields
sheetId

number

The ID of the sheet. Must be non-negative. This field cannot be changed once set.

title

string

The name of the sheet.

index

number

The index of the sheet within the spreadsheet. When adding or updating sheet properties, if this field is excluded then the sheet is added or moved to the end of the sheet list. When updating sheet indices or inserting sheets, movement is considered in "before the move" indexes. For example, if there were 3 sheets (S1, S2, S3) in order to move S1 ahead of S2 the index would have to be set to 2. A sheet index update request is ignored if the requested index is identical to the sheets current index or if the requested new index is equal to the current sheet index + 1.

sheetType

enum ( SheetType )

The type of sheet. Defaults to GRID . This field cannot be changed once set.

gridProperties

object ( GridProperties )

Additional properties of the sheet if this sheet is a grid. (If the sheet is an object sheet, containing a chart or image, then this field will be absent.) When writing it is an error to set any grid properties on non-grid sheets.

hidden

boolean

True if the sheet is hidden in the UI, false if it's visible.

tabColor

object ( Color )

The color of the tab in the UI.

rightToLeft

boolean

True if the sheet is an RTL sheet instead of an LTR sheet.

SheetType

The kind of sheet.

Enums
SHEET_TYPE_UNSPECIFIED Default value, do not use.
GRID The sheet is a grid.
OBJECT The sheet has no grid and instead has an object like a chart or image.

GridProperties

Properties of a grid.

JSON representation
{
  "rowCount": number,
  "columnCount": number,
  "frozenRowCount": number,
  "frozenColumnCount": number,
  "hideGridlines": boolean,
  "rowGroupControlAfter": boolean,
  "columnGroupControlAfter": boolean
}
Fields
rowCount

number

The number of rows in the grid.

columnCount

number

The number of columns in the grid.

frozenRowCount

number

The number of rows that are frozen in the grid.

frozenColumnCount

number

The number of columns that are frozen in the grid.

hideGridlines

boolean

True if the grid isn't showing gridlines in the UI.

rowGroupControlAfter

boolean

True if the row grouping control toggle is shown after the group.

columnGroupControlAfter

boolean

True if the column grouping control toggle is shown after the group.

GridData

Data in the grid, as well as metadata about the dimensions.

JSON representation
{
  "startRow": number,
  "startColumn": number,
  "rowData": [
    {
      object (RowData)
    }
  ],
  "rowMetadata": [
    {
      object (DimensionProperties)
    }
  ],
  "columnMetadata": [
    {
      object (DimensionProperties)
    }
  ]
}
Fields
startRow

number

The first row this GridData refers to, zero-based.

startColumn

number

The first column this GridData refers to, zero-based.

rowData[]

object ( RowData )

The data in the grid, one entry per row, starting with the row in startRow. The values in RowData will correspond to columns starting at startColumn .

rowMetadata[]

object ( DimensionProperties )

Metadata about the requested rows in the grid, starting with the row in startRow .

columnMetadata[]

object ( DimensionProperties )

Metadata about the requested columns in the grid, starting with the column in startColumn .

RowData

Data about each cell in a row.

JSON representation
{
  "values": [
    {
      object (CellData)
    }
  ]
}
Fields
values[]

object ( CellData )

The values in the row, one per column.

DimensionProperties

Properties about a dimension.

JSON representation
{
  "hiddenByFilter": boolean,
  "hiddenByUser": boolean,
  "pixelSize": number,
  "developerMetadata": [
    {
      object (DeveloperMetadata)
    }
  ]
}
Fields
hiddenByFilter

boolean

True if this dimension is being filtered. This field is read-only.

hiddenByUser

boolean

True if this dimension is explicitly hidden.

pixelSize

number

The height (if a row) or width (if a column) of the dimension in pixels.

developerMetadata[]

object ( DeveloperMetadata )

The developer metadata associated with a single row or column.

ConditionalFormatRule

A rule describing a conditional format.

JSON representation
{
  "ranges": [
    {
      object (GridRange)
    }
  ],

  // Union field rule can be only one of the following:
  "booleanRule": {
    object (BooleanRule)
  },
  "gradientRule": {
    object (GradientRule)
  }
  // End of list of possible types for union field rule.
}
Fields
ranges[]

object ( GridRange )

The ranges that are formatted if the condition is true. All the ranges must be on the same grid.

Union field rule . The rule controlling this conditional format, exactly one must be set. rule can be only one of the following:
booleanRule

object ( BooleanRule )

The formatting is either "on" or "off" according to the rule.

gradientRule

object ( GradientRule )

The formatting will vary based on the gradients in the rule.

BooleanRule

A rule that may or may not match, depending on the condition.

JSON representation
{
  "condition": {
    object (BooleanCondition)
  },
  "format": {
    object (CellFormat)
  }
}
Fields
condition

object ( BooleanCondition )

The condition of the rule. If the condition evaluates to true, the format is applied.

format

object ( CellFormat )

The format to apply. Conditional formatting can only apply a subset of formatting: bold , italic , strikethrough , foreground color & background color .

GradientRule

A rule that applies a gradient color scale format, based on the interpolation points listed. The format of a cell will vary based on its contents as compared to the values of the interpolation points.

JSON representation
{
  "minpoint": {
    object (InterpolationPoint)
  },
  "midpoint": {
    object (InterpolationPoint)
  },
  "maxpoint": {
    object (InterpolationPoint)
  }
}
Fields
minpoint

object ( InterpolationPoint )

The starting interpolation point.

midpoint

object ( InterpolationPoint )

An optional midway interpolation point.

maxpoint

object ( InterpolationPoint )

The final interpolation point.

InterpolationPoint

A single interpolation point on a gradient conditional format. These pin the gradient color scale according to the color, type and value chosen.

JSON representation
{
  "color": {
    object (Color)
  },
  "type": enum (InterpolationPointType),
  "value": string
}
Fields
color

object ( Color )

The color this interpolation point should use.

type

enum ( InterpolationPointType )

How the value should be interpreted.

value

string

The value this interpolation point uses. May be a formula. Unused if type is MIN or MAX .

InterpolationPointType

The kind of interpolation point.

Enums
INTERPOLATION_POINT_TYPE_UNSPECIFIED The default value, do not use.
MIN The interpolation point uses the minimum value in the cells over the range of the conditional format.
MAX The interpolation point uses the maximum value in the cells over the range of the conditional format.
NUMBER The interpolation point uses exactly the value in InterpolationPoint.value .
PERCENT

The interpolation point is the given percentage over all the cells in the range of the conditional format. This is equivalent to NUMBER if the value was: =(MAX(FLATTEN(range)) * (value / 100)) + (MIN(FLATTEN(range)) * (1 - (value / 100))) (where errors in the range are ignored when flattening).

PERCENTILE The interpolation point is the given percentile over all the cells in the range of the conditional format. This is equivalent to NUMBER if the value was: =PERCENTILE(FLATTEN(range), value / 100) (where errors in the range are ignored when flattening).

FilterView

A filter view.

JSON representation
{
  "filterViewId": number,
  "title": string,
  "range": {
    object (GridRange)
  },
  "namedRangeId": string,
  "sortSpecs": [
    {
      object (SortSpec)
    }
  ],
  "criteria": {
    string: {
      object(FilterCriteria)
    },
    ...
  }
}
Fields
filterViewId

number

The ID of the filter view.

title

string

The name of the filter view.

range

object ( GridRange )

The range this filter view covers.

When writing, only one of range or namedRangeId may be set.

namedRangeId

string

The named range this filter view is backed by, if any.

When writing, only one of range or namedRangeId may be set.

sortSpecs[]

object ( SortSpec )

The sort order per column. Later specifications are used when values are equal in the earlier specifications.

criteria

map (key: number, value: object ( FilterCriteria ))

The criteria for showing/hiding values per column. The map's key is the column index, and the value is the criteria for that column.

SortSpec

A sort order associated with a specific column or row.

JSON representation
{
  "dimensionIndex": number,
  "sortOrder": enum (SortOrder),
  "foregroundColor": {
    object (Color)
  },
  "backgroundColor": {
    object (Color)
  }
}
Fields
dimensionIndex

number

The dimension the sort should be applied to.

sortOrder

enum ( SortOrder )

The order data should be sorted.

foregroundColor

object ( Color )

The text color to sort by. Mutually exclusive with sorting by background fill color. Requests to set this field will fail with a 400 error if background color is also set.

backgroundColor

object ( Color )

The background fill color to sort by. Mutually exclusive with sorting by text color. Requests to set this field will fail with a 400 error if foreground color is also set.

ProtectedRange

A protected range.

JSON representation
{
  "protectedRangeId": number,
  "range": {
    object (GridRange)
  },
  "namedRangeId": string,
  "description": string,
  "warningOnly": boolean,
  "requestingUserCanEdit": boolean,
  "unprotectedRanges": [
    {
      object (GridRange)
    }
  ],
  "editors": {
    object (Editors)
  }
}
Fields
protectedRangeId

number

The ID of the protected range. This field is read-only.

range

object ( GridRange )

The range that is being protected. The range may be fully unbounded, in which case this is considered a protected sheet.

When writing, only one of range or namedRangeId may be set.

namedRangeId

string

The named range this protected range is backed by, if any.

When writing, only one of range or namedRangeId may be set.

description

string

The description of this protected range.

warningOnly

boolean

True if this protected range will show a warning when editing. Warning-based protection means that every user can edit data in the protected range, except editing will prompt a warning asking the user to confirm the edit.

When writing: if this field is true, then editors is ignored. Additionally, if this field is changed from true to false and the editors field is not set (nor included in the field mask), then the editors will be set to all the editors in the document.

requestingUserCanEdit

boolean

True if the user who requested this protected range can edit the protected area. This field is read-only.

unprotectedRanges[]

object ( GridRange )

The list of unprotected ranges within a protected sheet. Unprotected ranges are only supported on protected sheets.

editors

object ( Editors )

The users and groups with edit access to the protected range. This field is only visible to users with edit access to the protected range and the document. Editors are not supported with warningOnly protection.

Editors

The editors of a protected range.

JSON representation
{
  "users": [
    string
  ],
  "groups": [
    string
  ],
  "domainUsersCanEdit": boolean
}
Fields
users[]

string

The email addresses of users with edit access to the protected range.

groups[]

string

The email addresses of groups with edit access to the protected range.

domainUsersCanEdit

boolean

True if anyone in the document's domain has edit access to the protected range. Domain protection is only supported on documents within a domain.

BasicFilter

The default filter associated with a sheet.

JSON representation
{
  "range": {
    object (GridRange)
  },
  "sortSpecs": [
    {
      object (SortSpec)
    }
  ],
  "criteria": {
    string: {
      object(FilterCriteria)
    },
    ...
  }
}
Fields
range

object ( GridRange )

The range the filter covers.

sortSpecs[]

object ( SortSpec )

The sort order per column. Later specifications are used when values are equal in the earlier specifications.

criteria

map (key: number, value: object ( FilterCriteria ))

The criteria for showing/hiding values per column. The map's key is the column index, and the value is the criteria for that column.

BandedRange

A banded (alternating colors) range in a sheet.

JSON representation
{
  "bandedRangeId": number,
  "range": {
    object (GridRange)
  },
  "rowProperties": {
    object (BandingProperties)
  },
  "columnProperties": {
    object (BandingProperties)
  }
}
Fields
bandedRangeId

number

The id of the banded range.

range

object ( GridRange )

The range over which these properties are applied.

rowProperties

object ( BandingProperties )

Properties for row bands. These properties are applied on a row-by-row basis throughout all the rows in the range. At least one of rowProperties or columnProperties must be specified.

columnProperties

object ( BandingProperties )

Properties for column bands. These properties are applied on a column- by-column basis throughout all the columns in the range. At least one of rowProperties or columnProperties must be specified.

BandingProperties

Properties referring a single dimension (either row or column). If both BandedRange.row_properties and BandedRange.column_properties are set, the fill colors are applied to cells according to the following rules:

For example, the first row color takes priority over the first column color, but the first column color takes priority over the second row color. Similarly, the row header takes priority over the column header in the top left cell, but the column header takes priority over the first row color if the row header is not set.

JSON representation
{
  "headerColor": {
    object (Color)
  },
  "firstBandColor": {
    object (Color)
  },
  "secondBandColor": {
    object (Color)
  },
  "footerColor": {
    object (Color)
  }
}
Fields
headerColor

object ( Color )

The color of the first row or column. If this field is set, the first row or column will be filled with this color and the colors will alternate between firstBandColor and secondBandColor starting from the second row or column. Otherwise, the first row or column will be filled with firstBandColor and the colors will proceed to alternate as they normally would.

firstBandColor

object ( Color )

The first color that is alternating. (Required)

secondBandColor

object ( Color )

The second color that is alternating. (Required)

footerColor

object ( Color )

The color of the last row or column. If this field is not set, the last row or column will be filled with either firstBandColor or secondBandColor , depending on the color of the previous row or column.

DimensionGroup

A group over an interval of rows or columns on a sheet, which can contain or be contained within other groups. A group can be collapsed or expanded as a unit on the sheet.

JSON representation
{
  "range": {
    object (DimensionRange)
  },
  "depth": number,
  "collapsed": boolean
}
Fields
range

object ( DimensionRange )

The range over which this group exists.

depth

number

The depth of the group, representing how many groups have a range that wholly contains the range of this group.

collapsed

boolean

This field is true if this group is collapsed. A collapsed group remains collapsed if an overlapping group at a shallower depth is expanded.

A true value does not imply that all dimensions within the group are hidden, since a dimension's visibility can change independently from this group property. However, when this property is updated, all dimensions within it are set to hidden if this field is true, or set to visible if this field is false.

Slicer

A slicer in a sheet.

JSON representation
{
  "slicerId": number,
  "spec": {
    object (SlicerSpec)
  },
  "position": {
    object (EmbeddedObjectPosition)
  }
}
Fields
slicerId

number

The ID of the slicer.

spec

object ( SlicerSpec )

The specification of the slicer.

position

object ( EmbeddedObjectPosition )

The position of the slicer. Note that slicer can be positioned only on existing sheet. Also, width and height of slicer can be automatically adjusted to keep it within permitted limits.

SlicerSpec

The specifications of a slicer.

JSON representation
{
  "dataRange": {
    object (GridRange)
  },
  "filterCriteria": {
    object (FilterCriteria)
  },
  "columnIndex": number,
  "applyToPivotTables": boolean,
  "title": string,
  "textFormat": {
    object (TextFormat)
  },
  "backgroundColor": {
    object (Color)
  },
  "horizontalAlignment": enum (HorizontalAlign)
}
Fields
dataRange

object ( GridRange )

The data range of the slicer.

filterCriteria

object ( FilterCriteria )

The filtering criteria of the slicer.

columnIndex

number

The column index in the data table on which the filter is applied to.

applyToPivotTables

boolean

True if the filter should apply to pivot tables. If not set, default to True .

title

string

The title of the slicer.

textFormat

object ( TextFormat )

The text format of title in the slicer.

backgroundColor

object ( Color )

The background color of the slicer.

horizontalAlignment

enum ( HorizontalAlign )

The horizontal alignment of title in the slicer. If unspecified, defaults to LEFT

FilterCriteria

Criteria for showing/hiding rows in a filter or filter view.

JSON representation
{
  "hiddenValues": [
    string
  ],
  "condition": {
    object (BooleanCondition)
  },
  "visibleBackgroundColor": {
    object (Color)
  },
  "visibleForegroundColor": {
    object (Color)
  }
}
Fields
hiddenValues[]

string

Values that should be hidden.

condition

object ( BooleanCondition )

A condition that must be true for values to be shown. (This does not override hiddenValues -- if a value is listed there, it will still be hidden.)

visibleBackgroundColor

object ( Color )

The background fill color to filter by; only cells with this fill color are shown. Mutually exclusive with all other filter criteria. Requests to set this field will fail with a 400 error if any other filter criteria field is set.

visibleForegroundColor

object ( Color )

The text color to filter by; only cells with this text color are shown. Mutually exclusive with all other filter criteria. Requests to set this field will fail with a 400 error if any other filter criteria field is set.