Search code examples
ms-officeoffice-jsexcel-2016

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


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   |
-------------------------

Solution

  • 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.