Excel JavaScript API Part 4: The shallow copy problem

I was trying to read the values from a Range and then write modified versions of the values to two different ranges. Should be straightforward:

async function setValue() {
try {
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
let rng1 = sheet.getRange("A1:A2").load("values");
await context.sync();
let rng2 = sheet.getRange("B4:b5");
let rng3 = sheet.getRange("B6:B7");
rng2.values = rng1.values;
rng3.values = rng1.values;
rng2.values[0][0] = rng2.values[0][0] + 0.9;
rng3.values[0][0] = rng3.values[0][0] + 0.01;
await context.sync();
});
console.log("Done!");
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

Well this does not work: both B4 and B6 have 0.91 added to them: wrong answer!

The reason this does not work is that JavaScript does something called “shallow copying” for objects and arrays. In effect this means that rng2 and rng3 are just different names for rng1: they all refer to the same set of values.

So when you change one variable you are actually changing them all!

Creating a real copy : “Deep Copying”

A “Deep Copy” is one where the values really are copied rather than just adding an extra name for the values. JQuery has a handy method for doing this: JQuery.extend ($ is short for JQuery)

// create deep copy of rng1
let deep1 = [];
$.extend(true, deep1, rng1.values);
// set rng2 = deep copy
rng2.values = deep1;

So now I can assign the deep copy to rng2.
But if I also assign the same deep copy to rng3 I am back in the same trap: rng2 and rng3 would just be different names for the deep copy!

So I need to either create another deep copy to use for rng3 or assign rng1 to rng3.
Of course then when I change rng3 I am also changing rng1 but that does not matter (although probably bad practice) because I am not returning rng1 back to the worksheet.
(rng1 never appears on the left of the = assignment)

async function setValue() {
try {
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getActiveWorksheet();
let rng1 = sheet.getRange("A1:A2").load("values");
await context.sync();
// create deep copy of rng1
let deep1 = [];
$.extend(true, deep1, rng1.values);
let rng2 = sheet.getRange("B4:b5");
let rng3 = sheet.getRange("B6:B7");
// set rng2 = deep copy
rng2.values = deep1;
// set rng3 = rng1
rng3.values = rng1.values;
rng2.values[0][0] = rng2.values[0][0] + 0.9;
rng3.values[0][0] = rng3.values[0][0] + 0.01;
await context.sync();
});
console.log("Done!");
}
catch (error) {
OfficeHelpers.Utilities.log(error);
}
}

Conclusion

Shallow copies are a wonderful JavaScript “Gotcha” for VBA programmers!

Advertisement