Evaluation by named ranges by JavaScript API does not work for certain formulas

[chengtie]

I'm trying to rely on named range to evaluate a formula by JavaScript API. Although this approach works for most formulas, I found that the evaluated results are not correct for certains formulas such as certain formulas with INDEX.

I made this gist in Script Lab, part of which is as follows. In this sample, we define several named ranges with =INDEX({1,2,3;4,5,6},{1;2},1), =INDEX(Sheet1!$B$3:$B$8,3), =INDEX(Sheet1!$B$3:$B$8,{3,4}) by JavaScript API. They work well in formulas in the workbook. However, their evaluated values ([[1]], null, null) returned by JavaScript API are not correct in the code.

Does anyone know why it does not work for those formulas? Are there any other APIs we could use?

I have certain control over the formulas, for instance, I could transform them. Is it possible to transform those formulas to formulas on which JavaScript APIs for evaluation work?

Are there any workarounds?

async function test() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // setup cell values
    sheet.getRange("B3:B8").values = [[1],[2],[3],[4],[5],[6]];
    sheet.getRange("F3").formulas = [["=name1"]]
    sheet.getRange("F7").formulas = [["=name2"]]
    sheet.getRange("F9").formulas = [["=name3"]]
    await context.sync();

    // clean names:
    var name1 = context.workbook.names.getItemOrNullObject("name1");
    var name2 = context.workbook.names.getItemOrNullObject("name2");
    var name3 = context.workbook.names.getItemOrNullObject("name3");
    name1.load();
    name2.load();
    name3.load();
    await context.sync();
    if (name1.value) name1.delete();
    if (name2.value) name2.delete();
    if (name3.value) name3.delete();
    await context.sync();

    // add names:
    context.workbook.names.add("name1", "=INDEX({1,2,3;4,5,6},{1;2},1)"); 
    context.workbook.names.add("name2", "=INDEX(Sheet1!$B$3:$B$8,3)"); 
    context.workbook.names.add("name3", "=INDEX(Sheet1!$B$3:$B$8,{3,4}"); 
    await context.sync();

    // show evaluated values:
    var name1 = context.workbook.names.getItemOrNullObject("name1");
    var name2 = context.workbook.names.getItemOrNullObject("name2");
    var name3 = context.workbook.names.getItemOrNullObject("name3");
    const avs1 = name1.arrayValues.load();
    const avs2 = name2.arrayValues.load();
    const avs3 = name3.arrayValues.load()
    await context.sync();
    console.log("name1: ", avs1.values)
    console.log("name2: ", avs2.values)
    console.log("name3: ", avs3.values)

    // show names:
    const namedItems = context.workbook.names.load();
    await context.sync();
    console.log("This worksheet contains " + namedItems.items.length + " named items.");
    for (let i = 0; i < namedItems.items.length; i++) {
      console.log(JSON.stringify(namedItems.items[i])) + "\n";
    }
    await context.sync();
  });
}

[guliums]

Hi @chengtie, thanks for reaching us and the great detailed description.

When INDEX returns a single cell, it could work if we change the formula to use VALUETOTEXT function to convert the reference to value. name2 = "=VALUETOTEXT(INDEX(Sheet1!$B$3:$B$8,3))"

if INDEX returns an array, unfortunately based on my investigation, we does not support this kind of named item to get its range as we only support simple range like =A1 currently. You might go to Microsoft 365 Developer Platform Ideas Forum to submit a new feature request. As for workaround, I could come up for now is to use a real cell to set the formula and let Excel calculate the result then get the result using range.values API like this:

await Excel.run(async (context) => { const tmpSheet = context.workbook.worksheets.add(); let r = tmpSheet.getRange("A1"); r.values = [["=name3"]]; r.load("hasSpill"); await context.sync();

if (r.hasSpill)
{
  r = r.getSpillingToRange();

}
r.load("values");

await context.sync();
console.log(r.values);

tmpSheet.delete();
await context.sync();

});

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[ghost]

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.

[jkpieterse]

I'd like to have this fixed. I have published an add-in to manage range names (Name Manager for Excel). One of its features I've implemented is selecting the range a name points to. This currently fails for range names which use a formula to calculate a reference such as INDEX.