shashank created CAMEL-25302:
--------------------------------
Summary: camel-google-sheets - the
google-sheets:application-x-struct data type gives the custom column names to
the wrong columns when the range does not start at column A (values lost or
repeated), and miscomputes columns beyond ZZ
Key: CAMEL-25302
URL: https://issues.apache.org/jira/browse/CAMEL-25302
Project: Camel
Issue Type: Bug
Components: camel-google-sheets
Reporter: shashank
The data type {{google-sheets:application-x-struct}}
({{GoogleSheetsJsonStructDataTypeTransformer}}, used by the google-sheets
source and sink Kamelets) maps the columns of a range to JSON property names.
With {{columnNames}} ("Optional custom column names that map to cell
coordinates based on their position", Kamelet property) the k-th name should be
the name of the k-th column of the range.
{{CellCoordinate.getColumnName(columnIndex, columnStartIndex, columnNames)}}
picks the name with
{code:java}
index = columnIndex % columnStartIndex; // when columnStartIndex > 0
{code}
which is the position only for a range that starts at column A. For other
ranges:
* range {{B2:D3}}, names {{name,age,city}}: every column is named {{name}}.
Reading, the JSON object of a row is {{\{"name":"Oslo"\}}} instead of
{{\{"name":"Ann","age":"31","city":"Oslo"\}}} (the later columns overwrite the
earlier ones). Writing, the row {{\{"name":"Ann","age":31,"city":"Oslo"\}}} is
sent as {{[Ann, Ann, Ann]}}: the sheet gets the first value in every column;
* range {{C1:E1}}, names {{name,age}}: {{name, age, name}} instead of {{name,
age, E}};
* with the default {{columnNames}} ({{A}}) and a range from column B on, every
column is named {{A}} and a read row keeps only its last value.
Separately, the A1 column letters are wrong from three letters on:
{{getColumnIndex("AAA1")}} is 52 instead of 702 (it adds {{(letter + 1) * 26}}
for every letter but the last), and {{getColumnName(702)}} throws
{{ArrayIndexOutOfBoundsException}} (it writes at most one overflow letter).
Google Sheets has columns up to ZZZ.
h3. Reproduction
* {{GoogleSheetsJsonStructDataTypeTransformerTest}}: three new tests, all fail
on main: read of a {{ValueRange}} for {{Sheet1!B2:D3}} with names
{{name,age,city}}; split read of {{C1:E1}} with names {{name,age}}; write of a
JSON row for {{B1:D1}} ({{expected: <[Ann, 31, Oslo]> but was: <[Ann, Ann,
Ann]>}}).
* New {{CellCoordinateTest}}: names by position for ranges starting at A, B and
C (fails on main), three-letter columns {{AAA}}/{{XFD}} and the range
{{ZZ1:AAB2}} (fails), name/index round trip for all 18278 columns up to ZZZ
(fails at 702), and a control for one and two letters (passes on main). It also
pins the default names {{A}} for a range starting at B ({{A}}, {{C}}, {{D}}).
h3. Proposed fix
* Position of a column in the range: {{columnIndex - columnStartIndex}};
columns after the last custom name keep their A1 name, as today for ranges
starting at A.
* Column letters as a number in bijective base 26 (A=1 .. Z=26, AA=27, ...),
for both directions. Names and indexes up to ZZ are unchanged.
For ranges that start at column A the names are exactly as today, so all
existing tests pass (camel-google-sheets: 33 tests).
The default column names: the Kamelets pass {{columnNames=A}} when the user
sets none. With the fix the first column of any range takes the first name, so
with the default it is still named {{A}}, as today: a single column range such
as {{B:B}} is read as {{\{"A": ...\}}} and written from the property {{A}}
before and after the fix, so such working setups keep working. The other
columns of a wider range keep their A1 name ({{B2:D3}}: {{A}}, {{C}}, {{D}})
instead of all being named {{A}}. Treating the default {{A}} as "no custom
names" would give nicer names ({{B}}, {{C}}, {{D}}) but would rename the column
of every {{B:B}} setup that works today, so the fix keeps the positional rule
that the Kamelet property describes. (The column naming of
{{application-x-struct}} is not described in the Camel documentation, only in
the Kamelet property descriptions.)
No upgrade note: the output changes only where main repeats a name inside the
range. For a range starting at column {{s > 0}}, the fix and main can differ
only from column {{2s}} on, and there main gives column {{s}} and column {{2s}}
the same name (Lean theorem {{fix_eq_main_or_main_repeats}}), so such a row
lost a value when read and repeated one when written. Ranges at most {{s}}
columns wide, and all ranges starting at A, are named as before.
Found with a Lean 4 model of {{getColumnName}} and of the column letter
arithmetic: the property "the k-th column of the range gets the k-th custom
name" fails for every row of a range starting at column B (all columns get the
first name, theorem {{main_from_B_all_first}}) and holds for the fix for every
start column ({{fix_by_position}}); the property "index and name are inverse"
fails for every three-letter column ({{main_wrong_three}}, {{main_name_fails}})
and is proved for the fix for every column ({{fix_roundtrip}}), which names the
columns A..ZZ as today ({{fix_eq_main_name}}).
Affected: 4.14.x, 4.18.x and main (same code since the transformers moved from
the Kamelet utils to Camel, CAMEL-20087).
Duplicate check (2026-10-03): JIRA text "google-sheets" with "column" /
"columnNames" (CAMEL-20678, CAMEL-12967: other problems), "CellCoordinate"
(none). GitHub pull requests "google-sheets column", "CellCoordinate": none.
_Filed with Claude Code on behalf of allthingssecurity._
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
