Javascript使用电子表格范围A1表示法从2D数组中提取子数组 [英] Javascript extract subarray from 2D array using spreadsheet range A1 notation

问题描述

在许多

例如，假设自定义函数 getRange ：

  var SS = SpreadsheetApp.openById（myID）; //打开电子表格
 var sheet = SS.getSheetByName（ Test）; //获取工作表
 var sheetValues = sheet.getSheetValues（1,1，-1，-1）; //将工作表中的所有值复制到2D数组
 
 //示例：如何使用A1表示法调用该函数以提取子数组
 var subArray = getRange（sheetValues， A2：A ）; //从第二行开始的数组中返回一个列 
 subArray = getRange（sheetValues， A2）; //从数组（1,0）返回一个单元格 
 subArray = getRange（sheetValues， B2：D3）; //返回2D子数组
  

类似于此

编辑：

我从另一个类似的帖子，现在我已经正确设置了参数，就可以使用：

  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转码！

解决方案

我已将其工作，并结合了此处。

这是最终代码，可能需要进一步优化（并需要输入数据验证）。

  var矩阵= [[ a1， b1， c1， d1]，[ a2， b2 ， c2， d2]，[ a3， b3， c3， d3]，[ a4， b4， c4， d4]] console.log（  getRange（'a2：2'）返回：\n + JSON.stringify（getRange（ a2：2）））;函数getRange（textRange）{var startRow，startCol，endRow，endCol; var range = textRange.split（：）; var ret = cellToRoWCol（range [0]）; startRow = ret [0] startCol = ret [1]如果（startRow == -1）{startRow = 0; } if（startCol == -1）{startCol = 0; } if（range [1]）{ret = cellToRoWCol（range [1]）; endRow = ret [0] endCol = ret [1]如果（endRow == -1）{endRow = matrix.length; } if（endCol == -1）{endCol = matrix.length; }} else {//仅一个单元格endRow = startRow endCol = startCol}返回matrix.slice（startRow，endRow +1）.map（function（i）{返回i.slice（startCol，endCol + 1）;}）; }函数cellToRoWCol（cell）{//返回行&来自A1表示法的col var row = cell.replace（/ [^ 0-9] + / g，’’）; var letter = cell.replace（/ [^ a-zA-Z] + / g，’’..toUpperCase（）; var列= 0，长度= letter.length; for（var i = 0; i< length; i ++）{列+ =（letter.charCodeAt（i）-64）* Math.pow（26，长度-i-1）; }行=数字（行）-1;柱 - ; return [row，column];}  

Among many Google Apps Script best practices, as for script performance improvement, it is recommended to minimise calls to other services:

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.

As a result, when dealing with GAS on a spreadsheet, a common practice is to copy values from a sheet as a whole into a 2D array, do all manipulations on the array, then flush all data from array back to sheet.

Once you have copied all data from sheet into the 2D array, dealing with columns may be tricky, particularly with large column sets, therefore it could be handy to use a function that will extract/set data from the 2D array using A1 notation, as this allows to visually determine on the sheet what is the correct range, while using a Javascript transcoding function to identify columns and rows accordingly.

Without reinventing the wheel, I was wondering if someone wrote some code to extract data from a 2D array as a subarray, using a spreadsheet range A1 notation to reference the boundaries of the subarray.

For instance, assuming a custom function 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

Similar to this

Edit:

I have copied the following code from another similar post and this works now that I have correctly set parameters:

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))

I will further investigate on A1 transcoding!

解决方案

I've got it to work, combining a snippet from here.

Here's the final code, it may be further optimized (and requires input data validation).

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];
}

这篇关于Javascript使用电子表格范围A1表示法从2D数组中提取子数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！