This is an automated email from the ASF dual-hosted git repository.
cdeppisch pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/camel-kamelets.git
The following commit(s) were added to refs/heads/main by this push:
new 413ed9bd fix(#1653): Add Google Sheets Sink Kamelet
413ed9bd is described below
commit 413ed9bd5581dbd194927aef3688566abdab285c
Author: Christoph Deppisch <cdeppi...@redhat.com>
AuthorDate: Tue Sep 19 18:18:19 2023 +0200
fix(#1653): Add Google Sheets Sink Kamelet
- Add google-sheets-sink Kamelet that updates/appends range cell data to a
spreadsheet
- Add GoogleSheetsJsonStructDataType that supports transforming generic
JsonNode structs from/to ValueRange object
- Add generic json struct data type support to google-sheets-source Kamelet
to transform Google Sheets ValueRange object to pure row/column values
- Adjust google-sheets-source Kamelet to support custom columnNames and
majorDimension setting
- Expose data type information on google-sheets-source and -sink Kamelets
---
kamelets/google-sheets-sink.kamelet.yaml | 188 +++++++++
kamelets/google-sheets-source.kamelet.yaml | 79 +++-
library/camel-kamelets-utils/pom.xml | 18 +
.../converter/google/sheets/CellCoordinate.java | 195 ++++++++++
.../sheets/GoogleSheetsJsonStructDataType.java | 337 ++++++++++++++++
.../converter/google/sheets/RangeCoordinate.java | 149 +++++++
.../kamelets/utils/format/converter/json/Json.java | 78 ++++
.../transformer/google-sheets-application-x-struct | 18 +
.../sheets/GoogleSheetsJsonStructDataTypeTest.java | 429 +++++++++++++++++++++
.../kamelets/google-sheets-sink.kamelet.yaml | 188 +++++++++
.../kamelets/google-sheets-source.kamelet.yaml | 79 +++-
11 files changed, 1748 insertions(+), 10 deletions(-)
diff --git a/kamelets/google-sheets-sink.kamelet.yaml
b/kamelets/google-sheets-sink.kamelet.yaml
new file mode 100644
index 00000000..9adfdf07
--- /dev/null
+++ b/kamelets/google-sheets-sink.kamelet.yaml
@@ -0,0 +1,188 @@
+# ---------------------------------------------------------------------------
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+# ---------------------------------------------------------------------------
+apiVersion: camel.apache.org/v1
+kind: Kamelet
+metadata:
+ name: google-sheets-sink
+ annotations:
+ camel.apache.org/kamelet.support.level: "Stable"
+ camel.apache.org/catalog.version: "4.1.0-SNAPSHOT"
+ camel.apache.org/kamelet.icon:
"data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4KPCEtLSBHZW5lcmF0b3I6IEFkb2JlIElsbHVzdHJhdG9yIDI1LjAuMCwgU1ZHIEV4cG9ydCBQbHVnLUluIC4gU1ZHIFZlcnNpb246IDYuMDAgQnVpbGQgMCkgIC0tPgo8c3ZnIHZlcnNpb249IjEuMSIgaWQ9IkxheWVyXzEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgeG1sbnM6eGxpbms9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkveGxpbmsiIHg9IjBweCIgeT0iMHB4IgoJIHZpZXdCb3g9IjAgMCA2NCA4OCIgc3R5bGU9ImVuYWJsZS1iYWNrZ3JvdW5kOm5ldyAwIDAgNjQgODg7Ii
[...]
+ camel.apache.org/provider: "Apache Software Foundation"
+ camel.apache.org/kamelet.group: "Google Sheets"
+ camel.apache.org/kamelet.namespace: "GCP"
+ labels:
+ camel.apache.org/kamelet.type: "sink"
+spec:
+ definition:
+ title: "Google Sheets Sink"
+ description: |-
+ Send data to Google Sheets and update/append values on a spreadsheet.
+ required:
+ - spreadsheetId
+ - clientId
+ - accessToken
+ - refreshToken
+ - clientSecret
+ type: object
+ properties:
+ spreadsheetId:
+ title: Spreadsheet ID
+ description: The Spreadsheet ID to be used as identifier
+ type: string
+ clientId:
+ title: Client Id
+ description: Client ID of the sheets application
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ clientSecret:
+ title: Client Secret
+ description: Client Secret of the sheets application
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ accessToken:
+ title: Access Token
+ description: OAuth 2 access token for google sheets application. This
typically expires after an hour so refreshToken is recommended for long term
usage.
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ refreshToken:
+ title: Refresh Token
+ description: OAuth 2 refresh token for google sheets application.
Using this, the Google Calendar component can obtain a new accessToken whenever
the current one expires - a necessity if the application is long-lived.
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ applicationName:
+ title: Application Name
+ description: Google Sheets application name
+ type: string
+ operation:
+ title: Operation Mode
+ description: Operation to execute (update or append)
+ type: string
+ enum: [ "update", "append" ]
+ default: append
+ example: append
+ range:
+ title: Cells Range
+ description: The cell range of rows and columns to write data to.
+ type: string
+ example: "A1:B3"
+ majorDimension:
+ title: Major Dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ type: string
+ enum: [ "COLUMNS", "ROWS" ]
+ default: "ROWS"
+ example: "ROWS"
+ columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell coordinates
based on their position.
+ type: string
+ default: "A"
+ valueInputOption:
+ title: Value Input Option
+ description: Controls how the entered values should be be interpreted
when adding them.
+ type: string
+ enum: [ "USER_ENTERED", "RAW" ]
+ default: "USER_ENTERED"
+ example: "USER_ENTERED"
+ dataTypes:
+ in:
+ default: json-struct
+ types:
+ json-struct:
+ format: "google-sheets:application-x-struct"
+ description: |-
+ Special Json representation of Google Sheets ValueRange object
with just row and column values as a generic JsonNode.
+ Each cell value is represented by a Json property named after the
respective row (A-Z) or column (1-n) depending on the given majorDimension.
+ Custom column names are supported in order to use custom property
names instead of generic row (A-Z) or column (1-n) coordinates.
+ The given Json struct is ready to be transformed into a proper
Google Sheets ValueRange object that can be used in the update/append values
operation.
+ The data type uses a set of header entries to determine properties
such as spreadsheetId, the target cell range, the majorDimension and so on.
+ headers:
+ CamelGoogleSheets.range:
+ title: Range
+ description: Cells range to write data to.
+ default: A:A
+ type: string
+ CamelGoogleSheets.spreadsheetId:
+ title: Spreadsheet id
+ description: The Spreadsheet ID to be used as identifier.
+ type: string
+ CamelGoogleSheets.majorDimension:
+ title: Major dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ default: ROWS
+ type: string
+ CamelGoogleSheets.columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell
coordinates based on their position.
+ default: A
+ type: string
+ CamelGoogleSheets.valueInputOption:
+ title: Value Input Option
+ description: Controls how the entered values should be be
interpreted when adding them.
+ default: USER_ENTERED
+ type: string
+ mediaType: application/json
+ dependencies:
+ - "mvn:org.apache.camel.kamelets:camel-kamelets-utils:4.1.0-SNAPSHOT"
+ - "camel:core"
+ - "camel:jackson"
+ - "camel:kamelet"
+ - "camel:google-sheets"
+ template:
+ from:
+ uri: "kamelet:source"
+ steps:
+ - set-header:
+ name: CamelGoogleSheets.spreadsheetId
+ simple: "{{spreadsheetId}}"
+ - set-header:
+ name: CamelGoogleSheets.range
+ simple: "{{?range}}"
+ - set-header:
+ name: CamelGoogleSheets.majorDimension
+ simple: "{{?majorDimension}}"
+ - set-header:
+ name: CamelGoogleSheets.columnNames
+ simple: "{{?columnNames}}"
+ - set-header:
+ name: CamelGoogleSheets.valueInputOption
+ simple: "{{?valueInputOption}}"
+ - transform:
+ to-type: "google-sheets:application-x-struct"
+ - to:
+ uri: "google-sheets:data/{{operation}}"
+ parameters:
+ spreadsheetId: "{{spreadsheetId}}"
+ clientId: "{{clientId}}"
+ accessToken: "{{accessToken}}"
+ refreshToken: "{{refreshToken}}"
+ clientSecret: "{{clientSecret}}"
+ applicationName: "{{?applicationName}}"
+ range: "{{?range}}"
diff --git a/kamelets/google-sheets-source.kamelet.yaml
b/kamelets/google-sheets-source.kamelet.yaml
index 210333f7..36e4217e 100644
--- a/kamelets/google-sheets-source.kamelet.yaml
+++ b/kamelets/google-sheets-source.kamelet.yaml
@@ -91,20 +91,74 @@ spec:
type: string
splitResults:
title: Split Results
- description: True if value range result should be split into rows or
columns to process each of them individually.
+ description: True if value range result should be split into rows or
columns to process each of them individually.
type: boolean
x-descriptors:
- 'urn:alm:descriptor:com.tectonic.ui:checkbox'
default: true
range:
- title: Cells Range to get Data from
- description: the range of rows and columns in a sheet to get data from.
+ title: Cells Range
+ description: The range of rows and columns in a sheet to get data from.
type: string
example: "A1:B3"
- types:
+ majorDimension:
+ title: Major Dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ type: string
+ enum: [ "COLUMNS", "ROWS" ]
+ default: "ROWS"
+ example: "ROWS"
+ columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell coordinates
based on their position.
+ type: string
+ default: "A"
+ dataTypes:
+ default:
out:
- mediaType: application/json
+ default: json
+ headers:
+ CamelGoogleSheets.range:
+ title: Range
+ description: Cells range to write data to.
+ default: A:A
+ type: string
+ CamelGoogleSheets.spreadsheetId:
+ title: Spreadsheet id
+ description: The Spreadsheet ID to be used as identifier.
+ type: string
+ CamelGoogleSheets.majorDimension:
+ title: Major dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ default: ROWS
+ type: string
+ CamelGoogleSheets.columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell
coordinates based on their position.
+ default: A
+ type: string
+ CamelGoogleSheets.splitResults:
+ title: Split Results
+ description: True if value range result should be split into rows or
columns to process each of them individually.
+ default: "true"
+ type: boolean
+ types:
+ json:
+ format: "application-json"
+ description: |-
+ Json representation of a GoogleSheets ValueRange object that holds
all values for the given cell range.
+ Or Json array of values for a single row/column in the range when
'splitResults' mode is enabled.
+ mediaType: application/json
+ json-struct:
+ format: "google-sheets:application-x-struct"
+ description: |-
+ Special Json representation of Google Sheets ValueRange object
with just row and column values as a generic JsonNode.
+ Each cell value is represented by a Json property named after the
respective row (A-Z) or column (1-n) depending on the given majorDimension.
+ Custom column names are supported in order to use custom property
names instead of generic row (A-Z) or column (1-n) coordinates.
+ The produced Json struct is ready to be transformed back into a
proper Google Sheets ValueRange object that can be used in an update/append
values operation.
+ mediaType: application/json
dependencies:
+ - "camel:core"
- "camel:jackson"
- "camel:kamelet"
- "camel:google-sheets"
@@ -124,4 +178,19 @@ spec:
steps:
- marshal:
json: {}
+ - set-header:
+ name: CamelGoogleSheets.spreadsheetId
+ simple: "{{spreadsheetId}}"
+ - set-header:
+ name: CamelGoogleSheets.range
+ simple: "{{?range}}"
+ - set-header:
+ name: CamelGoogleSheets.majorDimension
+ simple: "{{?majorDimension}}"
+ - set-header:
+ name: CamelGoogleSheets.columnNames
+ simple: "{{?columnNames}}"
+ - set-header:
+ name: CamelGoogleSheets.splitResults
+ simple: "{{?splitResults}}"
- to: "kamelet:sink"
diff --git a/library/camel-kamelets-utils/pom.xml
b/library/camel-kamelets-utils/pom.xml
index b4f56daa..b5886188 100644
--- a/library/camel-kamelets-utils/pom.xml
+++ b/library/camel-kamelets-utils/pom.xml
@@ -97,6 +97,11 @@
<artifactId>camel-google-storage</artifactId>
<scope>provided</scope>
</dependency>
+ <dependency>
+ <groupId>org.apache.camel</groupId>
+ <artifactId>camel-google-sheets</artifactId>
+ <scope>provided</scope>
+ </dependency>
<!-- Dependencies for mongodb connection configuration -->
<dependency>
@@ -112,6 +117,12 @@
<version>${junit-jupiter-version}</version>
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.junit.jupiter</groupId>
+ <artifactId>junit-jupiter-params</artifactId>
+ <version>${junit-jupiter-version}</version>
+ <scope>test</scope>
+ </dependency>
<dependency>
<groupId>org.junit.jupiter</groupId>
@@ -120,6 +131,13 @@
<scope>test</scope>
</dependency>
+ <dependency>
+ <groupId>org.skyscreamer</groupId>
+ <artifactId>jsonassert</artifactId>
+ <version>${jsonassert-version}</version>
+ <scope>test</scope>
+ </dependency>
+
<!-- Logging -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
diff --git
a/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/CellCoordinate.java
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/CellCoordinate.java
new file mode 100644
index 00000000..60553b29
--- /dev/null
+++
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/CellCoordinate.java
@@ -0,0 +1,195 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.camel.kamelets.utils.format.converter.google.sheets;
+
+import java.util.List;
+import java.util.stream.Collectors;
+import java.util.stream.IntStream;
+
+import org.apache.camel.util.ObjectHelper;
+
+public class CellCoordinate {
+
+ private int rowIndex;
+ private int columnIndex;
+
+ /**
+ * Prevent direct instantiation
+ */
+ CellCoordinate() {
+ super();
+ }
+
+ /**
+ * Construct grid coordinate from given cell identifier representation in
A1 form. For instance convert
+ * cell id string "A1" to a coordinate with rowIndex=0, and columnIndex=0.
+ *
+ * @param cellId
+ * @return
+ */
+ public static CellCoordinate fromCellId(String cellId) {
+ CellCoordinate coordinate = new CellCoordinate();
+
+ if (cellId != null) {
+ coordinate.setRowIndex(getRowIndex(cellId));
+ coordinate.setColumnIndex(getColumnIndex(cellId));
+ }
+
+ return coordinate;
+ }
+
+ /**
+ * Evaluate the column index from cellId in A1 notation. Column name
letters are translated to numeric column index values.
+ * Column "A" will result in column index 0. Method does support columns
with combined name letters such as "AA" where this is
+ * the first column after "Z" resulting in a column index of 26.
+ *
+ * @param cellId
+ * @return
+ */
+ protected static int getColumnIndex(String cellId) {
+ char[] characters = cellId.toCharArray();
+ List<Integer> chars = IntStream.range(0, characters.length)
+ .mapToObj(i -> characters[i])
+ .filter(c -> !Character.isDigit(c))
+ .map(Character::toUpperCase)
+ .map(Character::getNumericValue)
+ .collect(Collectors.toList());
+
+ if (chars.size() > 1) {
+ int index = 0;
+ for (int i = 0; i < chars.size(); i++) {
+ if (i == chars.size() -1) {
+ index += chars.get(i) - Character.getNumericValue('A');
+ } else {
+ index += ((chars.get(i) - Character.getNumericValue('A'))
+ 1) * 26;
+ }
+ }
+ return index;
+ } else if (chars.size() == 1) {
+ return chars.get(0) - Character.getNumericValue('A');
+ } else {
+ return 0;
+ }
+ }
+
+ /**
+ * Evaluates the row index from a given cellId in A1 notation. Extracts
the row number and translates that to an numeric
+ * index value beginning with 0.
+ *
+ * @param cellId
+ * @return
+ */
+ protected static int getRowIndex(String cellId) {
+ char[] characters = cellId.toCharArray();
+ String index = IntStream.range(0, characters.length)
+ .mapToObj(i -> characters[i])
+ .filter(Character::isDigit)
+ .map(String::valueOf)
+ .collect(Collectors.joining());
+
+ if (ObjectHelper.isNotEmpty(index)) {
+ return Integer.parseInt(index) - 1;
+ }
+
+ return 0;
+ }
+
+ /**
+ * Evaluates column name in A1 notation based on the column index. Index 0
will be "A" and index 25 will be "Z". Method also supports
+ * name overflow where index 26 will be "AA" and index 51 will be "AZ" and
so on.
+ *
+ * @param columnIndex
+ * @return
+ */
+ public static String getColumnName(int columnIndex) {
+ String alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
+ StringBuilder columnName = new StringBuilder();
+
+ int index = columnIndex;
+ int overflowIndex = -1;
+ while (index > 25) {
+ overflowIndex++;
+ index -= 26;
+ }
+
+ if (overflowIndex >= 0) {
+ columnName.append(alphabet.toCharArray()[overflowIndex]);
+ }
+
+ columnName.append(alphabet.toCharArray()[index]);
+
+ return columnName.toString();
+ }
+
+ /**
+ * Special getter for column name where user is able to give set of user
defined column names. When given column index is resolvable via custom names
+ * the custom column name is returned otherwise the evaluated default
column name is returned.
+ *
+ * @param columnIndex
+ * @param columnStartIndex
+ * @param columnNames
+ * @return
+ */
+ public static String getColumnName(int columnIndex, int columnStartIndex,
String ... columnNames) {
+ String columnName = getColumnName(columnIndex);
+
+ int index;
+ if (columnStartIndex > 0) {
+ index = columnIndex % columnStartIndex;
+ } else {
+ index = columnIndex;
+ }
+
+ if (index < columnNames.length) {
+ String name = columnNames[index];
+ if (columnName.equals(name)) {
+ return columnName;
+ } else {
+ return name;
+ }
+ }
+
+ return columnName;
+ }
+
+ public int getRowIndex() {
+ return rowIndex;
+ }
+
+ /**
+ * Specifies the rowIndex.
+ *
+ * @param rowIndex
+ */
+ public void setRowIndex(int rowIndex) {
+ this.rowIndex = rowIndex;
+ }
+
+ public int getColumnIndex() {
+ return columnIndex;
+ }
+
+ /**
+ * Specifies the columnIndex.
+ *
+ * @param columnIndex
+ */
+ public void setColumnIndex(int columnIndex) {
+ this.columnIndex = columnIndex;
+ }
+}
diff --git
a/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/GoogleSheetsJsonStructDataType.java
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/GoogleSheetsJsonStructDataType.java
new file mode 100644
index 00000000..4336a10c
--- /dev/null
+++
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/GoogleSheetsJsonStructDataType.java
@@ -0,0 +1,337 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.camel.kamelets.utils.format.converter.google.sheets;
+
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Objects;
+import java.util.Optional;
+
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.google.api.services.sheets.v4.model.ValueRange;
+import org.apache.camel.CamelExecutionException;
+import org.apache.camel.InvalidPayloadException;
+import org.apache.camel.Message;
+import org.apache.camel.component.google.sheets.internal.GoogleSheetsConstants;
+import
org.apache.camel.component.google.sheets.stream.GoogleSheetsStreamConstants;
+import org.apache.camel.kamelets.utils.format.converter.json.Json;
+import org.apache.camel.spi.DataType;
+import org.apache.camel.spi.DataTypeTransformer;
+import org.apache.camel.spi.Transformer;
+import org.apache.camel.util.ObjectHelper;
+
+/**
+ * Data type supports generic JsonNode representation of Google Sheets row and
column values.
+ * Transforms generic JsonNode struct to/from a Google Sheets ValueRange
object.
+ * Supports both inbound and outbound transformation depending on the given
message body content.
+ * When Google Sheets ValueRange object is given as message body (e.g. as a
result of a get values operation) the transformer will transform into generic
Json struct.
+ * When generic Json struct is given as a message body transformer will
transform into a proper ValueRange object that is ready to be used in an
update/append values operation.
+ * Implementation also supports splitResults setting where a set of values is
split into its individual items.
+ */
+@DataTypeTransformer(name = "google-sheets:application-x-struct")
+public class GoogleSheetsJsonStructDataType extends Transformer {
+
+ private static final String ROW_PREFIX = "#";
+
+ @Override
+ public void transform(Message message, DataType fromType, DataType toType)
{
+ final Optional<ValueRange> valueRange = getValueRangeBody(message);
+
+ String range = message.getHeader(GoogleSheetsConstants.PROPERTY_PREFIX
+ "range", "A:A").toString();
+ String majorDimension =
message.getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "majorDimension",
RangeCoordinate.DIMENSION_ROWS).toString();
+ String spreadsheetId =
message.getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "spreadsheetId",
"").toString();
+ String[] columnNames =
message.getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "columnNames",
"A").toString().split(",");
+
+ boolean splitResults =
Boolean.parseBoolean(message.getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"splitResults", "false").toString());
+
+ if (valueRange.isPresent()) {
+ message.setBody(transformFromValueRangeModel(message,
valueRange.get(), spreadsheetId, range, majorDimension, columnNames));
+ } else if (splitResults) {
+ message.setBody(transformFromSplitValuesModel(message,
spreadsheetId, range, majorDimension, columnNames));
+ } else {
+ String valueInputOption =
message.getHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "valueInputOption",
"USER_ENTERED").toString();
+ message.setBody(transformToValueRangeModel(message, spreadsheetId,
range, majorDimension, valueInputOption, columnNames));
+ }
+ }
+
+ /**
+ * Constructs proper ValueRange object from given generic Json struct.
+ * @param message
+ * @param spreadsheetId
+ * @param range
+ * @param majorDimension
+ * @param valueInputOption
+ * @param columnNames
+ * @return
+ */
+ private ValueRange transformToValueRangeModel(Message message, String
spreadsheetId, String range, String majorDimension, String valueInputOption,
String[] columnNames) {
+ try {
+ List<String> jsonBeans = bodyAsJsonBeans(message);
+
+ ValueRange valueRange = new ValueRange();
+ List<List<Object>> values = new ArrayList<>();
+
+ if (ObjectHelper.isNotEmpty(jsonBeans)) {
+ final ArrayList<String> properties =
createCoordinateNameSpec(range, majorDimension, columnNames);
+
+ for (String json : jsonBeans) {
+ Map<String, Object> dataShape =
Json.MAPPER.reader().forType(Map.class).readValue(json);
+
+ if (dataShape.containsKey("spreadsheetId")) {
+ spreadsheetId =
Optional.ofNullable(dataShape.remove("spreadsheetId"))
+ .map(Object::toString)
+ .orElse(spreadsheetId);
+ }
+
+ List<Object> rangeValues = new ArrayList<>();
+ properties
+ .stream()
+ .filter(specEntry ->
!Objects.equals("spreadsheetId", specEntry))
+ .forEach(specEntry ->
rangeValues.add(dataShape.getOrDefault(specEntry, null)));
+
+ values.add(rangeValues);
+ }
+ }
+
+ valueRange.setMajorDimension(majorDimension);
+ valueRange.setValues(values);
+
+ message.setHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID,
spreadsheetId);
+ message.setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"spreadsheetId", spreadsheetId);
+ message.setHeader(GoogleSheetsStreamConstants.RANGE, range);
+ message.setHeader(GoogleSheetsConstants.PROPERTY_PREFIX + "range",
range);
+ message.setHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION,
majorDimension);
+ message.setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"majorDimension", majorDimension);
+ message.setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption", valueInputOption);
+ message.setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values", valueRange);
+
+ return valueRange;
+ } catch (InvalidPayloadException | JsonProcessingException e) {
+ throw new CamelExecutionException("Failed to apply Google Sheets
Json struct " +
+ "data type on exchange", message.getExchange(), e);
+ }
+ }
+
+ /**
+ * Construct generic Json struct from given ValueRange object.
+ * Json struct represents the row and column values only.
+ * @param message
+ * @param valueRange
+ * @param spreadsheetId
+ * @param range
+ * @param majorDimension
+ * @param columnNames
+ * @return
+ */
+ private List<String> transformFromValueRangeModel(Message message,
ValueRange valueRange, String spreadsheetId, String range, String
majorDimension, String[] columnNames) {
+ final List<String> jsonBeans = new ArrayList<>();
+
+ try {
+ if (valueRange != null) {
+ if (ObjectHelper.isNotEmpty(valueRange.getRange())) {
+ range = valueRange.getRange();
+ }
+ RangeCoordinate rangeCoordinate =
RangeCoordinate.fromRange(range);
+
+ if (ObjectHelper.isNotEmpty(valueRange.getMajorDimension())) {
+ majorDimension = valueRange.getMajorDimension();
+ }
+
+ if (ObjectHelper.equal(RangeCoordinate.DIMENSION_ROWS,
majorDimension)) {
+ for (List<Object> values : valueRange.getValues()) {
+ final Map<String, Object> model = new HashMap<>();
+ model.put("spreadsheetId", spreadsheetId);
+ int columnIndex =
rangeCoordinate.getColumnStartIndex();
+ for (Object value : values) {
+
model.put(CellCoordinate.getColumnName(columnIndex,
rangeCoordinate.getColumnStartIndex(), columnNames), value);
+ columnIndex++;
+ }
+
jsonBeans.add(Json.MAPPER.writer().writeValueAsString(model));
+ }
+ } else if
(ObjectHelper.equal(RangeCoordinate.DIMENSION_COLUMNS, majorDimension)) {
+ for (List<Object> values : valueRange.getValues()) {
+ final Map<String, Object> model = new HashMap<>();
+ model.put("spreadsheetId", spreadsheetId);
+ int rowIndex = rangeCoordinate.getRowStartIndex() + 1;
+ for (Object value : values) {
+ model.put(ROW_PREFIX + rowIndex, value);
+ rowIndex++;
+ }
+
jsonBeans.add(Json.MAPPER.writer().writeValueAsString(model));
+ }
+ }
+ }
+
+ } catch (IOException e) {
+ throw new CamelExecutionException("Failed to apply Google Sheets
Json struct " +
+ "data type on exchange", message.getExchange(), e);
+ }
+
+ return jsonBeans;
+ }
+
+ /**
+ * Construct generic Json struct from given split values model.
+ * Json struct represents the row and column values only.
+ * In split mode one single row/column is handled as an individual result.
+ * @param message
+ * @param spreadsheetId
+ * @param range
+ * @param majorDimension
+ * @param columnNames
+ * @return
+ */
+ private String transformFromSplitValuesModel(Message message, String
spreadsheetId, String range, String majorDimension, String[] columnNames) {
+ try {
+ final List<?> values = bodyAsJsonBeans(message);
+
+ final Map<String, Object> model = new HashMap<>();
+ model.put("spreadsheetId", spreadsheetId);
+
+ if (values != null) {
+ if
(ObjectHelper.isNotEmpty(message.getHeader(GoogleSheetsStreamConstants.RANGE)))
{
+ range =
message.getHeader(GoogleSheetsStreamConstants.RANGE).toString();
+ }
+ RangeCoordinate rangeCoordinate =
RangeCoordinate.fromRange(range);
+
+ if
(ObjectHelper.isNotEmpty(message.getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION)))
{
+ majorDimension =
message.getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION).toString();
+ }
+
+ if (ObjectHelper.equal(RangeCoordinate.DIMENSION_ROWS,
majorDimension)) {
+ int columnIndex = rangeCoordinate.getColumnStartIndex();
+ for (Object value : values) {
+ model.put(CellCoordinate.getColumnName(columnIndex,
rangeCoordinate.getColumnStartIndex(), columnNames), value);
+ columnIndex++;
+ }
+ } else if
(ObjectHelper.equal(RangeCoordinate.DIMENSION_COLUMNS, majorDimension)) {
+ int rowIndex = rangeCoordinate.getRowStartIndex() + 1;
+ for (Object value : values) {
+ model.put(ROW_PREFIX + rowIndex, value);
+ rowIndex++;
+ }
+ }
+ }
+
+ return Json.MAPPER.writer().writeValueAsString(model);
+ } catch (InvalidPayloadException | JsonProcessingException e) {
+ throw new CamelExecutionException("Failed to apply Google Sheets
Json struct " +
+ "data type on exchange", message.getExchange(), e);
+ }
+ }
+
+ /**
+ * Try to convert message body to a ValueRange object if possible.
+ * Returns empty optional when message body conversion is not applicable.
+ * @param message
+ * @return
+ */
+ private static Optional<ValueRange> getValueRangeBody(Message message) {
+ if (message.getBody() instanceof ValueRange) {
+ return Optional.of(message.getBody(ValueRange.class));
+ }
+
+ String jsonBody = message.getBody(String.class);
+ if (jsonBody != null) {
+ try {
+ ValueRange valueRange =
Json.MAPPER.reader().readValue(jsonBody, ValueRange.class);
+ return valueRange.getValues() != null ?
Optional.of(valueRange) : Optional.empty();
+ } catch (IOException e) {
+ return Optional.empty();
+ }
+ }
+
+ return Optional.empty();
+ }
+
+ /**
+ * Converts message body to list of Json objects. Supports different
message body types such as
+ * List, String, InputStream.
+ * @param message
+ * @return
+ * @throws JsonProcessingException
+ */
+ private static List<String> bodyAsJsonBeans(Message message) throws
JsonProcessingException, InvalidPayloadException {
+ if (message.getBody() == null) {
+ return Collections.emptyList();
+ }
+
+ if (message.getBody() instanceof List) {
+ return message.getBody(List.class);
+ }
+
+ String body = message.getMandatoryBody(String.class);
+ if (Json.isJsonArray(body)) {
+ return Json.arrayToJsonBeans(Json.MAPPER.reader().readTree(body));
+ } else if (Json.isJson(body)) {
+ return Collections.singletonList(body);
+ }
+
+ return Collections.emptyList();
+ }
+
+ /**
+ * Construct row and column coordinate names for given range.
+ * Supports mapping of custom column names to proper row/column
coordinates.
+ * @param range
+ * @param majorDimension
+ * @param columnNames
+ * @return
+ */
+ public static ArrayList<String> createCoordinateNameSpec(String range,
String majorDimension, String ... columnNames) {
+ ArrayList<String> names = new ArrayList<>();
+
+ RangeCoordinate coordinate = RangeCoordinate.fromRange(range);
+ if (ObjectHelper.equal(RangeCoordinate.DIMENSION_ROWS,
majorDimension)) {
+ createSchemaFromRowDimension(names, coordinate, columnNames);
+ } else if (ObjectHelper.equal(RangeCoordinate.DIMENSION_COLUMNS,
majorDimension)) {
+ createSchemaFromColumnDimension(names, coordinate);
+ }
+
+ return names;
+ }
+
+ /**
+ * Create dynamic json schema from row dimension. If split only a single
object "ROW" holding 1-n column values is
+ * created. Otherwise, each row results in a separate object with 1-n
column values as property.
+ * @param properties
+ * @param coordinate
+ * @param columnNames
+ */
+ private static void createSchemaFromRowDimension(ArrayList<String>
properties, RangeCoordinate coordinate, String ... columnNames) {
+ for (int i = coordinate.getColumnStartIndex(); i <
coordinate.getColumnEndIndex(); i++) {
+ properties.add(CellCoordinate.getColumnName(i,
coordinate.getColumnStartIndex(), columnNames));
+ }
+ }
+
+ /**
+ * Create dynamic json schema from column dimension. If split only a
single object "COLUMN" holding 1-n row values is
+ * created. Otherwise, each column results in a separate object with 1-n
row values as property.
+ * @param properties
+ * @param coordinate
+ */
+ private static void createSchemaFromColumnDimension(ArrayList<String>
properties, RangeCoordinate coordinate) {
+ for (int i = coordinate.getRowStartIndex() + 1; i <=
coordinate.getRowEndIndex(); i++) {
+ properties.add("#" + i);
+ }
+ }
+}
diff --git
a/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/RangeCoordinate.java
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/RangeCoordinate.java
new file mode 100644
index 00000000..403e84e1
--- /dev/null
+++
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/RangeCoordinate.java
@@ -0,0 +1,149 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.camel.kamelets.utils.format.converter.google.sheets;
+
+import java.util.StringJoiner;
+
+public final class RangeCoordinate extends CellCoordinate {
+
+ public static final String DIMENSION_ROWS = "ROWS";
+ public static final String DIMENSION_COLUMNS = "COLUMNS";
+
+ private int rowStartIndex;
+ private int rowEndIndex;
+
+ private int columnStartIndex;
+ private int columnEndIndex;
+
+ /**
+ * Prevent direct instantiation
+ */
+ private RangeCoordinate() {
+ super();
+ }
+
+ /**
+ * Construct range coordinates from range string representation in A1
form. For instance convert
+ * range string "A1:C2" to a coordinate with rowStartIndex=1,
rowEndIndex=2, columnStartIndex=1, columnEndIndex=3.
+ *
+ * Supports missing range ends with "A5" resulting in rowStartIndex=5,
rowEndIndex=6, columnStartIndex=1, columnEndIndex=2.
+ * @param range
+ * @return
+ */
+ public static RangeCoordinate fromRange(String range) {
+ RangeCoordinate coordinate = new RangeCoordinate();
+
+ String rangeExpression = normalizeRange(range);
+
+ if (rangeExpression.contains(":")) {
+ String[] coordinates = rangeExpression.split(":", -1);
+
+ coordinate.setRowStartIndex(getRowIndex(coordinates[0]));
+ coordinate.setColumnStartIndex(getColumnIndex(coordinates[0]));
+ coordinate.setRowEndIndex(getRowIndex(coordinates[1]) + 1);
+ coordinate.setColumnEndIndex(getColumnIndex(coordinates[1]) + 1);
+ } else {
+ CellCoordinate cellCoordinate =
CellCoordinate.fromCellId(rangeExpression);
+ coordinate.setRowIndex(cellCoordinate.getRowIndex());
+ coordinate.setColumnIndex(cellCoordinate.getColumnIndex());
+ coordinate.setRowStartIndex(cellCoordinate.getRowIndex());
+ coordinate.setColumnStartIndex(cellCoordinate.getColumnIndex());
+ coordinate.setRowEndIndex(cellCoordinate.getRowIndex() + 1);
+ coordinate.setColumnEndIndex(cellCoordinate.getColumnIndex() + 1);
+ }
+
+ return coordinate;
+ }
+
+ /**
+ * Removes optional sheet name from range expression if any.
+ * @param range
+ * @return
+ */
+ private static String normalizeRange(String range) {
+ if (range.contains("!")) {
+ return range.substring(range.indexOf('!') + 1);
+ } else {
+ return range;
+ }
+ }
+
+ /**
+ * Get all names of columns included in this range.
+ * @return
+ */
+ public String getColumnNames() {
+ StringJoiner delimitedList = new StringJoiner(",");
+ for (int i = columnStartIndex; i < columnEndIndex; i++) {
+ delimitedList.add(CellCoordinate.getColumnName(i));
+ }
+ return delimitedList.toString();
+ }
+
+ public int getRowStartIndex() {
+ return rowStartIndex;
+ }
+
+ /**
+ * Specifies the rowStartIndex.
+ *
+ * @param rowStartIndex
+ */
+ public void setRowStartIndex(int rowStartIndex) {
+ this.rowStartIndex = rowStartIndex;
+ }
+
+ public int getRowEndIndex() {
+ return rowEndIndex;
+ }
+
+ /**
+ * Specifies the rowEndIndex.
+ *
+ * @param rowEndIndex
+ */
+ public void setRowEndIndex(int rowEndIndex) {
+ this.rowEndIndex = rowEndIndex;
+ }
+
+ public int getColumnStartIndex() {
+ return columnStartIndex;
+ }
+
+ /**
+ * Specifies the columnStartIndex.
+ *
+ * @param columnStartIndex
+ */
+ public void setColumnStartIndex(int columnStartIndex) {
+ this.columnStartIndex = columnStartIndex;
+ }
+
+ public int getColumnEndIndex() {
+ return columnEndIndex;
+ }
+
+ /**
+ * Specifies the columnEndIndex.
+ *
+ * @param columnEndIndex
+ */
+ public void setColumnEndIndex(int columnEndIndex) {
+ this.columnEndIndex = columnEndIndex;
+ }
+}
diff --git
a/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/json/Json.java
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/json/Json.java
index cbcbe57c..b1ce762d 100644
---
a/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/json/Json.java
+++
b/library/camel-kamelets-utils/src/main/java/org/apache/camel/kamelets/utils/format/converter/json/Json.java
@@ -17,7 +17,15 @@
package org.apache.camel.kamelets.utils.format.converter.json;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+
+import com.fasterxml.jackson.core.JsonProcessingException;
+import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
+import com.fasterxml.jackson.databind.node.TextNode;
+import org.apache.camel.util.StringHelper;
public final class Json {
@@ -26,4 +34,74 @@ public final class Json {
private Json() {
// prevent instantiation of utility class
}
+
+ /**
+ * Checks given value to be a Json array of object representation.
+ * @param value
+ * @return
+ */
+ public static boolean isJson(String value) {
+ if (value == null) {
+ return false;
+ }
+
+ return isJsonObject(value) || isJsonArray(value);
+ }
+
+ /**
+ * Checks given value could be JSON object string.
+ * @param value
+ * @return
+ */
+ public static boolean isJsonObject(String value) {
+ if (value == null || value.isEmpty() || value.isBlank()) {
+ return false;
+ }
+
+ final String trimmed = value.trim();
+
+ return trimmed.charAt(0) == '{' && trimmed.charAt(trimmed.length() -
1) == '}';
+ }
+
+ /**
+ * Checks given value could be JSON array string.
+ * @param value
+ * @return
+ */
+ public static boolean isJsonArray(String value) {
+ if (value == null || value.isEmpty() || value.isBlank()) {
+ return false;
+ }
+
+ final String trimmed = value.trim();
+
+ return trimmed.charAt(0) == '[' && trimmed.charAt(trimmed.length() -
1) == ']';
+ }
+
+ /**
+ * Converts array json node to a list of json object strings. Used when
splitting a
+ * json array with split EIP.
+ * @param json
+ * @return
+ * @throws JsonProcessingException
+ */
+ public static List<String> arrayToJsonBeans(JsonNode json) throws
JsonProcessingException {
+ List<String> jsonBeans = new ArrayList<>();
+
+ if (json.isArray()) {
+ Iterator<JsonNode> it = json.elements();
+ while (it.hasNext()) {
+ Object item = it.next();
+ if (item instanceof TextNode) {
+
jsonBeans.add(StringHelper.removeLeadingAndEndingQuotes(((TextNode)
item).asText()));
+ } else {
+ jsonBeans.add(MAPPER.writeValueAsString(item));
+ }
+ }
+
+ return jsonBeans;
+ }
+
+ return jsonBeans;
+ }
}
diff --git
a/library/camel-kamelets-utils/src/main/resources/META-INF/services/org/apache/camel/datatype/transformer/google-sheets-application-x-struct
b/library/camel-kamelets-utils/src/main/resources/META-INF/services/org/apache/camel/datatype/transformer/google-sheets-application-x-struct
new file mode 100644
index 00000000..0af4a3fe
--- /dev/null
+++
b/library/camel-kamelets-utils/src/main/resources/META-INF/services/org/apache/camel/datatype/transformer/google-sheets-application-x-struct
@@ -0,0 +1,18 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+class=org.apache.camel.kamelets.utils.format.converter.google.sheets.GoogleSheetsJsonStructDataType
diff --git
a/library/camel-kamelets-utils/src/test/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/GoogleSheetsJsonStructDataTypeTest.java
b/library/camel-kamelets-utils/src/test/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/GoogleSheetsJsonStructDataTypeTest.java
new file mode 100644
index 00000000..3ceba8aa
--- /dev/null
+++
b/library/camel-kamelets-utils/src/test/java/org/apache/camel/kamelets/utils/format/converter/google/sheets/GoogleSheetsJsonStructDataTypeTest.java
@@ -0,0 +1,429 @@
+package org.apache.camel.kamelets.utils.format.converter.google.sheets;
+
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.Iterator;
+import java.util.List;
+import java.util.UUID;
+import java.util.stream.Stream;
+
+import com.google.api.services.sheets.v4.model.ValueRange;
+import org.apache.camel.Exchange;
+import org.apache.camel.component.google.sheets.internal.GoogleSheetsConstants;
+import
org.apache.camel.component.google.sheets.stream.GoogleSheetsStreamConstants;
+import org.apache.camel.impl.DefaultCamelContext;
+import org.apache.camel.spi.DataType;
+import org.apache.camel.support.DefaultExchange;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
+import org.skyscreamer.jsonassert.JSONAssert;
+import org.skyscreamer.jsonassert.JSONCompareMode;
+
+public class GoogleSheetsJsonStructDataTypeTest {
+
+ private final GoogleSheetsJsonStructDataType dataType = new
GoogleSheetsJsonStructDataType();
+ private DefaultCamelContext camelContext;
+
+ private String spreadsheetId;
+
+ @BeforeEach
+ void setup() {
+ this.camelContext = new DefaultCamelContext();
+ this.spreadsheetId = UUID.randomUUID().toString();
+ }
+
+ public static Stream<Arguments> transformFromSplitValuesData() {
+ return Stream.of(
+ Arguments.of("A1", "Sheet1", RangeCoordinate.DIMENSION_ROWS,
"A", Collections.singletonList("a1"),
+ "{\"spreadsheetId\":\"%s\", \"A\":\"a1\"}"),
+ Arguments.of("A1:A5", "Sheet1",
RangeCoordinate.DIMENSION_COLUMNS, "A", Arrays.asList("a1", "a2", "a3", "a4",
"a5"),
+ "{\"spreadsheetId\":\"%s\",
\"#1\":\"a1\",\"#2\":\"a2\",\"#3\":\"a3\",\"#4\":\"a4\",\"#5\":\"a5\"}"),
+ Arguments.of("A1:B2", "Sheet1",
RangeCoordinate.DIMENSION_ROWS, "A", Arrays.asList("a1", "b1"),
+ "{\"spreadsheetId\":\"%s\",
\"A\":\"a1\",\"B\":\"b1\"}"),
+ Arguments.of("A1:B2", "Sheet1",
RangeCoordinate.DIMENSION_ROWS, "Foo,Bar", Arrays.asList("a1", "b1"),
+ "{\"spreadsheetId\":\"%s\",
\"Foo\":\"a1\",\"Bar\":\"b1\"}"),
+ Arguments.of("A1:B2", "Sheet1",
RangeCoordinate.DIMENSION_COLUMNS, "A", Arrays.asList("a1", "a2"),
+ "{\"spreadsheetId\":\"%s\",
\"#1\":\"a1\",\"#2\":\"a2\"}")
+ );
+ }
+
+ public static Stream<Arguments> transformFromValueRangeData() {
+ return Stream.of(
+ Arguments.of("A1:A5", "Sheet1",
RangeCoordinate.DIMENSION_ROWS, "A",
+ Arrays.asList(Collections.singletonList("a1"),
+ Collections.singletonList("a2"),
+ Collections.singletonList("a3"),
+ Collections.singletonList("a4"),
+ Collections.singletonList("a5")),
+ Arrays.asList("{\"spreadsheetId\":\"%s\",
\"A\":\"a1\"}",
+ "{\"spreadsheetId\":\"%s\", \"A\":\"a2\"}",
+ "{\"spreadsheetId\":\"%s\", \"A\":\"a3\"}",
+ "{\"spreadsheetId\":\"%s\", \"A\":\"a4\"}",
+ "{\"spreadsheetId\":\"%s\", \"A\":\"a5\"}")),
+ Arguments.of("A1:A5", "Sheet1",
RangeCoordinate.DIMENSION_ROWS, "Foo",
+ Arrays.asList(Collections.singletonList("a1"),
+ Collections.singletonList("a2"),
+ Collections.singletonList("a3"),
+ Collections.singletonList("a4"),
+ Collections.singletonList("a5")),
+ Arrays.asList("{\"spreadsheetId\":\"%s\",
\"Foo\":\"a1\"}",
+ "{\"spreadsheetId\":\"%s\", \"Foo\":\"a2\"}",
+ "{\"spreadsheetId\":\"%s\", \"Foo\":\"a3\"}",
+ "{\"spreadsheetId\":\"%s\", \"Foo\":\"a4\"}",
+ "{\"spreadsheetId\":\"%s\", \"Foo\":\"a5\"}")),
+ Arguments.of("A1:A5", "Sheet1",
RangeCoordinate.DIMENSION_COLUMNS, "A",
+ Collections.singletonList(Arrays.asList("a1", "a2",
"a3", "a4", "a5")),
+ Collections.singletonList("{\"spreadsheetId\":\"%s\",
\"#1\":\"a1\",\"#2\":\"a2\",\"#3\":\"a3\",\"#4\":\"a4\",\"#5\":\"a5\"}")),
+ Arguments.of("A1:B2", "Sheet1",
RangeCoordinate.DIMENSION_ROWS, "A",
+ Arrays.asList(Arrays.asList("a1", "b1"),
Arrays.asList("a2", "b2")),
+ Arrays.asList("{\"spreadsheetId\":\"%s\",
\"A\":\"a1\",\"B\":\"b1\"}",
+ "{\"spreadsheetId\":\"%s\",
\"A\":\"a2\",\"B\":\"b2\"}")),
+ Arguments.of("A1:B2", "Sheet1",
RangeCoordinate.DIMENSION_ROWS, "Foo,Bar",
+ Arrays.asList(Arrays.asList("a1", "b1"),
Arrays.asList("a2", "b2")),
+ Arrays.asList("{\"spreadsheetId\":\"%s\",
\"Foo\":\"a1\",\"Bar\":\"b1\"}",
+ "{\"spreadsheetId\":\"%s\",
\"Foo\":\"a2\",\"Bar\":\"b2\"}")),
+ Arguments.of("A1:B2", "Sheet1",
RangeCoordinate.DIMENSION_COLUMNS, "A",
+ Arrays.asList(Arrays.asList("a1", "a2"),
Arrays.asList("b1", "b2")),
+ Arrays.asList("{\"spreadsheetId\":\"%s\",
\"#1\":\"a1\",\"#2\":\"a2\"}",
+ "{\"spreadsheetId\":\"%s\",
\"#1\":\"b1\",\"#2\":\"b2\"}"))
+ );
+ }
+
+ @ParameterizedTest
+ @MethodSource("transformFromSplitValuesData")
+ public void testTransformFromSplitValues(String range, String sheetName,
String majorDimension, String columnNames,
+ List<List<Object>> values, String
expectedValueModel) throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+
+ inbound.getMessage().setBody(values);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"splitResults", true);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"spreadsheetId", spreadsheetId);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", sheetName + "!" + range);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"majorDimension", majorDimension);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"columnNames", columnNames);
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+
+ String model = inbound.getMessage().getBody(String.class);
+ JSONAssert.assertEquals(String.format(expectedValueModel,
spreadsheetId), model, JSONCompareMode.STRICT);
+ }
+
+ @ParameterizedTest
+ @MethodSource("transformFromValueRangeData")
+ public void testTransformFromValueRange(String range, String sheetName,
String majorDimension, String columnNames,
+ List<List<Object>> values, List<String>
expectedValueModel) throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+
+ ValueRange valueRange = new ValueRange();
+ valueRange.setRange(sheetName + "!" + range);
+ valueRange.setMajorDimension(majorDimension);
+ valueRange.setValues(values);
+
+ inbound.getMessage().setBody(valueRange);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"spreadsheetId", spreadsheetId);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"columnNames", columnNames);
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ @SuppressWarnings("unchecked")
+ List<String> model = inbound.getMessage().getBody(List.class);
+ Assertions.assertEquals(expectedValueModel.size(), model.size());
+ Iterator<String> modelIterator = model.iterator();
+ for (String expected : expectedValueModel) {
+ JSONAssert.assertEquals(String.format(expected, spreadsheetId),
modelIterator.next(), JSONCompareMode.STRICT);
+ }
+ }
+
+ @Test
+ public void testTransformToEmptyValueRange() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"spreadsheetId", spreadsheetId);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1");
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption", "RAW");
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals(spreadsheetId,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
+ Assertions.assertEquals("A1",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("RAW",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(0L, valueRange.getValues().size());
+ }
+
+ @Test
+ public void testTransformToValueRangeRowDimension() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:B1");
+
+ String model = "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a1\"," +
+ "\"B\": \"b1\"" +
+ "}";
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals(spreadsheetId,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
+ Assertions.assertEquals("A1:B1",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(1L, valueRange.getValues().size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("b1", valueRange.getValues().get(0).get(1));
+ }
+
+ @Test
+ public void testTransformToValueRangeColumnNames() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:B1");
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"columnNames", "Foo,Bar");
+
+ String model = "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"Foo\": \"a1\"," +
+ "\"Bar\": \"b1\"" +
+ "}";
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals(spreadsheetId,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
+ Assertions.assertEquals("A1:B1",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(1L, valueRange.getValues().size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("b1", valueRange.getValues().get(0).get(1));
+ }
+
+ @Test
+ public void testTransformToValueRangeColumnDimension() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:A2");
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"majorDimension", RangeCoordinate.DIMENSION_COLUMNS);
+
+ String model = "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"#1\": \"a1\"," +
+ "\"#2\": \"a2\"" +
+ "}";
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals(spreadsheetId,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.SPREADSHEET_ID));
+ Assertions.assertEquals("A1:A2",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_COLUMNS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(1L, valueRange.getValues().size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("a2", valueRange.getValues().get(0).get(1));
+ }
+
+ @Test
+ public void testTransformToValueRangeMultipleRows() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:B2");
+
+ List<String> model = Arrays.asList("{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a1\"," +
+ "\"B\": \"b1\"" +
+ "}",
+ "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a2\"," +
+ "\"B\": \"b2\"" +
+ "}");
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals("A1:B2",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(2L, valueRange.getValues().size());
+ Assertions.assertEquals(2L, valueRange.getValues().get(0).size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("b1", valueRange.getValues().get(0).get(1));
+ Assertions.assertEquals(2L, valueRange.getValues().get(1).size());
+ Assertions.assertEquals("a2", valueRange.getValues().get(1).get(0));
+ Assertions.assertEquals("b2", valueRange.getValues().get(1).get(1));
+ }
+
+ @Test
+ public void testTransformToValueRangeMultipleColumns() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:B2");
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"majorDimension", RangeCoordinate.DIMENSION_COLUMNS);
+
+ List<String> model = Arrays.asList("{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"#1\": \"a1\"," +
+ "\"#2\": \"a2\"" +
+ "}",
+ "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"#1\": \"b1\"," +
+ "\"#2\": \"b2\"" +
+ "}");
+
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals("A1:B2",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_COLUMNS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(2L, valueRange.getValues().size());
+ Assertions.assertEquals(2L, valueRange.getValues().get(0).size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("a2", valueRange.getValues().get(0).get(1));
+ Assertions.assertEquals(2L, valueRange.getValues().get(1).size());
+ Assertions.assertEquals("b1", valueRange.getValues().get(1).get(0));
+ Assertions.assertEquals("b2", valueRange.getValues().get(1).get(1));
+ }
+
+ @Test
+ public void testTransformToValueRangeAutoFillColumnValues() throws
Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:C2");
+
+ List<String> model = Arrays.asList("{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a1\"," +
+ "\"C\": \"c1\"" +
+ "}",
+ "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a2\"," +
+ "\"B\": \"b2\"" +
+ "}");
+
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals("A1:C2",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(2L, valueRange.getValues().size());
+ Assertions.assertEquals(3L, valueRange.getValues().get(0).size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertNull(valueRange.getValues().get(0).get(1));
+ Assertions.assertEquals("c1", valueRange.getValues().get(0).get(2));
+ Assertions.assertEquals(3L, valueRange.getValues().get(1).size());
+ Assertions.assertEquals("a2", valueRange.getValues().get(1).get(0));
+ Assertions.assertEquals("b2", valueRange.getValues().get(1).get(1));
+ Assertions.assertNull(valueRange.getValues().get(1).get(2));
+ }
+
+ @Test
+ public void testTransformToValueRangeAutoFillRowValues() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:C3");
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"majorDimension", RangeCoordinate.DIMENSION_COLUMNS);
+
+ List<String> model = Arrays.asList("{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"#1\": \"a1\"," +
+ "\"#3\": \"c1\"" +
+ "}",
+ "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"#1\": \"a2\"," +
+ "\"#2\": \"b2\"" +
+ "}");
+
+ inbound.getMessage().setBody(model);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals("A1:C3",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_COLUMNS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(2L, valueRange.getValues().size());
+ Assertions.assertEquals(3L, valueRange.getValues().get(0).size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertNull(valueRange.getValues().get(0).get(1));
+ Assertions.assertEquals("c1", valueRange.getValues().get(0).get(2));
+ Assertions.assertEquals(3L, valueRange.getValues().get(1).size());
+ Assertions.assertEquals("a2", valueRange.getValues().get(1).get(0));
+ Assertions.assertEquals("b2", valueRange.getValues().get(1).get(1));
+ Assertions.assertNull(valueRange.getValues().get(1).get(2));
+ }
+
+ @Test
+ public void testTransformToValueRangeWithJsonArray() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:B2");
+
+ String body = "[{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a1\"," +
+ "\"B\": \"b1\"" +
+ "}," +
+ "{" +
+ "\"spreadsheetId\": \"" + spreadsheetId + "\"," +
+ "\"A\": \"a2\"," +
+ "\"B\": \"b2\"" +
+ "}]";
+ inbound.getMessage().setBody(body);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals("A1:B2",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(2L, valueRange.getValues().size());
+ Assertions.assertEquals(2L, valueRange.getValues().get(0).size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("b1", valueRange.getValues().get(0).get(1));
+ Assertions.assertEquals(2L, valueRange.getValues().get(1).size());
+ Assertions.assertEquals("a2", valueRange.getValues().get(1).get(0));
+ Assertions.assertEquals("b2", valueRange.getValues().get(1).get(1));
+ }
+
+ @Test
+ public void testTransformToValueRangeWithJsonObject() throws Exception {
+ Exchange inbound = new DefaultExchange(camelContext);
+ inbound.getMessage().setHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"range", "A1:B2");
+
+ String body = "{\"spreadsheetId\": \"" + spreadsheetId + "\", \"A\":
\"a1\", \"B\": \"b1\" }";
+ inbound.getMessage().setBody(body);
+
+ dataType.transform(inbound.getMessage(), DataType.ANY, DataType.ANY);
+
+ Assertions.assertEquals("A1:B2",
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.RANGE));
+ Assertions.assertEquals(RangeCoordinate.DIMENSION_ROWS,
inbound.getMessage().getHeader(GoogleSheetsStreamConstants.MAJOR_DIMENSION));
+ Assertions.assertEquals("USER_ENTERED",
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"valueInputOption"));
+
+ ValueRange valueRange = (ValueRange)
inbound.getMessage().getHeader(GoogleSheetsConstants.PROPERTY_PREFIX +
"values");
+ Assertions.assertEquals(1L, valueRange.getValues().size());
+ Assertions.assertEquals(2L, valueRange.getValues().get(0).size());
+ Assertions.assertEquals("a1", valueRange.getValues().get(0).get(0));
+ Assertions.assertEquals("b1", valueRange.getValues().get(0).get(1));
+ }
+}
diff --git
a/library/camel-kamelets/src/main/resources/kamelets/google-sheets-sink.kamelet.yaml
b/library/camel-kamelets/src/main/resources/kamelets/google-sheets-sink.kamelet.yaml
new file mode 100644
index 00000000..9adfdf07
--- /dev/null
+++
b/library/camel-kamelets/src/main/resources/kamelets/google-sheets-sink.kamelet.yaml
@@ -0,0 +1,188 @@
+# ---------------------------------------------------------------------------
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+# ---------------------------------------------------------------------------
+apiVersion: camel.apache.org/v1
+kind: Kamelet
+metadata:
+ name: google-sheets-sink
+ annotations:
+ camel.apache.org/kamelet.support.level: "Stable"
+ camel.apache.org/catalog.version: "4.1.0-SNAPSHOT"
+ camel.apache.org/kamelet.icon:
"data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4KPCEtLSBHZW5lcmF0b3I6IEFkb2JlIElsbHVzdHJhdG9yIDI1LjAuMCwgU1ZHIEV4cG9ydCBQbHVnLUluIC4gU1ZHIFZlcnNpb246IDYuMDAgQnVpbGQgMCkgIC0tPgo8c3ZnIHZlcnNpb249IjEuMSIgaWQ9IkxheWVyXzEiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgeG1sbnM6eGxpbms9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkveGxpbmsiIHg9IjBweCIgeT0iMHB4IgoJIHZpZXdCb3g9IjAgMCA2NCA4OCIgc3R5bGU9ImVuYWJsZS1iYWNrZ3JvdW5kOm5ldyAwIDAgNjQgODg7Ii
[...]
+ camel.apache.org/provider: "Apache Software Foundation"
+ camel.apache.org/kamelet.group: "Google Sheets"
+ camel.apache.org/kamelet.namespace: "GCP"
+ labels:
+ camel.apache.org/kamelet.type: "sink"
+spec:
+ definition:
+ title: "Google Sheets Sink"
+ description: |-
+ Send data to Google Sheets and update/append values on a spreadsheet.
+ required:
+ - spreadsheetId
+ - clientId
+ - accessToken
+ - refreshToken
+ - clientSecret
+ type: object
+ properties:
+ spreadsheetId:
+ title: Spreadsheet ID
+ description: The Spreadsheet ID to be used as identifier
+ type: string
+ clientId:
+ title: Client Id
+ description: Client ID of the sheets application
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ clientSecret:
+ title: Client Secret
+ description: Client Secret of the sheets application
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ accessToken:
+ title: Access Token
+ description: OAuth 2 access token for google sheets application. This
typically expires after an hour so refreshToken is recommended for long term
usage.
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ refreshToken:
+ title: Refresh Token
+ description: OAuth 2 refresh token for google sheets application.
Using this, the Google Calendar component can obtain a new accessToken whenever
the current one expires - a necessity if the application is long-lived.
+ type: string
+ format: password
+ x-descriptors:
+ - urn:alm:descriptor:com.tectonic.ui:password
+ - urn:camel:group:credentials
+ applicationName:
+ title: Application Name
+ description: Google Sheets application name
+ type: string
+ operation:
+ title: Operation Mode
+ description: Operation to execute (update or append)
+ type: string
+ enum: [ "update", "append" ]
+ default: append
+ example: append
+ range:
+ title: Cells Range
+ description: The cell range of rows and columns to write data to.
+ type: string
+ example: "A1:B3"
+ majorDimension:
+ title: Major Dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ type: string
+ enum: [ "COLUMNS", "ROWS" ]
+ default: "ROWS"
+ example: "ROWS"
+ columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell coordinates
based on their position.
+ type: string
+ default: "A"
+ valueInputOption:
+ title: Value Input Option
+ description: Controls how the entered values should be be interpreted
when adding them.
+ type: string
+ enum: [ "USER_ENTERED", "RAW" ]
+ default: "USER_ENTERED"
+ example: "USER_ENTERED"
+ dataTypes:
+ in:
+ default: json-struct
+ types:
+ json-struct:
+ format: "google-sheets:application-x-struct"
+ description: |-
+ Special Json representation of Google Sheets ValueRange object
with just row and column values as a generic JsonNode.
+ Each cell value is represented by a Json property named after the
respective row (A-Z) or column (1-n) depending on the given majorDimension.
+ Custom column names are supported in order to use custom property
names instead of generic row (A-Z) or column (1-n) coordinates.
+ The given Json struct is ready to be transformed into a proper
Google Sheets ValueRange object that can be used in the update/append values
operation.
+ The data type uses a set of header entries to determine properties
such as spreadsheetId, the target cell range, the majorDimension and so on.
+ headers:
+ CamelGoogleSheets.range:
+ title: Range
+ description: Cells range to write data to.
+ default: A:A
+ type: string
+ CamelGoogleSheets.spreadsheetId:
+ title: Spreadsheet id
+ description: The Spreadsheet ID to be used as identifier.
+ type: string
+ CamelGoogleSheets.majorDimension:
+ title: Major dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ default: ROWS
+ type: string
+ CamelGoogleSheets.columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell
coordinates based on their position.
+ default: A
+ type: string
+ CamelGoogleSheets.valueInputOption:
+ title: Value Input Option
+ description: Controls how the entered values should be be
interpreted when adding them.
+ default: USER_ENTERED
+ type: string
+ mediaType: application/json
+ dependencies:
+ - "mvn:org.apache.camel.kamelets:camel-kamelets-utils:4.1.0-SNAPSHOT"
+ - "camel:core"
+ - "camel:jackson"
+ - "camel:kamelet"
+ - "camel:google-sheets"
+ template:
+ from:
+ uri: "kamelet:source"
+ steps:
+ - set-header:
+ name: CamelGoogleSheets.spreadsheetId
+ simple: "{{spreadsheetId}}"
+ - set-header:
+ name: CamelGoogleSheets.range
+ simple: "{{?range}}"
+ - set-header:
+ name: CamelGoogleSheets.majorDimension
+ simple: "{{?majorDimension}}"
+ - set-header:
+ name: CamelGoogleSheets.columnNames
+ simple: "{{?columnNames}}"
+ - set-header:
+ name: CamelGoogleSheets.valueInputOption
+ simple: "{{?valueInputOption}}"
+ - transform:
+ to-type: "google-sheets:application-x-struct"
+ - to:
+ uri: "google-sheets:data/{{operation}}"
+ parameters:
+ spreadsheetId: "{{spreadsheetId}}"
+ clientId: "{{clientId}}"
+ accessToken: "{{accessToken}}"
+ refreshToken: "{{refreshToken}}"
+ clientSecret: "{{clientSecret}}"
+ applicationName: "{{?applicationName}}"
+ range: "{{?range}}"
diff --git
a/library/camel-kamelets/src/main/resources/kamelets/google-sheets-source.kamelet.yaml
b/library/camel-kamelets/src/main/resources/kamelets/google-sheets-source.kamelet.yaml
index 210333f7..36e4217e 100644
---
a/library/camel-kamelets/src/main/resources/kamelets/google-sheets-source.kamelet.yaml
+++
b/library/camel-kamelets/src/main/resources/kamelets/google-sheets-source.kamelet.yaml
@@ -91,20 +91,74 @@ spec:
type: string
splitResults:
title: Split Results
- description: True if value range result should be split into rows or
columns to process each of them individually.
+ description: True if value range result should be split into rows or
columns to process each of them individually.
type: boolean
x-descriptors:
- 'urn:alm:descriptor:com.tectonic.ui:checkbox'
default: true
range:
- title: Cells Range to get Data from
- description: the range of rows and columns in a sheet to get data from.
+ title: Cells Range
+ description: The range of rows and columns in a sheet to get data from.
type: string
example: "A1:B3"
- types:
+ majorDimension:
+ title: Major Dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ type: string
+ enum: [ "COLUMNS", "ROWS" ]
+ default: "ROWS"
+ example: "ROWS"
+ columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell coordinates
based on their position.
+ type: string
+ default: "A"
+ dataTypes:
+ default:
out:
- mediaType: application/json
+ default: json
+ headers:
+ CamelGoogleSheets.range:
+ title: Range
+ description: Cells range to write data to.
+ default: A:A
+ type: string
+ CamelGoogleSheets.spreadsheetId:
+ title: Spreadsheet id
+ description: The Spreadsheet ID to be used as identifier.
+ type: string
+ CamelGoogleSheets.majorDimension:
+ title: Major dimension
+ description: Specifies the major dimension that the given values
should use (ROWS or COLUMNS).
+ default: ROWS
+ type: string
+ CamelGoogleSheets.columnNames:
+ title: Column Names
+ description: Optional custom column names that map to cell
coordinates based on their position.
+ default: A
+ type: string
+ CamelGoogleSheets.splitResults:
+ title: Split Results
+ description: True if value range result should be split into rows or
columns to process each of them individually.
+ default: "true"
+ type: boolean
+ types:
+ json:
+ format: "application-json"
+ description: |-
+ Json representation of a GoogleSheets ValueRange object that holds
all values for the given cell range.
+ Or Json array of values for a single row/column in the range when
'splitResults' mode is enabled.
+ mediaType: application/json
+ json-struct:
+ format: "google-sheets:application-x-struct"
+ description: |-
+ Special Json representation of Google Sheets ValueRange object
with just row and column values as a generic JsonNode.
+ Each cell value is represented by a Json property named after the
respective row (A-Z) or column (1-n) depending on the given majorDimension.
+ Custom column names are supported in order to use custom property
names instead of generic row (A-Z) or column (1-n) coordinates.
+ The produced Json struct is ready to be transformed back into a
proper Google Sheets ValueRange object that can be used in an update/append
values operation.
+ mediaType: application/json
dependencies:
+ - "camel:core"
- "camel:jackson"
- "camel:kamelet"
- "camel:google-sheets"
@@ -124,4 +178,19 @@ spec:
steps:
- marshal:
json: {}
+ - set-header:
+ name: CamelGoogleSheets.spreadsheetId
+ simple: "{{spreadsheetId}}"
+ - set-header:
+ name: CamelGoogleSheets.range
+ simple: "{{?range}}"
+ - set-header:
+ name: CamelGoogleSheets.majorDimension
+ simple: "{{?majorDimension}}"
+ - set-header:
+ name: CamelGoogleSheets.columnNames
+ simple: "{{?columnNames}}"
+ - set-header:
+ name: CamelGoogleSheets.splitResults
+ simple: "{{?splitResults}}"
- to: "kamelet:sink"
