I have a code that sends an email for every row that satisfies the condition that column CQ= "SI", there are currently 3 rows that satisfy the condition, however when I run the code an email for the first row that meets the criteria is sent and then I get the error mail.getRange is not a function. I am trying to pull a cell that contains a list of emails and I don't understand why it works for the first email that is sent and then stops working. The error is in line 58: var mail= mail.getRange(1,1).getValues(); The code is the following:

function maildrops(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("REFERENCIAS");
var mail = spreadsheet.getSheetByName("emails");
var lista_refes = SpreadsheetApp.getActive().getSheetByName("REFERENCIAS").getRange("D2:D").getValues(); //event list
  var lista_refes_ok1 = lista_refes.reduce(function(ar, e) {
    if (e[0]) ar.push(e[0])
    return ar;
  }, []); 
var nombre = SpreadsheetApp.getActive().getSheetByName("REFERENCIAS").getRange("F2:F").getValues(); //event list
  var nombre1 = nombre.reduce(function(ar, e) {
    if (e[0]) ar.push(e[0])
    return ar;
  }, []);
var drop = SpreadsheetApp.getActive().getSheetByName("REFERENCIAS").getRange("BB2:BB").getValues(); //event list
  var dropv = SpreadsheetApp.getActive().getSheetByName("REFERENCIAS").getRange("CP2:CP").getValues(); //event list
  var tickn = SpreadsheetApp.getActive().getSheetByName("REFERENCIAS").getRange("CQ2:CQ").getValues(); //event list
  


for (var i = 0; i < lista_refes_ok1.length; i  ) {
    var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("REFERENCIAS");
    var nom = nombre1[i];
    var dropa = drop[i];
    var dropvv = dropv[i];
    var refe = lista_refes_ok1[i];
    var tick = tickn[i];

if (tick == "SI" ){
var subject = "SS23 cambios drop:  "   refe   ".";
    var body = "Hola chicos, el modelo "   refe   " "   nom  " ha cambiado del drop "   dropvv   " al drop "   dropa  ". \n\nCualquier cosa, podéis contestar a este mail :)";
    var mail= mail.getRange(1,1).getValues()
    GmailApp.sendEmail(mail, subject, body);
   }
}
  sheet.getRange('CP3:CP').activate();
  sheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: false});
}

The last rows of the code are trying to empty the content of column CP after the emails have been sent, I have not been able to check if this part of the code is right because I get the error before getting to these lines. Does anybody know how to fix the error? Any help is appreciated :)

CodePudding user response：

In your script, from var mail = spreadsheet.getSheetByName("emails");, mail is Sheet object. But, at var mail = mail.getRange(1, 1).getValues(), mail is declared at the 1st loop. By this, after the 2nd loop, mail becomes a 2-dimensional array. Under this condition, when var mail = mail.getRange(1, 1).getValues() is run, such the error occurs. I thought that this might be the reason for your issue. In order to remove this issue, how about the following modification?

From:

for (var i = 0; i < lista_refes_ok1.length; i  ) {
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("REFERENCIAS");
  var nom = nombre1[i];
  var dropa = drop[i];
  var dropvv = dropv[i];
  var refe = lista_refes_ok1[i];
  var tick = tickn[i];
  if (tick == "SI") {
    var subject = "SS23 cambios drop:  "   refe   ".";
    var body = "Hola chicos, el modelo "   refe   " "   nom   " ha cambiado del drop "   dropvv   " al drop "   dropa   ". \n\nCualquier cosa, podéis contestar a este mail :)";
    var mail = mail.getRange(1, 1).getValues()
    GmailApp.sendEmail(mail, subject, body);
  }
}

To:

var emailAddress = mail.getRange(1, 1).getValue();
for (var i = 0; i < lista_refes_ok1.length; i  ) {
  // var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("REFERENCIAS"); // It seems that this is not used.
  var nom = nombre1[i];
  var dropa = drop[i];
  var dropvv = dropv[i];
  var refe = lista_refes_ok1[i];
  var tick = tickn[i];
  if (tick == "SI") {
    var subject = "SS23 cambios drop:  "   refe   ".";
    var body = "Hola chicos, el modelo "   refe   " "   nom   " ha cambiado del drop "   dropvv   " al drop "   dropa   ". \n\nCualquier cosa, podéis contestar a este mail :)";
    GmailApp.sendEmail(emailAddress, subject, body);
  }
}