Identify origin of formula inputted

Identify origin of formula inputted

Posted by: jerome on 10 June 2020, 3:34 pm EST

• 

  jerome

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 10 June 2020, 3:34 pm EST

  Hi,

  Can we identify or distinguish if the inserted formula was from FormulaTextBox and not from active cell?

  Thanks in advance.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 11 June 2020, 9:18 am EST

  Hi Jerome,

  We are sorry but with the current API, it is not possible to distinguish if the added formula was added using the FormulaTextBox or SJS cell editor. Could you please explain more about your use case that why you need this functionality.

  Regards
  
  Sharad

• 

  jerome

  • Post Options:
  • Link
    Copy

  Posted 11 June 2020, 9:46 am EST

  Hi Sharad,

  Thank you for your reply and I understand it. Actually we are simulating some functionalities of MS Excel and here’s the task why I need to distinguish if the added formula was added using the FormulaTextBox:

  Task: Without using the INSERT FUNCTION dialogue box or the Formula bar, manually type a function into cell C2 that will give a result of “Fail” if the score in cell B2 is less than 70 and “Pass” if the score is equal to or greater than 70.

  If the end user made his formula through INSERT FUNCTION or FORMULA BAR, his answer would be incorrect.

  I hope this is clear and you can help me. Thanks in advance.

  Regards,

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 12 June 2020, 4:06 am EST

  Thanks for providing the additional information. If I understand correctly you need to restrict the user to enter the formula in particular Cell, this could be achieved by the following two workarounds.

  First:
  
  you may add keyup event to the formula text and check whether the value is formula Value and the active Cell is the restricted one. Please refer to the following code snippet and attached sample which demonstrates the same.

  document.getElementById("formulaTextBox").addEventListener("keyup", args => {
    let text = document.getElementById("formulaTextBox").value;
    let sheet = spread.getActiveSheet();
    let activeRow = sheet.getActiveRowIndex();
    let activeCol = sheet.getActiveColumnIndex();
    if (text[0] === "=" && activeRow === 3 && activeCol === 1) {
      document.getElementById("formulaTextBox").value = "";
      sheet.endEdit();
      alert("formula is allowed for this Cell");
    }
  });
  

  sample: https://codesandbox.io/s/gifted-wiles-mmkue?file=/src/index.js

  Second:
  
  You may handle the EnterCell event and disable the formula text box if the cell is restricted. Please refer to the following code snippet:

  spread.bind(GC.Spread.Sheets.Events.EnterCell, (e, args) => {
    let fbx = document.getElementById("formulaTextBox");
    if (args.row === 3 && args.col === 1) {
      fbx.setAttribute("disabled", true);
    } else {
      fbx.removeAttribute("disabled");
    }
  });
  

  API References:
  
  EnterCell: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Events~EnterCell_EV.html

  Let me know if this doesn’t fulfill your use case.

• 

  jerome

  • Post Options:
  • Link
    Copy

  Posted 12 June 2020, 10:22 am EST

  Hi Sharad,

  This is a great help. Thank you so much.