时间戳

首页 > 解决方案 > 尝试过滤二维数组并将它们推回谷歌表时发生错误

arrays - 尝试过滤二维数组并将它们推回谷歌表时发生错误

问题描述

我想在这里实现的过程是将数据放入 2D 数组中，将其过滤掉，然后仅在过滤后的数组中抓取选定的列数据并将其推回到同一个谷歌表中的新选项卡中。每个过程都有注释，所以你可以按照我在做什么。

function copyToAndFrom() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName("PendingX");//sheet with imported data
  var s2 = ss.getSheetByName("Resolved");//archive sheet
  var lr = s2.getLastRow()+1;//get last row of archive sheet + 1
  var dList = s1.getRange("PendingX!G2:G").getValues(); // Gets all the values of that specific column
  var dLast = dList.filter(String).length; //Gets the length of dList.

  var myArray = []; // A 2D array. I want a 12 column, dLast number of rows array.
  myArray.length = dLast;
  var tempArr = []; // Secondary array to get the filtered data from myArray array.
  var filteredArr = []; // Get only specific data from tempArr array.

  // myArray = s1.getRange('PendingX!D2:O').getValues();
  // The the above comment is the original sheet import with 400+ rows but for testing, Ive disabled it and assigned manually as below.                 
  myArray = [["Pending", , "abc", "def", "ghi", "jkl", "mno", "pqr", "stu", "vwy", "xzz", "aaa"], 
      ["Resolved", , "aabc", "adef", "aghi", "ajkl", "amno", "apqr", "astu", "avwy", "axzz", "aaaa"],
      ["Pending", , "babc", "bdef", "bghi", "bjkl", "bmno", "bpqr", "bstu", "bvwy", "bxzz", "baaa"]];

  var myrowindex, myString, mycolumnindex;
  tempArr[myrowindex] = [];
  for (myrowindex in myArray) {  //Loops through the number of rows in myArray.
    myString = myArray[myrowindex][0];
    if (myString === "Pending") {
      for (mycolumnindex in myArray[myrowindex]) { //Loops through each column of a row in myArray.
        var myColumnValue = myArray[myrowindex][mycolumnindex]; //Loads each component of myArray to a string.
        tempArr[myrowindex].push(myColumnValue); //Push the string into the tempArr array.
      }

      myColumnValue = null;
      myString = null;
    }
  }

  for (myrowindex in tempArr){  //Load data from columns 0,3,10 and 11 into filteredArr array.
    filteredArr.push(tempArr[myrowindex][0]);
    filteredArr.push(tempArr[myrowindex][3]);
    filteredArr.push(tempArr[myrowindex][11]);
    filteredArr.push(tempArr[myrowindex][12]);
  }

  Logger.log(tempArr.length);  // must be less than myArray.length but it is not.
  Logger.log(tempArr[0].length); // must be equal to myArray[0].length but it is not.
  Logger.log(tempArr); //Doesnt store data in a 2D Array but has to.

  //CODE FOR STORING DATA TO SHEET:
  for (var row in filteredArr) { //Loops through the number of rows in filteredArr.
    for (var col in filteredArr[row]) { //Loops through each column of a row in filteredArr.
      var myColumnValue = tempArr[row][col]; //Loads each component of filteredArr to a string.
      final = s2.getRange(2,1,row,col).setValue(myColumnValue); //Push the string into the sheet.
    }
  }
}

最近更改后的错误：

TypeError：无法调用未定义的方法“push”。（第 30 行，文件“CopyToFromScript”）

当前日志输出和预期日志输出：

当前记录器tempArr.length：

[19-02-01 19:25:08:933 IST] 22.0

预期的记录器tempArr.length：

[19-02-01 19:25:08:933 IST] 2.0

当前记录器tempArr[0].length：

[19-02-01 19:25:08:934 IST] 12.0

预期的记录器tempArr[0].length：

[19-02-01 19:25:08:933 IST] 12.0

当前记录器tempArr：

[19-02-01 19:25:08:935 IST] [Pending, abc, def, ghi, jkl, mno, pqr, stu, vwy, xzz, aaa, Pending, babc, bdef, bghi, bjkl, bmno, bpqr, bstu, bvwy, bxzz, baaa]

预期的记录器tempArr：

[19-02-01 19:25:08:935 IST] [[Pending, abc, def, ghi, jkl, mno, pqr, stu, vwy, xzz, aaa], [Pending, babc, bdef, bghi, bjkl, bmno, bpqr, bstu, bvwy, bxzz, baaa]]

当前记录器filteredArr：

[19-02-01 19:24:35:773 IST] [P, d, null, null, a, null, null, null, d, null, null, null, g, null, null, null, j, null, null, null, m, null, null, null, p, null, null, null, s, null, null, null, v, null, null, null, x, null, null, null, a, null, null, null, P, d, null, null, b, c, null, null, b, f, null, null, b, i, null, null, b, l, null, null, b, o, null, null, b, r, null, null, b, u, null, null, b, y, null, null, b, z, null, null, b, a, null, null, null, null, null, null]

预期的记录器filteredArr：

[19-02-01 19:24:35:773 IST] [[Pending, def, xzz, aaa], [Pending, bdef, bxzz, baaa]]

标签： arraysgoogle-apps-scriptgoogle-sheets

解决方案

我能够解决这个问题。它与在循环中正确定义二维阵列有关。我已经为任何想要检查的人提供了代码的固定部分。

var myrowindex, myString, mycolumnindex, validmyrowindex = 0;
for (myrowindex in myArray) {  
    myString = myArray[myrowindex][0];
    if (myString === "Resolved") {
        tempArr[validmyrowindex] = [];
        for (mycolumnindex = 0; mycolumnindex < 12; mycolumnindex++){
            if(tempArr[validmyrowindex] != undefined) {
                var myColumnValue = myArray[myrowindex][mycolumnindex];
                tempArr[validmyrowindex][mycolumnindex] = myColumnValue;
            } else {
                var myColumnValue = myArray[myrowindex][mycolumnindex];
                tempArr[validmyrowindex] = myColumnValue;
            }     
        myColumnValue = null;
        myString = null;
        }
    } else {validmyrowindex--}
        validmyrowindex++
}

原始代码中低于这一点的所有内容都是多余的。它被上面带有固定列的迭代所取代。基本setValues(array)函数用于将整个数组存储到工作表中。谢谢大家的宝贵解释和支持。

推荐阅读