Log in to start a topic

Custom functions and arguments

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

  • dave.welch

    • Post Options:
    • Link
      Copy

    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

  • dave.welch

    Posted 24 August 2020, 10:19 pm EST

    To add the data held within the dropdowns mentioned above are all stored in the database

  • avinash.pathak

    Posted 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

  • dave.welch

    Posted 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

  • avinash.pathak

    Posted 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

  • dave.welch

    Posted 31 August 2020, 1:26 am EST

    Thanks Avinash - This is great and solved the problem.

  • dave.welch

    Posted 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,

  • sharad.tomer

    Posted 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

  • dave.welch

    Posted 16 September 2020, 2:23 am EST - Updated 3 October 2022, 1:10 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

  • sharad.tomer

    Posted 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.

  • dave.welch

    Posted 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

    The “if” part always fails so, it “else” is executed.

    Is there anything I am missing?

    Please suggest.

    Thanks

  • sharad.tomer

    Posted 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

  • dave.welch

    Posted 17 September 2020, 9:22 pm EST - Updated 3 October 2022, 1:10 am 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

    The DOM generated -

    1075×330 38.9 KB

    As you can see the extra “” 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 “” has different attributes.

    994×310 23.3 KB

    Can you please suggest what am I doing wrong?

    Thanks.

  • sharad.tomer

    Posted 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

  • dave.welch

    Posted 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

  • sharad.tomer

    Posted 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" &&
      [b]curEditingFormulaEl.value[/b]
    ) {
      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

  • © 2023 MESCIUS inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.