Premium plugin : Formula editors advanced

JSpreadsheet Plugin : Formula editors advanced

For JSpreadsheet v7:

You can buy this plugin on Repo plugin formula Demo is available on demo formula

For JSpreadsheet v8 or v9:

You can buy this plugin on Repo plugin formula Demo is available on demo formula

Features

• Add formulabar for write formula (highlighted)
• Tape F2 after selection cell, and your focus is on formula bar for edit.
• When cell is readonly, formulabar show on readonly value of cell.
• Input formula bar is in multiline, tape ALT+ENTER and new line is created on formula bar.
• If cell type is not recognize for formula, first char "=" is denied.
• Range of cells present on formula is colored on focus in formula bar
• Support custom formula + documentation
• Support multi formula autocomplete + Documentation (helper)
• Change editor of cell with formula (highlighted) + Documentation
• Create an alias of formulas

Dependencies

• for v1.x JSpreadsheet Pro v7
• for v2.x JSpreadsheet Pro v8 of JSpreadsheet Pro v9 and Formula-Pro

Options of plugin

Option name	Description	Type	Default Value
allowAutocomplete	Allow autocomplete formula for help write formula	Boolean	true
allowMultiline	Allow multiline in editor	Boolean	true
allowHighlightFormula	Allow formula highlighted	Boolean	true
allowHelper	Allow show helper	Boolean	true
autocompleteSearchApproximative	Search if text is in formula (not only start by)	Boolean	false
aliasFormulas	Create automatically an alias of formula with documentation attached (@Since 2.3.0) Example : {"SI" : "SUMIF" }	Object ( property key = new function, property value = function source)	{}
allowFormulaOnTypes	Allow type of cell write formula start by "=". Each type is separate by space (@Since 2.1.0)	String	number text
separatorOfArguments	Separate of arguments in documentation	String	,

For translation (required on v7)

Option name	Default Value
text_about	About
text_example	Example
text_orientation	Orientation
text_results	Results
text_parameters	Parameters
text_link	Learn more

For JSS v8 you can use dictionary with jSuites

jSuites.setDictionary({
    "Example": "Exemple",
    "Results": "Resultats",
    "Parameters": "Paramètres",
    "Learn more": "En savoir plus",
    "Read more": "Lire plus",
});

Methods of plugin

Method	Description	Example
hideBar() → Void	Hide formula bar	jspreadsheet.current.plugins.formula.hide();
refreshDoc() → Void	Refresh Documentation after edit element of Formula documentation	jspreadsheet.current.plugins.formula.refreshDoc();
showBar() → Void	Show formula bar	jspreadsheet.current.plugins.formula.show();
formulabarInput	Get Element of formula	jspreadsheet.current.plugins.formula.formulabarInput

Formula documentation

You can write your documentation of formulas, by default, this documentation is the same of Google Sheet formula. For write your documentation, add new item on the var jspreadsheet.formulasDoc

1 item of jspreadsheet.formulasDoc is construct like :

jspreadsheet.formulasDoc['MYFUNCTION()'] = {
       syntax:"MYFUNCTION(value, value)",
       about:"My custom function description ",
       examples:"MYFUNCTION(2008, 7) equals 25",
       params:[
           {type:"Float", comment:"First value"},
           {type:"Float", comment:"Second value"}
        ],
       link:"https://mydocs.domain.ext/doc/123456"
};

Important on jspreadsheet.formulasDoc object, define key property formula with () to end else it considers it as a variable.

Property name	Description
syntax	Syntax of your formula
description	Description of your formula
examples	Example of your formula
params (Array)	Array for description parameters with for 1 param : type and comment
link	Link for external documentation. link can to be : string : Url of page object : { url: "UrlOfPage", text: "Label of Button", callback: function(documentation) {/* your action on click */} } function on load whom return void, juste change buttonElement properties: function(buttonElement, documentation) { // Exemple buttonElement.href = "yourUrl"; buttonElement.addEventListener("click", (e) => {}); // .... }

Get started

Header on page for v7

<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jspreadsheet.com/v7/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />

<script src="/path/to/jss.formula.js"></script>
<link rel="stylesheet" href="/path/to/jss.formula.css" type="text/css" />

Header on page for v8

<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />

<script src="https://jspreadsheet.com/v8/jspreadsheet.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v8/jspreadsheet.css" type="text/css" />

<script src="http://www.jspreadsheet.com/v8/plugins/formula.js"></script>

<script src="/path/to/jss.formula.js"></script>
<link rel="stylesheet" href="/path/to/jss.formula.css" type="text/css" />

Initialize plugin on JSpreadsheet

jspreadsheet(document.getElementById('spreadsheet'), {
	...
	plugins: [
      ...
      { 
         name:'formula',
         plugin:jss_formula,
         options:{
            allowFormulaOnTypes: "myCustomEditor",
            allowAutocomplete: false,
            allowHelper: false,
         }
      },
      ...  
    ],
    ...
});

Example with custom formula

Add on header of page after loading plugin

<script>
   var MY_CUSTOM_FORMULA = function(a,b) {return a+b;};
   jspreadsheet.formulasDoc["MY_CUSTOM_FORMULA()"] = {syntax:"MY_CUSTOM_FORMULA(number,number)",about:"One custom formula for test"};
</script>

Initialize plugin on JSpreadsheet

jspreadsheet(document.getElementById('spreadsheet'), {
	...
	plugins: [
      ...
   	{ name:'formula', plugin:jss_formula },
      ...  
    ],
    ...
});

Copyright and license

Copyright GBonnaire.fr and Code released under the commercial License. This plugin required license of Repo.gbonnaire.fr