Create user-defined function in javascript

[afroveo]

I want to create a function called MAXCONS that counts the maximum consequent occurrences of a specified value within given range. E.g.

If A1=“X”, A2=“X”, A3=“Z”, A4=“X”, A5=“Z”, A6=“Z”, A7=“Z”,
then MAXCONS(A1:A7, “X”) should return 2, because the value “X” appeared maximum 2 consequent times. And MAXCONS(A1:A7, “Z”) should return 3.

My questions:

1. Where can I learn how to do this?
2. Can I do this in Javascript? If yes, how?
3. Can you provide a sample/dummy function definition (without implementing the above logic), to which I can add the function body and use the function within a calc document?

Thank you!

[torreone]

I have your same problem, I know javascript very well and I would like to use it in LO.
JS allows things that the star basic does not allow, for example the use of collection (hashed objects)

However, I don’t find the interface with star basic and LO documents so simple.
So for now I postponed the problem

Just to get an idea, to start, I’ll give you a few links I had found in the past.
There is not much documentation available on js in the LO environment

https://forum.openoffice.org/en/forum/viewtopic.php?f=45&t=98785&p=474688&hilit=javascript#p474688

Need step-by-step instructions to create new JavaScript Macro

I need to create a macro in Calc. I’ve searched this forum and Google without luck. Title says it all. If no one can help with the JavaScript feature, I’ll be happy with Python. As of now, when I go to Tools | Macro | Organize Macros JavaScript and Python do not have the Create or Edit buttons enabled. Even worse, if I try to run any JavaScript sample, it crashes LibreOffice altogether. I’m pretty sure that this is not expected behavior. If I run the Python macros, they opens LibreOffice Writ…

https://forum.openoffice.org/fr/forum/viewtopic.php?f=8&t=49092

https://opengrok.libreoffice.org/xref/core/scripting/examples/javascript/

https://www.openoffice.org/framework/scripting/release-0.2/user-guide.html

[Ratslinger]

Hello,

Know very little about Javascript but gave it a go some time back. Only posting to relay a bit more on what is out there (somewhat sparse). See my answer here:

Solved: How to call JavaScript function from Basic

but the OP could not get it to work. Instead this question was answered here:

My Basic call to a JavaScript does nothing

An answer mentions, this appears to be able to be done without macros.

[mikekaganski]

JS allows things that the star basic does not allow, for example the use of collection (hashed objects)

Dim MyColl As New Collection

See VBA documentation; does not need VBA support.

[afroveo]

Thank you! It seems that using JS would be more complex than I expected. I coded the function in Basic.

public function MAXCO(vCellRangeValues as variant, val as string) as integer

    dim vCellValue as variant
    dim count as integer
    dim maxCount as integer
	
    for each vCellValue in vCellRangeValues
    	if vCellValue = val then
        	count = count + 1
    		if count > maxCount then
    			maxCount = count
    		end if
        else
        	count = 0
    	end if 
    next

    MAXCO = maxCount
end function

[mariosv]

It’s possible to do it with a formula without any macro.

Adjusting the ranges:
CONCAT(A$1:A$9999) to the maximum of rows to analyze
ROW($1:$512) to the maximum expected of char’s repetitions, to avoid slow down, reduce this one as much as possible.

• SUMPRODUCT it’s use only to no introduce the formula as array.

[afroveo]

Thanks I tried it, but I get the error #NAME?

Here is the formula I tried: =SUMPRODUCT(MAX(LEN(CONCAT(A$1:A$9999)))-MIN(LEN(regex(CONCAT(A$1:A$9999),B2&"{"&ROW($1:$512)&"}",""))))

I get the same #NAME? error if I try:
=regex(CONCAT(A$1:A$9999),B2&"{"&ROW($1:$512)&"}","")

My LO converts your “;” into “,”. Not sure why and whether it is important.

My LO Calc settings (I tried both with wildcards and regex):

ON: “Enable wildcards in formulas”
ON: “Enable regular expressions in formulas”

Formula Separators:
Function ,
Array Column ,
Array row ;

Any ideas how to get it work?

[afroveo]

Anyway, I coded it in Basic, so personal issue solved! Thanks!

[SM_Riga]

Regex() function was introduced starting from version 6.2. So if you have older version installed, Regex() is not recognized, causing #NAME? error. “;” delimeter is acceptable in any LO localization, while “,” can be confused with decimal delimeter depending on your locale. “;” is locale independent and is advised to use as standart delimeter globaly and thus on this LO Ask site too.

[mariosv]

Maybe your version it’s a bit old and doesn’t have REGEX() function. LibreOffice 6.2: Release Notes - The Document Foundation Wiki