[
https://issues.apache.org/jira/browse/CAMEL-25302?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Claus Ibsen resolved CAMEL-25302.
---------------------------------
Fix Version/s: 4.23.0
Resolution: Fixed
Fixed on main via https://github.com/apache/camel/pull/27343
> 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
> Priority: Major
> Fix For: 4.23.0
>
>
> 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)
