Unable to run the simpliest python macro for Calc udf

I’ve just started writing a python macro, here is how it looks now:

import uno
def main():
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets.getByName("Sheet1")
    return None

In the end, I need this to be a UDF function, so I also have Basic macro to invoke it:

Sub invoker()
Dim oScriptProvider, oScript
oScriptProvider = ThisComponent.getScriptProvider("")
oScript = oScriptProvider.getScript("vnd.sun.star.script:py.py$main?language=Python&location=user") 
oScript.invoke(Array(), Array(), Array())
End Sub

Yet it’s already giving me error:


What is wrong here?


  1. the Errormessage states AttributeError :Sheets - it seems there is no Spreadsheet_document in Focus ( maybe the Basic-IDE , or a MRI-Dialog or XRay-Dialog instead … none of them has Attribute: Sheets

  2. your main-function returns explicitly »None« … why do you assume thats the sheet-object occurs by magic into the basic-sub??

  3. the Basic-code itself is a sub BUT NOT a function with the optional property to return something.

  4. Beside all the previous points, it will never happen to pass the sheet-object back to the calc-function-call, because its not in the allowed set of simple Data-Types for Basic-UDFs (which are String’s, Doubles or array of array[s] (which entries of the mentioned Types))

Thank you for your answer.
I’m a complete novice in LibreOffice, so, could you please, elaborate a bit more? In both Basic and Python code, I don’t need any data or object to return, only to call Python and it will be the end of program. What is the benefit of creating a return? If you could refer me good code for Calc’s Python, it would be awesome.
I saw Python code for add-ins, however, it only use Class to make program communicate with Calc. Should I also use Class in Python instead of Functions?

Unable to run the simpliest python macro for Calc udf

It is not possible to implement any user defined function by means of macro code other than StarBasic. A Python function for Calc needs to be implemented as an add-in wrapped into an extension package. That StarBasic wrapper is a dirty hack.

Even when using the dirty hack with a Basic UDF calling a Python function, the Python function should not work with UNO objects. Spreadsheet functions take simple values and they return simple values. Whenever a user defined function makes use of some active document, active sheet or selection, failure hangs in the air.

1 Like

again, what exactly do you want to do?


sheet = doc.Sheets.getByName("Sheet1")


sheet = doc.Sheets['Sheet1']

but… What do you really want to do?

@elmau : of course the »dictionary-like-access« provided by pyuno since ~ LO5.? should be preferred … but both works the same way, so that isn’t a issue here.

Is that documented anywhere?


and https://cgit.freedesktop.org/libreoffice/core/commit/?id=af8143bc40cf2cfbc12e77c9bb7de01b655f7b30

1 Like

The problem is that in Python, there is no convenience like Basic’s ThisComponent. The XSCRIPTCONTEXT.getDocument() returns you the actually active component, which - in case of running the macro from the Basic IDE - is Basic IDE. ThisComponent in Basic would give you a reference to the last active component, other than Basic IDE, for your convenience…

Try running the macro from Calc, using ToolsMacrosRun Macro.