Menu
Sheets.spreadsheets.get By Data Filter
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](https://developers.google.com/sheets/api/guides/field-masks) 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.
POST
/v4/spreadsheets/{spreadsheetId}:getByDataFilterUse it in a workflow
- Add a step and choose the Google Sheets connector.
- Pick the Sheets.spreadsheets.get By Data Filter action (under spreadsheets).
- Fill in the fields below, then reference the result from later steps as
{{sheetsSpreadsheetsGetByDataFilter.response.data}}.
Request
Path parameters
Query parameters
Request body
Enter the body as JSON in the step's Body field.
{
"dataFilters": [
{
"a1Range": "string",
"developerMetadataLookup": {
"locationMatchingStrategy": "DEVELOPER_METADATA_LOCATION_MATCHING_STRATEGY_UNSPECIFIED",
"locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
"metadataId": 0,
"metadataKey": "string",
"metadataLocation": {
"dimensionRange": {
"dimension": "DIMENSION_UNSPECIFIED",
"endIndex": 0,
"sheetId": 0,
"startIndex": 0
},
"locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
"sheetId": 0,
"spreadsheet": false
},
"metadataValue": "string",
"visibility": "DEVELOPER_METADATA_VISIBILITY_UNSPECIFIED"
},
"gridRange": {
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
}
}
],
"includeGridData": false
}
Response
Returns 200 with an object. Read it in later steps with {{sheetsSpreadsheetsGetByDataFilter.response.data.<field>}}.
{
"dataSourceSchedules": [
{
"dailySchedule": {
"startTime": {
"hours": 0,
"minutes": 0,
"nanos": 0,
"seconds": 0
}
},
"enabled": false,
"monthlySchedule": {
"daysOfMonth": [
0
],
"startTime": {
"hours": 0,
"minutes": 0,
"nanos": 0,
"seconds": 0
}
},
"nextRun": {
"endTime": "string",
"startTime": "string"
},
"refreshScope": "DATA_SOURCE_REFRESH_SCOPE_UNSPECIFIED",
"weeklySchedule": {
"daysOfWeek": [
"DAY_OF_WEEK_UNSPECIFIED"
],
"startTime": {
"hours": 0,
"minutes": 0,
"nanos": 0,
"seconds": 0
}
}
}
],
"dataSources": [
{
"calculatedColumns": [
{
"formula": "string",
"reference": {
"name": "string"
}
}
],
"dataSourceId": "string",
"sheetId": 0,
"spec": {
"bigQuery": {
"projectId": "string",
"querySpec": {
"rawQuery": "string"
},
"tableSpec": {
"datasetId": "string",
"tableId": "string",
"tableProjectId": "string"
}
},
"parameters": [
{
"name": "string",
"namedRangeId": "string",
"range": {
"endColumnIndex": null,
"endRowIndex": null,
"sheetId": null,
"startColumnIndex": null,
"startRowIndex": null
}
}
]
}
}
],
"developerMetadata": [
{
"location": {
"dimensionRange": {
"dimension": "DIMENSION_UNSPECIFIED",
"endIndex": 0,
"sheetId": 0,
"startIndex": 0
},
"locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
"sheetId": 0,
"spreadsheet": false
},
"metadataId": 0,
"metadataKey": "string",
"metadataValue": "string",
"visibility": "DEVELOPER_METADATA_VISIBILITY_UNSPECIFIED"
}
],
"namedRanges": [
{
"name": "string",
"namedRangeId": "string",
"range": {
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
}
}
],
"properties": {
"autoRecalc": "RECALCULATION_INTERVAL_UNSPECIFIED",
"defaultFormat": {
"backgroundColor": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"backgroundColorStyle": {
"rgbColor": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"borders": {
"bottom": {
"color": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"colorStyle": {
"rgbColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"style": "STYLE_UNSPECIFIED",
"width": 0
},
"left": {
"color": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"colorStyle": {
"rgbColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"style": "STYLE_UNSPECIFIED",
"width": 0
},
"right": {
"color": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"colorStyle": {
"rgbColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"style": "STYLE_UNSPECIFIED",
"width": 0
},
"top": {
"color": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"colorStyle": {
"rgbColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"style": "STYLE_UNSPECIFIED",
"width": 0
}
},
"horizontalAlignment": "HORIZONTAL_ALIGN_UNSPECIFIED",
"hyperlinkDisplayType": "HYPERLINK_DISPLAY_TYPE_UNSPECIFIED",
"numberFormat": {
"pattern": "string",
"type": "NUMBER_FORMAT_TYPE_UNSPECIFIED"
},
"padding": {
"bottom": 0,
"left": 0,
"right": 0,
"top": 0
},
"textDirection": "TEXT_DIRECTION_UNSPECIFIED",
"textFormat": {
"bold": false,
"fontFamily": "string",
"fontSize": 0,
"foregroundColor": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"foregroundColorStyle": {
"rgbColor": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"italic": false,
"link": {
"uri": "string"
},
"strikethrough": false,
"underline": false
},
"textRotation": {
"angle": 0,
"vertical": false
},
"verticalAlignment": "VERTICAL_ALIGN_UNSPECIFIED",
"wrapStrategy": "WRAP_STRATEGY_UNSPECIFIED"
},
"iterativeCalculationSettings": {
"convergenceThreshold": 0,
"maxIterations": 0
},
"locale": "string",
"spreadsheetTheme": {
"primaryFontFamily": "string",
"themeColors": [
{
"color": {
"rgbColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"colorType": "THEME_COLOR_TYPE_UNSPECIFIED"
}
]
},
"timeZone": "string",
"title": "string"
},
"sheets": [
{
"bandedRanges": [
{
"bandedRangeId": 0,
"columnProperties": {
"firstBandColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"firstBandColorStyle": {
"rgbColor": null,
"themeColor": null
},
"footerColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"footerColorStyle": {
"rgbColor": null,
"themeColor": null
},
"headerColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"headerColorStyle": {
"rgbColor": null,
"themeColor": null
},
"secondBandColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"secondBandColorStyle": {
"rgbColor": null,
"themeColor": null
}
},
"range": {
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
},
"rowProperties": {
"firstBandColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"firstBandColorStyle": {
"rgbColor": null,
"themeColor": null
},
"footerColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"footerColorStyle": {
"rgbColor": null,
"themeColor": null
},
"headerColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"headerColorStyle": {
"rgbColor": null,
"themeColor": null
},
"secondBandColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"secondBandColorStyle": {
"rgbColor": null,
"themeColor": null
}
}
}
],
"basicFilter": {
"criteria": {},
"filterSpecs": [
{
"columnIndex": 0,
"dataSourceColumnReference": {
"name": null
},
"filterCriteria": {
"condition": null,
"hiddenValues": null,
"visibleBackgroundColor": null,
"visibleBackgroundColorStyle": null,
"visibleForegroundColor": null,
"visibleForegroundColorStyle": null
}
}
],
"range": {
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
},
"sortSpecs": [
{
"backgroundColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"backgroundColorStyle": {
"rgbColor": null,
"themeColor": null
},
"dataSourceColumnReference": {
"name": null
},
"dimensionIndex": 0,
"foregroundColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"foregroundColorStyle": {
"rgbColor": null,
"themeColor": null
},
"sortOrder": "SORT_ORDER_UNSPECIFIED"
}
]
},
"charts": [
{
"border": {
"color": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"colorStyle": {
"rgbColor": null,
"themeColor": null
}
},
"chartId": 0,
"position": {
"newSheet": false,
"overlayPosition": {
"anchorCell": null,
"heightPixels": null,
"offsetXPixels": null,
"offsetYPixels": null,
"widthPixels": null
},
"sheetId": 0
},
"spec": {
"altText": "string",
"backgroundColor": {
"alpha": null,
"blue": null,
"green": null,
"red": null
},
"backgroundColorStyle": {
"rgbColor": null,
"themeColor": null
},
"basicChart": {
"axis": null,
"chartType": null,
"compareMode": null,
"domains": null,
"headerCount": null,
"interpolateNulls": null,
"legendPosition": null,
"lineSmoothing": null,
"series": null,
"stackedType": null,
"threeDimensional": null,
"totalDataLabel": null
},
"bubbleChart": {
"bubbleBorderColor": null,
"bubbleBorderColorStyle": null,
"bubbleLabels": null,
"bubbleMaxRadiusSize": null,
"bubbleMinRadiusSize": null,
"bubbleOpacity": null,
"bubbleSizes": null,
"bubbleTextStyle": null,
"domain": null,
"groupIds": null,
"legendPosition": null,
"series": null
},
"candlestickChart": {
"data": null,
"domain": null
},
"dataSourceChartProperties": {
"dataExecutionStatus": null,
"dataSourceId": null
},
"filterSpecs": [
null
],
"fontName": "string",
"hiddenDimensionStrategy": "CHART_HIDDEN_DIMENSION_STRATEGY_UNSPECIFIED",
"histogramChart": {
"bucketSize": null,
"legendPosition": null,
"outlierPercentile": null,
"series": null,
"showItemDividers": null
},
"maximized": false
}
}
],
"columnGroups": [
{
"collapsed": false,
"depth": 0,
"range": {
"dimension": "DIMENSION_UNSPECIFIED",
"endIndex": 0,
"sheetId": 0,
"startIndex": 0
}
}
],
"conditionalFormats": [
{
"booleanRule": {
"condition": {
"type": null,
"values": null
},
"format": {
"backgroundColor": null,
"backgroundColorStyle": null,
"borders": null,
"horizontalAlignment": null,
"hyperlinkDisplayType": null,
"numberFormat": null,
"padding": null,
"textDirection": null,
"textFormat": null,
"textRotation": null,
"verticalAlignment": null,
"wrapStrategy": null
}
},
"gradientRule": {
"maxpoint": {
"color": null,
"colorStyle": null,
"type": null,
"value": null
},
"midpoint": {
"color": null,
"colorStyle": null,
"type": null,
"value": null
},
"minpoint": {
"color": null,
"colorStyle": null,
"type": null,
"value": null
}
},
"ranges": [
{
"endColumnIndex": null,
"endRowIndex": null,
"sheetId": null,
"startColumnIndex": null,
"startRowIndex": null
}
]
}
],
"data": [
{
"columnMetadata": [
{
"dataSourceColumnReference": null,
"developerMetadata": null,
"hiddenByFilter": null,
"hiddenByUser": null,
"pixelSize": null
}
],
"rowData": [
{
"values": null
}
],
"rowMetadata": [
{
"dataSourceColumnReference": null,
"developerMetadata": null,
"hiddenByFilter": null,
"hiddenByUser": null,
"pixelSize": null
}
],
"startColumn": 0,
"startRow": 0
}
],
"developerMetadata": [
{
"location": {
"dimensionRange": {
"dimension": null,
"endIndex": null,
"sheetId": null,
"startIndex": null
},
"locationType": "DEVELOPER_METADATA_LOCATION_TYPE_UNSPECIFIED",
"sheetId": 0,
"spreadsheet": false
},
"metadataId": 0,
"metadataKey": "string",
"metadataValue": "string",
"visibility": "DEVELOPER_METADATA_VISIBILITY_UNSPECIFIED"
}
],
"filterViews": [
{
"criteria": {},
"filterSpecs": [
{
"columnIndex": null,
"dataSourceColumnReference": null,
"filterCriteria": null
}
],
"filterViewId": 0,
"namedRangeId": "string",
"range": {
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
},
"sortSpecs": [
{
"backgroundColor": null,
"backgroundColorStyle": null,
"dataSourceColumnReference": null,
"dimensionIndex": null,
"foregroundColor": null,
"foregroundColorStyle": null,
"sortOrder": null
}
],
"title": "string"
}
],
"merges": [
{
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
}
],
"properties": {
"dataSourceSheetProperties": {
"columns": [
{
"formula": null,
"reference": null
}
],
"dataExecutionStatus": {
"errorCode": "string",
"errorMessage": "string",
"lastRefreshTime": "string",
"state": "string"
},
"dataSourceId": "string"
},
"gridProperties": {
"columnCount": 0,
"columnGroupControlAfter": false,
"frozenColumnCount": 0,
"frozenRowCount": 0,
"hideGridlines": false,
"rowCount": 0,
"rowGroupControlAfter": false
},
"hidden": false,
"index": 0,
"rightToLeft": false,
"sheetId": 0,
"sheetType": "SHEET_TYPE_UNSPECIFIED",
"tabColor": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"tabColorStyle": {
"rgbColor": {
"alpha": 0,
"blue": 0,
"green": 0,
"red": 0
},
"themeColor": "THEME_COLOR_TYPE_UNSPECIFIED"
},
"title": "string"
},
"protectedRanges": [
{
"description": "string",
"editors": {
"domainUsersCanEdit": false,
"groups": [
null
],
"users": [
null
]
},
"namedRangeId": "string",
"protectedRangeId": 0,
"range": {
"endColumnIndex": 0,
"endRowIndex": 0,
"sheetId": 0,
"startColumnIndex": 0,
"startRowIndex": 0
},
"requestingUserCanEdit": false,
"unprotectedRanges": [
{
"endColumnIndex": null,
"endRowIndex": null,
"sheetId": null,
"startColumnIndex": null,
"startRowIndex": null
}
],
"warningOnly": false
}
],
"rowGroups": [
{
"collapsed": false,
"depth": 0,
"range": {
"dimension": "DIMENSION_UNSPECIFIED",
"endIndex": 0,
"sheetId": 0,
"startIndex": 0
}
}
]
}
],
"spreadsheetId": "string",
"spreadsheetUrl": "string"
}
Need more? See the Google Sheets guide for connection setup and behaviour shared by every action.
Was this page helpful?