Office.js Excel Range getRow gives wrong range address

Question

In my Excel tab pane addin I want to get some rows from range based only on active selected rows.

Initially my data located in "Sheet1!H5:I16", and I select in worksheet only rows without tables f.e. "Sheet1!8:10"

For detect selected rows I get selected range and load some options:

Excel.run(function (ctx) { 
    var selectedRange = ctx.workbook.getSelectedRange();
    selectedRange.load(['address', 'rowIndex', 'rowCount']);
    return ctx.sync().then(function() {
            console.log(selectedRange.address);
    });
});

It gives me address "Sheet1!8:10" and selectedRange.rowIndex=7 and selectedRange.rowCount=3

Now I subtract my range row from selection:

var subsIndex = selectedRange.rowIndex - 5 // 5 is start index H5

And here I have my row inside data range rowIndex, it is 3.

Again: for whole worksheet my rowIndex is 7, for selection, intersected my data range, row index is 3.

Now I want to get first row of inside data range rowIndex(it must be Sheet1!H8:I10) from whole data Sheet1!H5:I16 based only on selected rows(selectedRange.rowIndex=7 and selectedRange.rowCount=3):

Excel.run(function (ctx) { 
    var sheetName = "Sheet1";
    var rangeAddress = "H5:I16"; // my whole data range
    var range = ctx.workbook.worksheets.getItem(sheetName).getRange(rangeAddress).getRow(subsIndex ); //try to get row number 3
    range.load('address');
    return ctx.sync().then(function() {
        console.log(range.address); // gives me Sheet1!H5:I16 ??? 
    });
});

Why getRange(3) gives me just whole range address Sheet1!H5:I16 insted address of row by index 3: Sheet1!H8:I8 ?

Orange: data range

Red: Needed row

I tried your sample, but the output is Sheet1!H8:I8, which is exactly what I would have expected? — Philip Rueker

Michael Zlatkovsky - Microsoft Michael Zlatkovsky - Microsoft · Accepted Answer · 2016-11-17T17:14:43

I'm not sure if I completely followed your scenario. But I think something like the following will work (and in fact, doesn't require you to load row indexes, etc.):

return Excel.run(function (ctx) {
    var selection = ctx.workbook.getSelectedRange();
    selection.format.fill.color = "purple";
    var range = selection.getRow(3);
    range.format.fill.color = "yellow"; 
    range.load('address');
    return ctx.sync()
        .then(function() {
            console.log(range.address);
        })
        .then(ctx.sync);
}).catch(console.log);

Office.js Excel Range getRow gives wrong range address

1 Answers