Setting values of range with an arbitrarily sized array in th Excel Javascript API?

Question

I have some nested array of data, for example something like [[1, 2], [2, 3, 4]], and I'd like to be able to update the values of cells on a specific worksheet with this data efficiently.

The problem I'm having is that the data isn't square, and so I'm currently:

1. looping over the data and getting each sub-array, and then checking this sub-array's length
2. getting a range object for a row that corresponds to the index in the outer array, that is the length of the sub-array I'm processing currently.
3. Setting the values of this single-row range object.

I'm wondering if there's some way to do all this value setting in a single step. How do I build a range object that a) will let me write outside it's bounds, or b) is very not rectangular? Or is there some other better way to approach this entirely?

Thanks so much for any help. I've been a bit stuck on figuring if this is the best solution for a few days. Thanks again!

Answer 1

I think you could still make it up a rectangle range, in your example, you may use [[1, 2, ,], [2, 3, 4,]]

async function setValues() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");

    const data = [[1,2,,], [2,3,4,]];

    const range = sheet.getRange("B5:D6");
    range.values = data;

    await context.sync();
  });
}

Here is the test result, please note the old value won't be replaced if you skipped it in range assignment.