AppScript在发送电子邮件后更新每一行的状态

Question

下面的appscript尝试为每行发送一封电子邮件，其中"ESTADO"；不是"；ENVIADO"；然后更新"；ESTADO"；用"；ENVIADO"；每发送一封电子邮件。

function seguimientosemail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("SeguimientoDNI2");
var range = sheet.getRange("A2:D");
var UserData = range.getValues();
for (i in UserData) {
var row = UserData[i];
var cliente = row[0];
var tracking = row[1];
var email = row[2];
var estado = row[3];
if (estado != "ENVIADO" && estado != "ESTADO" && email != ""){
MailApp.sendEmail(row[2], "Custom mail", "Hello " + cliente + ", This is an email report of your tracking code. Your tracking is " + tracking);
sheet.getRange(i,4).setValue("ENVIADO");
SpreadsheetApp.flush();
}
}
}

给定此示例表

CLIENTE TRACKINGPARACOPIAR  EMAIL   ESTADO
Gladis Ortiz.   135081342   [e-mail]    ENVIADO
María Soledad Hernandez     135081360   [e-mail]    
Cristina Orozco 135081387   [e-mail]    

它更新如下：

CLIENTE TRACKINGPARACOPIAR  EMAIL   ENVIADO
Gladis Ortiz.   135081342   [e-mail]    ENVIADO
María Soledad Hernandez     135081360   [e-mail]    
Cristina Orozco 135081387   [e-mail]    

但预期输出是这样的：

CLIENTE TRACKINGPARACOPIAR  EMAIL   ESTADO
Gladis Ortiz.   135081342   [e-mail]    ENVIADO
María Soledad Hernandez     135081360   [e-mail] ENVIADO
Cristina Orozco 135081387   [e-mail] ENVIADO

我试着做

sheet.getRange(i+1,4).setValue("ENVIADO");

但它不起作用。我做错了什么？

Answer 1

由于您从工作表的第二行开始：getRange("A2:D")，因此您应该在getRange参数中添加2：

sheet.getRange(i+2,4).setValue("ENVIADO");

不要忘记，JavaScript中的数组索引总是以zero开头。如果A2(第二行(对应于UserData数组的第一个元素行(0(，则需要添加2来匹配它们。

---

解决方案：

function seguimientosemail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("SeguimientoDNI2");
var sheet_size=sheet.getRange('A:A').getValues().filter(String).length +1;
var range = sheet.getRange("A2:D"+sheet_size);
var UserData = range.getValues();

for (let i=0; i<UserData.length;i++) {
var row = UserData[i];
var cliente = row[0];
var tracking = row[1];
var email = row[2];
var estado = row[3];
if (estado != "ENVIADO" && estado != "ESTADO" && email != ""){
MailApp.sendEmail(row[2], "Custom mail", "Hello " + cliente + ", This is an email report of your tracking code. Your tracking is " + tracking);
sheet.getRange(i+2,4).setValue("ENVIADO");
SpreadsheetApp.flush();
}
}
}