Google Sheets Source
Provided by: "Apache Software Foundation"
Support Level for this Kamelet is: "Stable"
Reads the cells of a Google Sheets spreadsheet again and again, at a regular interval, and sends each row of the range as a message.
Use it to feed the content of a spreadsheet that people maintain, such as a price list, a contact list or a list of tasks, to another system on a schedule. To write rows to a spreadsheet, use google-sheets-sink.
Output: with range set, the body is one row as JSON text, a JSON array of the values of the row (or of one column, with majorDimension set to COLUMNS), such as ["Alice","3"]; with splitResults set to false, it is the whole range as JSON text, with the rows in its values field. Without range, the body is the description of the spreadsheet (its sheets and properties) as JSON text, not its cells. The CamelGoogleSheets.spreadsheetId and CamelGoogleSheets.range headers repeat the options.
Good to know: every poll reads the whole range again, by default every 500 milliseconds, so the same rows come again and again until repeatCount is reached; it does not track which rows are new. It logs in to Google either with OAuth (an OAuth client of a Google Cloud project, and an access token and a refresh token of a Google account that may read the spreadsheet), or with the key of a service account in serviceAccountKey together with scopes; share the spreadsheet with the email address of the service account, or set delegate.
Configuration Options
The following table summarizes the configuration options available for the google-sheets-source Kamelet:
| Property | Name | Description | Type | Default | Example |
|---|---|---|---|---|---|
Spreadsheet ID | Required The ID of the spreadsheet, the long part of its address between | string | 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms | ||
Access Token | An OAuth 2 access token of the Google account, with access to Google Sheets. It expires after about an hour; the Kamelet then gets a new one with | string | |||
Application Name | A name for your application, sent to Google with each call, such as | string | my-company-sheets/1.0 | ||
Client ID | The client ID of an OAuth client, created in the Google Cloud console under APIs & Services > Credentials, in a project where the Google Sheets API is enabled. Not needed when logging in with | string | |||
Client Secret | The client secret of the OAuth client of | string | |||
Column Names | Sets the | string | A | name,qty | |
Delay | How long to wait between two reads of the spreadsheet, in milliseconds. For example, 60000 is one minute. | integer | 500 | 60000 | |
Delegate User | The email address of a user of your Google Workspace, whose spreadsheets the service account of | string | alice@example.com// | ||
Major Dimension | Whether the cells are read by rows ( Enum values: * COLUMNS * ROWS | string | ROWS | ROWS | |
Max Results | The most rows of each range to send, counted from the first, or the most columns with | integer | 100 | ||
Cells Range | The cells to read, in A1 notation, optionally with the name of the sheet, such as | string | Sheet1!A2:C100 | ||
Refresh Token | An OAuth 2 refresh token of the Google account, which the Kamelet uses to get a new access token when the current one expires. You get it together with the access token, for example from the Google OAuth 2.0 Playground. Not needed when logging in with | string | |||
Repeat Count | How many times to read the spreadsheet in total, such as 1 to read it once. When it is not set, it is read again every | integer | 1 | ||
Scopes | The permissions to ask Google for, as URLs separated by commas, such as | string | https://www.googleapis.com/auth/spreadsheets.readonly | ||
Service Account Key | The JSON key of a Google service account, to log in with instead of OAuth: | string | file:/etc/google/service-account.json | ||
Split Results | If true, each row of | boolean | true | ||
Value Render Option | How the cells are read: Enum values: * FORMATTED_VALUE * UNFORMATTED_VALUE * FORMULA | string | UNFORMATTED_VALUE |
Dependencies
At runtime, the google-sheets-source Kamelet relies upon the presence of the following dependencies:
-
camel:jackson3
-
camel:kamelet
-
camel:google-sheets
Camel JBang usage
Prerequisites
-
You’ve installed JBang.
-
You have executed the following command:
jbang app install camel@apache/camel Supposing you have a file named route.yaml with this content:
- route:
from:
uri: "kamelet:google-sheets-source"
parameters:
.
.
.
steps:
- to:
uri: "kamelet:log-sink" You can now run it directly through the following command
camel run route.yaml Google Sheets Source Kamelet Description
Authentication methods
The Kamelet logs in to Google in one of three ways:
-
With OAuth 2.0:
clientIdandclientSecretof an OAuth client from your Google Cloud Console application, anaccessToken(it expires after about an hour) and arefreshTokento get new ones. Do not setscopeswith OAuth. -
With a service account: its JSON key in
serviceAccountKey, asfile:<path>,classpath:<resource>orbase64:<Base64 of the key file>, andscopes, such ashttps://www.googleapis.com/auth/spreadsheets.readonly. Share the spreadsheet with the email address of the service account, or setdelegateto the email address of a user of your Google Workspace; a Workspace administrator must first allow the client ID of the service account, with these scopes, under Security > Access and data control > API controls > Domain-wide delegation. -
With none of them set, the Application Default Credentials and
scopes: the key file named in theGOOGLE_APPLICATION_CREDENTIALSenvironment variable, or the service account of the Google Cloud machine it runs on.
Output format
The Kamelet produces spreadsheet data in JSON format from the specified Google Sheets document.
Configuration
The Kamelet requires the following parameter:
-
spreadsheetId: The Spreadsheet ID to be used as events source
To log in with OAuth: clientId, clientSecret, accessToken and refreshToken. To log in with a service account: serviceAccountKey, scopes and optionally delegate.
Optional parameters about the cells:
-
range: The cells to read, such asSheet1!A2:C100 -
valueRenderOption:FORMATTED_VALUE(as shown, such as$1.50, the default),UNFORMATTED_VALUE(numbers as numbers, such as1.5) orFORMULA(the formula, such as=B2*2) -
maxResults: The most rows of each range to send, counted from the first; Google still returns the whole range
Usage example
apiVersion: camel.apache.org/v1alpha1
kind: KameletBinding
metadata:
name: google-sheets-source-binding
spec:
source:
ref:
kind: Kamelet
apiVersion: camel.apache.org/v1alpha1
name: google-sheets-source
properties:
spreadsheetId: "1BxEBc3z8Zm4x7_5uW9QdQpVmW8QFvOoE"
clientId: "{{client-id}}"
clientSecret: "{{client-secret}}"
accessToken: "{{access-token}}"
refreshToken: "{{refresh-token}}"
sink:
ref:
kind: Service
apiVersion: v1
name: my-service Example with a Service Account
Reads the first 100 rows of Prices!A2:C once, with numbers as numbers, with the key of a service account the spreadsheet is shared with.
- route:
from:
uri: "kamelet:google-sheets-source"
parameters:
spreadsheetId: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
serviceAccountKey: "file:/etc/google/service-account.json"
scopes: "https://www.googleapis.com/auth/spreadsheets.readonly"
range: "Prices!A2:C"
valueRenderOption: "UNFORMATTED_VALUE"
maxResults: 100
repeatCount: 1
steps:
- to:
uri: "kamelet:log-sink"