我正在尝试从在应用程序脚本中过滤的 Google Sheets 行中提取单个数组值。我已根据列中空单元格的条件成功过滤数据/行。但现在,我不断收到以下错误: TypeError: Cannot read property '0' of undefined at selectRecords(SendNotifications:21:22)
请注意,我想同时访问所有已过滤的行。例如,如果我有第 1 行和第 2 行,并且如果我想要这两行中的所有 5 列,那么我的结果应该是:
[行:3][第 1 列][第 2 列][第 3 列][第 4 列][第 5 列]
[行:7][第 1 列][第 2 列][第 3 列][第 4 列][第 5 列]
[行:8][第 1 列][第 2 列][第 3 列][第 4 列][第 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;
};
问题/解决方案:
- Counter
i
从 2 开始,比如说最后一行,因此,lastData
is 5. dataValues
是一个从 3 开始到 5 结束的二维数组 => 3 行数据,索引分别为 0、1 和 2。但是i
从 2 开始,到 5 结束。i
击中 3,var row =dataValues[i];
> dataValues[3]
将undefined
. row
is undefined
and undefined
不是一个数组。那么你
无法读取未定义的属性“0”
示例脚本:
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}`);
});
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)