Send mail when a cell is changed and several conditions are met

Question

Send mail when a cell is changed and several conditions are met

Navigation

#1 by (2 votes)

1

I have been trying to find an answer for this specific problem and I can not find the right answer. I have a spreadsheet and I want it to send an email every time the cell data is modified in the L column of a particular sheet, I have a project activator for this particular case that triggers the function when editing the sheet. calculation. This is the result:

function sendNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = ss.getActiveCell().getA1Notation();
  var row = sheet.getActiveRange().getRow();
  var column = sheet.getActiveRange().getColumn()
  var cellvalue = ss.getActiveCell().getValue().toString();
  var recipients = "[email protected]";
  var message = '';
  //column L is number 12, when the result is zero it won´t enter the if
  if(column-12){ 
  //do nothing
    message='no hago nada'
  }
  else{
  //Only if I am in this specific sheet
    if(sheet.getName()=='Olmedo Trabajos de Pepe'){
    //Only if the cell is not empty
      if(cellvalue!=''){
        var subject =' Fotos de promoción: ' + sheet.getRange('G'+ sheet.getActiveCell().getRowIndex()).getValue();
        var body = ' Promoción: ' + sheet.getRange('G'+ sheet.getActiveCell().getRowIndex()).getValue() +'\n Pedido de Olmedo ' + sheet.getRange('A'+ sheet.getActiveCell().getRowIndex()).getValue() + '\n Oferta Panorama ' + sheet.getRange('m'+ sheet.getActiveCell().getRowIndex()).getValue() +'\n Ya tiene fotos en la dirección \n' + sheet.getRange('L'+ sheet.getActiveCell().getRowIndex()).getValue();
        //Then I´ll send an email with the information from the table.
        MailApp.sendEmail(recipients, subject, body);
      }
    }
  }
};

As you can see, as a result of my ignorance of Javascript, there are things that are not very ugly, like doing 3 if instead of one because I have not found a way to concatenate all the conditions into one (I tried with "& & ").

javascript google-apps-script

asked by Pablo_1985 24.01.2017 в 13:31

source

1 answer

Doubt with GET sending data Apply onBackPressed in a fragment

score 2 · Accepted Answer

Here is an example of what a script would be called when a change occurs. The script sends a message when you modify one of three cells, B1 , B2 , B3 of Hoja 1 and that each of these meet a specific condition.

Note: To facilitate the tracking, the data of the cells is obtained individually. For optimal performance, reduce the number of calls to the API, for example using getDataRange

function enviarEmail() {
  //Iniciamos
  //Validar si hay cuota disponible. En caso contrario avisar al usuario y terminar.
  var cuotaDisponible = MailApp.getRemainingDailyQuota();
  Logger.log('Cuota disponible previa: %s', cuotaDisponible);
  if(cuotaDisponible < 1) {
    Browser.msgBox('No queda cuota disponible por hoy. Intenta más tarde.');
    return;
  }
  // Obtener la celda actual
  var celdaActual = SpreadsheetApp.getActiveRange();
  /*
   * En caso de que la celda actual sea B1, B2 o B3 
   * y que se cumplan otras condiciones, enviar un
   * mensaje
   */
  switch (celdaActual.getA1Notation()){
    case 'B1':
    case 'B2':
    case 'B3':
    var libro = SpreadsheetApp.getActiveSpreadsheet();
    var hoja = libro.getSheetByName('Hoja 1');
    //Valor B1
    var destinatario = hoja.getRange(1, 2).getValue();
    //Valor B2
    var asunto = hoja.getRange(2, 2).getValue();
    //Valor B3
    var cuerpo = hoja.getRange(3, 2).getValue();
    //Validar que se cumplen las condiciones requeridas
    if(
        // Dirección de correo. Sólo validar que la longitud mínima sea 3
        destinatario.length > 3 && 
        // Asunto. Validar que tenga al menos un caracter
        asunto.length > 1 && 
        // Cuerpo. Validar que tenga al menos un caracter
        cuerpo.length > 1
    ){
      //Enviar mensaje
      MailApp.sendEmail(destinatario, asunto, cuerpo);
      //Aviso al usuario
      Browser.msgBox('Mensaje enviado');
    } else {
      //Aviso al usuario
      Browser.msgBox('No se envió el mensaje');
    }
    /*
     * En caso de agregar otros casos, prevenir que se ejecuten
     * "sin querer"
     */
    break;
  }
  //Terminamos
  return;
}

Note: I created a gist with the code of this answer - > link