script le email field in googledocs

Asked

Viewed 68 times

0

Hello, I have a spreadsheet in Googledocs, which has a field with one or more emails, in this case, seperados by comma. The script reads the field and sends an email to the registered destination in the field email. However, the email is only received by the recipient if he is alone in the field. If it contains more than one email, no one receives it. For example, if the record contains two comma-separated emails, as in "[email protected], [email protected]", no one receives if it contains only [email protected], then it normally receives. inserir a descrição da imagem aqui

javascript spreadsheets

  • How are these fields read and how is the email sent? Probably when there are 2 or more emails, the sending script reads everything as if it were just 1 email and does not work because it goes as invalid email.

  • I take all emails from the field with this code: var emailAddress = dataRange[i][7]; And send with this: Mailapp.sendemail(emailAddress, Subject, "", {htmlBody: message});

  • there is no solution, so...type, the script le until you find a comma and send the email, then read the q has after the comma and send also...

2 answers

0

Break the value of the field into array and call the function that sends the emails within one for...of. This way each email address will be sent separately to the function, not all together when there is more than 1 email in the field:

// quebro a variável emailAddress em array pela vírgula
var Emails = emailAddress.split(",");
for(var Email of Emails){
   // o .trim() é importante para eliminar espaços
   MailApp.sendEmail(Email.trim(), subject, "", {htmlBody: message});
}

  • Okay, but where do I pass the field parameter that contains the email? in the original code, it fetches the email in a cell [i][7], for example, row 1, column 7 of the spreadsheet, and stores it in the variable emailAddressvar. Hence, the script le o q is in this variable and sends the email. In this code you sent, how would it look? he is not passing emailAddress = dataRange[i][7] as parameter. Grateful.

  • The variable emailAddressvar Just stay where you are. The code I put in is only converting the value into an array so it can be sent one by one if there is more than 1 email in the cell.

  • The parameter is now Email.trim(), which is every email in the cell.

  • Give the message "Missing ; after the for-loop initializer. (line 25, "Send email" file). However, I don’t know where to put ";". I tried after the for and it doesn’t happen.;

  • No for, the variable "Email" must start with the uppercase "E": for (var Email of emails) {... you put with tiny "and": for (var email of emails) {

  • Yes, I already fixed it...I forgot to mention in the previous msg]...but the same error continues. Thank you.

  • What is this line 25 of error?

  • is the for... for line (var email of emails) {

  • So I don’t know what it can be. The for doesn’t need ;. This code should work smoothly, since it is only separating the emails by comma and sending them to function one by one separately.

  • Does it have anything to do with that nesting? I have one for, one if inside the for, and one else is inside the if...of a look. Grateful.

  • I believe that there are no problems in nesting. O for is synchronous: see, it enters the first for, enters the if and then the second for. After processing the second for, it goes back to the first and so on. Do a test with console.log. Create a test() function and place a console.log to see if the emails are going... I was going to do a Jsfiddle for you to see....

  • Here’s what it would look like: https://jsfiddle.net/vvmxLrgn/

  • Then just watch the console to see if emails will appear

  • It doesn’t send the emails because when it sends it falls into my mailbox. This happens when I only have one email in the field. If I have more than one, separated by comma, as is the case, do not send.

  • The error was no. I commented (take a look at the updated code). And it’s already emailing the 2 recipients in the field. I can leave anyway?

  • I tested with 3 emails and sends to the 3. What mass!

  • Strange, it was not supposed to work not rs.. I think the error was that you had not closed the FOR with the key }

  • Ok, it’s working without the for. But why do you think it’s necessary? I’m afraid to remove it from the code and have some unwanted effect.

  • Check it out: this variable Email.trim() vineyard from for... without the for should give error, because the variable is without any origin... make a test: leave it, close it with } and change the "email" to "Email" (with a capital "E").

  • It has to look exactly like this: https://jsfiddle.net/vvmxLrgn/1/

  • Strange, because it doesn’t really work that way. Well, I’ll keep without it and if you have any problems, I’ll come back here again. Thank you very much, and good afternoon.

Show 16 more comments

0


Here’s my code. I adapted it with your solution, but it’s giving an error: 

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 0;  // First row of data to process
  var numRows = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
      var dataRange = sheet.getRange(2, 1, numRows-startRow,lastCol).getValues();  //Get all values except the header rows


  for (var i=0;i<numRows-startRow;i++){
     var expire = dataRange[i][8]; 

    if (expire < 10) {
      var emailAddress = dataRange[i][7];
      var subject = "Você tem um prazo de auditoria vencendo em " + dataRange[i][8] + " dias";
      var emails = emailAddress.split(",");
      var dataEnvio = Utilities.formatDate(dataRange[i][0], "GMT", "dd/MM/yyyy");
  var dataFim = Utilities.formatDate(dataRange[i][4], "GMT", "dd/MM/yyyy");
      var message = "<P><i style='color: black; font-size: 11px; font-weight:bold';> *Esta é a Proposta de encaminhamento da COAUD, de " + dataEnvio + "\n" + ", que deve ser respondida até "  
      + dataFim + "<P><a style='color: black; font-size: 12px; font-weight:bold';>Identificação do Documento: </a>" + dataRange[i][2] + "<P><b style='color: black; font-size: 12px; "
      + " font-weight:bold';> Recomendação de Auditoria: </b> " + dataRange[i][3] 
      +"<P> ----------------------------------------------------------------------------------------------------------------------------------------------------------------"
      + "------------------------------------------------</P>" 
      + "<P><i style='color: red; font-size: 10px';> NOTA: Amparada na Portaria X monitoramento. Para acessá-lo, consulte o manual de monitoramento, disponível "  
      + "<A href='https://www.a.x'>aqui</A>. Contate a COA pelos telefones: XXXXXXXX/6779 e e-mail: [email protected], para maiores informações </i></P>";
     // for (var email of emails) {
           MailApp.sendEmail(Email.trim(), subject, "", {htmlBody: message});


//  MailApp.sendEmail(Email.trim(), emailAddress, subject, "", {htmlBody: message});
   }

}

}

  • Just remembering that I already had one for......

Browser other questions tagged javascript spreadsheets

You are not signed in. Login or sign up in order to post.