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);
})();
Hello @Dario_BCS
Could you please provide a file sample where you run this macro? We will take a look at it.
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);
})();
Thank you for the file and the code! As far as I understand, you’re looking for a method similar to Api.GetDefName that you used in the code, am I right? Did you create this macro in another editor?
Hey @Alexandre, I am simply looking for OnlyOffice’s recommended way to get spreadsheet scoped defined names, not just worksheet scoped defined names.
I only get Api.GetDefName("known_name")
to return a spreadsheet name object if I pass it a name that I know to exist. I hoped your, or some other reader’s experience, might be different than mine. So something like GetDefName(), but it would need to return all spreadsheet scoped names.
Does it not seem odd to you there is oSheet.GetDefNames()
to get worksheet names, but not spreadsheet names?
Perhaps there is an undocumented arg to extend GetDefNames() to include spreadsheet names.
My cobbled macro arrived with the example file, right? Sorry I can’t confirm the editing chain, as I sometimes flip back and forth between the Editors’ Macros plugin and Notepad++ 8.7.5 64-bit. Why do you ask?
Hello @Dario_BCS
Thank you for the provided details! We are checking the situation.
Hello @Dario_BCS
Unfortunately, there’s no ready-to-go solution for this request. We have added it to internal tracksystem and we have started working on necessary API methods for the described scenario. I will update this thread once we have something to share.
Thanks @Alexandre, I am standing by.
1 Like