解析谷歌表单中的一个表，然后将所有匹配的行数据存储在我可以从中提取信息的数组中(GAS/JavaScript)

Question

如果我的代码很乱，很抱歉，我是编码新手。到目前为止，我一直能够使事情顺利进行。

我想解析我的数据库，从所有符合匹配条件的行中获取信息，并将这些信息存储在一个数组中，每组数据都在数组的新级别上。这样我就可以稍后在报告页面上打印这些信息。

问题：

当我调试此代码时；newArray"；变量具有正确的数字"0"；对象"；，由于缺乏适当的编码语言，但每一个的内容都是"；函数()"；其中没有任何真实数据。因此，关于我如何在for循环中推送和/或获取数据的问题无法正常工作，我不太确定原因。

function kanbanView() {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
//  var activeSheet = activeSpreadsheet.getActiveSheet();
var activeSheet = activeSpreadsheet.getSheetByName("ACTIVE");
var activeRange = activeSpreadsheet.getActiveRange();
//  var activeCell = activeSpreadsheet.getActiveCell();
var activeCell = activeSheet.getRange(19, 10);
// ^^ remove later, mock cell for debugger.
var activeCellRow = activeCell.getRow();
var activeCellColumn = activeCell.getColumn();
var lastColumn = 17;
var firstColumn = 1;
var lastRow = activeSheet.getLastRow();
var activeCellValue = activeCell.getValue();
var findCriteria = activeCellValue;
var foundCriteria = 0;
var contentRange = activeSheet.getRange(1, 1, 5000, 17);
var contentRangeValues = contentRange.getValues();
var contentRangeFormats = contentRange.getFontColors();
var sourceColumn = 10;
//-------------------------------------------Parsing--------------------------------------------------
var foundValues = [];
var newArray = [];
var foundRow;
var iName;
var iStatus;
var iRating;
var iAction;
var iPosition;
var iLocation;
var iClient;
for (var i = 0; i < lastRow; i++) {
if (activeColumnRangeValues[i][0] == findCriteria) {
foundRow = i;
iName = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 7).getValue;
iStatus = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 5).getValue;
iRating = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 4).getValue;
iAction = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 6).getValue;
iPosition = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 8).getValue;
iLocation = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 9).getValue;
iClient = activeSpreadsheet.getSheetByName("ACTIVE").getRange(foundRow, 10).getValue;
newArray.push([iName, iStatus, iRating, iAction, iPosition, iLocation, iClient,
]);
foundValues.push(foundRow + 1);
}
}

Answer 1

我认为这符合您的要求：

function kanbanView() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("ACTIVE");
const vA = sh.getRange(1,1,sh.getLastRow(),17).getValues();
const findCriteria = sh.getRange(19, 10).getValue();
const newArray=[];
const foundValues=[];
vA.forEach((r,i)=>{
if(r[0]==findCriteria) {
newArray.push([r[6],r[4],r[3],r[5],r[7],r[8],r[9]]);
foundValues.push(i+1); 
}

});
console.log(JSON.stringify(newArray));
console.log(JSON.stringify(foundValues));
} 

这是我的数据：

COL10>td style="text align:left；">9>td style="text align=：left；">27<10><21>27<10>>>>26>>td style="ext-align:left；left；">4

COL1		COL8	COL9	COL11	COL17	COL18	COL19	COL20
20	2	5	20	4
2	11	10	22	25
4	23	7	5	1	18
27	26	8	9	25	6	12	14	0	29	21	29	2
21	21	10	7	15	17
19	27	19	29	12	29	11	9
25	5	4	26	6	6	12
17	6	18	21	13	11
17	3	13	1	13	8
20	18	16	1	1
28	20	28	28	4	27
12	21	29	22	18	14	15	2	7	8	10	9	14
15	7	10	0	6	10
27	16	28	17	12
21	1	14	18	27	0
21	4	24	21	20	22
15	8	1	25	10	1	16	0	0	17
10	0	21	29	22	12
11	12	6	15	9	18
19	27	29	28	25
11	15	10	15	6	5	21	19	18	16
6	20	25	1	0	21
21	2	27	5	10	6
2		0	24	29	6	3
17	7	6	10	14	26
13	12	27	18	26	25	0	16	16	1	4	9	23
29	28	15	29	12	13	18	26	21	3	25	0	3
22	0	21	4	0	5
23	21	2	13	9	27
29	28	0	22	23	12