Simple python user defined function in LibreOffice Calc

Question

Question. I would like to create simple user defined function (UDF) using python for LibreOffice Calc:

def MySum(a, b, c):
    s = a+b+c
    return s

Most likely answer will involve some non-python technicalities, so I want reference to some quick-start guide about what they are and how to use them.

My research.

1. There was a similar question: https://superuser.com/questions/1297120/use-a-python-function-as-formula-in-libreoffice-calc-cells. and https://www.youtube.com/watch?v=s0t7tkwL1Nk It was too complicated to comprahend, and function there used static cells, I want them to be any cells.

2. I found that one of the methods to use python functions in LO Calc is by creating Add-In: https://wiki.openoffice.org/wiki/Calc/Add-In/Python_How-To It involved some non-python IDE code withour any quick-start reference about how to understand and write code like this.

3. Also I found that python scripts can be run in Calc using APSO: Basic Wrapper for LibreOffice Calc Python UDF

4. One more similar question: Calling a python function from within LibreCalc I wasn't able to comprehend it.

5. I found that some of LO Basic terminology, which is used to create python macro, is covered here: https://documentation.libreoffice.org/assets/Uploads/Documentation/en/CG4.1/PDF/CG4112-CalcMacros.pdf and here https://wiki.openoffice.org/w/images/d/d4/0300CS33-CalcGuide.pdf

I wasn't satisfied with this answers. Either answers weren't complete or too complicated or there were a lot of non-python commands without reference to some guide that would explain what they are. Also most of the information was posted long time ago.

Update. My attempt. I tried to replicate actions done in first post. So I did this in APSO console:

    def VOL(a, b, c):
    v = a*b*c
    return v

    def call_vol():
        oSheet = XSCRIPTCONTEXT.getDocument().getSheets().getByIndex(0)
        cell_a = oSheet.getCellRangeByName("A1")
        cell_b = oSheet.getCellRangeByName("A2")
        cell_c = oSheet.getCellRangeByName("A3")
        cell_result = oSheet.getCellRangeByName("B1")
        cell_result.setValue(
            VOL(
                cell_a.getValue(),
                cell_b.getValue(),
                cell_c.getValue()))
    
    g_exportedScripts = call_vol,

And I got an error:

Traceback (most recent call last):
  File "/usr/lib/python3.10/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<console>", line 1, in <module>
NameError: name 'call_vol' is not defined
>>> portedScripts = call_vol,
Traceback (most recent call last):
  File "/usr/lib/python3.10/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<console>", line 1, in <module>
NameError: name 'call_vol' is not defined
>>> 

P.S. I am chemist, so many things you find ridiculously simple may be inaccessibly hard to comprehend for me. So be simple.

Answer 1

I tried to replicate actions done in first post. So I did this in APSO console.

From those two statements alone, it sounds like you barely even looked at the first post. It says:

Once it is installed, go to Tools -> Macros -> Organize python scripts. Expand My Macros and go to Menu -> Create module. Name it "custom_functions.py".

Why you thought to put all of that code into the console I have no idea, but if one were to do that, there are at least two problems:

1. Copy and paste into that console doesn't seem to maintain indentation, so you would need to enter it all by typing or line by line instead of pasting the whole block. That's why the error shows that call_vol is not defined. Python requires correct indentation to define its structure, unlike languages such as Java.
2. The console will not store the code. It needs to be stored in either user, share, or document so that the UDF wrapper can find it. Also, g_exportedScripts indicates which functions to export from a module to be called from Tools → Macros → Run Macro. A module in python is synonymous with a text file containing code.

Anyway, here is a user-defined function to call that code.

Function Call_VOL(a,b,c)
    Dim oScriptProvider, oScript
    oScriptProvider = ThisComponent.getScriptProvider()
    oScript = oScriptProvider.getScript(_
        "vnd.sun.star.script:custom_functions.py$VOL?language=Python&location=user")
    Call_VOL = oScript.invoke(array(a,b,c), array(), array())
End Function

Then for example enter =CALL_VOL(1;2;3) into a cell in the spreadsheet.