I'm writing a Office Add-in for Excel which needs to add a worksheet conditionally: when it's not there: add it and fill it. When it's there: fill it.
Now I do see these API's:
In both cases I would end-up with a sheet by the name of 'Sample'. But how do I know which to select?
The crude solution will be to try to get the sheet first and then when that fails (all async in the .sync()-call) add the sheet:
async function checkedAddSheet() {
Excel.run(async (context) => {
// For debugging:
OfficeExtension.config.extendedErrorLogging = true;
var sheet = context.workbook.worksheets.getItem(NEW_DATA_SHEET_NAME);
sheet.load("name, position");
return context.sync()
.then(function () {
console.log(`Found worksheet named "${sheet.name}" in position ${sheet.position}`);
});
}).then(function () {
console.log("Done");
}).catch(function (error) {
console.error(error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
console.log("Failed to get, let's add...");
addSheet();
});
}
function addSheet() {
Excel.run(async (context) => {
var sheet = context.workbook.worksheets.add(NEW_DATA_SHEET_NAME);
sheet.load("name, position");
return context.sync()
.then(function () {
console.log(`Worksheet named "${sheet.name}" was added in position ${sheet.position}`);
});
}).then(function () {
console.log("Done");
}).catch(function (error) {
console.error(error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
console.log("Failed to get, let's add...");
});
}
This could be made nicer by checking the type of error but it works.