Description: When the LET function is used, if it's written in lowercase "let", LibreOffice Calc will show an error result of "#NAME?". Function names should not be treated as case-sensitive, so this should get fixed in LibreOffice Calc. As background, whenever you export a Google Sheet into either Excel or OpenDocument format, LET is converted to lowercase, wherever it appears in the formula. It's not within my control to just use uppercase, as the working source files are all Google Sheets. I am attempting to sync hundreds of Google Sheets documents to ODS for offline work which will still need to sync back to Google. (using Linux Insync utility). example that fails: =IF($A3="","", let(act,UPPER(TRIM($B3)), IF(act<>"MTM", $M3, $M3 + $W3 ) ) ) Steps to Reproduce: 1. Create a Google Sheet containing these values in A1 to A5: Active, 3, 4, 5, =IF(A1<>"Active","N/A",LET(avg, AVERAGE(B1:D1), IF(avg>=4, "Great", IF(avg>=3, "Good", "Poor")))) The formula in A5 should display a value of "Great". 2. File Menu -> Download -> OpenDocument 3. Open the file with LibreOffice Calc and see that A5 now shows "#NAME?". 4. Edit the formula to uppercase LET and it will show "Great" again. Actual Results: cell A5 shows "#NAME?". Expected Results: cell A5 shows "Great". Reproducible: Always User Profile Reset: No Additional Info: Locale: en-CA.UTF-8 Module: SpreadsheetDocument OS: Linux Mint 22.3 Cinnamon OS is 64bit: yes Kernel 6.17.0-14-generic LibreOffice 26.2.0.3 Flatpak edition Build: afbbd0df0edb6d40b450b0337ac646b0913a760c
Created attachment 205506 [details] test file
This is bug in google, in the google spreadsheet is LET writer in upper letters, but exported in small letters. This function return #DIV/0 in google. But OpenFormula standard https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017942_715980110 says "Function names are case-insensitive." Setting to New. Regina, please can you check it?
Updated sample to reproduce: (changed AVERAGE range only) Active, 3, 4, 5, =IF(A1<>"Active","N/A",LET(avg, AVERAGE(B1:D1), IF(avg>=4, "Great", IF(avg>=3, "Good", "Poor"))))
Created attachment 205507 [details] updated test file that matches testcase expected/actual
(In reply to raal from comment #2) > This is bug in google The LET function is not (yet) part of ODF, so _I guess_ GS does not know how to export it to an ODS file as LO expects it. If that LET() function is not marked in some specific way, how could LO identify it as the LET() function in order to upper-case it? How would LO identify the term "let" in lower-case differently than any other lower-case term (e.g. "avg")? I am aware that "avg" is not the name of any function in Calc, but what would happen in such potential case? How would Calc differentiate what should be treated as a valid function (and apply upper-case to it) and what to leave as-is?
To the OP, I would suggest saving from GS to an xlsx file (that should understand LET as a function), and then opening that xlsx file with LO Calc (unless there is some specific feature that cannot be saved as xlsx). My guess is that Calc will recognize that LET function correctly. Please let us know. A simple alternative could be to use Calc itself to Find_and_Replace "let" for "LET" (case-sensitive) in bulk.
I did some more digging. GS export: of:=...; let(avg; ... IF(avg>=...)) (has no namespace, no scoped variable markers) → LO flags as error. LO export (working example): of:=...; COM.MICROSOFT.LET(_xlpm.avg; ... IF(_xlpm.avg>=...)) Therefore LO does not identify LET via capitalization; it identifies it via the COM.MICROSOFT namespace and _xlpm. variable prefixing. GS’s ODS export omits those markers, so LO can’t interpret the formula as LET under OpenFormula compilation. BTW, I have also exported GS to .xlsx with the same result, LO shows "#NAME?" when opening the .xlsx file. Proposed behavior (import-only, no spec violation) When importing ODS formulas: 1. If parser encounters let( in of:= context 2. And function name resolves to no OpenFormula function 3. But Calc has a known implementation of LET 4. Then rewrite internally to: COM.MICROSOFT.LET(...) and apply _xlpm. scoping to bound identifiers The ambiguity concern (“how do we know it’s not some other function named let?”) does not apply: * let( is a function call token * LET is a known Excel-compatible function implemented by Calc * No OpenFormula function named let exists This is a deterministic, low-risk import mapping. And it would be a real improvement to interoperability.
A couple more data points. Using Excel online: * downloading .xlsx --> LO opens fine. * Exporting as ODS --> LO opens fine. So this issue is limited to how GS exports LET functions, in either ODS or .xlsx exports.
Created attachment 205509 [details] ODS Book exported with Excel (works in LO)
(In reply to calcaneus from comment #7) > Proposed behavior (import-only, no spec violation) > > When importing ODS formulas: When saving from GS to an xlsx file, if you then open that xlsx file in Excel (so, no action from LO Calc whatsoever), does Excel parse that "let" (lower-case) function correctly? Is it treated as the expected LET() function by Excel?
Yes, Excel opens the file and the function parses correctly and displays the expected value. The difference between the GS exported xlsx vs Excel saved xlsx is below: GS export to xlsx: <f>IF(A1<>"Active","N/A",LET(avg, AVERAGE(A2:A4), IF(avg>=4, "Great", IF(avg>=3, "Good", "Poor"))))</f> Excel save as xlsx: <f>IF(A1<>"Active","N/A",_xlfn.LET(_xlpm.avg, AVERAGE(A2:A4), IF(_xlpm.avg>=4, "Great", IF(_xlpm.avg>=3, "Good", "Poor"))))</f> In xlsx format, both LET functions are uppercase. However, the GS example omits the "_xlfn." function scope and "_xlpm." variable prefixes, but Excel still handles it fine.