Object Exists

If you run a context.sync and you are requesting objects that do not exist, an exception will be thrown and the promise will be rejected.

Detecting Using a Try-Catch

You can use this technique to check if an object exists.

try { 
   var _sheet = context.workbook.worksheets.getItem("Sheet4");
catch (error) {
   var _isExpected = (error instanceof OfficeExtension.Error &&
                      error.code === Excel.ErrorCodes.itemNotFound);

   if (_isExpected == true) {
      console.log("sheet does not exist");
   } else {
      console.log("something else happened");

If you want to do an operation only if an object exists then you will need an extra context.sync.
This technique can only be used to check for a single object.

Detecting Using a OrNullObject

An alternative method for checking if an object exists is to use the methods that are suffixed with 'OrNullObject'.
This technique can be useful when you want to do something if the object exists or something else if the object does not exist.
This method lets you check more than one object in a single context.sync.
This technique can check whether an object derived from a 'OrNullObject' method is null.
This technique can also check if a 'derived' object is null.

var _sheetNull = context.workbook.worksheets.getItemOrNullObject("Sheet4"); 
var _rangeSelected = context.workbook.getSelectedRange();
var _rangeNull = _sheetNull.getRange("A:A").getIntersectionOrNullObject(_rangeSelected);

if (_rangeNull.isNullObject == false) {
   console.log("they do intersect");
} else {
   console.log("there is no intersection");

There is no need to use the IsNullObject property on 'regular' objects, only 'null' objects.

OrNullObject Methods

A large number of the objects provide methods that are suffixed with 'OrNullObject'.
These methods will not throw an exception.

var _sheetNull = context.workbook.worksheets.getItemOrNullObject("Sheet4"); 
var _tableNull = _sheetNull.tables.getItemOrNullObject("TableName");
var _rangeNull = _tableNull.getDataBodyRange();

This code will not generate any exceptions even if the worksheet or the table do not exist.
This technique is useful when you only want to take an action when something does exist or otherwise do nothing.

Regular Object vs Null Object

Any object that is derived from a 'OrNullObject' method looks like a regular object but actually behaves very different.
These types of objects are referred to as 'Null Objects' as opposed to 'Regular Objects'.
Any object derived from an object that has come from a 'OrNullObject' method will also behave very different.
It is this different behaviiour which makes chaining 'get' commands across null objects possible.

getItem vs getItemOrNullObject

There is a subtle difference between these two methods when working with Null Objects.
The following lines of code look very similar.

var _table1 = _worksheets.getItemOrNullObject("Sheet2").tables.getItem("TableName"); 
var _table2 = _worksheets.getItemOrNullObject("Sheet2").tables.getItemOrNullObject("TableName");

Lets consider three different scenarios.
(1) worksheet does exist and table does exist
_table1 is created as a regular object.
_table2 is created as a regular object.

(2) worksheet does not exist and table does exist
_table1 is created as a null object.
_table2 is created as a null object.

(3) worksheet does exist but table does not exist
_table1 is created as a regular object (and will throw an exception).
_table2 is created as a null object.

This is a very important point because once a null object has been created more 'null objects' can quickly appear.
If an object exists using the getItemOrNullObject method it will just return a regular object.

© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext