User defined function in Calc using python

[alo]

I have the following python function (for the purpose of testing user defined functions):

def add_number(x,y):
z = x+y
return z

It is stored in the following document:

/home/MYUSERNAME/.config/libreoffice/4/user/Scripts/python/python_functions_for_lo_calc.py

It is referenced by the following Libreoffice basic macro to generate a user defined function for Calc:
Function add_number(x,y)
Dim myScript as Object
myScript = ThisComponent.getScriptProvider().getScript(“vnd.sun.star.script:python_functions_for_lo_calc.py$add_number?language=Python&location=user”)
add_number = myScript.invoke(Array(x,y), Array(), Array())
End Function

When I start a new Calc document and use the formula in a cell (e.g. C3) =add_number(2,2) it will give the correct answer of 4.

When I save the Calc document and re-open it from within Libreoffice (that is by going ‘File’ → ‘Open’ and the navigate to the document it also works.

But, if I open the document from the system file manager (in my case using nemo in Cinnamon in Debian 12)

I get the following error message:

Basic Runtime error. Property or method not found: getScriptProvider.

It then highlights the following line:
myScript = ThisComponent.getScriptProvider().getScript(“vnd.sun.star.script:python_functions_for_lo_calc.py$add_number?language=Python&location=user”)

What is going wrong? I am using the default Libreoffice in Debian 12. I get the same problem using Linux Mint. I have also downloaded the latest 24.3.2 from Libreoffice and installed on Debian 12 and I get the same problem.

I am guessing this is due to Libreoffice components not initialising in time before being called by the function.

[Villeroy]

Either you write a complete add-in (extension package with spreadsheet functions) in Python or you write a simple function in StarBasic’s “Standard” library.
As a third way you may call Python functions from StarBasic functions.

A useful Python extension adding Python’s string functions to Calc: Apache OpenOffice Community Forum - [Calc][oxt] A function for all python string methods - (View topic)

[alo]

Yes, and I am doing the third way, calling a python function from a star basic function. But that doesn’t solve the problem that I am facing.

I will just have to open the document via the LO menus rather than directly from the system file manager.

The ability to have ChatGPT (or other AI) write complex python functions and then have LO make that a simple LO calc function really is fantastic and makes LO extremely powerful.

I think writing a complete Add-In would be beyond my expertise.

[alo]

Global g_MasterScriptProvider As Object

Function getMasterScriptProvider() As Object
    If IsNull(g_MasterScriptProvider) Then
        Dim oMasterScriptProviderFactory As Object
        oMasterScriptProviderFactory = createUnoService("com.sun.star.script.provider.MasterScriptProviderFactory")
        g_MasterScriptProvider = oMasterScriptProviderFactory.createScriptProvider("")
    End If
    getMasterScriptProvider = g_MasterScriptProvider
End Function

Function add_number(x, y) As Variant
    On Error GoTo ErrorHandler
    Dim myScript As Object
    Dim provider As Object

    provider = getMasterScriptProvider()
    myScript = provider.getScript("vnd.sun.star.script:python_functions_for_lo_calc.py$add_number?language=Python&location=user")
    add_number = myScript.invoke(Array(x, y), Array(), Array())
    Exit Function