Print all the formula parameter arguments in custom function

Print all the formula parameter arguments in custom function

Posted by: jeff on 12 July 2020, 5:48 pm EST

• 

  jeff

  • Post Options:
  • Link
    Copy

  Posted 12 July 2020, 5:48 pm EST

  How do I print all the formula parameter arguments for my custom functions?

  I am looking for a flag that tells me

  1. A flag that tells me if it is a cell, range, formula, or table reference
  2. The string value of the cell, table, or range reference.

  The code mirror has an alert where I expect the reference.
  
  https://codesandbox.io/s/spreadjs-custom-formula-reference-d5gj2?file=/src/index.js

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 14 July 2020, 1:31 am EST

  Hi Jeff,

  We are sorry but current API, it is not possible to get this info. Could you please explain more about your use case that why you need this info?

  Regards
  
  Sharad

• 

  jeff

  • Post Options:
  • Link
    Copy

  Posted 16 July 2020, 3:53 am EST

  I want to add additional context to the calculation if it is a named range.

  Is there a way I can determine if the range is a table range using the row,col and rowcount.

• 

  jeff

  • Post Options:
  • Link
    Copy

  Posted 17 July 2020, 8:54 pm EST

  Is there a way to look up a table or named range reference with a cell index and range length?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 20 July 2020, 3:38 am EST

  You may use the sheet.tables.find() method to check the range belongs to a table. Please refer to the following code snippet and the updated sample:

  MYSUM.prototype.evaluate = function(p1) {
      if (typeof p1 === "object") {
        var arr = p1.toArray();
        var t1 = sheet.tables.find(p1.getRow(), p1.getColumn());
        var t2 = sheet.tables.find(
          p1.getRow() + p1.getRowCount() - 1,
          p1.getColumn() + p1.getColumnCount() - 1
        );
        if (t1 && t2 && t1.name() === t2.name()) {
          var tname = t1.name();
          alert("range has the table\ntable name: " + tname);
        }
  
        // alert('print the first parameter of the function "Table1[Credit]" here');
        return arr.reduce((a, b) => a + b, 0);
      }
      return "#VALUE!";
    };
  

  https://codesandbox.io/s/spreadjs-custom-formula-reference-ifml6?file=/src/index.js