Custom functions and arguments

Posted by: dave.welch on 24 August 2020, 9:58 pm EST

  • Posted 24 August 2020, 9:58 pm EST

    Hello,
    I am currently evaluating Spreadjs. I have created a custom function, however I want the user to be able to select the parameters/arguments from a few dropdown menus (looks similar to the function arguments popup). Is there a way of creating this arguments popup for my custom function so that when a user created the function they can create / edit the arguments in the popup?
    Thanks,
    Dave
  • Replied 24 August 2020, 10:19 pm EST

    To add the data held within the dropdowns mentioned above are all stored in the database
  • Replied 26 August 2020, 5:39 pm EST

    Hi Dave,

    You may create and popup box with dropdown and on submit you need to apply the formula with selected argument from the dropdowns. Please refer to the following code snippet which demonstrates a simple custom function applied on the submit button using bootstrap modal.

    document.getElementById("submit").addEventListener("click", () => {
    let name = document.getElementById("name").value;
    let name2 = document.getElementById("name2").value;

    if (name || name2) {
    let formula = `=Name(` + `"` + name + `"` + `,"` + name2 + `")`;
    let sheet = spread.getActiveSheet();

    sheet
    .getCell(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex())
    .formula(formula);
    }
    $("#myModal").modal("hide");
    });


    sample: https://codesandbox.io/s/recursing-aryabhata-j62l6?file=/src/index.js:127-581


    Regards

    Avinash
  • Replied 27 August 2020, 1:34 am EST

    HI Avinash,

    Thank you for the example and sample code.

    I have a small question regarding the RETURN statement.

    I have created a custom function

    var valueFromDB = 0;
    addCustomFunction();

    function addCustomFunction() {
    function MyFunction() {
    this.name = "MYFUNCTION";
    this.maxArgs = 5;
    this.minArgs = 5;
    }

    MyFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
    MyFunction.prototype.evaluate = function(arg1, arg2, arg3, arg4, arg5) {
    /* I call a function which does an AJAX call to DB to get the value*/
    getUnitValue(params);

    /* I need to wait here before the getUnitValue function returns value from DB*/
    return valueFromDB;
    };

    var myFunction = new MyFunction();
    activeSheet.addCustomFunction(myFunction);
    }

    function getUnitValue(params){
    /* I call an AJAX function to get the value from DB */
    /* then set the value */
    valueFromDB = 100;
    }


    How do I wait for my function to finish before I return the value?

    Thanks
  • Marked as Answer

    Replied 27 August 2020, 9:35 pm EST

    Hi Dave,

    For this, you may use the Async custom function. Please refer to the following code snippet and attached sample which demonstrates the same.

    function addCustomFunction() {
    var asyncSum = function () {};
    asyncSum.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction(
    "ASUM",
    1,
    255
    );
    asyncSum.prototype.defaultValue = function () {
    return "Loading...";
    };
    asyncSum.prototype.evaluateAsync = function (context) {
    // use setTimeout to simulate server side evaluation
    // in read world it maybe an ajax post to server for evaluation
    var args = arguments;
    setTimeout(function () {
    var result = 0;
    for (var i = 1; i < args.length; i++) {
    result += args[i];
    }
    result *= 2;
    context.setAsyncResult(result);
    }, 2000);
    };
    spread.getActiveSheet().addCustomFunction(new asyncSum());
    }


    sample: https://codesandbox.io/s/unruffled-feather-phj3q?file=/src/index.js:418-1147


    API References:

    Async Function Demo: https://www.grapecity.com/spreadjs/demos/features/calculation/async-function#demo_source_name
  • Replied 31 August 2020, 1:26 am EST

    Thanks Avinash - This is great and solved the problem.
  • Replied 15 September 2020, 12:21 am EST

    Hi Avinash,

    I am able to get the custom function working correctly (Thanks for your example).
    I call the modal page on click of button to enter my params.
    Suppose on the current cell, I already have another function defined e.g "=SUM(A1 + ". and then click the button to add my formula.
    Now I want my custom function formula from modal page to append to the already current formula in cell.
    After I enter my params in modal page on submit the formula should be "=SUM(A1 + MYFORMULA(1,2,3) )"

    var formula = "myFormulaFromModalPage";
    var r = activeSheet.getActiveRowIndex(),
    c = activeSheet.getActiveColumnIndex(),
    currValue = activeSheet.getValue(r, c); // To get the current formula in the cell already
    console.log(currValue); // This is blank
    activeSheet
    .getCell(r, c)
    .formula(currValue + formula);


    Can you please suggest what I am doing wrong? How to get this working?

    Thanks,
  • Replied 15 September 2020, 7:12 pm EST

    Hi Dave,

    You may use the getFormula method to get the cell formula. Please refer to the following code snippet:
    var formula = activeSheet.getFormula(r, c);
    console.log(formula);


    API reference:
    https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~getFormula.html

    Regards
  • Replied 16 September 2020, 2:23 am EST

    Hi Sharad,
    I have created an example of what I am doing here.
    https://codesandbox.io/s/strange-poincare-9pnum?file=/src/index.js

    Suppose in cell A8 I type "=SUM( B2 + " (image 1) and click APply formula to Active Cell Button, it opens up a popup and I enter my details. When I close the modal, it should append to "=SUM(B2 + ".


    But it is not happening.
    When I click the button to open modal, the cell loses its value (image 2)



    Please suggest how to append correctly.

    Thanks
  • Replied 16 September 2020, 10:07 pm EST

    Hi Dave,

    Thank you for the sample, I've updated it, please refer to the same.
    https://codesandbox.io/s/serene-bhaskara-4hlo2?file=/src/index.js
    Don't forget to notice the gcuielement="gcEditingInput" attribute on the button and popup. This attribute is used to prevent the edit mode from ending when you click the button.
  • Replied 17 September 2020, 1:49 am EST

    Hi Sharad,
    Thanks for your answer.
    I followed your steps and added gcuielement="gcEditingInput" on both my modal popup and button.

    My HTML snippet for button is
    <button class="my-class" gcuielement="gcEditingInput" onclick="void(0);" type="button" id="add-function" title="Add Formula at the Selected Cell" aria-label="Add Formula at the Selected Cell">
    Add Custom Function</button>

    My Modal Popup HTML is
    <div id="customFormula" class="t-DialogRegion js-modal js-draggable js-resizable js-dialog-size600x400" gcuielement="gcEditingInput" style="width: auto; min-height: 0px; max-height: none; height: 351px;">

    -- Inner HTML--

    </div>


    On Submit button code
    var formula = "MY CUSTOM FORMULA" // Hardcoded as of now
    var r = activeSheet.getActiveRowIndex(),
    c = activeSheet.getActiveColumnIndex();
    var curEditingFormulaEl = document.querySelector(
    '[gcuielement="gcSpread"] [gcuielement="gcEditingInput"]'
    );
    console.log(curEditingFormulaEl); /* This will not have any innerText attribute*/
    if (curEditingFormulaEl && curEditingFormulaEl.innerText) {
    var curFormulaText = curEditingFormulaEl.innerText;
    console.log("Here VP == " + curFormulaText); /* This is NULL */
    curEditingFormulaEl.innerText = curFormulaText + formula;
    curEditingFormulaEl.focus();
    } else {
    activeSheet
    .getCell(r, c)
    .formula(formula); // Append the new Formula
    }


    The console.log for curEditingFormulaEl
    <textarea tabindex="-1" autocomplete="off" contenteditable="true" style="font: 14.6667px Calibri; outline: none; resize: none; border: none; padding: 1px; vertical-align: top; min-height: 0px; box-sizing: content-box; background: transparent; overflow-wrap: normal; overflow: hidden; text-align: left; width: 66px; height: 18px; max-width: 693px; float: none;"></textarea>

    The "if" part always fails so, it "else" is executed.
    Is there anything I am missing?

    Please suggest.

    Thanks
  • Replied 17 September 2020, 4:50 pm EST

    Hi Dave,

    It seems like querySelector is selector is selecting the wrong element. Could you please share a sample replicating the issue so that we could further investigate it and assist you accordingly

    Thank you
  • Replied 17 September 2020, 9:22 pm EST

    Hi Sharad,
    I have created a sample example here -
    https://codesandbox.io/s/jolly-worker-ipxqq?file=/index.html

    Please ignore the modal popup. Could not get that working. You can see a button at the bottom when you click "Apply Formula" button.
    Here the code seems to work.

    But this is not happening in my case.
    My div for grid is
    <div id="ss" style="width:100%;height:600px"></div>


    The DOM generated -


    As you can see the extra "<textarea>" component generated with gcuielement="gcEditingInput". Not sure why.
    This is the queryElement which is getting picked up.

    This is not present in the example on codesandbox. The "<textarea>" has different attributes.


    Can you please suggest what am I doing wrong?

    Thanks.
  • Replied 20 September 2020, 11:19 pm EST

    Hi Dave,

    It seems that in your application editor type is set to textArea for text cell type. In this case, you may check if the tagname is textarea and if yes then instead of innerText property use the value property. Please refer to the following code snippet and the updated sample:
    if (curEditingFormulaEl && curEditingFormulaEl.tagName === "TEXTAREA") {
    var curValue = curEditingFormulaEl.value;
    curEditingFormulaEl.value = curValue + formula;
    curEditingFormulaEl.focus();
    } else if (curEditingFormulaEl && curEditingFormulaEl.innerText) {
    var curFormulaText = curEditingFormulaEl.innerText;
    curEditingFormulaEl.innerText = curFormulaText + formula;
    curEditingFormulaEl.focus();
    } else {
    sheet
    .getCell(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex())
    .formula(formula); // Append the new Formula
    }

    https://codesandbox.io/s/gallant-leakey-p8o4t?file=/src/index.js

    Regards
  • Replied 21 September 2020, 8:40 pm EST

    Hi Sharad,
    Thanks for your examples.

    Just a couple questions -
    Below is my code
    if (curEditingFormulaEl && curEditingFormulaEl.tagName === "TEXTAREA") {
    var curValue = curEditingFormulaEl.value;
    curEditingFormulaEl.value = curValue + formula;// Append the new Formula
    curEditingFormulaEl.focus();
    } else if (curEditingFormulaEl && curEditingFormulaEl.innerText) {
    var curFormulaText = curEditingFormulaEl.innerText;
    curEditingFormulaEl.innerText = curFormulaText + formula; // Append the new Formula
    curEditingFormulaEl.focus();
    } else {
    activeSheet
    .getCell(r, c)
    .formula("=" + formula);
    }


    1. In this if I have something ( like "=SUM(A1 + ") in my cell before I click on button, this appends the function. But it doesnot calculate the value immediately. The Cell stays there with the full function text. I have to press "enter" for it to calculate.
    Is there anything that can be done to calculate the value immediately?

    2. If I dont have anything in cell and click button to get my function - it doesnot go into ELSE part at all. and the function returned is not held in cell. Am I missing something.
    As a workaround for this I enter "="(something in cell) in the cell and click button for function.

    Please suggest.

    Thanks
  • Replied 22 September 2020, 5:11 pm EST

    Regarding #1, You may call sheet.endEdit() to exit the edit mode and calculate the formula result.

    Regarding #2, You may update the if condition as follows:
    if (
    curEditingFormulaEl &&
    curEditingFormulaEl.tagName === "TEXTAREA" &&
    curEditingFormulaEl.value
    ) {
    var curValue = curEditingFormulaEl.value;
    curEditingFormulaEl.value = curValue + formula;
    curEditingFormulaEl.focus();
    sheet.endEdit();
    } else if (curEditingFormulaEl && curEditingFormulaEl.innerText) {
    var curFormulaText = curEditingFormulaEl.innerText;
    curEditingFormulaEl.innerText = curFormulaText + formula;
    curEditingFormulaEl.focus();
    sheet.endEdit();
    } else {
    sheet
    .getCell(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex())
    .formula(formula); // Append the new Formula
    }

    Updated sample: https://codesandbox.io/s/dazzling-breeze-ekrgv?file=/src/index.js
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels