Official API or way to check if a cell is part of dynamic array

Question

I'm making an example to check the behaviour of JavaScript APIs over dynamic array.

I first enter a dynamic array =MUNIT(3) in Cell A1 of a sheet. Then, I realize that all the formulas[0][0], formulaR1C1[0][0] and formulaLocal[0][0] over Range A1 returns =MUNIT(3). However, all the formulas[0][0], formulaR1C1[0][0] and formulaLocal[0][0] over other cells like B1, A2 returns an empty string.

Thus, is there an official JavaScript API or an official way to check if a cell like B1 is within the range of a dynamic array?

PS: one way I guess is to check if the formula of a cell is empty and its value is non-empty, but I hope there is a better way.

Answer 1

Yes, you could try our beta API which will release soon, range.hasSpill and you can also get the parent by range.getSpillParent(), here is the sample code you could have a try.

  await Excel.run(async (context) => {
    // Check the current cell for any spill parents or spilling-to ranges.
    const currentRange = context.workbook.getSelectedRange();

    currentRange.load("hasSpill");
    await context.sync();
    if (currentRange.hasSpill)
    {
      const spillParent = currentRange.getSpillParentOrNullObject();
      spillParent.load("address");
      await context.sync();
      console.log(`The selected cell has a spill parent: ${spillParent.address}`);

    }
  });

Please note: to preview the beta API, you would need to reference the beta library on the CDN https://appsforoffice.microsoft.com/lib/beta/hosted/office.js