/v4/spreadsheets/{spreadsheetId}:getByDataFilter

POST

spreadsheets

Returns the spreadsheet at the given ID. The caller must specify the spreadsheet ID. This method differs from GetSpreadsheet in that it allows selecting which subsets of spreadsheet data to return by specifying a dataFilters parameter. Multiple DataFilters can be specified. Specifying one or more data filters returns the portions of the spreadsheet that intersect ranges matched by any of the filters. By default, data within grids is not returned. You can include grid data one of 2 ways: * Specify a field mask listing your desired fields using the fields URL parameter in HTTP * Set the includeGridData parameter to true. If a field mask is set, the includeGridData parameter is ignored For large spreadsheets, as a best practice, retrieve only the specific spreadsheet fields that you want.

Request Example

Shell

JavaScript

Java

Swift

curl --location --request POST 'https://sheets.googleapis.com//v4/spreadsheets/:getByDataFilter' \
--header 'Content-Type: application/json' \
--data-raw '{
    "dataFilters": [
        {
            "a1Range": "string",
            "developerMetadataLookup": {},
            "gridRange": {}
        }
    ],
    "includeGridData": true
}'

Response Example

Request

Path Params

spreadsheetId

string

required

The spreadsheet to request.

Body Params application/json

The request for retrieving a Spreadsheet.

dataFilters

array[object (DataFilter) {3}]

optional

The DataFilters used to select which ranges to retrieve from the spreadsheet.

a1Range

string

optional

Selects data that matches the specified A1 range.

developerMetadataLookup

object

optional

gridRange

object

optional

includeGridData

boolean

optional

True if grid data should be returned. This parameter is ignored if a field mask was set in the request.

Examples

Responses

🟢200Successful response

application/json

Body

Resource that represents a spreadsheet.

dataSourceSchedules

array[object (DataSourceRefreshSchedule) {6}]

read-onlyoptional

Output only. A list of data source refresh schedules.

dailySchedule

object (DataSourceRefreshDailySchedule)

optional

Daily refresh schedule.

enabled

boolean

optional

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

monthlySchedule

object (DataSourceRefreshMonthlySchedule)

optional

Monthly refresh schedule.

nextRun

object (Interval)

optional

Output only. The time interval of the next run.

refreshScope

enum<string>

optional

The scope of the refresh. Must be ALL_DATA_SOURCES.

Allowed values:

DATA_SOURCE_REFRESH_SCOPE_UNSPECIFIEDALL_DATA_SOURCES

weeklySchedule

object (DataSourceRefreshWeeklySchedule)

optional

Weekly refresh schedule.

dataSources

array[object (DataSource) {4}]

optional

A list of external data sources connected with the spreadsheet.

calculatedColumns

array[object (DataSourceColumn) {2}]

optional

All calculated columns in the data source.

dataSourceId

string

optional

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

sheetId

integer <int32>

optional

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.

spec

object (DataSourceSpec)

optional

The DataSourceSpec for the data source connected with this spreadsheet.

developerMetadata

array [object]

optional

The developer metadata associated with a spreadsheet.

namedRanges

array[object (NamedRange) {3}]

optional

The named ranges defined in a spreadsheet.

name

string

optional

The name of the named range.

namedRangeId

string

optional

The ID of the named range.

range

object

optional

properties

object (SpreadsheetProperties)

optional

Overall properties of a spreadsheet.

autoRecalc

enum<string>

optional

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

Allowed values:

RECALCULATION_INTERVAL_UNSPECIFIEDON_CHANGEMINUTEHOUR

defaultFormat

object (CellFormat)

optional

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)

optional

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

locale

string

optional

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.

spreadsheetTheme

object (SpreadsheetTheme)

optional

Theme applied to the spreadsheet.

timeZone

string

optional

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.

title

string

optional

The title of the spreadsheet.

sheets

array[object (Sheet) {13}]

optional

The sheets that are part of a spreadsheet.

bandedRanges

array[object (BandedRange) {4}]

optional

The banded (alternating colors) ranges on this sheet.

basicFilter

object (BasicFilter)

optional

The filter on this sheet, if any.

charts

array[object (EmbeddedChart) {4}]

optional

The specifications of every chart on this sheet.

columnGroups

array[object (DimensionGroup) {3}]

optional

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

conditionalFormats

array[object (ConditionalFormatRule) {3}]

optional

The conditional format rules in this sheet.

data

array[object (GridData) {5}]

optional

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). For a DATA_SOURCE sheet, you can not request a specific range, the GridData contains all the values.

developerMetadata

array [object]

optional

The developer metadata associated with a sheet.

filterViews

array[object (FilterView) {7}]

optional

The filter views in this sheet.

merges

array [object]

optional

The ranges that are merged together.

properties

object (SheetProperties)

optional

The properties of the sheet.

protectedRanges

array[object (ProtectedRange) {8}]

optional

The protected ranges in this sheet.

rowGroups

array[object (DimensionGroup) {3}]

optional

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

slicers

array[object (Slicer) {3}]

optional

The slicers on this sheet.

spreadsheetId

string

optional

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

spreadsheetUrl

string

optional

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

/v4/spreadsheets/{spreadsheetId}:batchUpdate