Calc scripts as worksheet functions in cell formulae [closed]

[cbg.jahn]

Re: Calc scripts as worksheet functions in cell formulae

2020-01-03 revision per advice of Mike Kaganski.

Dear LO community:

There was a 2004 proposal for a feature as noted in the subject. The proposal was voted off per subsequent 2006 assessment. Much thought and deliberation by technical experts of the then OO development team was recorded in the link below. The discussion was thorough, and lengthy, containing both merits and counter arguments. Please refer to the link below for details, to minimize replication in this inquiry.

2004-01-26 Enable Calc to use Scripting Framework scripts as Calc functions

http://bz.apache.org/ooo/show_bug.cgi?id=24829

2004-01-26 Duncan Foster. Proposal.

2006-02-10 Mathias Bauer. Will not fix. (1) Low demand. (2) Slower than Basic.

Inquiry and discussion of this feature emerged in later years. Script languages included BeanShell, Java, Python, etc. Some references are provided for representation, not meant to overlook the vast contributors endeavoring in script development.

I am curious about the two main decision factors in the 2006 assessment.

(1) low demand → Does it remain true in 2020?

(2) slower than Basic → What might be the technical cause?

Info. or URL to the past relevant discussion is appreciated.

Sincerely,

[references]

2004-01-26 Enable Calc to use Scripting Framework scripts as Calc functions

http://bz.apache.org/ooo/show_bug.cgi?id=24829

2004-01-26 Duncan Foster. Proposal.

2006-02-10 Mathias Bauer. Will not fix. (1) Low demand. (2) Slower than Basic.

20080119 [BeanShell] OOo Calc macro example

http://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=1829

20080120 [Java] OOo Writer and Calc macro examples

http://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=1844

20151206 Interface-oriented programming in OpenOffice / LibreOffice : automate your office tasks with Python Macros

http://christopher5106.github.io/office/2015/12/06/openoffice-libreoffice-automate-your-office-tasks-with-python-macros.html

20110929 How can I call a Python macro in a cell formula in OpenOffice.Org Calc?

http://www.stackoverflow.com/questions/7591656

Outis citing Villeroy work on inactive oooforum.org → Python functions from OO.org Basic

20180221 Use a Python function as formula in LibreOffice Calc cells

http://www.superuser.com/questions/1297120

Jim K → no easy direct call to Python, need wrapper in Basic,

20180610 Define a LibreOffice Calc custom function in Python

http://www.stackoverflow.com/questions/50785510

Jim K → no easy direct call to Python

JLOP Java LibreOffice Programming

Prof./Dr. Andrew Davison

http://fivedots.coe.psu.ac.th/~ad/jlop

Part 01, Basics; Chapter 01, LibreOffice API Concepts

01.%20Concepts.pdf

page 14, comparison, ease of Basic (or constraint)

Basic = easier access to service / component,

Basic API = script running inside documents, no need of the client component context

Basic code need = loader, service manager, desktop

Basic simplification

(1) Basic script outside OO document

Dim oSM, oDesk, oDoc as Object

Set oSM = CreateObject( “com.sun.star.ServiceManager” )

Set oDesk = oSM.createInstance( “com.sun.star.frame.Desktop” )

Set oDoc = oDesk.loadComponentFromURL( “file:///C:/tmp/testdoc.odt”, “_blank”, 0, noArgs() )

↓

(2) Basic script inside OO document

Set oSM = CreateObject( “com.sun.star.ServiceManager” )

Set oDesk = oSM.createInstance( “com.sun.star.frame.Desktop” )

Set oDoc = oDesk.CurrentComponent

↓

(3) default objects of service manager + desktop

Set oDoc = StarDesktop.CurrentComponent

Set oDoc = ThisComponent

↓

(4) add service

set oSFA = CreateUnoService( “com.sun.star.ucb.SimpleFileAccess” )

oSFA.CreateFolder( dirName )

[mikekaganski]

Personally I find this an awful reading. To try to answer this, I must first understand what was the problem and the question; and for that, I seem to need to read through all those links, try to see what was in mind of @cbg.jahn, and hope I was right … no, thanks. This is the task of asker - to create an essence out of what asker learned, in a precise form, and then, after the question itself (rather self-contained, which possibly might be answered even without further reading) was asked, the following links supplement the question, not substitute it. Not fulfilling this task, this is lack of respect to those who might want to help and answer.

[cbg.jahn]

2020-01-03 revision per advice of Mike Kaganski. Thanks.

[mikekaganski]

So - is your long question boils down to this:

It is easy to write user-defined spreadsheet functions in Basic; but there’s no way to call functions written in other scripting languages the same way; a proposal to allow functions of any scripting language supported by the program was considered in #i24829, and WONTFIXed because of (1) low demand, and (2) potentially low performance. The questions are, if (1) is still true today, and what might be the technical cause for (2)?

The answers to those questions are: (1) this is unknown, and (2) this is unknown. There’s no other way to evaluate “demand” than see relevant bug in our bug tracker; do you know the number of duplicates and CCd users in relevant issue? The technical reason for (2) was known to the developer who devoted some time to that, and saw something, but didn’t bother to elaborate.

[mikekaganski]

Anyway, there’s no body in TDF that orders developers what to do, whatever demand might be. If developers like a task, or if they are paid for it by customers, they work on something. If someone wants to jump in and implement something with low demand (and which will introduce more incompatibilities), then welcome and then there will be a technical discussion, not based on demand level. Doers decide.

[mikekaganski]

Now reading through so-called “references”, I see that e.g. [20080119], [20080120], [20151206], [JLOP], [Part 01] are irrelevant to the question; they simply show macros, and don’t even touch spreadsheet functions in those languages. Why post them here and waste other’s time reading them in the hope to figure how could they be relevant?

[cbg.jahn]

Noted → no direct insight available to the questions at present.

Thanks for the auxiliary info. and advice. Marking the subject as [closed].