How to group rows or columns in Excel using Office JS API

Question

I am converting a VSTO Excel add-in to a Web Excel add-in (using JavaScript API for Office). In my VSTO C# code I have the following line:

worksheet.Rows[rowStart + ":" + rowEnd].Group()

and a similar line for columns:

worksheet.Columns[colStart + ":" + colEnd].Group();

What is the equivalent API call for Office-JS? Could not find a relevant entry in the API Docs

Answer 1

I'm afraid that that kind of grouping is not yet supported in office.js. Please vote up the suggestion in the Office Developer Suggestion box: Grouping and ungrouping rows and columns.

Answer 2

See --> https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges-group

https://docs.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#excel-excel-range-group-member(1)

Here is how I did it. First thing I noticed was that I wanted certain groups to be collapsed by default. I accomplished this w/ hidden = true which combined w/ grouping, its more of a hide by default.

const Rng_Group_Obj = {
    "group": {
        "groupOptionString": {
            "ByRows": "ByRows",
            "ByColumns": "ByColumns",
        },
    },
    "columnHidden": true,
    "rowHidden": true,
}

---

function Do_Group_Rng(rng, ByColumns, ByRows, Opt_Hidden_By_Default) {
    if (ByColumns == true) {
        rng.group(Rng_Group_Obj.group.groupOptionString.ByColumns)
        if (Opt_Hidden_By_Default == true) { rng.columnHidden = Rng_Group_Obj.columnHidden }
    }
    if (ByRows == true) {
        rng.group(Rng_Group_Obj.group.groupOptionString.ByRows)
        if (Opt_Hidden_By_Default == true) { rng.rowHidden = Rng_Group_Obj.rowHidden }
    }
    return true;
}

---

var ws = context.workbook.worksheets.getActiveWorksheet()

var rng = ws.getRange("G:K")
Do_Group_Rng(rng,true,false,true)

var rng = ws.getRange("4:7")
Do_Group_Rng(rng, false, true, true)