如何从Google表格的应用脚本中的过滤行中提取单个数组值 [英] How to extract individual array value from filtered row in app script for google sheets

问题描述

我正在尝试从Google表格行中提取单个数组值，该行已在应用脚本中过滤。我已经根据一列中空单元格的条件成功过滤了数据/行。但是现在，我继续收到以下错误：TypeError：无法读取selectRecords（SendNotifications：21：22）上未定义的属性 0

I am trying to extract individual array values from a Google Sheets Row which was filtered in app script. I have successfully filtered data/rows based on conditions of a empty cells in a column. But now, I keep getting the following error: TypeError: Cannot read property '0' of undefined at selectRecords(SendNotifications:21:22)

请注意，我想同时访问所有过滤的行。例如，如果我有第1行和第2行，并且如果我希望这两行中的所有5列，我的结果应该是：

Please note that I want to access all filtered rows all at the same time. For example, if I have Row 1 and 2, and if I want all 5 columns within these two rows, my result should be:

[row：3] [col 1] [col 2] [col 3] [col 4] [col 5]

[row: 3][col 1][col 2][col 3][col 4][col 5]

[行：7] [col 1] [col 2] [col 3] [col 4] [col 5]

[row: 7][col 1][col 2][col 3][col 4][col 5]

[行：8] [col 1] [col 2] [col 3] [col 4] [col 5]

[row: 8][col 1][col 2][col 3][col 4][col 5]

谢谢。

/* Global Prameters */
var ssss = SpreadsheetApp.getActiveSheet();
var rows = ssss.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();  
var columnToCheck = ssss.getRange("J:J").getValues();
var columnToCheck2 = ssss.getDataRange().getValues();
var lastData = getLastRowSpecial(columnToCheck2);
var lastRow = getLastRowSpecial(columnToCheck);

function selectRecords() {    
var dataRange = ssss.getRange(3,1, lastRow, ssss.getLastColumn()); // This 
 // range is to select column 9
var headerRowNumber = 3;
var dataValues = dataRange.getValues();

for (var i=2; i < lastData; i++){
  var row =dataValues[i];
  var headerRowNumber = 2;  
  var mydata1 = dataValues.filter(row => row[9] == ""); // This returns 
   // all row contents where column 9 is empty.
  var getData = row[0] + "\n" + row[1] + "\n" + row[2] + "\n" + row[3] + 
                "\n" + row[4] + "\n" + row[5];  // <<<< This is where 
                                                        error generates.
var email = row[13];
var test = row[9];
var message = getData; // Second column
var subject = 'Sending emails from a Spreadsheet';
MailApp.sendEmail(emailAddress, subject, message);
} Logger.log(getData);
}   

function getLastRowSpecial(range){ // Function to limit getDataRange() or 
                   // get data without looping through the entire sheet
var rowNum = 0;
var blank = false;
for(var row = 0; row < range.length; row++){
  if(range[row][0] === "" && !blank){
    rowNum = row;
    blank = true;
  }else if(range[row][0] !== ""){
    blank = false;
  };
};
return rowNum;
};

推荐答案

问题/解决方案：

• 计数器 i 从2开始，即最后一行，因此， lastData dataValues 是一个二维数组，从3开始，以5结尾=> 3行数据的索引分别为0、1和2。但是 i 从2开始，以5结尾。一旦 i 达到3， var row = dataValues [i]; > dataValues [3] 是未定义的。 row 是 undefined ，而 undefined 不是数组。因此，您



Issues/Solution:

• Counter i starts at 2, say last row and hence, lastData is 5. dataValues is a 2d array starting from 3 and ending at 5 => 3 rows of data with indexes 0, 1 and 2. But i starts at 2 and ends at 5. As soon as i hits 3, var row =dataValues[i];> dataValues[3] will be undefined. row is undefined and undefined is not a array. So, you




无法读取未定义的属性 0

Cannot read property '0' of undefined







• Array.filter 不在适当位置进行过滤。而是返回过滤后的数组。使用过滤后的数组。

  • Array.filter doesn't filter in place. Instead it returns the filtered array. Use the filtered array.

    getDataRange（）仅返回到lastRow的数据而不是整个工作表。因此通常不需要 getLastRowSpecial

    getDataRange() only returns the data upto the lastRow and not the whole sheet. So usually, it is not needed to getLastRowSpecial

    function selectRecords() {
      const ss = SpreadsheetApp.getActiveSheet();
      const dataRange = ss.getDataRange();
      const headers = 2;
      const dataValues = dataRange
        .offset(headers, 0, dataRange.getNumRows() - headers)//offsetting the headers from the whole range
        .getValues();
    
      dataValues
        .filter(row => row[9] == '') //filtered data where row[9] is empty
        .forEach(row => {
          //forEach filtered row do>
          let message =
            row[0] +
            '\n' +
            row[1] +
            '\n' +
            row[2] +
            '\n' +
            row[3] +
            '\n' +
            row[4] +
            '\n' +
            row[5]; 
          let email = row[13];
          let subject = 'Sending emails from a Spreadsheet';
          MailApp.sendEmail(email, subject, message);
          Logger.log(`${subject}: ${message} sent to ${email}`);
        });
    }
    

    这篇关于如何从Google表格的应用脚本中的过滤行中提取单个数组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！