Overview
Configuration is provided for establishing connections with the Google Sheets service. The configuration is used in the REST Connection Manager.
Setup
Press icon to get more information about the connection parameters.
Obtain data
Use REST Source component to get data from service resource.
Insert data
Use REST Destination component to insert data into service resource.
Quick Start
In this task, we will show you Step-by-Step how-to create a connection to Google sheets service using COZYROC's REST Connection Manager.
Configuration
Base URL address: https://sheets.googleapis.com/v4
.
- OAuth
-
The authentication uses an authorized token. The token will be refreshed with the following expression:
{{=Date.now() + (response.expires_in - 300) * 1000}}
.The authentication has the following user-defined parameters:
- Scope:
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
Bearer {{=token.Access}}
Documentation: https://developers.google.com/sheets/guides/authorizing.
- [Read] action
-
Endpoint URL address:
https://www.googleapis.com/drive/v3/files
.
The result is extracted from:{{=response.files}}
.
The result is paginated.The action has the following user-defined parameters:
- folder: Specifies parent folder from which to get list of available spreadsheets.
The following request parameters will be automatically processed:
-
pageToken:
{{=parameters.iterator}}
-
pageSize:
{{=parameters.batchSize}}
-
fields:
nextPageToken, files({{=_.filter(_.without(parameters.fields, 'properties', 'sheets', 'namedRanges'), function(item) { return item.indexOf('.') == -1; }).join()}})
-
q:
"{{=(parameters.get('folder') && Sheets.getFileId(parameters.folder)) || 'root'}}" in parents and mimeType='application/vnd.google-apps.spreadsheet' and not trashed
-
_includeUserParameters:
{{=parameters}}
Documentation: https://developers.google.com/drive/v3/reference/files/list.
- [Create] action
-
Endpoint URL address:
/spreadsheets
.
The action uses POST method.The following request parameters will be automatically processed:
-
application/json:
{{=item}}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/create.
-
application/json:
- id
A key field. Template: ShortText.
- name
Template: ShortText. A read-only field.
- description
Template: LongText. A read-only field.
- parents
Template: LongText. A read-only field. A composite field.
- version
Data type: DT_I8 A read-only field.
- webViewLink
Template: ShortText. A read-only field.
- createdTime
Template: DateTime. A read-only field.
- modifiedTime
Template: DateTime. A read-only field.
- lastModifyingUser
A read-only field. Field components:
-
Uses template:
ShortText
. - Contains the following components: kind, displayName, photoLink, me, permissionId, emailAddress.
-
Uses template:
- shared
Data type: DT_BOOL A read-only field.
- capabilities
A read-only field. Field components:
- Contains the following components: canAddChildren, canChangeCopyRequiresWriterPermission, canChangeViewersCanCopyContent, canComment, canCopy, canDelete, canDownload, canEdit, canListChildren, canMoveItemIntoTeamDrive, canReadRevisions, canRemoveChildren, canRename, canShare, canTrash, canUntrash.
- viewersCanCopyContent
Data type: DT_BOOL A read-only field.
- properties
-
The value is gathered from
/spreadsheets/{{=item.id}}
address. The gathered value is processed with{{=response.properties}}
expression. Field components:-
Uses template:
ShortText
. - Contains the following components: title, locale, autoRecalc, timeZone, defaultFormat.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#SpreadsheetProperties.
-
Uses template:
- sheets
-
The value is gathered from
/spreadsheets/{{=item.id}}
address. The gathered value is processed with{{=response.sheets}}
expression. Field components:- An array.
- Contains the following components: properties, data, merges, conditionalFormats, filterViews, protectedRanges, basicFilter, charts.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#Sheet.
- namedRanges
The value is gathered from
/spreadsheets/{{=item.id}}
address. The gathered value is processed with{{=response.namedRanges}}
expression. Field components:- An array.
-
Uses template:
NamedRange
.
- [Read] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}
.
The result is extracted from:{{=jsonPath(response, 'sheets[*].properties')}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to retrieve available sheets. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
fields:
sheets.properties
-
_includeUserParameters:
{{=parameters}}
- [Create] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.
The result is extracted from:{{=response.replies[0].addSheet.properties}}
.
The action uses POST method.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ addSheet: { properties: item } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#AddSheetRequest.
- [Update] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.
The action uses POST method.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ updateSheetProperties: { properties: _.extend({ sheetId: item.sheetId }, item), fields: _.keys(item).join() } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateSheetPropertiesRequest.
- [Delete] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.
The action uses POST method.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to delete sheet. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ deleteSheet: { sheetId: item.sheetId } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#DeleteSheetRequest.
The resource includes the fields from the SheetProperties template.
- [Read] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}/values/{{=parameters.range}}
.
The result is extracted from:{{=Sheets.fromValuesArray(parameters, response.values)}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to read from. Use the following syntax: folder/subFolder/fileName
- range: Required. Specifies A1 notation range. For further information check: https://developers.google.com/sheets/guides/concepts#a1_notation
- firstRowColumnNames: Specifies if first range row contains column names.
The following request parameters will be automatically processed:
-
majorDimension:
ROWS
-
_includeUserParameters:
{{=parameters}}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/get.
- [Create] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}/values/{{=parameters.range}}:append
.
The result is extracted from:{{=undefined}}
.
The action uses POST method.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write. Use the following syntax: folder/subFolder/fileName
- range: Required. Specifies A1 notation range. For further information check: https://developers.google.com/sheets/guides/concepts#a1_notation
- firstRowColumnNames: Specifies if first range row contains column names.
- clear: Specifies range to clear.
The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}}
-
valueInputOption:
USER_ENTERED
-
application/json:
{{={ range: parameters.range, majorDimension: 'ROWS', values: Sheets.toValuesArray(parameters, batch) } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/append.
- [Update] action
-
Endpoint URL address:
.
The result is extracted from:{{=Sheets.updateData(parameters, batch)}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write. Use the following syntax: folder/subFolder/fileName
- range: Required. Specifies A1 notation range. For further information check: https://developers.google.com/sheets/guides/concepts#a1_notation
- firstRowColumnNames: Specifies if first range row contains column names.
- clear: Specifies range to clear.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/update.
- [External]
-
The external fields list is extracted from:
{{=Sheets.getSourceFields(parameters)}}
.- {{=external.name}}
Data type: {{=external.type}} ( length {{=external.length}} )
- [Read] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}
.
The result is extracted from:{{=response.namedRanges}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to retrieve available named ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
fields:
namedRanges
-
_includeUserParameters:
{{=parameters}}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets.
- [Create] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write named ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ addNamedRange: { namedRange: item } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#AddNamedRangeRequest.
- [Update] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write named ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ updateNamedRange: { namedRange: _.extend({ namedRangeId: item.namedRangeId }, item), fields: _.keys(item).join() } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateNamedRangeRequest.
- [Delete] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to delete named ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ deleteNamedRange: { namedRangeId: item.namedRangeId } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#DeleteNamedRangeRequest.
The resource includes the fields from the NamedRange template.
- [Read] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}
.
The result is extracted from:{{=(_.find(response.sheets, function(sheet){ return sheet.properties.sheetId == parameters.sheetId; }) || {}).protectedRanges}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to read from. Use the following syntax: folder/subFolder/fileName
- sheetId: Required. Specifies sheet identifier.
The following request parameters will be automatically processed:
-
fields:
sheets
-
_includeUserParameters:
{{=parameters}}
- [Create] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write protected ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ addProtectedRange: { protectedRange: item } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#AddProtectedRangeRequest.
- [Update] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write protected ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ updateProtectedRange: { protectedRange: _.extend({ protectedRangeId: item.protectedRangeId }, item), fields: _.keys(item).join() } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateProtectedRangeRequest.
- [Delete] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to delete protected ranges. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ deleteProtectedRange: { protectedRangeId: item.protectedRangeId } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#DeleteProtectedRangeRequest.
The resource includes the fields from the ProtectedRange template.
- [Read] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}
.
The result is extracted from:{{=(_.find(response.sheets, function(sheet){ return sheet.properties.sheetId == parameters.sheetId; }) || {}).conditionalFormats}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to read from. Use the following syntax: folder/subFolder/fileName
- sheetId: Required. Specifies sheet identifier.
The following request parameters will be automatically processed:
-
fields:
sheets
-
_includeUserParameters:
{{=parameters}}
- [Create] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write conditional format rule. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ addConditionalFormatRule: { rule: item } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#AddConditionalFormatRuleRequest.
- [Update] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write conditional format rule. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ sheetId: item.sheetId, index: item.index, newIndex: item.newIndex, updateConditionalFormatRule: { rule: item } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateConditionalFormatRuleRequest.
- [Delete] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to delete conditional format rule. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ deleteConditionalFormatRule: { sheetId: item.sheetId, index: item.index } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#DeleteConditionalFormatRuleRequest.
The resource includes the fields from the ConditionalFormatRule template.
- sheetId
A key field. Data type: DT_I4
- index
A key field. Data type: DT_I4
- newIndex
Data type: DT_I4
- [Read] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}
.
The result is extracted from:{{=(_.find(response.sheets, function(sheet){ return sheet.properties.sheetId == parameters.sheetId; }) || {}).filterViews}}
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to read from. Use the following syntax: folder/subFolder/fileName
- sheetId: Required. Specifies sheet identifier.
The following request parameters will be automatically processed:
-
fields:
sheets
-
_includeUserParameters:
{{=parameters}}
- [Create] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write filter view. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ addFilterView: { filter: item } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#AddFilterViewRequest.
- [Update] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to write filter view. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ updateFilterView: { filter: _.extend({ filterViewId: item.filterViewId }, item), fields: _.keys(item).join() } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#UpdateFilterViewRequest.
- [Delete] action
-
Endpoint URL address:
/spreadsheets/{{=Sheets.getFileId(parameters.filePath)}}:batchUpdate
.The action has the following user-defined parameters:
- filePath: Required. Specifies path to spreadsheet to delete filter view. Use the following syntax: folder/subFolder/fileName
The following request parameters will be automatically processed:
-
application/json:
{{={ requests: [{ deleteFilterView: { filterId: item.filterViewId } }] } }}
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets/request#DeleteFilterViewRequest.
The resource includes the fields from the FilterView template.
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- ExtendedValue
-
Field components:
-
Uses template:
LongText
. - Contains the following components: numberValue, stringValue, boolValue, formulaValue, errorValue.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#ExtendedValue.
-
Uses template:
- Color
-
Field components:
- Contains the following components: red, green, blue, alpha.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#Color.
- Border
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: style, width, color.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#Border.
-
Uses template:
- TextFormat
-
Field components:
-
Uses template:
Color
. - Contains the following components: foregroundColor, fontFamily, fontSize, bold, italic, strikethrough, underline.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#TextFormat.
-
Uses template:
- CellFormat
-
Field components:
-
Uses template:
Color
. - Contains the following components: numberFormat, backgroundColor, borders, padding, horizontalAlignment, verticalAlignment, wrapStrategy, textDirection, textFormat, hyperlinkDisplayType.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#CellFormat.
-
Uses template:
- PivotGroup
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: sourceColumnOffset, showTotals, valueMetadata, sortOrder, valueBucket.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#PivotGroup.
-
Uses template:
- DimensionProperties
-
Field components:
- Contains the following components: hiddenByFilter, hiddenByUser, pixelSize.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#DimensionProperties.
- GridRange
-
Field components:
- Contains the following components: sheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#GridRange.
- BooleanCondition
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: type, values.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#BooleanCondition.
-
Uses template:
- InterpolationPoint
-
Field components:
-
Uses template:
Color
. - Contains the following components: color, type, value.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#InterpolationPoint.
-
Uses template:
- SortSpec
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: dimensionIndex, sortOrder.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#SortSpec.
-
Uses template:
- FilterCriteria
Template: LongText. A composite field.
- ChartData
-
Field components:
- Contains the following components: sourceRange.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#ChartData.
- SheetProperties
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: sheetId, title, index, sheetType, gridProperties, hidden, tabColor, rightToLeft.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#SheetProperties.
-
Uses template:
- NamedRange
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: namedRangeId, name, range.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#NamedRange.
-
Uses template:
- ProtectedRange
-
Field components:
-
Uses template:
GridRange
. - Contains the following components: protectedRangeId, range, namedRangeId, description, warningOnly, requestingUserCanEdit, unprotectedRanges, editors.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#ProtectedRange.
-
Uses template:
- ConditionalFormatRule
-
Field components:
- Contains the following components: ranges, booleanRule, gradientRule.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#ConditionalFormatRule.
- FilterView
-
Field components:
-
Uses template:
ShortText
. - Contains the following components: filterViewId, title, range, namedRangeId, sortSpecs, criteria.
Documentation: https://developers.google.com/sheets/reference/rest/v4/spreadsheets#FilterView.
-
Uses template:
Knowledge Base
- Where can I find the documentation for the Google Sheets Connection?
- How to manually generate a Google Services Token?
Samples
What's New
- New: Improved parameter entry.
- New: Support for shared drives.
- Fixed: Failed with error "Maximum number of named properties reached." when a folder contains more than 16000 files (Thank you, Dave).
- New: Updated to use new OAuth2 2.0 authorization addresses.
- Fixed: Various fixes (Thank you Karen).
- New: Introduced connection.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.