CODEWITHSUNDEEP
exceloffice-js
Rafael
Rafael

Reputation: 41

Excel Javascript API - join range or complex range selection

In Excel macro you can do something simple like:

Range("C8:G8,C12:H12,C19:I19").Value = 1

this gives me an opportunity to create a complex range selection and reduce the number of api calls and sync queue.

But when I do

const range = activeWorkSheet.getRange("C8:G8,C12:H12,C19:I19");       
range.format.fill.color = "yellow";

I get

InvalidArgument: The argument is invalid or missing or has an incorrect format.

There is also no getJoinRange or getUnionRange I could use.

Is there a workaround? I am having some performance issue as I do thing like format a row based on odd/even.

Upvotes: 3

Views: 438

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

There is a beta feature forthcoming, which will allow multi-area ranges. Its syntax is still TBD, it might be exactly what you wrote (with range being allowed to be a multi-area range), or perhaps we'll keep Range a single contiguous object and have parallel methods like worksheet.getMultiAreaRange("C8:G8, C12:H12, C19:I19) to do what you would like.

Also, what version of Office do you have and are you on Insider Fast, by any chance?

Re. performance, can you post your exact use-case as a minimal snippet? There may be some optimizations you can do, even barring multi-area ranges.

Update

If all you're doing is a 3x3 or a 5x5 (i.e., not something super-huge), you don't need multi-area support. You can just do:

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const rowCount = 5;
    const columnCount = 5;
    const range = sheet.getRangeByIndexes(0, 0, rowCount, columnCount);
    for (let row = 0; row < rowCount; row = row + 2) {
        range.getRow(row).format.fill.color = "purple";
    }

    await context.sync()
});

Upvotes: 1

Related Questions