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 |
-------------------------
I've had a hard time wrapping my head around the Office-js way of doing things, but I was able to get it mostly working with something like:
...
sheet = ctx.workbook.worksheets.getItem("Sheet1");
sheet.getRange("C:C").insert('right');
await ctx.sync();
let range = sheet.getRange("C4:C7");
range.values = [["foo"],["bar"]];
await ctx.sync();
...
I was originally hoping to add a new column to the right of the column I started with and add data in that new column, but since I don't actually hard code addresses in my code, this turned out to be easier and still operate close enough to what I wanted... but I am definitely still open to a better or more correct way of doing this.