1. Home
  2. Forum
  3. JavaScript - EJ 2
  4. Custom function not working

Custom function not working

I tried using the example for custom functions in the EJ2 Spreadsheet documentation, but I get a #NAME? error.  What am I doing wrong?



My code is below:

<!DOCTYPE html>

<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no" />
<meta http-equiv="x-ua-compatible" content="ie=edge">


  <link rel='nofollow' href="dist/material-ej2all.css" rel="stylesheet" />
  <script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
</head>
<body>
Formula Test
<div>
<div id="SS"></div>
</div>
</body>
<script>


// Custom function to calculate percentage between two cell values.
function calculatePercentage(firstCell, secondCell) {
return Number(firstCell) / Number(secondCell);
}


var SS = new ej.spreadsheet.Spreadsheet({
height: 800,
scrollSettings: {
isFinite: true
},
sheets: [{
name: "Sheet1",
rowCount: 100,
colCount: 26
}],
});


SS.addCustomFunction(calculatePercentage, 'PERCENTAGE');
SS.appendTo('#SS');


SS.updateCell({value: 100	}, 'B2');
SS.updateCell({value: 25	}, 'B3');
SS.updateCell({formula: '=PERCENTAGE(B2,B3)' }, 'B4');
</script>
</html>

4 Replies
GK Gayathri KarunaiAnandam Syncfusion Team April 27, 2022 10:27 AM UTC

Hi John,


We have checked your code snippet. You have to use the spreadsheet related code snippets inside the spreadsheet initialization. We have prepared a sample in which we have added custom function in created event.

Please find the below API link,


API: https://ej2.syncfusion.com/javascript/documentation/api/spreadsheet/#created


Please find the below documentation to know about custom function,


https://ej2.syncfusion.com/javascript/documentation/spreadsheet/formulas/#user-defined-functions


We have the modified your code snippet:


<html lang="en">

<head>

        <!-- Essential JS 2 Spreadsheet's dependents material theme -->

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-base/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-inputs/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-buttons/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-splitbuttons/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-lists/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-navigations/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-popups/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-dropdowns/styles/material.css rel="stylesheet" type="text/css"/>

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-grids/styles/material.css rel="stylesheet" type="text/css"/>

        <!-- Essential JS 2 Spreadsheet material theme -->

        <link rel='nofollow' href=http://cdn.syncfusion.com/ej2/20.1.50/ej2-spreadsheet/styles/material.css rel="stylesheet" type="text/css"/>

  <script src=https://cdn.syncfusion.com/ej2/20.1.50/dist/ej2.min.js  type="text/javascript"></script>

</head>

<body>

Formula Test

<div>

<div id="SS"></div>

</div>

</body>

<script>

 

// Custom function to calculate percentage between two cell values.

function calculatePercentage(firstCell, secondCell) {

return Number(firstCell) / Number(secondCell);

}

 

var SS = new ej.spreadsheet.Spreadsheet({

height: 800,

scrollSettings: {

isFinite: true

},

sheets: [{

name: "Sheet1",

rowCount: 100,

colCount: 26

}],

created: function () {

SS.addCustomFunction(calculatePercentage, 'PERCENTAGE');

SS.updateCell({value: 100             }, 'B2');

SS.updateCell({value: 25               }, 'B3');

SS.updateCell({formula: '=PERCENTAGE(B2,B3)' }, 'B4');

}

});

 

SS.appendTo('#SS');

 

</script>

</html>


Please find the output below,


Please check and get back to us, if you need further assistance.


Regards,

Gayathri K


JO John April 27, 2022 01:30 PM UTC

Thank you.  In my first attempt before logging this ticket, my created event function was never being called.  So, that is why I put the code after the instantiation of SS.  

I do have a working example now, so thank you.

Before closing this ticket, can you explain the difference between these two scripts?

<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript">

<script src=https://cdn.syncfusion.com/ej2/20.1.50/dist/ej2.min.js type="text/javascript">

By the way, I have thoroughly enjoyed using the EJ2 controls in my current project.  Obviously, there's a bit of a learning curve, but for the most part it's been a real pleasure.  They look awesome.


JO John April 27, 2022 06:00 PM UTC

The real function that I need to code in my project takes a range as a parameter, e.g. "=MYSUM(B10:B20)".  Now, I have it working except that the formula doesn't recompute whenever a cell value within the B10:B20 range changes.  Therefore, the displayed value is "stale".  

Do custom functions not get added to the dependency tree so that they get recalculated automatically?



GK Gayathri KarunaiAnandam Syncfusion Team April 28, 2022 10:53 AM UTC

Hi John,


Thanks for your valuable feedback.


Query: Can you explain the difference between these two scripts? <script src=https://cdn.syncfusion.com/ej2/dist/ej2.min.js type="text/javascript"> <script src=https://cdn.syncfusion.com/ej2/20.1.50/dist/ej2.min.js type="text/javascript">


We have provided version specified CDN link after our Volume 1 2022 main release. We refresh our source every week Tuesday and the CDN versions also gets updated. So, please use version specified CDN links.


Query: I need to code in my project takes a range as a parameter, e.g. "=MYSUM(B10:B20)".


We have provided support to pass multiple range in custom function in our 20.1.50 weekly patch release. When you pass ranges in custom function, we pass the argument as a string which denotes the cell address. By using the address you can perform your actions. We have prepared a sample in which we have  created a custom function called MYSUM in which cell range values are added. Please find the below code snippet,


<script>

// Custom function to calculate sum on the values in range.

function calculateSum(firstCell) {

if (firstCell.includes(':')) { // cell range

        firstValue = 0;

        var sheet = SS.getActiveSheet();

        var indexes= ej.spreadsheet.getRangeIndexes(firstCell);

        for (var i = indexes[0]; i <= indexes[2]; i++) {

            for (var j = indexes[1]; j <= indexes[3]; j++) {

                //You can perform your actions here

               firstValue += Number(ej.spreadsheet.getCell(i, j, sheet).value);                                   

            }

        }                    return firstValue ;

 

    } else {

        firstValue = Number(firstCell);

      return firstValue

    }

}

 

 

var SS = new ej.spreadsheet.Spreadsheet({

created: function () {

SS.addCustomFunction(calculateSum, 'MYSUM');

SS.updateCell({value: 100             }, 'B2');

SS.updateCell({value: 25               }, 'B3');

SS.updateCell({formula: '=MYSUM(B2:B3)' }, 'B4');

}

});

 

SS.appendTo('#SS');

 

</script>


For your reference, please check the below sample,


Sample: https://www.syncfusion.com/downloads/support/forum/174660/ze/Cust_sprdt-774329859


Please check and get back to us, if you need further assistance.


Regards,

Gayathri K


Loader.
Up arrow icon