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

score 2 · Accepted Answer · answered Apr 25 '19 at 01:26

2

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.

answered Apr 25 '19 at 01:26

Rick Kirkham

9,038
1
14
32

1

Thanks Rick. When I started the conversion from VSTO to office.js, I went in with the expectation of parity between the capabilities of the two frameworks, but this example proves me wrong. Can you think of a document that catalogs the gaps in office.js like this one? – July.Tech Apr 25 '19 at 14:28
2

I'm sorry. There's no list like that. The best you can do is look through the [Excel Requirement Sets](https://learn.microsoft.com/en-us/office/dev/add-ins/reference/requirement-sets/excel-api-requirement-sets) and the Excel JavaScript API reference: [Released Excel APIs](https://learn.microsoft.com/en-us/javascript/api/excel_release?view=office-js). – Rick Kirkham Apr 25 '19 at 20:00
Rick, you guys should update your SO Posts! Feel like I'm on the "bleeding edge" of new tech kinda :P, This is my 2nd post today w/ an "official" answer of "no thats not possible" that I get to answer/ with "Here is how" (obviously it wasn't at the time). See https://stackoverflow.com/a/71533702/5079799 – FreeSoftwareServers Mar 19 '22 at 02:53

FreeSoftwareServers · Answer 2 · 2022-03-26T01:04:59.400

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

https://learn.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)

This was the first time in the docs I saw you can get a range via `getRange` w/ numbers!! Getting a range via letters is so `its my first time writing a macro in VBA`. I've been using `getRangeByIndexes` but boy did that take some re-arranging my brain from VBA to get used to. Using #'s in `getRange` might just be perfect for me, though it will be reminiscent of my VBA days. Picture `getRange(Int_var + ":" + IntTwo_var)` ... Untested, wondering if I'll need Triple `"""` or some `Chr(#)` foo lol. Good old times in VBA. — FreeSoftwareServers, Mar 19 '22 at 03:21
I guess thats only rows tho, no cols. I'm getting used to `getRangeByIndexes` but it wasn't easy. — FreeSoftwareServers, Mar 19 '22 at 03:24
So....`getRange` isn't zero indexed like `getRangeByIndexes` so thats out. — FreeSoftwareServers, Mar 21 '22 at 02:44

score 1 · Answer 3 · answered Apr 08 '23 at 13:10

Excel's JavaScript API does now support grouping and ungrouping rows and columns, collapsing them (hiding their details), and expanding them (showing their details).

You can group rows (or columns) via the group method, ungroup them via ungroup, hide the details of the group (like using the [-] button to the left of the grouped rows) via hideGroupDetails, and show the details of the group (like clicking the [+] button to the left of the grouped rows) via showGroupDetails.

Assuming your rowStart and rowEnd are cell/range reference strings, then:

worksheet.getRange(`${rowStart}:${rowEnd}`).group(Excel.GroupOption.byRows);

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

3 Answers3

Linked