[Spreadsheet] How to retrieve reference of the cell from which the Custom Function is called?

Desktop editors
,
1

[ONLYOFFICE Desktop Editors - version 8.2.2.22 (x64 exe)]

I want to create a custom function (let’s call it SHEETNAME() ) that simply writes the name of the sheet in the cell where the formula is put.

For example:
I have a document with three sheets called “one”, “two” and “three” I write =SHEETNAME() in the cell A1 of each of the sheets and as a result I have in the A1 cells of the three sheets respectively the strings “one”, “two” and “three”

I wrote this custom function:

(function()
{
    /**
     *Returns the sheet name
     * @customfunction
     * @returns {string} The sheet name.
    */
    function sheetName() {
        return Api.GetActiveSheet().GetName();
    }
    Api.AddCustomFunction(sheetName);
})();

but it doesn’t work because it takes as sheet reference the active sheet name that is not always the name of the sheet where the cell is located. For example if I save and close the document when I’m in the sheet “two” on the next document opening it opens on sheet two and when it calculates the formulas of all the sheets it will always take “two” as Active Sheet. As a result I will have always “two” in the A1 cells also of the sheets one or three.

How can I retrieve the reference of the cell from which the custom function is located so that I can retrieve the sheet from it?

2

Hello,

I was able to insert the name of each sheet into A1 cell with this macro:

(function()
{
    var oSheets = Api.GetSheets();
    oSheets.forEach(function(sheet) {
        var oRange = sheet.GetRange("A1");
        var oName = sheet.GetName();
        oRange.SetValue(oName);
});
})();

Please let me know if this helps.

3

So if the custom function is added like this, =SHEETNAME() inserts sheet name to A1 cell of each sheet.

(function()
{
/**
     *Returns the sheet name
     * @customfunction
     * @returns {string} The sheet name.
    */
    function sheetName() {
        var oSheets = Api.GetSheets();
        oSheets.forEach(function(sheet) {
            var oRange = sheet.GetRange("A1");
            var oName = sheet.GetName();
            oRange.SetValue(oName);
        });
    }
    Api.AddCustomFunction(sheetName);
})();
4

Hi Carl,
thanks for your answer, but this is not exactly what I want to obtain.
I want to create a custom function that adds the sheet name in the same cell in which the function is written, not just in the A1 of each and every sheet.
Maybe my example was not totally clear.

5

I see. You can either reference the sheet by its name (Api.GetSheet("Sheet1") or by selecting it so that it becomes the active sheet (Api.GetActiveSheet()). Indeed, after reopening the file the active sheet is the one where the focus was when the file was closed, so you need to select the required sheet and use the recalculation feature so that the function result is displayed correctly.