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)
    }
  ],
  "dataSources": [
    {
      object (DataSource)
    }
  ],
  "dataSourceSchedules": [
    {
      object (DataSourceRefreshSchedule)
    }
  ],
  "comments": [
    {
      object (CommentThread)
    }
  ],
  "commentsViewMode": enum (CommentsViewMode)
}
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.
dataSources[]

object (DataSource)

A list of external data sources connected with the spreadsheet.
dataSourceSchedules[]

object (DataSourceRefreshSchedule)

Output only. A list of data source refresh schedules.
comments[]

object (CommentThread)

The comment threads associated with the spreadsheet.

commentsViewMode

enum (CommentsViewMode)

Output only. The comments view mode applied to the spreadsheet.

SpreadsheetProperties

Properties of a spreadsheet.

JSON representation 
{
  "title": string,
  "locale": string,
  "autoRecalc": enum (RecalculationInterval),
  "timeZone": string,
  "defaultFormat": {
    object (CellFormat)
  },
  "iterativeCalculationSettings": {
    object (IterativeCalculationSettings)
  },
  "spreadsheetTheme": {
    object (SpreadsheetTheme)
  },
  "importFunctionsExternalUrlAccessAllowed": boolean
}
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 result in calculation errors.
spreadsheetTheme

object (SpreadsheetTheme)

Theme applied to the spreadsheet.
importFunctionsExternalUrlAccessAllowed

boolean

Whether to allow external URL access for image and import functions. Read only when true. When false, you can set to true. This value will be bypassed and always return true if the admin has enabled the allowlisting feature.

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.

IterativeCalculationSettings

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

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

integer

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.

SpreadsheetTheme

Represents spreadsheet theme

JSON representation 
{
  "primaryFontFamily": string,
  "themeColors": [
    {
      object (ThemeColorPair)
    }
  ]
}
Fields
primaryFontFamily

string

Name of the primary font family.
themeColors[]

object (ThemeColorPair)

The spreadsheet theme color pairs. To update you must provide all theme color pairs.

ThemeColorPair

A pair mapping a spreadsheet theme color type to the concrete color it represents.

JSON representation 
{
  "colorType": enum (ThemeColorType),
  "color": {
    object (ColorStyle)
  }
}
Fields
colorType

enum (ThemeColorType)

The type of the spreadsheet theme color.
color

object (ColorStyle)

The concrete color corresponding to the theme color type.

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.

DataSource

Information about an external data source in the spreadsheet.

JSON representation 
{
  "dataSourceId": string,
  "spec": {
    object (DataSourceSpec)
  },
  "calculatedColumns": [
    {
      object (DataSourceColumn)
    }
  ],
  "sheetId": integer
}
Fields
dataSourceId

string

The spreadsheet-scoped unique ID that identifies the data source. Example: 1080547365.
spec

object (DataSourceSpec)

The DataSourceSpec for the data source connected with this spreadsheet.
calculatedColumns[]

object (DataSourceColumn)

All calculated columns in the data source.
sheetId

integer

The ID of the Sheet connected with the data source. The field cannot be changed once set.

When creating a data source, an associated DATA_SOURCE sheet is also created, if the field is not specified, the ID of the created sheet will be randomly generated.

DataSourceSpec

This specifies the details of the data source. For example, for BigQuery, this specifies information about the BigQuery source.

JSON representation 
{
  "parameters": [
    {
      object (DataSourceParameter)
    }
  ],

  "bigQuery": {
    object (BigQueryDataSourceSpec)
  },
  "looker": {
    object (LookerDataSourceSpec)
  }
}
Fields
parameters[]

object (DataSourceParameter)

The parameters of the data source, used when querying the data source.
Union field spec. The actual specification per data source type. spec can be only one of the following:
bigQuery

object (BigQueryDataSourceSpec)

A BigQueryDataSourceSpec.
looker

object (LookerDataSourceSpec)

A [LookerDatasourceSpec][].

BigQueryDataSourceSpec

The specification of a BigQuery data source that's connected to a sheet.

JSON representation 
{
  "projectId": string,

  "querySpec": {
    object (BigQueryQuerySpec)
  },
  "tableSpec": {
    object (BigQueryTableSpec)
  }
}
Fields
projectId

string

The ID of a BigQuery enabled Google Cloud project with a billing account attached. For any queries executed against the data source, the project is charged.
Union field spec. The actual specification. spec can be only one of the following:
querySpec

object (BigQueryQuerySpec)

A BigQueryQuerySpec.
tableSpec

object (BigQueryTableSpec)

A BigQueryTableSpec.

BigQueryQuerySpec

Specifies a custom BigQuery query.

JSON representation 
{
  "rawQuery": string
}
Fields
rawQuery

string

The raw query string.

BigQueryTableSpec

Specifies a BigQuery table definition. Only native tables are allowed.

JSON representation 
{
  "tableProjectId": string,
  "tableId": string,
  "datasetId": string
}
Fields
tableProjectId

string

The ID of a BigQuery project the table belongs to. If not specified, the projectId is assumed.
tableId

string

The BigQuery table id.
datasetId

string

The BigQuery dataset id.

LookerDataSourceSpec

The specification of a Looker data source.

JSON representation 
{
  "instanceUri": string,
  "model": string,
  "explore": string
}
Fields
instanceUri

string

A Looker instance URL.
model

string

Name of a Looker model.
explore

string

Name of a Looker model explore.

DataSourceParameter

A parameter in a data source's query. The parameter allows the user to pass in values from the spreadsheet into a query.

JSON representation 
{

  "name": string

  "namedRangeId": string,
  "range": {
    object (GridRange)
  }
}
Fields
Union field identifier. The parameter identifier. identifier can be only one of the following:
name

string

Named parameter. Must be a legitimate identifier for the DataSource that supports it. For example, BigQuery identifier.
Union field value. The parameter value. value can be only one of the following:
namedRangeId

string

ID of a NamedRange. Its size must be 1x1.
range

object (GridRange)

A range that contains the value of the parameter. Its size must be 1x1.

DataSourceRefreshSchedule

Schedule for refreshing the data source.

Data sources in the spreadsheet are refreshed within a time interval. You can specify the start time by clicking the Scheduled Refresh button in the Sheets editor, but the interval is fixed at 4 hours. For example, if you specify a start time of 8 AM , the refresh will take place between 8 AM and 12 PM every day.

JSON representation 
{
  "enabled": boolean,
  "refreshScope": enum (DataSourceRefreshScope),
  "nextRun": {
    object (Interval)
  },

  "dailySchedule": {
    object (DataSourceRefreshDailySchedule)
  },
  "weeklySchedule": {
    object (DataSourceRefreshWeeklySchedule)
  },
  "monthlySchedule": {
    object (DataSourceRefreshMonthlySchedule)
  }
}
Fields
enabled

boolean

True if the refresh schedule is enabled, or false otherwise.
refreshScope

enum (DataSourceRefreshScope)

The scope of the refresh. Must be ALL_DATA_SOURCES.
nextRun

object (Interval)

Output only. The time interval of the next run.
Union field schedule_config. Schedule configurations schedule_config can be only one of the following:
dailySchedule

object (DataSourceRefreshDailySchedule)

Daily refresh schedule.
weeklySchedule

object (DataSourceRefreshWeeklySchedule)

Weekly refresh schedule.
monthlySchedule

object (DataSourceRefreshMonthlySchedule)

Monthly refresh schedule.

DataSourceRefreshScope

The data source refresh scopes.

Enums
DATA_SOURCE_REFRESH_SCOPE_UNSPECIFIED Default value, do not use.
ALL_DATA_SOURCES Refreshes all data sources and their associated data source objects in the spreadsheet.

DataSourceRefreshDailySchedule

A schedule for data to refresh every day in a given time interval.

JSON representation 
{
  "startTime": {
    object (TimeOfDay)
  }
}
Fields
startTime

object (TimeOfDay)

The start time of a time interval in which a data source refresh is scheduled. Only hours part is used. The time interval size defaults to that in the Sheets editor.

TimeOfDay

Represents a time of day. The date and time zone are either not significant or are specified elsewhere. An API may choose to allow leap seconds. Related types are google.type.Date and google.protobuf.Timestamp.

JSON representation 
{
  "hours": integer,
  "minutes": integer,
  "seconds": integer,
  "nanos": integer
}
Fields
hours

integer

Hours of a day in 24 hour format. Must be greater than or equal to 0 and typically must be less than or equal to 23. An API may choose to allow the value "24:00:00" for scenarios like business closing time.
minutes

integer

Minutes of an hour. Must be greater than or equal to 0 and less than or equal to 59.
seconds

integer

Seconds of a minute. Must be greater than or equal to 0 and typically must be less than or equal to 59. An API may allow the value 60 if it allows leap-seconds.
nanos

integer

Fractions of seconds, in nanoseconds. Must be greater than or equal to 0 and less than or equal to 999,999,999.

DataSourceRefreshWeeklySchedule

A weekly schedule for data to refresh on specific days in a given time interval.

JSON representation 
{
  "startTime": {
    object (TimeOfDay)
  },
  "daysOfWeek": [
    enum (DayOfWeek)
  ]
}
Fields
startTime

object (TimeOfDay)

The start time of a time interval in which a data source refresh is scheduled. Only hours part is used. The time interval size defaults to that in the Sheets editor.
daysOfWeek[]

enum (DayOfWeek)

Days of the week to refresh. At least one day must be specified.

DayOfWeek

Represents a day of the week.

Enums
DAY_OF_WEEK_UNSPECIFIED The day of the week is unspecified.
MONDAY Monday
TUESDAY Tuesday
WEDNESDAY Wednesday
THURSDAY Thursday
FRIDAY Friday
SATURDAY Saturday
SUNDAY Sunday

DataSourceRefreshMonthlySchedule

A monthly schedule for data to refresh on specific days in the month in a given time interval.

JSON representation 
{
  "startTime": {
    object (TimeOfDay)
  },
  "daysOfMonth": [
    integer
  ]
}
Fields
startTime

object (TimeOfDay)

The start time of a time interval in which a data source refresh is scheduled. Only hours part is used. The time interval size defaults to that in the Sheets editor.
daysOfMonth[]

integer

Days of the month to refresh. Only 1-28 are supported, mapping to the 1st to the 28th day. At least one day must be specified.

Interval

Represents a time interval, encoded as a Timestamp start (inclusive) and a Timestamp end (exclusive).

The start must be less than or equal to the end. When the start equals the end, the interval is empty (matches no time). When both start and end are unspecified, the interval matches any time.

JSON representation 
{
  "startTime": string,
  "endTime": string
}
Fields
startTime

string (Timestamp format)

Optional. Inclusive start of the interval.

If specified, a Timestamp matching this interval will have to be the same or after the start.
endTime

string (Timestamp format)

Optional. Exclusive end of the interval.

If specified, a Timestamp matching this interval will have to be before the end.

CommentThread

Represents a single comment thread inside a spreadsheet.

JSON representation 
{
  "commentId": string,
  "anchorId": string,
  "headPost": {
    object (Post)
  },
  "replies": [
    {
      object (Post)
    }
  ],
  "status": enum (Status),

  "plainTextQuote": string
}
Fields
commentId

string

The unique ID of the comment thread.
anchorId

string

The ID of the CommentAnchor in the sheet that this thread is tied to.
headPost

object (Post)

The first post in the thread.
replies[]

object (Post)

Replies to the head post.
status

enum (Status)

Whether the thread is open or resolved.
Union field quote. The quoted text from the document when the comment was created. quote can be only one of the following:
plainTextQuote

string

The quoted text from the spreadsheet when the comment was created, formatted as plain-text.

Post

Represents a single post in a comment thread.

JSON representation 
{
  "postId": string,
  "content": string,
  "contentHtml": string,
  "author": {
    object (PostAuthor)
  },
  "createTime": string,
  "updateTime": string,
  "deleted": boolean,
  "fromImportedSpreadsheet": boolean,
  "fromCopiedSpreadsheet": boolean,
  "assigneeEmail": string,
  "commentAction": enum (CommentActionType)
}
Fields
postId

string

Output only. The unique ID of the post.
content

string

The content of the post.

Required to be non-empty if commentAction is not RESOLVE or REOPEN.

This text content will be handled similarly to comments created in the Sheets editor. It will have similar behaviors for formatting, notifications, etc.

May not exceed 2048 UTF-8 code units.
contentHtml

string

Output only. The content of the post as HTML.
author

object (PostAuthor)

Output only. The user who created the post.
createTime

string (Timestamp format)

Output only. The time the post was created.
updateTime

string (Timestamp format)

Output only. The time the post was last updated.
deleted

boolean

Output only. Whether the post is deleted. If true, content and author fields will be empty.
fromImportedSpreadsheet

boolean

Output only. Whether the post is from an imported spreadsheet. This field cannot be set directly by callers.
fromCopiedSpreadsheet

boolean

Output only. Whether the post is from a copied spreadsheet. This field cannot be set directly by callers.
assigneeEmail

string

Optional. The email of the user who is being newly assigned to the thread as part of this post.

Returns a 400 bad request error if:

  • The parent thread is a CommentThread whose headPost does not have an assignee.

  • commentAction is specified as RESOLVE or REOPEN.

  • assigneeEmail exceeds 2048 UTF-8 code units.
commentAction

enum (CommentActionType)

Action taken as part of creating the post.

PostAuthor

Represents a user who authored a comment post.

JSON representation 
{
  "displayName": string,
  "me": boolean,
  "anonymous": boolean,
  "user": string
}
Fields
displayName

string

The display name of the user. May be absent if the author is anonymous.
me

boolean

Whether the user is the authenticated user making the request.
anonymous

boolean

Whether the user is anonymous.
user

string

The resource name of the post author user, which can also be used to identify the user in the Google People API. Format: users/{user}. Will not be populated if the anonymous field is true or if the post is from an imported spreadsheet.

CommentActionType

The action taken with this reply to a comment thread.

Enums
COMMENT_ACTION_TYPE_UNSPECIFIED Default value. This value is unused.
NO_COMMENT_ACTION_CHANGE No action change in this post.
RESOLVE This post resolves the thread.
REOPEN This post reopens the thread.

Status

The status options of the comment thread.

Enums
STATUS_UNSPECIFIED Default value. This value is unused.
OPEN The comment thread is open.
RESOLVED The comment thread is resolved.

CommentsViewMode

The comments view mode applied to the spreadsheet that indicates if comments are included. It provides options for reading the spreadsheet with or without comments and comment anchors.

Enums
COMMENTS_VIEW_MODE_UNSPECIFIED The CommentsViewMode is unspecified; COMMENTS_VIEW_MODE_OMITTED is applied.
COMMENTS_VIEW_MODE_DEFAULT_FOR_CURRENT_ACCESS The CommentsViewMode applied to the returned spreadsheet depends on the user's current access level. If the user only has view access, COMMENTS_VIEW_MODE_OMITTED is applied. Otherwise, COMMENTS_VIEW_MODE_INCLUDED is applied.
COMMENTS_VIEW_MODE_OMITTED The returned spreadsheet has comments omitted.
COMMENTS_VIEW_MODE_INCLUDED

The returned spreadsheet has comments included.

Requests to retrieve a spreadsheet using this mode will return a 403 error if the user does not have permission to view comments.

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.