CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
sebas
sebas

Reputation: 742

AppScript updates every row state after sending email

The following appscript tries to send an email for each row where "ESTADO" is not "ENVIADO" and then updating "ESTADO" with "ENVIADO" for every email sent.

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();
}
}
}

Given this example table

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

It updates like this:

CLIENTE TRACKINGPARACOPIAR  EMAIL   ENVIADO
Gladis Ortiz.   135081342   [email protected]    ENVIADO
María Soledad Hernandez     135081360   [email protected]    
Cristina Orozco 135081387   [email protected]

But expected output is this:

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

I tried to do

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

but it didnt work. What am I doing wrong?

Upvotes: 1

Views: 79

Answers (1)

Marios
Marios

Reputation: 27370

Since you start from the second row of the sheet: getRange("A2:D"), you should add 2 in the getRange argument:

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

Don't forget that array index in JavaScript always starts with zero. If A2 (second row) corresponds to the first element-row (0) of the UserData array, then you need to add 2 to match them.

Solution:

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();
}
}
}

Upvotes: 1

Related Questions