Get Last Column in Visible Views Index - Excel - Office-JS

Question

I'm trying to filter the last column on a worksheet but I can't seem to get the Index of the column. To be clear, I need the index relative to the worksheet, no the range. I used VisibleView to find the Column, but there may be hidden rows, so my plan is to then load that column via getRangeByIndexes but I need the relative columnIndex to the worksheet.

I've tried a bunch of variations of the below, but I either get Object doesn't support 'getColumn' or columnIndex is undefined

Note: In the below example I've hardcoded 7 as that will be the last column relative to the VisibleView (Columns and rows are already hidden), but I'd like this to by dynamic for other functions and just returnthe "last visible column index".

var ws = context.workbook.worksheets.getActiveWorksheet()
var visible_rng = ws.getUsedRange(true).getVisibleView()
visible_rng.load(["columnCount", "columnIndex"])
await context.sync();
console.log('visible_rng.columnIndex')
console.log(visible_rng.getCell(0,7).columnIndex)
console.log(visible_rng.getColumn(7).columnIndex)

Answer 1

Well this method seems a bit hacky, please share if you know a better way! But, first thing I found was that getVisibleView only metions rows in the Description.

Represents the visible rows of the current range.

I decided to try getSpecialCells and was able to load the address property. I then had to use split and get the last column LETTER and convert this to the Index.

I also wanted the columnCount but this wasn't working w/ getSpecialCells so I polled that from getVisibleView and return an Object relating to Visible Views that I can build on the function later if I need more details.

Here it is:

async function Get_Visible_View_Details_Obj(context, ws) {
    var visible_rng = ws.getUsedRange(true).getSpecialCells("Visible");
    visible_rng.load("address")
    var visible_view_rng = ws.getUsedRange(true).getVisibleView()
    visible_view_rng.load("columnCount")
    await context.sync();
    var Filter_Col_Index = visible_rng.address
    var Filter_Col_Index = Filter_Col_Index.split(",")
    var Filter_Col_Index = Filter_Col_Index[Filter_Col_Index.length - 1]
    var Filter_Col_Index = Filter_Col_Index.split("!")[1]
    if (Filter_Col_Index.includes(":") == true) {
        var Filter_Col_Index = Filter_Col_Index.split(":")[1]
    }
    var Filter_Col_Index = Get_Alpha_FromString(Filter_Col_Index)
    var Filter_Col_Index = Get_Col_Index_From_Letters(Filter_Col_Index)
    var Filter_Col_Index_Obj = {
        "last_col_ws_index": Filter_Col_Index,
        "columnCount": visible_view_rng.columnCount,
    }
    return Filter_Col_Index_Obj
}

---

Helper Funcs:

function Get_Alpha_FromString(str) {
    return str.replace(/[^a-z]/gi, '');
}

---

function Get_Col_Index_From_Letters(str) {
    str = str.toUpperCase();
    let out = 0, len = str.length;
    for (pos = 0; pos < len; pos++) {
        out += (str.charCodeAt(pos) - 64) * Math.pow(26, len - pos - 1);
    }
    return out - 1;
}