[Google Sheets] Improvement of dynamic data typing (Separators, thousand spaces, and impacts on sheet architecture)

Google Workspace Developers Q&A
1

Hello Google Team and Community,

I would like to raise a recurring pain point in Google Sheets regarding the management and dynamic typing of numerical data when using complex formulas (specifically ARRAYFORMULA, VLOOKUP, and arithmetic operations).

The Issue Encountered: When processing imported or cross-referenced data lists with varying original formats:

  1. Decimal Separators: Using a period . in a sheet set to French locale (where the comma , is the default standard) instantly converts the value into an alphanumeric/text string.

  2. Thousand Separators (Spaces): Numbers containing thousand spaces (specifically non-breaking spaces CHAR(160), such as 10 000,01) are also locked into text format.

  3. Conflicts During Multiplication: As soon as a direct arithmetic operation (e.g., Number * Quantity) is integrated into a formula attempting to handle text conversion, the system breaks. The multiplication fails, truncates decimals (e.g., 0.8 converting to 0), or returns a #VALUE! error.

Tests Conducted with Gemini: After several optimization attempts assisted by AI (tests carried out with both Gemini Alpha and the standard Gemini version), we tried integrating an inline SUBSTITUTE / TEXT / VALUE layer directly nested within the formula to handle both calculation and conversion simultaneously. All tests proved unsuccessful: the formula lost decimal precision, rounded inconsistently (e.g., 0.0 turning into 0.8), or generated a blank error-masking output.

Heavy Impacts on Maintenance and Structure: To “trick” the system and work around these instabilities, we are forced to artificially split the data flow:

  • Bloated Spreadsheets: Necessity to create intermediate (hidden) columns dedicated solely to raw calculation, followed by additional columns to re-apply the visual layout via =ARRAYFORMULA(SUBSTITUTE(column; ","; ".")).

  • Maintenance Complexity: Moving original formulas, multiplying dependencies between columns, and cluttering the DOM of the sheet creates risks of broken chains whenever a user modifies the table structure.

  • Unpredictable Computational Costs: These workarounds add layers of repetitive array formulas that slow down overall document execution performance.

Suggested Improvements for Google Sheets:

  • Allow arithmetic operators (such as *) to automatically typecast numeric text strings containing periods or non-breaking thousand spaces into calculable numbers.

  • Provide seamless native handling on the fly, without forcing users to duplicate columns to separate calculation logic from display logic.

Thank you for listening and for your ongoing work on this tool!