REST Resource: spreadsheets

Resource: Spreadsheet

Resource that represents a spreadsheet.

JSON representation
{
  "spreadsheetId": string,
  "properties": {
    object(SpreadsheetProperties)
  },
  "sheets": [
    {
      object(Sheet)
    }
  ],
  "namedRanges": [
    {
      object(NamedRange)
    }
  ],
  "spreadsheetUrl": string,
  "developerMetadata": [
    {
      object(DeveloperMetadata)
    }
  ],
}
Fields
spreadsheetId

string

The ID of the spreadsheet. This field is read-only.

properties

object(SpreadsheetProperties)

Overall properties of a spreadsheet.

sheets[]

object(Sheet)

The sheets that are part of a spreadsheet.

namedRanges[]

object(NamedRange)

The named ranges defined in a spreadsheet.

spreadsheetUrl

string

The url of the spreadsheet. This field is read-only.

developerMetadata[]

object(DeveloperMetadata)

The developer metadata associated with a spreadsheet.

SpreadsheetProperties

Properties of a spreadsheet.

JSON representation
{
  "title": string,
  "locale": string,
  "autoRecalc": enum(RecalculationInterval),
  "timeZone": string,
  "defaultFormat": {
    object(CellFormat)
  },
  "iterativeCalculationSettings": {
    object(IterativeCalculationSettings)
  },
}
Fields
title

string

The title of the spreadsheet.

locale

string

The locale of the spreadsheet in one of the following formats:

  • an ISO 639-1 language code such as en

  • an ISO 639-2 language code such as fil, if no 639-1 code exists

  • a combination of the ISO language code and country code, such as en_US

Note: when updating this field, not all locales/languages are supported.

autoRecalc

enum(RecalculationInterval)

The amount of time to wait before volatile functions are recalculated.

timeZone

string

The time zone of the spreadsheet, in CLDR format such as America/New_York. If the time zone isn't recognized, this may be a custom time zone such as GMT-07:00.

defaultFormat

object(CellFormat)

The default format of all cells in the spreadsheet. CellData.effectiveFormat will not be set if the cell's format is equal to this default format. This field is read-only.

iterativeCalculationSettings

object(IterativeCalculationSettings)

Determines whether and how circular references are resolved with iterative calculation. Absence of this field means that circular references will result in calculation errors.

RecalculationInterval

An enumeration of the possible recalculation interval options.

Enums
RECALCULATION_INTERVAL_UNSPECIFIED Default value. This value must not be used.
ON_CHANGE Volatile functions are updated on every change.
MINUTE Volatile functions are updated on every change and every minute.
HOUR Volatile functions are updated on every change and hourly.

CellFormat

The format of a cell.

JSON representation
{
  "numberFormat": {
    object(NumberFormat)
  },
  "backgroundColor": {
    object(Color)
  },
  "borders": {
    object(Borders)
  },
  "padding": {
    object(Padding)
  },
  "horizontalAlignment": enum(HorizontalAlign),
  "verticalAlignment": enum(VerticalAlign),
  "wrapStrategy": enum(WrapStrategy),
  "textDirection": enum(TextDirection),
  "textFormat": {
    object(TextFormat)
  },
  "hyperlinkDisplayType": enum(HyperlinkDisplayType),
  "textRotation": {
    object(TextRotation)
  },
}
Fields
numberFormat

object(NumberFormat)

A format describing how number values should be represented to the user.

backgroundColor

object(Color)

The background color of the cell.

borders

object(Borders)

The borders of the cell.

padding

object(Padding)

The padding of the cell.

horizontalAlignment

enum(HorizontalAlign)

The horizontal alignment of the value in the cell.

verticalAlignment

enum(VerticalAlign)

The vertical alignment of the value in the cell.

wrapStrategy

enum(WrapStrategy)

The wrap strategy for the value in the cell.

textDirection

enum(TextDirection)

The direction of the text in the cell.

textFormat

object(TextFormat)

The format of the text in the cell (unless overridden by a format run).

textRotation

object(TextRotation)

The rotation applied to text in a cell

NumberFormat

The number format of a cell.

JSON representation
{
  "type": enum(NumberFormatType),
  "pattern": string,
}
Fields
type

enum(NumberFormatType)

The type of the number format. When writing, this field must be set.

pattern

string

Pattern string used for formatting. If not set, a default pattern based on the user's locale will be used if necessary for the given type. See the Date and Number Formats guide for more information about the supported patterns.

NumberFormatType

The number format of the cell. In this documentation the locale is assumed to be en_US, but the actual format depends on the locale of the spreadsheet.

Enums
NUMBER_FORMAT_TYPE_UNSPECIFIED The number format is not specified and is based on the contents of the cell. Do not explicitly use this.
TEXT Text formatting, e.g 1000.12
NUMBER Number formatting, e.g, 1,000.12
PERCENT Percent formatting, e.g 10.12%
CURRENCY Currency formatting, e.g $1,000.12
DATE Date formatting, e.g 9/26/2008
TIME Time formatting, e.g 3:59:00 PM
DATE_TIME Date+Time formatting, e.g 9/26/08 15:59:00
SCIENTIFIC Scientific number formatting, e.g 1.01E+03

Color

Represents a color in the RGBA color space. This representation is designed for simplicity of conversion to/from color representations in various languages over compactness; for example, the fields of this representation can be trivially provided to the constructor of "java.awt.Color" in Java; it can also be trivially provided to UIColor's "+colorWithRed:green:blue:alpha" method in iOS; and, with just a little work, it can be easily formatted into a CSS "rgba()" string in JavaScript, as well. Here are some examples:

Example (Java):

 import com.google.type.Color;

 // ...
 public static java.awt.Color fromProto(Color protocolor) {
   float alpha = protocolor.hasAlpha()
       ? protocolor.getAlpha().getValue()
       : 1.0;

   return new java.awt.Color(
       protocolor.getRed(),
       protocolor.getGreen(),
       protocolor.getBlue(),
       alpha);
 }

 public static Color toProto(java.awt.Color color) {
   float red = (float) color.getRed();
   float green = (float) color.getGreen();
   float blue = (float) color.getBlue();
   float denominator = 255.0;
   Color.Builder resultBuilder =
       Color
           .newBuilder()
           .setRed(red / denominator)
           .setGreen(green / denominator)
           .setBlue(blue / denominator);
   int alpha = color.getAlpha();
   if (alpha != 255) {
     result.setAlpha(
         FloatValue
             .newBuilder()
             .setValue(((float) alpha) / denominator)
             .build());
   }
   return resultBuilder.build();
 }
 // ...

Example (iOS / Obj-C):

 // ...
 static UIColor* fromProto(Color* protocolor) {
    float red = [protocolor red];
    float green = [protocolor green];
    float blue = [protocolor blue];
    FloatValue* alpha_wrapper = [protocolor alpha];
    float alpha = 1.0;
    if (alpha_wrapper != nil) {
      alpha = [alpha_wrapper value];
    }
    return [UIColor colorWithRed:red green:green blue:blue alpha:alpha];
 }

 static Color* toProto(UIColor* color) {
     CGFloat red, green, blue, alpha;
     if (![color getRed:&red green:&green blue:&blue alpha:&alpha]) {
       return nil;
     }
     Color* result = [Color alloc] init];
     [result setRed:red];
     [result setGreen:green];
     [result setBlue:blue];
     if (alpha <= 0.9999) {
       [result setAlpha:floatWrapperWithValue(alpha)];
     }
     [result autorelease];
     return result;
}
// ...

Example (JavaScript):

// ...

var protoToCssColor = function(rgb_color) {
   var redFrac = rgb_color.red || 0.0;
   var greenFrac = rgb_color.green || 0.0;
   var blueFrac = rgb_color.blue || 0.0;
   var red = Math.floor(redFrac * 255);
   var green = Math.floor(greenFrac * 255);
   var blue = Math.floor(blueFrac * 255);

   if (!('alpha' in rgb_color)) {
      return rgbToCssColor_(red, green, blue);
   }

   var alphaFrac = rgb_color.alpha.value || 0.0;
   var rgbParams = [red, green, blue].join(',');
   return ['rgba(', rgbParams, ',', alphaFrac, ')'].join('');
};

var rgbToCssColor_ = function(red, green, blue) {
  var rgbNumber = new Number((red << 16) | (green << 8) | blue);
  var hexString = rgbNumber.toString(16);
  var missingZeros = 6 - hexString.length;
  var resultBuilder = ['#'];
  for (var i = 0; i < missingZeros; i++) {
     resultBuilder.push('0');
  }
  resultBuilder.push(hexString);
  return resultBuilder.join('');
};

// ...
JSON representation
{
  "red": number,
  "green": number,
  "blue": number,
  "alpha": number,
}
Fields
red

number

The amount of red in the color as a value in the interval [0, 1].

green

number

The amount of green in the color as a value in the interval [0, 1].

blue

number

The amount of blue in the color as a value in the interval [0, 1].

alpha

number

The fraction of this color that should be applied to the pixel. That is, the final pixel color is defined by the equation:

pixel color = alpha * (this color) + (1.0 - alpha) * (background color)

This means that a value of 1.0 corresponds to a solid color, whereas a value of 0.0 corresponds to a completely transparent color. This uses a wrapper message rather than a simple float scalar so that it is possible to distinguish between a default value and the value being unset. If omitted, this color object is to be rendered as a solid color (as if the alpha value had been explicitly given with a value of 1.0).

Borders

The borders of the cell.

JSON representation
{
  "top": {
    object(Border)
  },
  "bottom": {
    object(Border)
  },
  "left": {
    object(Border)
  },
  "right": {
    object(Border)
  },
}
Fields
top

object(Border)

The top border of the cell.

bottom

object(Border)

The bottom border of the cell.

left

object(Border)

The left border of the cell.

right

object(Border)

The right border of the cell.

Border

A border along a cell.

JSON representation
{
  "style": enum(Style),
  "width": number,
  "color": {
    object(Color)
  },
}
Fields
style

enum(Style)

The style of the border.

width
(deprecated)

number

The width of the border, in pixels. Deprecated; the width is determined by the "style" field.

color

object(Color)

The color of the border.

Style

The style of a border.

Enums
STYLE_UNSPECIFIED The style is not specified. Do not use this.
DOTTED The border is dotted.
DASHED The border is dashed.
SOLID The border is a thin solid line.
SOLID_MEDIUM The border is a medium solid line.
SOLID_THICK The border is a thick solid line.
NONE No border. Used only when updating a border in order to erase it.
DOUBLE The border is two solid lines.

Padding

The amount of padding around the cell, in pixels. When updating padding, every field must be specified.

JSON representation
{
  "top": number,
  "right": number,
  "bottom": number,
  "left": number,
}
Fields
top

number

The top padding of the cell.

right

number

The right padding of the cell.

bottom

number

The bottom padding of the cell.

left

number

The left padding of the cell.

HorizontalAlign

The horizontal alignment of text in a cell.

Enums
HORIZONTAL_ALIGN_UNSPECIFIED The horizontal alignment is not specified. Do not use this.
LEFT The text is explicitly aligned to the left of the cell.
CENTER The text is explicitly aligned to the center of the cell.
RIGHT The text is explicitly aligned to the right of the cell.

VerticalAlign

The vertical alignment of text in a cell.

Enums
VERTICAL_ALIGN_UNSPECIFIED The vertical alignment is not specified. Do not use this.
TOP The text is explicitly aligned to the top of the cell.
MIDDLE The text is explicitly aligned to the middle of the cell.
BOTTOM The text is explicitly aligned to the bottom of the cell.

WrapStrategy

How to wrap text in a cell.

Enums
WRAP_STRATEGY_UNSPECIFIED The default value, do not use.
OVERFLOW_CELL

Lines that are longer than the cell width will be written in the next cell over, so long as that cell is empty. If the next cell over is non-empty, this behaves the same as CLIP. The text will never wrap to the next line unless the user manually inserts a new line. Example:

| First sentence. |
| Manual newline that is very long. <- Text continues into next cell
| Next newline.   |
LEGACY_WRAP

This wrap strategy represents the old Google Sheets wrap strategy where words that are longer than a line are clipped rather than broken. This strategy is not supported on all platforms and is being phased out. Example:

| Cell has a |
| loooooooooo| <- Word is clipped.
| word.      |
CLIP

Lines that are longer than the cell width will be clipped. The text will never wrap to the next line unless the user manually inserts a new line. Example:

| First sentence. |
| Manual newline t| <- Text is clipped
| Next newline.   |
WRAP

Words that are longer than a line are wrapped at the character level rather than clipped. Example:

| Cell has a |
| loooooooooo| <- Word is broken.
| ong word.  |

TextDirection

The direction of text in a cell.

Enums
TEXT_DIRECTION_UNSPECIFIED The text direction is not specified. Do not use this.
LEFT_TO_RIGHT The text direction of left-to-right was set by the user.
RIGHT_TO_LEFT The text direction of right-to-left was set by the user.

TextFormat

The format of a run of text in a cell. Absent values indicate that the field isn't specified.

JSON representation
{
  "foregroundColor": {
    object(Color)
  },
  "fontFamily": string,
  "fontSize": number,
  "bold": boolean,
  "italic": boolean,
  "strikethrough": boolean,
  "underline": boolean,
}
Fields
foregroundColor

object(Color)

The foreground color of the text.

fontFamily

string

The font family.

fontSize

number

The size of the font.

bold

boolean

True if the text is bold.

italic

boolean

True if the text is italicized.

strikethrough

boolean

True if the text has a strikethrough.

underline

boolean

True if the text is underlined.

HyperlinkDisplayType

Whether to explicitly render a hyperlink. If not specified, the hyperlink is linked.

Enums
LINKED A hyperlink should be explicitly rendered.
PLAIN_TEXT A hyperlink should not be rendered.

TextRotation

The rotation applied to text in a cell.

JSON representation
{

  // Union field type can be only one of the following:
  "angle": number,
  "vertical": boolean,
  // End of list of possible types for union field type.
}
Fields
Union field type. The type of rotation, vertical or angled. type can be only one of the following:
angle

number

The angle between the standard orientation and the desired orientation. Measured in degrees. Valid values are between -90 and 90. Positive angles are angled upwards, negative are angled downwards.

Note: For LTR text direction positive angles are in the counterclockwise direction, whereas for RTL they are in the clockwise direction

vertical

boolean

If true, text reads top to bottom, but the orientation of individual characters is unchanged. For example:

| V |
| e |
| r |
| t |
| i |
| c |
| a |
| l |

IterativeCalculationSettings

Settings to control how circular dependencies are resolved with iterative calculation.

JSON representation
{
  "maxIterations": number,
  "convergenceThreshold": number,
}
Fields
maxIterations

number

When iterative calculation is enabled, the maximum number of calculation rounds to perform.

convergenceThreshold

number

When iterative calculation is enabled and successive results differ by less than this threshold value, the calculation rounds stop.

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)
    }
  ],
}
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 (i.e. alternating colors) ranges on this sheet.

developerMetadata[]

object(DeveloperMetadata)

The developer metadata associated with a 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 will be 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 will be 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,
}
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.

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.

CellData

Data about a specific cell.

JSON representation
{
  "userEnteredValue": {
    object(ExtendedValue)
  },
  "effectiveValue": {
    object(ExtendedValue)
  },
  "formattedValue": string,
  "userEnteredFormat": {
    object(CellFormat)
  },
  "effectiveFormat": {
    object(CellFormat)
  },
  "hyperlink": string,
  "note": string,
  "textFormatRuns": [
    {
      object(TextFormatRun)
    }
  ],
  "dataValidation": {
    object(DataValidationRule)
  },
  "pivotTable": {
    object(PivotTable)
  },
}
Fields
userEnteredValue

object(ExtendedValue)

The value the user entered in the cell. e.g, 1234, 'Hello', or =NOW() Note: Dates, Times and DateTimes are represented as doubles in serial number format.

effectiveValue

object(ExtendedValue)

The effective value of the cell. For cells with formulas, this will be the calculated value. For cells with literals, this will be the same as the userEnteredValue. This field is read-only.

formattedValue

string

The formatted value of the cell. This is the value as it's shown to the user. This field is read-only.

userEnteredFormat

object(CellFormat)

The format the user entered for the cell.

When writing, the new format will be merged with the existing format.

effectiveFormat

object(CellFormat)

The effective format being used by the cell. This includes the results of applying any conditional formatting and, if the cell contains a formula, the computed number format. If the effective format is the default format, effective format will not be written. This field is read-only.

note

string

Any note on the cell.

textFormatRuns[]

object(TextFormatRun)

Runs of rich text applied to subsections of the cell. Runs are only valid on user entered strings, not formulas, bools, or numbers. Runs start at specific indexes in the text and continue until the next run. Properties of a run will continue unless explicitly changed in a subsequent run (and properties of the first run will continue the properties of the cell unless explicitly changed).

When writing, the new runs will overwrite any prior runs. When writing a new userEnteredValue, previous runs will be erased.

dataValidation

object(DataValidationRule)

A data validation rule on the cell, if any.

When writing, the new data validation rule will overwrite any prior rule.

pivotTable

object(PivotTable)

A pivot table anchored at this cell. The size of pivot table itself is computed dynamically based on its data, grouping, filters, values, etc. Only the top-left cell of the pivot table contains the pivot table definition. The other cells will contain the calculated values of the results of the pivot in their effectiveValue fields.

ExtendedValue

The kinds of value that a cell in a spreadsheet can have.

JSON representation
{

  // Union field value can be only one of the following:
  "numberValue": number,
  "stringValue": string,
  "boolValue": boolean,
  "formulaValue": string,
  "errorValue": {
    object(ErrorValue)
  },
  // End of list of possible types for union field value.
}
Fields
Union field value. The type of value in a cell. If no field is set, the cell has no data. value can be only one of the following:
numberValue

number

Represents a double value. Note: Dates, Times and DateTimes are represented as doubles in "serial number" format.

stringValue

string

Represents a string value. Leading single quotes are not included. For example, if the user typed '123 into the UI, this would be represented as a stringValue of "123".

boolValue

boolean

Represents a boolean value.

formulaValue

string

Represents a formula.

errorValue

object(ErrorValue)

Represents an error. This field is read-only.

ErrorValue

An error in a cell.

JSON representation
{
  "type": enum(ErrorType),
  "message": string,
}
Fields
type

enum(ErrorType)

The type of error.

message

string

A message with more information about the error (in the spreadsheet's locale).

ErrorType

The type of error.

Enums
ERROR_TYPE_UNSPECIFIED The default error type, do not use this.
ERROR Corresponds to the #ERROR! error.
NULL_VALUE Corresponds to the #NULL! error.
DIVIDE_BY_ZERO Corresponds to the #DIV/0 error.
VALUE Corresponds to the #VALUE! error.
REF Corresponds to the #REF! error.
NAME Corresponds to the #NAME? error.
NUM Corresponds to the #NUM! error.
N_A Corresponds to the #N/A error.
LOADING Corresponds to the Loading... state.

TextFormatRun

A run of a text format. The format of this run continues until the start index of the next run. When updating, all fields must be set.

JSON representation
{
  "startIndex": number,
  "format": {
    object(TextFormat)
  },
}
Fields
startIndex

number

The character index where this run starts.

format

object(TextFormat)

The format of this run. Absent values inherit the cell's format.

DataValidationRule

A data validation rule.

JSON representation
{
  "condition": {
    object(BooleanCondition)
  },
  "inputMessage": string,
  "strict": boolean,
  "showCustomUi": boolean,
}
Fields
condition

object(BooleanCondition)

The condition that data in the cell must match.

inputMessage

string

A message to show the user when adding data to the cell.

strict

boolean

True if invalid data should be rejected.

showCustomUi

boolean

True if the UI should be customized based on the kind of condition. If true, "List" conditions will show a dropdown.

BooleanCondition

A condition that can evaluate to true or false. BooleanConditions are used by conditional formatting, data validation, and the criteria in filters.

JSON representation
{
  "type": enum(ConditionType),
  "values": [
    {
      object(ConditionValue)
    }
  ],
}
Fields
type

enum(ConditionType)

The type of condition.

values[]

object(ConditionValue)

The values of the condition. The number of supported values depends on the condition type. Some support zero values, others one or two values, and ConditionType.ONE_OF_LIST supports an arbitrary number of values.

ConditionType

The type of condition.

Enums
CONDITION_TYPE_UNSPECIFIED The default value, do not use.
NUMBER_GREATER The cell's value must be greater than the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
NUMBER_GREATER_THAN_EQ The cell's value must be greater than or equal to the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
NUMBER_LESS The cell's value must be less than the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
NUMBER_LESS_THAN_EQ The cell's value must be less than or equal to the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
NUMBER_EQ The cell's value must be equal to the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
NUMBER_NOT_EQ The cell's value must be not equal to the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
NUMBER_BETWEEN The cell's value must be between the two condition values. Supported by data validation, conditional formatting and filters. Requires exactly two ConditionValues.
NUMBER_NOT_BETWEEN The cell's value must not be between the two condition values. Supported by data validation, conditional formatting and filters. Requires exactly two ConditionValues.
TEXT_CONTAINS The cell's value must contain the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
TEXT_NOT_CONTAINS The cell's value must not contain the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
TEXT_STARTS_WITH The cell's value must start with the condition's value. Supported by conditional formatting and filters. Requires a single ConditionValue.
TEXT_ENDS_WITH The cell's value must end with the condition's value. Supported by conditional formatting and filters. Requires a single ConditionValue.
TEXT_EQ The cell's value must be exactly the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
TEXT_IS_EMAIL The cell's value must be a valid email address. Supported by data validation. Requires no ConditionValues.
TEXT_IS_URL The cell's value must be a valid URL. Supported by data validation. Requires no ConditionValues.
DATE_EQ The cell's value must be the same date as the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.
DATE_BEFORE The cell's value must be before the date of the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue that may be a relative date.
DATE_AFTER The cell's value must be after the date of the condition's value. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue that may be a relative date.
DATE_ON_OR_BEFORE The cell's value must be on or before the date of the condition's value. Supported by data validation. Requires a single ConditionValue that may be a relative date.
DATE_ON_OR_AFTER The cell's value must be on or after the date of the condition's value. Supported by data validation. Requires a single ConditionValue that may be a relative date.
DATE_BETWEEN The cell's value must be between the dates of the two condition values. Supported by data validation. Requires exactly two ConditionValues.
DATE_NOT_BETWEEN The cell's value must be outside the dates of the two condition values. Supported by data validation. Requires exactly two ConditionValues.
DATE_IS_VALID The cell's value must be a date. Supported by data validation. Requires no ConditionValues.
ONE_OF_RANGE The cell's value must be listed in the grid in condition value's range. Supported by data validation. Requires a single ConditionValue, and the value must be a valid range in A1 notation.
ONE_OF_LIST The cell's value must in the list of condition values. Supported by data validation. Supports any number of condition values, one per item in the list. Formulas are not supported in the values.
BLANK The cell's value must be empty. Supported by conditional formatting and filters. Requires no ConditionValues.
NOT_BLANK The cell's value must not be empty. Supported by conditional formatting and filters. Requires no ConditionValues.
CUSTOM_FORMULA The condition's formula must evaluate to true. Supported by data validation, conditional formatting and filters. Requires a single ConditionValue.

ConditionValue

The value of the condition.

JSON representation
{

  // Union field value can be only one of the following:
  "relativeDate": enum(RelativeDate),
  "userEnteredValue": string,
  // End of list of possible types for union field value.
}
Fields
Union field value. The value of the condition, exactly one must be set. value can be only one of the following:
relativeDate

enum(RelativeDate)

A relative date (based on the current date). Valid only if the type is DATE_BEFORE, DATE_AFTER, DATE_ON_OR_BEFORE or DATE_ON_OR_AFTER.

Relative dates are not supported in data validation. They are supported only in conditional formatting and conditional filters.

userEnteredValue

string

A value the condition is based on. The value will be parsed as if the user typed into a cell. Formulas are supported (and must begin with an =).

RelativeDate

Controls how a date condition is evaluated.

Enums
RELATIVE_DATE_UNSPECIFIED Default value, do not use.
PAST_YEAR The value is one year before today.
PAST_MONTH The value is one month before today.
PAST_WEEK The value is one week before today.
YESTERDAY The value is yesterday.
TODAY The value is today.
TOMORROW The value is tomorrow.

PivotTable

A pivot table.

JSON representation
{
  "source": {
    object(GridRange)
  },
  "rows": [
    {
      object(PivotGroup)
    }
  ],
  "columns": [
    {
      object(PivotGroup)
    }
  ],
  "criteria": {
    string: {
      object(PivotFilterCriteria)
    },
    ...
  },
  "values": [
    {
      object(PivotValue)
    }
  ],
  "valueLayout": enum(PivotValueLayout),
}
Fields
source

object(GridRange)

The range the pivot table is reading data from.

rows[]

object(PivotGroup)

Each row grouping in the pivot table.

columns[]

object(PivotGroup)

Each column grouping in the pivot table.

criteria

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

An optional mapping of filters per source column offset.

The filters will be 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.

values[]

object(PivotValue)

A list of values to include in the pivot table.

valueLayout

enum(PivotValueLayout)

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

GridRange

A range on a sheet. All indexes are zero-based. Indexes are half open, e.g the start index is inclusive and the end index is exclusive -- [startIndex, endIndex). Missing indexes indicate the range is unbounded on that side.

For example, if "Sheet1" is sheet ID 0, then:

Sheet1!A1:A1 == sheetId: 0, startRowIndex: 0, endRowIndex: 1, startColumnIndex: 0, endColumnIndex: 1

Sheet1!A3:B4 == sheetId: 0, startRowIndex: 2, endRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2

Sheet1!A:B == sheetId: 0, startColumnIndex: 0, endColumnIndex: 2

Sheet1!A5:B == sheetId: 0, startRowIndex: 4, startColumnIndex: 0, endColumnIndex: 2

Sheet1 == sheetId:0

The start index must always be less than or equal to the end index. If the start index equals the end index, then the range is empty. Empty ranges are typically not meaningful and are usually rendered in the UI as #REF!.

JSON representation
{
  "sheetId": number,
  "startRowIndex": number,
  "endRowIndex": number,
  "startColumnIndex": number,
  "endColumnIndex": number,
}
Fields
sheetId

number

The sheet this range is on.

startRowIndex

number

The start row (inclusive) of the range, or not set if unbounded.

endRowIndex

number

The end row (exclusive) of the range, or not set if unbounded.

startColumnIndex

number

The start column (inclusive) of the range, or not set if unbounded.

endColumnIndex

number

The end column (exclusive) of the range, or not set if unbounded.

PivotGroup

A single grouping (either row or column) in a pivot table.

JSON representation
{
  "sourceColumnOffset": number,
  "showTotals": boolean,
  "valueMetadata": [
    {
      object(PivotGroupValueMetadata)
    }
  ],
  "sortOrder": enum(SortOrder),
  "valueBucket": {
    object(PivotGroupSortValueBucket)
  },
}
Fields
sourceColumnOffset

number

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.

showTotals

boolean

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

valueMetadata[]

object(PivotGroupValueMetadata)

Metadata about values in the grouping.

sortOrder

enum(SortOrder)

The order the values in this group should be sorted.

valueBucket

object(PivotGroupSortValueBucket)

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

PivotGroupValueMetadata

Metadata about a value in a pivot grouping.

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

object(ExtendedValue)

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

collapsed

boolean

True if the data corresponding to the value is collapsed.

SortOrder

A sort order.

Enums
SORT_ORDER_UNSPECIFIED Default value, do not use this.
ASCENDING Sort ascending.
DESCENDING Sort descending.

PivotGroupSortValueBucket

Information about which values in a pivot group should be used for sorting.

JSON representation
{
  "valuesIndex": number,
  "buckets": [
    {
      object(ExtendedValue)
    }
  ],
}
Fields
valuesIndex

number

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

buckets[]

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.

PivotValue

The definition of how a value in a pivot table should be calculated.

JSON representation
{
  "summarizeFunction": enum(PivotValueSummarizeFunction),
  "name": string,

  // Union field value can be only one of the following:
  "sourceColumnOffset": number,
  "formula": string,
  // End of list of possible types for union field value.
}
Fields
summarizeFunction

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.

name

string

A name to use for the value.

Union field value. The data to use for the values in the pivot table. Exactly one value must be set. value can be only one of the following:
sourceColumnOffset

number

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.

formula

string

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

PivotValueSummarizeFunction

A function to summarize a pivot value.

Enums
PIVOT_STANDARD_VALUE_FUNCTION_UNSPECIFIED The default, do not use.
SUM Corresponds to the SUM function.
COUNTA Corresponds to the COUNTA function.
COUNT Corresponds to the COUNT function.
COUNTUNIQUE Corresponds to the COUNTUNIQUE function.
AVERAGE Corresponds to the AVERAGE function.
MAX Corresponds to the MAX function.
MIN Corresponds to the MIN function.
MEDIAN Corresponds to the MEDIAN function.
PRODUCT Corresponds to the PRODUCT function.
STDEV Corresponds to the STDEV function.
STDEVP Corresponds to the STDEVP function.
VAR Corresponds to the VAR function.
VARP Corresponds to the VARP function.
CUSTOM Indicates the formula should be used as-is. Only valid if PivotValue.formula was set.

PivotValueLayout

The layout of pivot values.

Enums
HORIZONTAL Values are laid out horizontally (as columns).
VERTICAL Values are laid out vertically (as rows).

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 will be 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 will be 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 will use the minimum value in the cells over the range of the conditional format.
MAX The interpolation point will use the maximum value in the cells over the range of the conditional format.
NUMBER The interpolation point will use exactly the value in InterpolationPoint.value.
PERCENT

The interpolation point will be 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 will be 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),
}
Fields
dimensionIndex

number

The dimension the sort should be applied to.

sortOrder

enum(SortOrder)

The order data should be sorted.

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.

EmbeddedChart

A chart embedded in a sheet.

JSON representation
{
  "chartId": number,
  "spec": {
    object(ChartSpec)
  },
  "position": {
    object(EmbeddedObjectPosition)
  },
}
Fields
chartId

number

The ID of the chart.

spec

object(ChartSpec)

The specification of the chart.

position

object(EmbeddedObjectPosition)

The position of the chart.

ChartSpec

The specifications of a chart.

JSON representation
{
  "title": string,
  "altText": string,
  "titleTextFormat": {
    object(TextFormat)
  },
  "titleTextPosition": {
    object(TextPosition)
  },
  "subtitle": string,
  "subtitleTextFormat": {
    object(TextFormat)
  },
  "subtitleTextPosition": {
    object(TextPosition)
  },
  "fontName": string,
  "maximized": boolean,
  "backgroundColor": {
    object(Color)
  },
  "hiddenDimensionStrategy": enum(ChartHiddenDimensionStrategy),

  // Union field chart can be only one of the following:
  "basicChart": {
    object(BasicChartSpec)
  },
  "pieChart": {
    object(PieChartSpec)
  },
  "bubbleChart": {
    object(BubbleChartSpec)
  },
  "candlestickChart": {
    object(CandlestickChartSpec)
  },
  "orgChart": {
    object(OrgChartSpec)
  },
  "histogramChart": {
    object(HistogramChartSpec)
  },
  // End of list of possible types for union field chart.
}
Fields
title

string

The title of the chart.

altText

string

The alternative text that describes the chart. This is often used for accessibility.

titleTextFormat

object(TextFormat)

The title text format. Strikethrough and underline are not supported.

titleTextPosition

object(TextPosition)

The title text position. This field is optional.

subtitle

string

The subtitle of the chart.

subtitleTextFormat

object(TextFormat)

The subtitle text format. Strikethrough and underline are not supported.

subtitleTextPosition

object(TextPosition)

The subtitle text position. This field is optional.

fontName

string

The name of the font to use by default for all chart text (e.g. title, axis labels, legend). If a font is specified for a specific part of the chart it will override this font name.

maximized

boolean

True to make a chart fill the entire space in which it's rendered with minimum padding. False to use the default padding. (Not applicable to Geo and Org charts.)

backgroundColor

object(Color)

The background color of the entire chart. Not applicable to Org charts.

hiddenDimensionStrategy

enum(ChartHiddenDimensionStrategy)

Determines how the charts will use hidden rows or columns.

Union field chart. The specific chart specification, exactly one value must be set. chart can be only one of the following:
basicChart

object(BasicChartSpec)

A basic chart specification, can be one of many kinds of charts. See BasicChartType for the list of all charts this supports.

pieChart

object(PieChartSpec)

A pie chart specification.

bubbleChart

object(BubbleChartSpec)

A bubble chart specification.

candlestickChart

object(CandlestickChartSpec)

A candlestick chart specification.

orgChart

object(OrgChartSpec)

An org chart specification.

histogramChart

object(HistogramChartSpec)

A histogram chart specification.

TextPosition

Position settings for text.

JSON representation
{
  "horizontalAlignment": enum(HorizontalAlign),
}
Fields
horizontalAlignment

enum(HorizontalAlign)

Horizontal alignment setting for the piece of text.

BasicChartSpec

The specification for a basic chart. See BasicChartType for the list of charts this supports.

JSON representation
{
  "chartType": enum(BasicChartType),
  "legendPosition": enum(BasicChartLegendPosition),
  "axis": [
    {
      object(BasicChartAxis)
    }
  ],
  "domains": [
    {
      object(BasicChartDomain)
    }
  ],
  "series": [
    {
      object(BasicChartSeries)
    }
  ],
  "headerCount": number,
  "threeDimensional": boolean,
  "interpolateNulls": boolean,
  "stackedType": enum(BasicChartStackedType),
  "lineSmoothing": boolean,
  "compareMode": enum(BasicChartCompareMode),
}
Fields
chartType

enum(BasicChartType)

The type of the chart.

legendPosition

enum(BasicChartLegendPosition)

The position of the chart legend.

axis[]

object(BasicChartAxis)

The axis on the chart.

domains[]

object(BasicChartDomain)

The domain of data this is charting. Only a single domain is supported.

series[]

object(BasicChartSeries)

The data this chart is visualizing.

headerCount

number

The number of rows or columns in the data that are "headers". If not set, Google Sheets will guess how many rows are headers based on the data.

(Note that BasicChartAxis.title may override the axis title inferred from the header values.)

threeDimensional

boolean

True to make the chart 3D. Applies to Bar and Column charts.

interpolateNulls

boolean

If some values in a series are missing, gaps may appear in the chart (e.g, segments of lines in a line chart will be missing). To eliminate these gaps set this to true. Applies to Line, Area, and Combo charts.

stackedType

enum(BasicChartStackedType)

The stacked type for charts that support vertical stacking. Applies to Area, Bar, Column, and Stepped Area charts.

lineSmoothing

boolean

Gets whether all lines should be rendered smooth or straight by default. Applies to Line charts.

compareMode

enum(BasicChartCompareMode)

The behavior of tooltips and data highlighting when hovering on data and chart area.

BasicChartType

How the chart should be visualized.

Enums
BASIC_CHART_TYPE_UNSPECIFIED Default value, do not use.
BAR A bar chart.
LINE A line chart.
AREA An area chart.
COLUMN A column chart.
SCATTER A scatter chart.
COMBO A combo chart.
STEPPED_AREA A stepped area chart.

BasicChartLegendPosition

Where the legend of the chart should be positioned.

Enums
BASIC_CHART_LEGEND_POSITION_UNSPECIFIED Default value, do not use.
BOTTOM_LEGEND The legend is rendered on the bottom of the chart.
LEFT_LEGEND The legend is rendered on the left of the chart.
RIGHT_LEGEND The legend is rendered on the right of the chart.
TOP_LEGEND The legend is rendered on the top of the chart.
NO_LEGEND No legend is rendered.

BasicChartAxis

An axis of the chart. A chart may not have more than one axis per axis position.

JSON representation
{
  "position": enum(BasicChartAxisPosition),
  "title": string,
  "format": {
    object(TextFormat)
  },
  "titleTextPosition": {
    object(TextPosition)
  },
}
Fields
position

enum(BasicChartAxisPosition)

The position of this axis.

title

string

The title of this axis. If set, this overrides any title inferred from headers of the data.

format

object(TextFormat)

The format of the title. Only valid if the axis is not associated with the domain.

titleTextPosition

object(TextPosition)

The axis title text position.

BasicChartAxisPosition

The position of a chart axis.

Enums
BASIC_CHART_AXIS_POSITION_UNSPECIFIED Default value, do not use.
BOTTOM_AXIS The axis rendered at the bottom of a chart. For most charts, this is the standard major axis. For bar charts, this is a minor axis.
LEFT_AXIS The axis rendered at the left of a chart. For most charts, this is a minor axis. For bar charts, this is the standard major axis.
RIGHT_AXIS The axis rendered at the right of a chart. For most charts, this is a minor axis. For bar charts, this is an unusual major axis.

BasicChartDomain

The domain of a chart. For example, if charting stock prices over time, this would be the date.

JSON representation
{
  "domain": {
    object(ChartData)
  },
  "reversed": boolean,
}
Fields
domain

object(ChartData)

The data of the domain. For example, if charting stock prices over time, this is the data representing the dates.

reversed

boolean

True to reverse the order of the domain values (horizontal axis).

ChartData

The data included in a domain or series.

JSON representation
{
  "sourceRange": {
    object(ChartSourceRange)
  },
}
Fields
sourceRange

object(ChartSourceRange)

The source ranges of the data.

ChartSourceRange

Source ranges for a chart.

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

object(GridRange)

The ranges of data for a series or domain. Exactly one dimension must have a length of 1, and all sources in the list must have the same dimension with length 1. The domain (if it exists) & all series must have the same number of source ranges. If using more than one source range, then the source range at a given offset must be contiguous across the domain and series.

For example, these are valid configurations:

domain sources: A1:A5
series1 sources: B1:B5
series2 sources: D6:D10

domain sources: A1:A5, C10:C12
series1 sources: B1:B5, D10:D12
series2 sources: C1:C5, E10:E12

BasicChartSeries

A single series of data in a chart. For example, if charting stock prices over time, multiple series may exist, one for the "Open Price", "High Price", "Low Price" and "Close Price".

JSON representation
{
  "series": {
    object(ChartData)
  },
  "targetAxis": enum(BasicChartAxisPosition),
  "type": enum(BasicChartType),
}
Fields
series

object(ChartData)

The data being visualized in this chart series.

targetAxis

enum(BasicChartAxisPosition)

The minor axis that will specify the range of values for this series. For example, if charting stocks over time, the "Volume" series may want to be pinned to the right with the prices pinned to the left, because the scale of trading volume is different than the scale of prices. It is an error to specify an axis that isn't a valid minor axis for the chart's type.

type

enum(BasicChartType)

The type of this series. Valid only if the chartType is COMBO. Different types will change the way the series is visualized. Only LINE, AREA, and COLUMN are supported.

BasicChartStackedType

When charts are stacked, range (vertical axis) values are rendered on top of one another rather then from the horizontal axis. For example, the two values 20 and 80 would be drawn from 0, with 80 being 80 units away from the horizontal axis. If they were stacked, 80 would be rendered from 20, putting it 100 units away from the horizontal axis.

Enums
BASIC_CHART_STACKED_TYPE_UNSPECIFIED Default value, do not use.
NOT_STACKED Series are not stacked.
STACKED Series values are stacked, each value is rendered vertically beginning from the top of the value below it.
PERCENT_STACKED Vertical stacks are stretched to reach the top of the chart, with values laid out as percentages of each other.

BasicChartCompareMode

The compare mode type, which describes the behavior of tooltips and data highlighting when hovering on data and chart area.

Enums
BASIC_CHART_COMPARE_MODE_UNSPECIFIED Default value, do not use.
DATUM Only the focused data element is highlighted and shown in the tooltip.
CATEGORY All data elements with the same category (e.g., domain value) are highlighted and shown in the tooltip.

PieChartSpec

A pie chart.

JSON representation
{
  "legendPosition": enum(PieChartLegendPosition),
  "domain": {
    object(ChartData)
  },
  "series": {
    object(ChartData)
  },
  "threeDimensional": boolean,
  "pieHole": number,
}
Fields
legendPosition

enum(PieChartLegendPosition)

Where the legend of the pie chart should be drawn.

domain

object(ChartData)

The data that covers the domain of the pie chart.

series

object(ChartData)

The data that covers the one and only series of the pie chart.

threeDimensional

boolean

True if the pie is three dimensional.

pieHole

number

The size of the hole in the pie chart.

PieChartLegendPosition

Where the legend of the chart should be positioned.

Enums
PIE_CHART_LEGEND_POSITION_UNSPECIFIED Default value, do not use.
BOTTOM_LEGEND The legend is rendered on the bottom of the chart.
LEFT_LEGEND The legend is rendered on the left of the chart.
RIGHT_LEGEND The legend is rendered on the right of the chart.
TOP_LEGEND The legend is rendered on the top of the chart.
NO_LEGEND No legend is rendered.
LABELED_LEGEND Each pie slice has a label attached to it.

BubbleChartSpec

A bubble chart.

JSON representation
{
  "legendPosition": enum(BubbleChartLegendPosition),
  "bubbleLabels": {
    object(ChartData)
  },
  "domain": {
    object(ChartData)
  },
  "series": {
    object(ChartData)
  },
  "groupIds": {
    object(ChartData)
  },
  "bubbleSizes": {
    object(ChartData)
  },
  "bubbleOpacity": number,
  "bubbleBorderColor": {
    object(Color)
  },
  "bubbleMaxRadiusSize": number,
  "bubbleMinRadiusSize": number,
  "bubbleTextStyle": {
    object(TextFormat)
  },
}
Fields
legendPosition

enum(BubbleChartLegendPosition)

Where the legend of the chart should be drawn.

bubbleLabels

object(ChartData)

The data containing the bubble labels. These do not need to be unique.

domain

object(ChartData)

The data containing the bubble x-values. These values locate the bubbles in the chart horizontally.

series

object(ChartData)

The data contianing the bubble y-values. These values locate the bubbles in the chart vertically.

groupIds

object(ChartData)

The data containing the bubble group IDs. All bubbles with the same group ID will be drawn in the same color. If bubbleSizes is specified then this field must also be specified but may contain blank values. This field is optional.

bubbleSizes

object(ChartData)

The data contianing the bubble sizes. Bubble sizes are used to draw the bubbles at different sizes relative to each other. If specified, groupIds must also be specified. This field is optional.

bubbleOpacity

number

The opacity of the bubbles between 0 and 1.0. 0 is fully transparent and 1 is fully opaque.

bubbleBorderColor

object(Color)

The bubble border color.

bubbleMaxRadiusSize

number

The max radius size of the bubbles, in pixels. If specified, the field must be a positive value.

bubbleMinRadiusSize

number

The minimum radius size of the bubbles, in pixels. If specific, the field must be a positive value.

bubbleTextStyle

object(TextFormat)

The format of the text inside the bubbles. Underline and Strikethrough are not supported.

BubbleChartLegendPosition

Where the legend of the chart should be positioned.

Enums
BUBBLE_CHART_LEGEND_POSITION_UNSPECIFIED Default value, do not use.
BOTTOM_LEGEND The legend is rendered on the bottom of the chart.
LEFT_LEGEND The legend is rendered on the left of the chart.
RIGHT_LEGEND The legend is rendered on the right of the chart.
TOP_LEGEND The legend is rendered on the top of the chart.
NO_LEGEND No legend is rendered.
INSIDE_LEGEND The legend is rendered inside the chart area.

CandlestickChartSpec

A candlestick chart.

JSON representation
{
  "domain": {
    object(CandlestickDomain)
  },
  "data": [
    {
      object(CandlestickData)
    }
  ],
}
Fields
domain

object(CandlestickDomain)

The domain data (horizontal axis) for the candlestick chart. String data will be treated as discrete labels, other data will be treated as continuous values.

data[]

object(CandlestickData)

The Candlestick chart data. Only one CandlestickData is supported.

CandlestickDomain

The domain of a CandlestickChart.

JSON representation
{
  "data": {
    object(ChartData)
  },
  "reversed": boolean,
}
Fields
data

object(ChartData)

The data of the CandlestickDomain.

reversed

boolean

True to reverse the order of the domain values (horizontal axis).

CandlestickData

The Candlestick chart data, each containing the low, open, close, and high values for a series.

JSON representation
{
  "lowSeries": {
    object(CandlestickSeries)
  },
  "openSeries": {
    object(CandlestickSeries)
  },
  "closeSeries": {
    object(CandlestickSeries)
  },
  "highSeries": {
    object(CandlestickSeries)
  },
}
Fields
lowSeries

object(CandlestickSeries)

The range data (vertical axis) for the low/minimum value for each candle. This is the bottom of the candle's center line.

openSeries

object(CandlestickSeries)

The range data (vertical axis) for the open/initial value for each candle. This is the bottom of the candle body. If less than the close value the candle will be filled. Otherwise the candle will be hollow.

closeSeries

object(CandlestickSeries)

The range data (vertical axis) for the close/final value for each candle. This is the top of the candle body. If greater than the open value the candle will be filled. Otherwise the candle will be hollow.

highSeries

object(CandlestickSeries)

The range data (vertical axis) for the high/maximum value for each candle. This is the top of the candle's center line.

CandlestickSeries

The series of a CandlestickData.

JSON representation
{
  "data": {
    object(ChartData)
  },
}
Fields
data

object(ChartData)

The data of the CandlestickSeries.

OrgChartSpec

An org chart. Org charts require a unique set of labels in labels and may optionally include parentLabels and tooltips. parentLabels contain, for each node, the label identifying the parent node. tooltips contain, for each node, an optional tooltip.

For example, to describe an OrgChart with Alice as the CEO, Bob as the President (reporting to Alice) and Cathy as VP of Sales (also reporting to Alice), have labels contain "Alice", "Bob", "Cathy", parentLabels contain "", "Alice", "Alice" and tooltips contain "CEO", "President", "VP Sales".

JSON representation
{
  "nodeSize": enum(OrgChartNodeSize),
  "nodeColor": {
    object(Color)
  },
  "selectedNodeColor": {
    object(Color)
  },
  "labels": {
    object(ChartData)
  },
  "parentLabels": {
    object(ChartData)
  },
  "tooltips": {
    object(ChartData)
  },
}
Fields
nodeSize

enum(OrgChartNodeSize)

The size of the org chart nodes.

nodeColor

object(Color)

The color of the org chart nodes.

selectedNodeColor

object(Color)

The color of the selected org chart nodes.

labels

object(ChartData)

The data containing the labels for all the nodes in the chart. Labels must be unique.

parentLabels

object(ChartData)

The data containing the label of the parent for the corresponding node. A blank value indicates that the node has no parent and is a top-level node. This field is optional.

tooltips

object(ChartData)

The data containing the tooltip for the corresponding node. A blank value results in no tooltip being displayed for the node. This field is optional.

OrgChartNodeSize

The size of the org charg nodes.

Enums
ORG_CHART_LABEL_SIZE_UNSPECIFIED Default value, do not use.
SMALL The small org chart node size.
MEDIUM The medium org chart node size.
LARGE The large org chart node size.

HistogramChartSpec

A histogram chart. A histogram chart groups data items into bins, displaying each bin as a column of stacked items. Histograms are used to display the distribution of a dataset. Each column of items represents a range into which those items fall. The number of bins can be chosen automatically or specified explicitly.

JSON representation
{
  "series": [
    {
      object(HistogramSeries)
    }
  ],
  "legendPosition": enum(HistogramChartLegendPosition),
  "showItemDividers": boolean,
  "bucketSize": number,
  "outlierPercentile": number,
}
Fields
series[]

object(HistogramSeries)

The series for a histogram may be either a single series of values to be bucketed or multiple series, each of the same length, containing the name of the series followed by the values to be bucketed for that series.

legendPosition

enum(HistogramChartLegendPosition)

The position of the chart legend.

showItemDividers

boolean

Whether horizontal divider lines should be displayed between items in each column.

bucketSize

number

By default the bucket size (the range of values stacked in a single column) is chosen automatically, but it may be overridden here. E.g., A bucket size of 1.5 results in buckets from 0 - 1.5, 1.5 - 3.0, etc. Cannot be negative. This field is optional.

outlierPercentile

number

The outlier percentile is used to ensure that outliers do not adversely affect the calculation of bucket sizes. For example, setting an outlier percentile of 0.05 indicates that the top and bottom 5% of values when calculating buckets. The values are still included in the chart, they will be added to the first or last buckets instead of their own buckets. Must be between 0.0 and 0.5.

HistogramSeries

A histogram series containing the series color and data.

JSON representation
{
  "barColor": {
    object(Color)
  },
  "data": {
    object(ChartData)
  },
}
Fields
barColor

object(Color)

The color of the column representing this series in each bucket. This field is optional.

data

object(ChartData)

The data for this histogram series.

HistogramChartLegendPosition

Where the legend of the chart should be positioned.

Enums
HISTOGRAM_CHART_LEGEND_POSITION_UNSPECIFIED Default value, do not use.
BOTTOM_LEGEND The legend is rendered on the bottom of the chart.
LEFT_LEGEND The legend is rendered on the left of the chart.
RIGHT_LEGEND The legend is rendered on the right of the chart.
TOP_LEGEND The legend is rendered on the top of the chart.
NO_LEGEND No legend is rendered.
INSIDE_LEGEND The legend is rendered inside the chart area.

ChartHiddenDimensionStrategy

Determines how charts should handle source rows that are hidden. Hidden rows include both manually hidden and hidden by a filter.

Enums
CHART_HIDDEN_DIMENSION_STRATEGY_UNSPECIFIED Default value, do not use.
SKIP_HIDDEN_ROWS_AND_COLUMNS Charts will skip hidden rows and columns.
SKIP_HIDDEN_ROWS Charts will skip hidden rows only.
SKIP_HIDDEN_COLUMNS Charts will skip hidden columns only.
SHOW_ALL Charts will not skip any hidden rows or columns.

EmbeddedObjectPosition

The position of an embedded object such as a chart.

JSON representation
{

  // Union field location can be only one of the following:
  "sheetId": number,
  "overlayPosition": {
    object(OverlayPosition)
  },
  "newSheet": boolean,
  // End of list of possible types for union field location.
}
Fields
Union field location. The location of the object. Exactly one value must be set. location can be only one of the following:
sheetId

number

The sheet this is on. Set only if the embedded object is on its own sheet. Must be non-negative.

overlayPosition

object(OverlayPosition)

The position at which the object is overlaid on top of a grid.

newSheet

boolean

If true, the embedded object will be put on a new sheet whose ID is chosen for you. Used only when writing.

OverlayPosition

The location an object is overlaid on top of a grid.

JSON representation
{
  "anchorCell": {
    object(GridCoordinate)
  },
  "offsetXPixels": number,
  "offsetYPixels": number,
  "widthPixels": number,
  "heightPixels": number,
}
Fields
anchorCell

object(GridCoordinate)

The cell the object is anchored to.

offsetXPixels

number

The horizontal offset, in pixels, that the object is offset from the anchor cell.

offsetYPixels

number

The vertical offset, in pixels, that the object is offset from the anchor cell.

widthPixels

number

The width of the object, in pixels. Defaults to 600.

heightPixels

number

The height of the object, in pixels. Defaults to 371.

GridCoordinate

A coordinate in a sheet. All indexes are zero-based.

JSON representation
{
  "sheetId": number,
  "rowIndex": number,
  "columnIndex": number,
}
Fields
sheetId

number

The sheet this coordinate is on.

rowIndex

number

The row index of the coordinate.

columnIndex

number

The column index of the coordinate.

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 will be 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 will be 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.

NamedRange

A named range.

JSON representation
{
  "namedRangeId": string,
  "name": string,
  "range": {
    object(GridRange)
  },
}
Fields
namedRangeId

string

The ID of the named range.

name

string

The name of the named range.

range

object(GridRange)

The range this represents.

Methods

batchUpdate

Applies one or more updates to the spreadsheet.

create

Creates a spreadsheet, returning the newly created spreadsheet.

get

Returns the spreadsheet at the given ID.

getByDataFilter

Returns the spreadsheet at the given ID.

Send feedback about...