Issue for tracking development issues with Google Sheets Basic compatibility checked: - [x] Generate version of basic example file in Google Sheets - [x] Check basic compatibility with `{tidyods}` functions `tidyods::read_ods_cells()` can read an ODS file generated from Google Sheets, but naturally it has its own weirdnesses. ## Similarities with LibreOffice ### XML namespace The Google generated ODS has the same XML namespace set-up as LibreOffice (same 35 namespaces, Excel has just 15). ### Dates Dates that are pure dates (15/06/2022) not date-times (15/06/2022 13:24:56) are stored as just date components (Excel stores these as a date-time at midnight). ## Similarities with Microsoft Excel ### Whitespace replication Google ODS follows the white space replication approach used by Microsoft Excel (`text:s` tags with a `text:c` attribute). ### Trailing blank cells Like Excel a Google ODS has replicated blank cells at the end of a row (i.e. with column repetition) and at the end of the sheet (i.e. with row repetition). However, end of sheet replication is different in that there are three blank rows instead of just one, the penultimate row is replicated (over 1 million times in the example file) but is preceded and followed by a blank row that is not repeated. Column repetition is just over 1,000 columns in the example file, but unlike rows this is the final cell in the row. ## Google ODS's unique weirdnesses ### Comments with author When saving a comment thread the format is different from Excel, the author's name is included as a text component. ```r cell_tbl$cell_annotation[c(156, 166)] #> [1] "Test comment\n-Matt Kerlogue" #> [2] "Test comment 1\n-Matt Kerlogue\nReply to comment\n-Matt Kerlogue" ``` In Excel the author information for a comment thread is not retained. ```r cell_tbl$cell_annotation[c(154, 164)] #> [1] "Comment:\n Test comment" #> [2] "Comment:\n Test comment 1\nReply:\n Reply to comment" ``` LibreOffice does not support comment threads, only singular "note" annotations. ### Booleans **!! THIS IS VERY DIFFERENT !!** Boolean (`TRUE`/`FALSE`) values are not stored as booleans! They are stored with a numeric value (`0` for `FALSE`, `1` for `TRUE`, in the `office:value` attribute) and the cell's general text (what ultimately becomes `cell_content`). As a result the `office:value-type` of a Google boolean is **`float`** not `boolean`! The boolean aspect of the value is generated via the use of formula (`table:formula` attribute) of either `of:=FALSE()` or `of:=TRUE()`. Probably need consideration for how to deal with in the smart rectifier.