Forums Home / Spread / SpreadJS
Posted by: dean.kinnear on 13 March 2023, 5:21 am EST
dean.kinnear
Posted 13 March 2023, 5:21 am EST
In Excel custom functions, you can get the function’s parent cell address.
e.g.
Public Function Test() As String
Test = Application.Caller.Address
End Function
Is there a way get the caller’s address?
If had a function in cell A1, the custom function that cell in Excel would return “$A$1”
ankit.kumar
Posted 14 March 2023, 2:53 pm EST
Hello Dean,
You can create a custom function which returns the address of its parent cell. Below steps explain how you can create such custom function. First, create a custom function by setting a function’s prototype to the object of GC.Spread.CalcEngine.Functions.Function() class.
After creating the custom function, you need to override its method isContextSensitive() to get the function’s context in the parameter of evaluate method of function. In the evaluate() method of the function, you can get range formula using GC.Spread.Sheets.CalcEngine.rangesToFormula() method and return the range string(parent cell’s reference).
Please refer to the code snippet and attached sample for further understanding
// creating a custom function
function Custom() {
this.name = "CUSTOM";
this.minArgs = 0;
this.maxArgs = 0;
}
Custom.prototype = new GC.Spread.CalcEngine.Functions.Function();
Custom.prototype.isContextSensitive = function () {
return true;
}
Custom.prototype.evaluate = function (context) {
let row = context.row;
let col = context.column;
let range = sheet.getRange(row, col, 1, 1);
let formula = GC.Spread.Sheets.CalcEngine.rangesToFormula([range]);
return formula;
}
// adds function to the spread
spread.addCustomFunction(new Custom());
sheet.setFormula(2, 2, 'CUSTOM()');
Please let us know if you still face any issues.
Doc reference
GC.Spread.CalcEngine.Functions.Function class: https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.CalcEngine.Functions.Function
spread.addCustomFunction():https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Workbook#addcustomfunction
GC.Spread.Sheets.CalcEngine.rangesToFormula(): https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#rangestoformula
Regards,
Ankit
dean.kinnear
Posted 14 March 2023, 10:44 pm EST
Hi Ankit,
This is close, but how do I get the parent sheet object of the custom function?
In the sample, it assumes sheet is the first sheet. But I need the sheet object of the context so I know which sheet the function is on.
Thanks!
Dean
avinash.pathak
Posted 15 March 2023, 7:39 pm EST
Hello,
You can get the sheet object inside the evaluate method of the custom function. For that, the custom function’s isContextSensive() method should return true. When isContextSensitive() method return true, the context of function is provided in the arguments of the evaluate method of the custom function. You can get sheet object from the context of the function with the statement context.source.getSheet().
Please refer to the code snippet and attached sample for further understanding.
// creating a custom function
function Custom() {
this.name = “CUSTOM”;
this.minArgs = 0;
this.maxArgs = 0;
}
Custom.prototype = new GC.Spread.CalcEngine.Functions.Function();
Custom.prototype.isContextSensitive = function () {
return true;
}
Custom.prototype.evaluate = function (context) {
let sheet = context.source.getSheet();
let row = context.row;
let col = context.column;
let range = sheet.getRange(row, col, 1, 1);
let formula = GC.Spread.Sheets.CalcEngine.rangesToFormula([range]);
return
${formula}, ${sheet.name()}GC.Spread.CalcEngine.Functions.Function class: GC.Spread.CalcEngine.Functions.Function class: https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.CalcEngine.Functions.Function
Regards,
Avinash
dean.kinnear
Posted 15 March 2023, 11:43 pm EST
One last thing. I have a custom function “DCREFERSTOSHEETNAME”
The function can have zero or one argument(s). I
If there is an argument, I need to get the reference’s parent sheet and address (see below):
RefersToRangeFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
RefersToRangeFunction.prototype.isContextSensitive = function () {
return true;
}
RefersToRangeFunction.prototype.acceptsReference = function (argIndex) {
return argIndex = 1 ? true : false;
};
function RefersToRangeFunction() {
this.name = ‘DCREFERSTOSHEETNAME’;
this.maxArgs = 1;
this.minArgs = 0;
}
RefersToRangeFunction.prototype.evaluate = function (context, reference) {
if (reference != null) {
//How to get worksheet name and address from reference object?
} else { var row = context.row; var col = context.column; var sheet = context.source.getSheet(); let range = sheet.getRange(row, col, 1, 1); var sheetName = sheet.name(); var address = GC.Spread.Sheets.CalcEngine.rangesToFormula([range]); return "'" + sheetName + "'!" + address; }
}
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction(‘DCREFERSTOSHEETNAME’, new RefersToRangeFunction());
Thanks!
Dean
avinash.pathak
Posted 20 March 2023, 1:30 am EST
Hello,
As I can understand, you want to get the Sheet name and cell range reference when there is one argument provided in the custom function DCREFERSTOSHEETNAME. When there is one argument, you can get the argument in reference form by returning true from function.acceptsReference() method. By doing this, you will get the reference object in the evaluate() method instead of argument value. You can find the range of reference object using reference.getRow(), reference.getColumn(), reference.getRowCount(), and reference.getColumnCount() methods. To convert the range object of cells to formula string, you can utilize GC.Spread.Sheets.CalcEngine.rangesToFormula() method.
You may refer to the code snippet and attached sample which explains the same.
RefersToRangeFunction.prototype.evaluate = function (context, reference) {
if (reference != null) {
//How to get worksheet name and address from reference object
let row = reference.getRow();
let col = reference.getColumn();
let rowCount = reference.getRowCount();
let colCount = reference.getColumnCount();
let sheet = context.source.getSheet();
let range = sheet.getRange(row, col, rowCount, colCount);
let formulaString = GC.Spread.Sheets.CalcEngine.rangesToFormula([range]);
return “'” + sheet.name() + “'!” + formulaString;
}
else {
var row = context.row;
var col = context.column;
var sheet = context.source.getSheet();
let range = sheet.getRange(row, col, 1, 1);
var sheetName = sheet.name();
var address = GC.Spread.Sheets.CalcEngine.rangesToFormula([range]);
return “'” + sheetName + “'!” + address;
}
}
Sample: https://jscodemine.grapecity.com/share/k8kb06g3Uk_aTHIgEL0q5Q/?defaultOpen={“OpenedFileName”%3A[“%2Findex.html”%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Findex.html"}
Doc reference
GC.Spread.Sheets.CalcEngine.rangesToFormula(): https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#rangestoformula
Regards,
Avinash
dean.kinnear
Posted 20 March 2023, 2:16 am EST
Perfect. Exactly what I was looking for. Is there documentation on the reference and context object’s methods, and properties? I didn’t find any.
Thanks!
avinash.pathak
Posted 20 March 2023, 3:27 pm EST
Hello,
We would like to inform you that the function context and arguments reference are the internal parts of SpreadJS. which is why this is not documented. We are sorry for the inconvenience.
Please let us know if you face any difficulties. We will make sure to help you accordingly.
Regards,
Avinash
dean.kinnear
Posted 20 March 2023, 10:24 pm EST
Gotcha. If I used these objects and methods since they are not documented, is there a chance they can be deprecated in the future?
If so, is there any way they can be public instead of internal?
Thanks,
Dean
avinash.pathak
Posted 22 March 2023, 4:43 am EST
Hello,
We have made an enhancement request on your behalf. We will update you regarding this as we get any information from the team. The internal ID for this issue will be SJS-17508
Regards,
Avinash
