在应用程序脚本电子表格中选择连续范围(Select contiguous range in App

是有可能得到周围的特定小区，在电子表格类似为Ctrl + A的范围内？

Answer 1:

我有一个数，其中的表是由创建的电子表格的QUERY()函数，所以边界是柔性的。在过去，我已经使出设置一个名为范围是，我预计是最大的查询结果将需要的大小，并利用这些命名的范围进行其他操作。

不再！

这里的一个效用函数， getContiguousRange()其接受在A1表示法的细胞的位置，并返回Range包含它的邻接小区。这种代码可在要点。

/**
 * Return the contiguous Range that contains the given cell.
 *
 * @param {String} cellA1 Location of a cell, in A1 notation.
 * @param {Sheet} sheet   (Optional) sheet to examine. Defaults
 *                          to "active" sheet.
 *
 * @return {Range} A Spreadsheet service Range object.
 */
function getContiguousRange(cellA1,sheet) {
  // Check for parameters, handle defaults, throw error if required is missing
  if (arguments.length < 2) 
    sheet = SpreadsheetApp.getActiveSheet();
  if (arguments.length < 1)
    throw new Error("getContiguousRange(): missing required parameter.");

  // A "contiguous" range is a rectangular group of cells whose "edge" contains
  // cells with information, with all "past-edge" cells empty.
  // The range will be no larger than that given by "getDataRange()", so we can
  // use that range to limit our edge search.
  var fullRange = sheet.getDataRange();
  var data = fullRange.getValues();

  // The data array is 0-based, but spreadsheet rows & columns are 1-based.
  // We will make logic decisions based on rows & columns, and convert to
  // 0-based values to reference the data.
  var topLimit = fullRange.getRowIndex(); // always 1
  var leftLimit = fullRange.getColumnIndex(); // always 1
  var rightLimit = fullRange.getLastColumn();
  var bottomLimit = fullRange.getLastRow();

  // is there data in the target cell? If no, we're done.
  var contiguousRange = SpreadsheetApp.getActiveSheet().getRange(cellA1);
  var cellValue = contiguousRange.getValue();
  if (cellValue = "") return contiguousRange;

  // Define the limits of our starting dance floor
  var minRow = contiguousRange.getRow();
  var maxRow = minRow;
  var minCol = contiguousRange.getColumn();
  var maxCol = minCol;
  var chkCol, chkRow;  // For checking if the edge is clear

  // Now, expand our range in one direction at a time until we either reach
  // the Limits, or our next expansion would have no filled cells. Repeat
  // until no direction need expand.
  var expanding;
  do {
    expanding = false;
    // Move it to the left
    if (minCol > leftLimit) {
      chkCol = minCol - 1;
      for (var row = minRow; row <= maxRow; row++)  {
        if (data[row-1][chkCol-1] != "") {
          expanding = true;
          minCol = chkCol; // expand left 1 column
          break;
        }
      }
    }

    // Move it on up
    if (minRow > topLimit) {
      chkRow = minRow - 1;
      for (var col = minCol; col <= maxCol; col++)  {
        if (data[chkRow-1][col-1] != "") {
          expanding = true;
          minRow = chkRow; // expand up 1 row
          break;
        }
      }
    }

    // Move it to the right
    if (maxCol < rightLimit) {
      chkCol = maxCol + 1;
      for (var row = minRow; row <= maxRow; row++)  {
        if (data[row-1][chkCol-1] != "") {
          expanding = true;
          maxCol = chkCol; // expand right 1 column
          break;
        }
      }
    }

    // Then get on down
    if (maxRow < bottomLimit) {
      chkRow = maxRow + 1;
      for (var col = minCol; col <= maxCol; col++)  {
        if (data[chkRow-1][col-1] != "") {
          expanding = true;
          maxRow = chkRow; // expand down 1 row
          break;
        }
      }
    }

  } while (expanding);  // Lather, rinse, repeat

  // We've found the extent of our contiguous range - return a Range object.
  return sheet.getRange(minRow, minCol, (maxRow - minRow + 1), (maxCol - minCol + 1))
}

作为测试，认为此电子表格。它有两个连续范围，都与粗糙边缘。

下面是我们的测试功能：

function testRanges() {
  var range1 = getContiguousRange("C3").getA1Notation();
  var range2 = getContiguousRange("B8").getA1Notation();
  debugger; // Pause if running in debugger
}

而这就是我们得到：

我希望帮助！

Answer 2:

我尝试了上面，我不能发表评论，由于缺乏信誉，在工作表中有多个工作表，发现如果你改变了路线，它的工作更好：VAR contiguousRange = SpreadsheetApp.getActiveSheet（）getRange（cellA1） ; 使用var contiguousRange = sheet.getRange（cellA1）;