How to set cell formula using odfpy

Question

I keep getting Err:508 or #Name? (Err:525) when opening a spreadsheet created with odfpy and putting a formula in a cell with the following code:

tc = TableCell( valuetype="string", formula=calc, value=0 )

In the spreadsheet, the formula looks fine, and any edit to it together with reverting the edit, so there is no net change to the formula, makes it think it has changed, re-evaluates it, and it works fine. But not until it is tweaked. What am I missing?

Here's the formula I'm using, in case that is relevant:

=TEXT(NOW()+0*LEN(CONCAT(A2:F999));"YYYY.MM.DD.HH.MM.SS")

(purpose, to timestamp the most recent edit to a range of cells). I note that at the time the formula is inserted in row 1, that other rows haven't been inserted yet, but a few are in subsequent steps. But I wouldn't think any attempt to evaluate the range would occur until loaded into LibreOffice, so that doesn't seem a likely cause of the error.

---

I already am using ; not , in the function parameters, which seems to be the most successful answer for other people that encounter this error, and I'm using the English install, which is the other seeming issue some have with copy/pasting formulas. But still no joy, and not much that is relevant shows up in searching.

Answer 1

Well, this is weird, but probably documented somewhere.

The most helpful thing that I was able to find by searching was this answer. While that was not easy to find, because it was very generic, and didn't turn up in searches for "formula", it did provide a debug technique which enabled me to compare the formula that was getting inserted initially, as in my question, with what was there after the editing "tweak". "CONCAT" has to be prefixed with "COM.MICROSOFT.". Not at all obvious to the first-time formula insertion attempt.