In Google Apps Script, is Range.getNextDataCell(...) supposed to fail when Range is hidden?

user_13143804 0
Viewed 577

In the Google Apps Script Spreadsheet Service –

Where tgt_range is a Range object, the line tgt_range.getNextDataCell(SpreadsheetApp.Direction.DOWN) generates an "Exception: Invalid argument" if the group in the sheet within which tgt_range is contained is collapsed. However, the line runs without exception if the group is expanded.

I cannot find any documentation indicating that a range must be visible/expanded in order for the .getNextDataCell(...) method to execute.

Does anyone know if this is a bug or whether the behavior is by design?

google-apps-script
1 Answers

This is a bug.

Methods that use the data region (range expanded in the four cardinal Directions) (e.g. getNextDataCell(direction), getDataRegion() throw an error if the starting (top-left) cell of the Range is hidden.

The specific error, though, is different depending on the method. While getNextDataCell throws Invalid argument, getDataRegion causes the script to run for about 30-40 seconds before throwing Service Spreadsheets failed while accessing document with id {SPREADSHEET_ID}.

As a workaround, unhide the top-left cell of the range before calling this method, and hide it again after that.

Related bugs:

user_106120110
Related