Calc macro: getDocument() and ThisComponent return null

synthc · February 3, 2020, 6:48am

I’ve written a basic macro for calc that calls a python script to do a calculation and then return the result to the cell that calls the macro. It works just fine except when opening the spreadsheet.

When opening the spreadsheet (during startup) ThisDocument (in basic) and XSCRIPTCONTEXT.getDocument() are both null. I managed to fix the basic error by moving the script from “My Macros” into the actual .ods in the Organize Macros menu, but then the python script still errors out because XSCRIPTCONTEXT.getDocument() returns nothing. I can call the macro again by re-entering the call in the cell and it works just fine. This appears to be a known bug: https://bugs.documentfoundation.org/show_bug.cgi?id=92770

I can’t move the python script into the document (instead of “My Macros”) because then the basic script can’t find it - I can’t find any way to customize this script URI in basic in order to point it to the document itself: “vnd.sun.star.script:Cell_Functions.py$calcEffectValue?language=Python&location=user”

Is there a way to delay script initialization until the document has fully loaded and ThisDocument returns a proper value? Or is there some other way to load the document? In the python script, I’m only accessing the document to get my named ranges from it, are there any alternative ways to get this data?

doc = XSCRIPTCONTEXT.getDocument()
nameDict = {}
for range in doc.NamedRanges:
    nameDict[range.getName()] = range.getReferredCells().getDataArray()[0][0]

I get the error: “‘NoneType’ object has no attribute ‘NamedRanges’”, because doc is null.

Basic macro that calls the python script:

function cev(a as String) as double
Dim scriptPro As Object, myScript As Object, currentCom As Object
scriptPro = ThisComponent.getScriptProvider()
myScript = scriptPro.getScript( _
       "vnd.sun.star.script:Cell_Functions.py$calcEffectValue?language=Python&location=user")
cev = myScript.invoke(Array(a), Array(), Array())
end function

LibreOffice Version: 6.3.4.2 (x64)

Python 3.8

Ratslinger · February 4, 2020, 6:01am

Hello,

I confess not to be proficient in either Python or Calc.

Do currently use Python code (primarily in Base) attached to Open Document events and there is no problem with doc = XSCRIPTCONTEXT.getDocument(). However do see this as a problem when used in your situation.

Have looked at the referred to bug report and can say this is not the same problem. What I don’t understand is why there is no error in Basic just before calling the Python routine but there is in Python.

Now I don’t have an actual answer (this actually should be filed as a new bug report) but have an optional work around it you wish.

Since ‘This Component’ is available in Basic as the document is being opened, send it as another argument in the call.

So your Basic macro would be:

function cev(a as String) as double
Dim scriptPro As Object, myScript As Object, currentCom As Object
dim a1(1), b1(0), c1(0) as variant 
a1(0) = ThisComponent
a1(1) = a
scriptPro = ThisComponent.getScriptProvider()
myScript = scriptPro.getScript( _
       "vnd.sun.star.script:Cell_Functions.py$calcEffectValue?language=Python&location=user")
cev = myScript.invoke(a1, b1, c1)
end function

Then adjust your Python script accordingly for doc. Testing shows this to work when opening the file without error.

Also, to call python from embedded in document, change location=user to location=document.

synthc · February 5, 2020, 12:20am

Hi,

Your solution worked, thanks. I admit I’m not very competent with basic to (and completely new to LibreOffice basic) so I have no clue what these a, b, c variables are.

I tried targeting the script in the document using location=document (as this seems like a cleaner way to do this) but it threw a keyerror on “document”. I also don’t understand why the basic script works but the python script errors out, since the document is clearly loaded before the python script is even called.

Ratslinger · February 5, 2020, 1:02am

@synthc,

location=document is only going to work if you embed the Python script into the document. This can be done in many ways but an extension APSO is probably the easiest → Jean-Marc Zambon / apso · GitLab

Of the arrays A1, B1 and C1 only A1 should be a concern. This contains your arguments sent to the script. B1 and C1 are necessary in the statement but are empty.

synthc · February 5, 2020, 2:03am

I am using APSO. The python script definitely exists in the document - if I open it with 7-zip and check the scripts directory, my script is in there (it also shows up in both a built-in and APSO manage python scripts windows). But it’s working now on startup, so I don’t see much of a need to bother with the embedded script, just curious that it isn’t working.

Ratslinger · February 5, 2020, 4:35am

Unfortunately I had not tested the startup using an embedded Python script. This does error and it appears this is related to your original error. Something is not loading properly in regard to Python scripts. It does work properly once the document is fully open.

I did provide the notation of calling the script from the document because of your comment in the question. Should have tested for this situation.

Glad you do have something that works.

LibreOfficiant · March 13, 2020, 9:51am

X-scripting calls are documented in LibreOffice online/local help:

Hoping this will help!