Evaluate a formula by named ranges and JavaScript API

Question

I'm looking for a way to evaluate a formula by JavaScript API. I'm thinking if named ranges could help.

I made this gist in Script Lab, part of which is as follows. In this sample, we see formulas such as =100+200, =A2, =name2+100 can be indeed evaluated by named ranges; evaluated values can be accessed by the property value. However, =Sample!A2+100 cannot be evaluated well; the value of A2 seems to be considered as 0 here.

So does anyone know how to fix the code to make formulas like =Sample!A2+100 to be correctly evaluated?

$("#test").click(() => tryCatch(test));

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

    // setup cell values
    sheet.getRange("A2").values = [[3000]];
    await context.sync();

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

    // add names:
    sheet.names.add("name1", "=100+200"); // "value" evaluates well to 300
    sheet.names.add("name2", "=A2"); // "value" evaluates to "Sample!$A$2"
    sheet.names.add("name3", "=name2+100"); // "value" evaluates well to 3100
    sheet.names.add("name4", "=Sample!A2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.
    await context.sync();

    // show names:
    const namedItems = context.workbook.worksheets.getActiveWorksheet().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();
  });
}

Result:

Answer 1

If you update these two lines:

    sheet.names.add("name2", "=A2"); // "value" evaluates to "Sample!$A$2"
    sheet.names.add("name4", "=Sample!A2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.

to this:

    sheet.names.add("name2", "=$A$2"); // "value" evaluates to "Sample!$A$2"
    sheet.names.add("name4", "=Sample!$A$2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.

I show that the issue is fixed.