Office-js: Insert a new column in Excel and partially fill it with new data

Question

As excited as I am about the new Office.js, I seem to struggle to get it to perform even the most basic excel operations and have had extremely limited success finding answers on their api.

Anyways, how can I insert a new column into excel and partially fill it with data?

I have something like:

Excel.run(async (ctx) => { 
    let sheet = ctx.workbook.worksheets.getItem("Sheet1");
    let range = sheet.getRange("C4:C5").insert('right');
    range.values = [["foo"],["bar"]];
    await ctx.sync();   
}).catch(function (error) {
    console.log(error);
});

But the problem with this code is that is that it inserts the new data where I want it, to the right of column C, but instead of creating a new column, it just shifts over the data in all the other columns, but only on rows 4-5 which messes up the rest of my spreadsheet/table.

Instead, I'd like to first, insert a new column to the right of C, and then in that new column, assign it an array of values to partially fill it. Thanks.

---

Edit:
This is what I'm getting:

 _______________________
|  C  |  D  |  E  |  F  |
-------------------------
| ... | john| 4   | ... |
| ... | jeff| 87  | ... |
| ... | mary| 9   | ... |
| ... |  foo| cory| 54  |    <--- just shifts data in rows 4 and 5 over
| ... |  bar| eric| 33  |
| ... | paul| 5   | ... |
-------------------------
   ^----------------------------- column I have a reference/address too (column C)

This is what I want:

 _______________________
|  C  |  D  |  E  |  F  |
-------------------------
| ... |     | john| 4   |   
| ... |     | jeff| 87  |
| ... |     | mary| 9   |
| ... |  foo| cory| 54  |
| ... |  bar| eric| 33  |
| ... |     | paul| 5   |
-------------------------

Answer 1

I struggled with this, coming from VBA. The trick it took me a while to understand was that an entire column must be passed to .insert.

I made a small function which will return the new range relative to the old range and takes an optional column header and you can specify "column header up one".

This is because the range I am giving to the function is a single column in a table, but only the body, not including the header. The returning range is also the body, but the header has been set to one row above the rng.

Example:

function Get_InsertCol(rng, opt_colheader, opt_colheaderupone, opt_shiftdir) {
    if (opt_shiftdir == undefined) { opt_shiftdir = 'right' }
    rng.getColumn(0).getEntireColumn().getOffsetRange(0, 1).insert(opt_shiftdir);
    if (opt_colheader != undefined) {
        if (opt_colheaderupone == undefined) {
            rng.getRow(0).getOffsetRange(0, 1).values = opt_colheader
        } else {
            rng.getRow(0).getOffsetRange(-1, 1).values = opt_colheader
        }
    };
    return rng.getOffsetRange(0, 1);
}

---

export async function helloworld(event) {
    try {
        await Excel.run(async (context) => {
            //Start Func
            var ws = context.workbook.worksheets.getActiveWorksheet();

            ws.getRange("B2").values = "ColumnHeader"
            var rng = ws.getRange("B3:B5")
            rng.values = "testdata"

            var offsetrng = Get_InsertCol(rng,"ColHeader",true)
            offsetrng.select();

            await context.sync();
        });
    } catch (error) {
        console.error(error);
    }
    event.completed();
}

Result: