Javascript使用电子表格范围A1表示法从二维数组中提取子数组

Question

众多Google Apps Script best practices ，至于脚本性能提升，建议尽量减少对其他服务的调用:

Using JavaScript operations within your script is considerably faster than calling other services. Anything you can accomplish within Google Apps Script itself will be much faster than making calls that need to fetch data from Google's servers or an external server, such as requests to Spreadsheets, Docs, Sites, Translate, UrlFetch, and so on. Your scripts will run faster if you can find ways to minimize the calls the scripts make to those services.

因此，在处理电子表格上的 GAS 时，通常的做法是将工作表中的值作为一个整体复制到二维数组中，对数组进行所有操作，然后将数组中的所有数据刷新回工作表。

一旦您将工作表中的所有数据复制到二维数组中，处理列可能会很棘手，尤其是对于大型列集，因此使用一个函数会很方便，该函数将使用 A1 从二维数组中提取/设置数据表示法，因为这允许在工作表上直观地确定什么是正确的范围，同时使用 Javascript 转码功能相应地识别列和行。

在不重新发明轮子的情况下，我想知道是否有人编写了一些代码来从二维数组中提取数据作为子数组，使用电子表格范围 A1 符号来引用子数组的边界。

例如，假设一个自定义函数getRange:

var SS = SpreadsheetApp.openById(myID); // open spreadsheet
var sheet = SS.getSheetByName("Test"); // get sheet
var sheetValues = sheet.getSheetValues(1,1,-1,-1); // copy all values from sheet to 2D array

// samples: how the function could be invoked to extract subarray using A1 notation 
var subArray = getRange(sheetValues, "A2:A"); // returns a "column" from the array begin at row 2
subArray = getRange(sheetValues, "A2"); // returns a "cell" from the array (1,0)
subArray = getRange(sheetValues, "B2:D3"); // returns a 2D subarray

类似于this

编辑:

我从 another similar post 复制了以下代码现在我已经正确设置了参数，这有效:

var matrix = [
          ["a1", "b1", "c1", "d1"],
          ["a2", "b2", "c2", "d2"],
          ["a3", "b3", "c3", "d3"],
          ["a4", "b4", "c4", "d4"]
        ]
var startRow = 1
var startCol = 0
var endRow = 2
var endCol = 0

var section = matrix.slice(startRow, endRow + 1).map(i => i.slice(startCol, endCol + 1))
console.log(JSON.stringify(section))

我会进一步研究A1转码!

Answer 1

我已经开始工作了，结合了 here 中的一个片段.

这是最终代码，它可能会进一步优化(并且需要输入数据验证)。

var matrix = [
   ["a1", "b1", "c1", "d1"],
   ["a2", "b2", "c2", "d2"],
   ["a3", "b3", "c3", "d3"],
   ["a4", "b4", "c4", "d4"]
]

console.log("getRange('a2:2') returns:\n" + JSON.stringify(getRange("a2:2")));

function getRange(textRange) {
   var startRow, startCol, endRow, endCol;
   var range = textRange.split(":");
   var ret = cellToRoWCol(range[0]);
   startRow = ret[0]
   startCol = ret[1]
   if (startRow == -1) {
      startRow = 0;
   }
   if (startCol == -1) {
      startCol = 0;
   }

   if (range[1]) {
      ret = cellToRoWCol(range[1]);
      endRow = ret[0]
      endCol = ret[1]
      if (endRow == -1) {
         endRow = matrix.length;
      }
      if (endCol == -1) {
         endCol = matrix.length;
      }
   } else { // only one cell
      endRow = startRow
      endCol = startCol
   }

   return matrix.slice(startRow, endRow + 1).map(function(i) {
      return i.slice(startCol, endCol + 1);
   });
}

function cellToRoWCol(cell) {
   // returns row & col from A1 notation
   var row = cell.replace(/[^0-9]+/g, '');
   var letter = cell.replace(/[^a-zA-Z]+/g, '').toUpperCase();

   var column = 0,
      length = letter.length;
   for (var i = 0; i < length; i++) {
      column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
   }

   row = Number(row) - 1;
   column--;

   return [row, column];
}