Since LibreOffice Calc supports macros, and the builtin functions are somewhat limited, I'd like to write my own functions in Python and then use them as formulas in individual cells. Ideally, I would have a Python file like:
def VOL(a, b, c): v = a*b*c return v
either embedded in the document or as a
custom_functions.py file in a straightforward location. I would then be able to write
=VOL(A1, A2, B3) in a cell and have it calculate the result. Bad input or other errors should produce error text in the cell, same as how builtin calc functions do it.
I can go to Tools > Macros > Organize macros... > LibreOffice Basic. This gives me a dialog where I can apparently create a new function in the way I describe above (I haven't tested it). Unfortunately this appears to require writing it in Basic, not Python. I don't really want to learn Basic just for this, I want to leverage my knoweldge and experience in Python.
If I go to Tools > Macros > Organize macros... > Python, the options for creating new macros are grayed out. After digging a bit on the internet, I discovered that apparently LibreOffice (well, actually upstream OpenOffice) doesn't have its own Python IDE "yet", so they just put a grayed out button in the UI with no explanation as a placeholder. I say "yet" in quotes because I found people complaining about this in 2012 just to be told that it will be fixed any day now.
I also found some information about writing Python extensions using some sort of Python interop library. This appears to require importing a lot of hefty libraries, even the most basic examples require dozens of lines of complex boilerplate code, and debugging is probably a pain. It would be much easier to just write a normal Python script that reads data from the spreadsheet and then writes it back. However the functions I want are relatively trivial, and being able to see and navigate data effectively is very helpful to develop and debug my exploratory analysis, so being able to define the function in simple Python and play with it inside a Calc spreadsheet would be very convenient. Is there a way to do this?
I am on LibreOffice 126.96.36.199 and Windows 7, I can use either Python 2 or 3, but prefer 3.