google script equivalent of xlDown, xlRight

I recently started creating scripts for google spreadsheet documents, the javascript based scripts.

Unfortunately, I could not find the equivalent of xls vba range(“A1”).end(xlDown) in native google script functions.

As such I created a working js equivalent. Only issue with it is performance. It is not really fast, but this is more due to Apps Script rather than the code itself or the fact that it is written in js.
In case someone find a native equivalent to this function, please do not hesitate to share.

/**
* V0.2 => PERF IMPROVED
*
* Home-made equivalent of xls range().end(xlToRight) function
* In case a native alternative is found, use the native solution instead of this workaround for perf reasons
*
* Various cases:
* A1: 1
* A2: <empty>
* A3: 3
* A4: 4
* A5: <empty>
* A6: <empty>
* A7: 7
*
* Case 1: active cell is A1 => moving down
*  => returned cell must be A3
* Case 2: active cell is A2 => moving down
*  => returned cell must be A3
* Case 3: active cell is A3 => moving down
*  => returned cell must be A4
* Case 4: active cell is A4 => moving down
*  => returned cell must be A7
* Case 5: active cell is A5 => moving down
*  => returned cell must be A7
* Case 6: active cell is A6 => moving down
*  => returned cell must be A7
* Case 7: active cell is A7 => moving down
*  => returned cell must be last cell when moving in this direction
*
* NOTE: same cases apply with other directions (up|left|right)
*  - 26s for 650 rows => far too long but far better than previous version => will keep this version while optimizing execution when calling it to try to minimize path being walked through
*
* LIMITATIONS:
* - in its current implementation, there is no distinction being done between active cell and top-left-most cell of the provided range as is currently the case in xls (=> to be improved later on if required)
*
* @param range $cRange
* @param string $direction right|left|top|bottom
* @return range
*/
function rdRangeEnd(cRange, direction)
{
 var i = 0, rowOffsetMult = 0, colOffsetMult = 0, rowOffset = 0, colOffset = 0, cSheet = cRange.getSheet(), ignoreFirst = cRange.getValue() != "";
 switch (direction) {
  case 'left':
   colOffsetMult = -1;
   break;
  case 'right':
   colOffsetMult = 1;
   break;
  case 'up':
   rowOffsetMult = -1;
   break;
  case 'down':
   rowOffsetMult = 1;
   break;
  default:
   throw "Unknown value for direction '" + direction + "'";
  }

 var row = cRange.getRow(), col = cRange.getColumn(), cValue = "", nextValue = cRange.getValue(), lastRow = cSheet.getLastRow(), lastCol = cSheet.getLastColumn();
 do {
  cValue = nextValue;
  if ((rowOffsetMult == -1 && row == 1) || (colOffsetMult == -1 && col == 1)) {
   return cRange.offset(rowOffsetMult * (cRange.getRow()-1), colOffsetMult * (cRange.getColumn()-1)); //move to top-most|left-most cell
  }
  if (rowOffsetMult == 1 && row > lastRow) {
   return cRange.offset(cSheet.getMaxRows() - cRange.getLastRow(), 0); //move to last row
  }
  if (colOffsetMult == 1 && col > lastCol) {
   return cRange.offset(0, cSheet.getMaxColumns() - cRange.getLastColumn()); //move to last column
  }

  row = row + rowOffsetMult;
  col = col + colOffsetMult;
  nextValue = cSheet.getRange(row, col).getValue();
  i = i+1;
 } while ((ignoreFirst && i < 2) || ((nextValue == "" && cValue == "") || (nextValue != "" && cValue != "")));

 if (nextValue == "") {
  row = row - 1 * rowOffsetMult;
  col = col - 1 * colOffsetMult;
 }
 return cRange.offset(row - cRange.getRow(), col - cRange.getColumn());
}

Usage:

upCell = rdRangeEnd(myCell, 'up'); //'up|down|right|left'

sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...