Google Apps Script: `Those rows are out of bounds.` error

Troubleshooting 'Those rows are out of bounds.' in Google Apps Script

Understand and resolve the common 'Those rows are out of bounds.' error when working with Google Sheets in Google Apps Script, ensuring your scripts interact correctly with sheet data.

The error message "Those rows are out of bounds." is a frequent hurdle for developers working with Google Apps Script and Google Sheets. This error typically occurs when your script attempts to access or manipulate a range of cells that does not exist within the specified sheet. This article will delve into the common causes of this error and provide practical solutions to help you write robust and error-free Google Apps Script code.

Understanding the 'Out of Bounds' Error

At its core, the 'out of bounds' error signifies that your script is trying to interact with a row index or a range of rows that falls outside the actual dimensions of the Google Sheet. Google Sheets are dynamic, but every sheet has a defined number of rows and columns. When you request data from, or attempt to write data to, a row number that is greater than the total number of rows available in the sheet, or a negative row number, this error is triggered.

Logical flow leading to an 'out of bounds' error.

Common Causes and Solutions

Several scenarios can lead to this error. Identifying the root cause in your specific script is the first step towards a solution. Here are the most common culprits and how to address them.

1. Incorrect Row/Column Indexing

Google Apps Script uses 1-based indexing for rows and columns when interacting with Range objects (e.g., getRange(row, column, numRows, numColumns)). If you're accustomed to 0-based indexing from other programming languages, this can be a common source of error, especially when calculating dynamic ranges.

function incorrectIndexingExample() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // This will cause an error if the sheet has no rows, or if row 0 is attempted
  // getRange expects 1-based indexing
  try {
    sheet.getRange(0, 1).setValue("Error"); // Incorrect: Row 0 does not exist
  } catch (e) {
    Logger.log("Caught error: " + e.message);
  }

  // Correct way to access the first row
  sheet.getRange(1, 1).setValue("Correct");
}

Example of incorrect 0-based indexing causing an error.

2. Accessing Beyond the Last Row of Data

A very common scenario is attempting to get the last row of data using getLastRow() and then trying to access a row beyond it, or misinterpreting its return value. If getLastRow() returns 0 (meaning the sheet is empty), trying to access row 1 will still be out of bounds if you're trying to read data, but it's valid for writing to the first row.

function beyondLastRowExample() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Returns the index of the last row with content

  // Scenario 1: Sheet is empty (lastRow is 0), trying to read from row 1
  if (lastRow === 0) {
    try {
      // This will cause an error if you try to get a range of 1 row from row 1 when lastRow is 0
      // It's trying to read data that doesn't exist.
      sheet.getRange(1, 1, 1, 1).getValues(); 
    } catch (e) {
      Logger.log("Caught error for empty sheet read: " + e.message);
    }
    // Correct: If sheet is empty, you can write to row 1
    sheet.getRange(1, 1).setValue("Header");
    lastRow = 1; // Update lastRow after writing
  }

  // Scenario 2: Trying to access a row beyond the actual last row with data
  // For example, if lastRow is 5, trying to get data from row 6
  var nextRow = lastRow + 1;
  try {
    // This will cause an error if nextRow is greater than the sheet's max rows
    // or if you try to read from an empty row using getRange(row, col, numRows, numCols)
    sheet.getRange(nextRow, 1, 1, 1).getValues(); 
  } catch (e) {
    Logger.log("Caught error for accessing beyond data: " + e.message);
  }

  // Correct: To append data, use the row after the last row with content
  sheet.getRange(lastRow + 1, 1).setValue("New Data");
}

Handling getLastRow() and appending data correctly.

3. Incorrect Range Dimensions

When using getRange(row, column, numRows, numColumns), ensure that numRows and numColumns do not extend beyond the sheet's boundaries from the starting row and column. For instance, if a sheet has 100 rows, and you try to get a range starting at row 90 with numRows set to 20, it will result in an error because 90 + 20 - 1 = 109, which exceeds row 100.

function incorrectRangeDimensionsExample() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var maxRows = sheet.getMaxRows();
  var maxCols = sheet.getMaxColumns();

  // Example: Sheet has 100 rows, trying to get 20 rows starting from row 90
  var startRow = 90;
  var numRowsToGet = 20;

  if (startRow + numRowsToGet - 1 > maxRows) {
    Logger.log("Attempted range exceeds sheet's maximum rows.");
    // Adjust numRowsToGet to fit within bounds
    numRowsToGet = maxRows - startRow + 1;
  }

  try {
    // This will now be within bounds if adjusted
    var range = sheet.getRange(startRow, 1, numRowsToGet, 1);
    Logger.log("Successfully got range: " + range.getA1Notation());
  } catch (e) {
    Logger.log("Caught error for range dimensions: " + e.message);
  }
}

Adjusting range dimensions to prevent 'out of bounds' errors.

4. Empty Sheets and getDataRange()

If a sheet is completely empty, sheet.getDataRange() will return a range representing A1, but its getValues() will return an empty 2D array. Trying to iterate over this or access range.getRow() or range.getNumRows() might lead to unexpected behavior if not handled carefully, though it typically doesn't directly cause 'out of bounds' for getRange itself.

function emptySheetDataRangeExample() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear(); // Ensure sheet is empty for this test

  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  Logger.log("Data range A1 notation: " + dataRange.getA1Notation()); // Usually A1
  Logger.log("Values from data range: " + JSON.stringify(values)); // Will be [[]]

  // If you then try to process values assuming there's at least one row/column
  if (values.length > 0 && values[0].length > 0) {
    Logger.log("First cell value: " + values[0][0]);
  } else {
    Logger.log("Sheet is empty or data range is effectively empty.");
  }
}

Handling getDataRange() on an empty sheet.

Best Practices to Avoid the Error

To minimize the occurrence of 'Those rows are out of bounds.' errors, adopt these best practices in your Google Apps Script development:

1. Always Use 1-Based Indexing

Consistently use 1-based indexing for rows and columns when interacting with Google Sheets methods like getRange(), insertRow(), deleteRow(), etc.

2. Validate Row/Column Numbers

Before calling getRange() or similar methods, always check that your calculated row and column numbers are positive and within the sheet's getMaxRows() and getMaxColumns() limits. This is especially crucial when dealing with user input or dynamically determined ranges.

3. Handle Empty Sheets Gracefully

When working with getLastRow() or getDataRange(), anticipate that a sheet might be empty. Check if getLastRow() returns 0 or if getDataRange().getValues() returns an empty array [[]] and adjust your logic accordingly.

4. Use appendRow() for New Data

When adding new rows of data, prefer sheet.appendRow(rowContents) over calculating getLastRow() + 1 and then using getRange().setValues(). appendRow() automatically handles finding the next available row and is less prone to 'out of bounds' errors.

5. Test with Edge Cases

Thoroughly test your scripts with various sheet states: completely empty, with a single row, with many rows, and with rows containing empty cells. This helps uncover potential 'out of bounds' issues.