Excel columns shifting incorrectly when deleting a column.

[WadeBenz]

We have several excel spread sheets where we need to have a dynamic number of columns. Usually we do this by adding an additional columns and removing the ones that are not needed. We have now run into a scenario where when we delete a column the data in the cells to the right of the column shift unexpectantly to the left. Please note that this is just a simple example that explains the issue.

Example
We have this spread sheet:

We then have something like this on the template to remove the column:

With this standard removeColumn from the forum:

function removeColumn(index, opts) {
    // just to get an idea whats inside
    console.log(JSON.stringify(opts.data.root.$xlsxTemplate['xl/worksheets/sheet1.xml']))
    const rows = opts.data.root.$xlsxTemplate['xl/worksheets/sheet1.xml'].worksheet.sheetData[0].row
     for (const r of rows) {
        r.c.splice(index, 1)
        for (let c of r.c) {
            console.log (c.$.r);
            // let excel recalculate cell names
            delete c.$.r
        }
    }    
}

So in this example we are just trying to remove column B (ordinal 1). When this run we get this as a result.

What I would expact is this.

I am not sure what happens here, Everything gets compressed to the left and not sure what happens to the value of E3.

I thought maybe it had to do with the delete c.$.r function call so I change it to delete c.r to see what would happen. I get this for a result them:

It removed the values in column B, but did not delete the column. Also it still completely lost the value of E3.

Where can I find more documentation on what the delete c.$.r does or any other help on how to remove columns.