Google Apps Script – Check if Reference is Out of Range

google sheetsgoogle-apps-script

I have some Google Apps Script code which works with Google Sheets. In one segment of code there are some instances where it throws an exception.

Here is the exception:

Exception: Cell reference out of range

Here is the line of code (and the one preceding it) that is throwing the exception:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Reservations');
  var cell = sheet.getActiveCell(); // This is where the exception is thrown

This error happens but not consistently. What I want to do is check if sheet.getActiveCell() is pointing to a reference out of range or if it is returning a valid value. If it is pointing to a reference that is out of range I would like to set the value of cell to an alternative value.

How do I check if sheet.getActiveCell() is returning a value that is in range?

Best Answer

If you can use getCurrentCell, then instead of throwing an exception, it returns null if there is no active cell. There are some difference between the getCurrentCell and getActiveCell though.

With getCurrentCell, you can do this:

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Reservations');
 var cell = sheet.getCurrentCell();
 if(cell) {
   // Rest of your code that needs access to the active cell
 } else {
   // There is no active cell, do something else
 }

If you can't use getCurrentCell, then you can use a try-catch block to catch the exception.

 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Reservations');
 try {
   var cell = sheet.getActiveCell();
   // Rest of your code that needs access to the active cell
 } catch (e) {
   // There is no active cell, do something else
 }
Related Topic