如何在谷歌应用程序脚本中的forEach循环中设置谷歌工作表中目标单元格的值

Question

我编写了一个脚本，用于搜索工作表，找到符合7个参数的行，返回这些行的数据，并向每个匹配行中的地址发送电子邮件。作为这个过程的一部分，我想将"上次联系日期"的单元格值更新为电子邮件发送日期(这部分并不难(。我正在努力获得包含上次联系日期的单元格的A1表示法，我相信我需要它才能使用setValue((方法。以下是代码的相关部分：

function sendFollowUps () {
// Gets data from defined range of active sheet
var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test List').getRange(2,1,152,12).getValues()
// User-defined cutoff date, everything on or before will be contacted
var cutoffDate = new Date('11/10/2021')
console.log('Cutoff date is: ' + cutoffDate)
// selects and returns only firms that haven't been met, schedule, or declined and have a date of last contact on or before the cutoffDate
var filteredRows = rows.filter(function(row) {
if (row[10] && row[10] != 'Met' && row[10] != 'Responded' && row[10] != 'Warm' && row[10] != 'Scheduled' && row[10] != 'Declined' && row[11] <= cutoffDate) {
return row
}
})
// for each firm returned by filteredRows, pulls email, firstName of investor, and firmName
filteredRows.forEach(function(row) {
var email = row[6]
var firstName = row[4].split(' ')[0]
var firmName = row[0]
var sendToEmails = ['...']
// Customized email message with firstName, firmName updated per investor
var messageSubject = '...'
var messageBody = `...`
// Adds investor email to recipient list
sendToEmails.push(email)
// Formats recipient list to CSV string
sendToEmails.join(',')
// Sends email with customized firstName and firmName to email in row
GmailApp.sendEmail(sendToEmails, messageSubject, messageBody, {'from': '[e-mail]'})
followUpEmails.push(email)
}

我试过在forEach循环中设置row[11] = sendDate，但这实际上并没有更新工作表中的单元格值。我也尝试过使用SpreadsheetApp.getSheetByName('Test List').getRange(row[11])、SpreadsheetApp.getSheetByName('Test List').getActiveCell()和SpreadsheetApp.getSheetByName('Test List').getCurrentCell(row[11])，它们也不太好用。

如何获取行[11]单元格的A1表示法以便使用setValue方法？

Answer 1

A1表示法几乎不可能获得，因为您首先要filter。这已经在前面的回答中提到了。但是，您可以修改阵列，然后在整个范围内.setValues()修改后的阵列：

const range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test List').getRange(2,1,152,12);
const rows = range.getValues();

然后在.forEach()内部，

row[11]=sentDate;

forEach():外部

range.setValues(rows);

请注意，这将用值替换所有公式(如果有的话(。

Answer 2

我怀疑这不正常，因为您无法比较自var cutoffDate = new Date('11/10/2021')以来的日期

var filteredRows = rows.filter(function(row) {
if (row[10] && row[10] != 'Met' && row[10] != 'Responded' && row[10] != 'Warm' && row[10] != 'Scheduled' && row[10] != 'Declined' && row[11] <= cutoffDate) {
return row
}
})

此外，像现在这样使用过滤器也破坏了foreach中索引和数据集中值索引之间的连续性。我不会过滤数据，而是将数据搜索作为forEach 的一部分

我认为你这样做会更好：

function sendFollowUps() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('Test List');
const vs = sh.getRange(2, 1, 152, 12).getValues()
var cutoffDate = new Date('11/10/2021').valueOf();
vs.forEach(row => {
if (row[10] && row[10] != 'Met' && row[10] != 'Responded' && row[10] != 'Warm' && row[10] != 'Scheduled' && row[10] != 'Declined' && new Date(row[11]).valueOf() <= cutoffDate) {
var email = row[6];
var firstName = row[4].split(' ')[0];
var firmName = row[0];
var sendToEmails;
var messageSubject;
var messageBody;
sendToEmails.push(email)
sendToEmails.join(',')
GmailApp.sendEmail(sendToEmails, messageSubject, messageBody, { 'from': '[e-mail]' });
followUpEmails.push(email)
}
});
}