Get Workbook Scoped Defined Names

Desktop editors
1

OS version: Win 10
App version: 8.2.2.22 (x64 exe)
Downloaded from: ONLYOFFICE website

This macro gets all the worksheet scoped defined names in a workbook, but how do we get the workbook scoped defined names?

/**
 * Get_Defined_Names, 2401 could only get worksheet scoped names; not workbook scoped
 */
(function()
{
    const getBookNames = true;
    let names = [];
    let log = "Export Defined Names:";

    const getSheetDefNames = (function (oSheet)
    {
        let props = [];
        let sheetDefNames = [];
        sheetDefNames = oSheet.GetDefNames();
        sheetDefNames.forEach (function (definedName) {
            let name = definedName.GetName();
            if (undefined === name)  return;  // continue
            let formula = definedName.GetRefersTo();
            log += `\n ${name}, ${formula}`;
            names.push( [name, formula] );
        });
    });

    if (getBookNames) {
        let sheets = [];
        sheets = Api.GetSheets();
        sheets.forEach (function (sheet) {
            getSheetDefNames(sheet);
        });
    } else {
        const aSheet = Api.GetActiveSheet();
        getSheetDefNames(aSheet);
    }
    console.log(log);
    console.log(names);
})();
2

Hello @Dario_BCS
Could you please provide a file sample where you run this macro? We will take a look at it.

3

Hello @Alexandre,
Thank you, and here is an example spreadsheet (a.k.a. workbook) with two differently scoped defined names, one spreadsheet(workbook) and the other worksheet.
Defined Names.xlsx (9.4 KB)
The spreadsheet includes the updated and renamed macro to prove we could get a workbook scoped defined name, if that name was already known, with Api.GetDefName("<name>");

Updated macro follows

 * my_Get_Defined_Names, 2401 could only get worksheet scoped name; not workbook scoped names
 */
(function()
{
    const getBookNames = true;
    const knownBookScopedDefName = "workbook_defined";
    const defNames = [];
    let log = "Export Defined Names:";

    const my_Get_Defined_Names = (function (oSheet)
    {
        const sheetDefNames = oSheet.GetDefNames();  // array
        sheetDefNames.forEach ((oBookDefName) => {
            let name = oBookDefName.GetName();
            if (undefined === name)  return;  // continue
            let formula = oBookDefName.GetRefersTo();
            // GetRefersToRange().GetAddress();  // too like formula 
            log += `\n ${name}, ${formula}`;
            defNames.push( [name, formula] );
        });
    });

    if (getBookNames) {
        // perhaps need a workbook/spreadsheet method GetDefNames
        //let oBookNames = Api.GetDefName ();  // returned undefined
        // Confirm we can get a book scoped defined name
        const oBookNames = Api.GetDefName (knownBookScopedDefName);
        Object.keys(oBookNames).forEach(key => {
            console.log(key, oBookNames[key]);
        });

        let sheets = [];
        sheets = Api.GetSheets();
        sheets.forEach ((sheet) => {
            my_Get_Defined_Names(sheet);
        });
    } else {
        const aSheet = Api.GetActiveSheet();
        my_Get_Defined_Names(aSheet);
    }
    console.log(log);
    console.log(defNames);
})();