Envoi email suite modification cellule
Bonjour,
Je vous contacte car j'ai un soucis dans un script sur Sheet. J'ai un fichier listant les demandes d'absence de personne, et pour chaque réponse, j'ai une liste déroulante qui permet de sélectionner "Validé" ou "Refus". Je tente de créer un script qui permet d'envoyer un message au demandeur suite à la modification de cette case.
J'y arrive presque. A chaque modification, j'ai bien un prompt qui demande si je veux envoyer à la bonne personne l'email, et lorsque je réponds oui, le prompt se ferme et... bin plus rien... aucune trace de courriel envoyé ni reçu... Je ne saisis pas pourquoi. Je vous joins une copie du fichier excel et du code, si vous avez une idée...
Merci
Le code créé:
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
var value = range.getValue();
var emailColumn = sheet.getRange(row, 2);
var datedebutColomn = sheet.getRange(row, 4);
var datefinColomn = sheet.getRange(row, 4);
var emailAddress = emailColumn.getValue();
var datedebut = datedebutColomn.getValue();
var datefin = datefinColomn.getValue();
var rowOfCellEdited = range.getRow();
if (column == 9 && row > 1 && emailAddress != "" && rowOfCellEdited == range.getRow()) {
var response = Browser.msgBox("Voulez-vous envoyez un courriel pour prévenir "+emailAddress+ " ?", Browser.Buttons.YES_NO);
if (response == "Oui") {
if (columnOfCellEdited == 9) {
var body = '';
var subject = '';
if (response == 'Validé') {
subject = "Demande d'absences validée";
body = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récap de la demande<br>" +sheet.getRange(row, 1)+ " type : " +sheet.getRange(row, 3)+ "\" du " + sheet.getRange(row, 4)+" au " + sheet.getRange(row, 5) + sheet.getRange(row, 1);
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(rowOfCellEdited, 12).setValue(new Date());
}
else if (response == 'refus de la demande') {
var body = '';
var subject = '';
if (response == 'Refus') {
subject = "Demande d'absences refusée";
body = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récap de la demande<br>" +sheet.getRange(row, 1)+ " type : " +sheet.getRange(row, 3)+ "\" du " + sheet.getRange(row, 4)+" au " + sheet.getRange(row, 5) + sheet.getRange(row, 1) + ".<br><br>Motif du refus: "+sheet.getRange(row, 11);
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(rowOfCellEdited, 12).setValue(new Date());
}
}
}}
}
var sheet = e.source.getActiveSheet();
var range = e.range;
if (range.getColumn() == 9) {
var row = range.getRow();
var time = new Date();
time = Utilities.formatDate(time, "GMT+1", "dd/MM/yyyy HH:mm:ss");
sheet.getRange(row, 11).setValue(time);
}
}
La copie du fichier sheet:
![capture d ecran 2024 01 08 a 09 59 25](https://forum.excel-pratique.com/file/img/1/128467_659bb98f24697793843055.png)
Bonjour,
Ajoute des logs voir si le déroulé est bon :
Browser.Buttons.YES_NO);
Logger.log("prompt')
if (response == "Oui") {
Logger.log("prompt OUI")
if (columnOfCellEdited == 9) {
var body = '';
var subject = '';
Logger.log("test réponse avec donnée : '+response)
if (response == 'Validé') {
Logger.log("test réponse VALIDE")
subject = "Demande d'absences validée";
body = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récap de la demande<br>" +sheet.getRange(row, 1)+ " type : " +sheet.getRange(row, 3)+ "\" du " + sheet.getRange(row, 4)+" au " + sheet.getRange(row, 5) + sheet.getRange(row, 1);
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(rowOfCellEdited, 12).setValue(new Date());
}
else if (response == 'refus de la demande') {
Logger.log("test réponse REFUSE")
var body = '';
var subject = '';
if (response == 'Refus') {
subject = "Demande d'absences refusée";
body = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récap de la demande<br>" +sheet.getRange(row, 1)+ " type : " +sheet.getRange(row, 3)+ "\" du " + sheet.getRange(row, 4)+" au " + sheet.getRange(row, 5) + sheet.getRange(row, 1) + ".<br><br>Motif du refus: "+sheet.getRange(row, 11);
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(rowOfCellEdited, 12).setValue(new Date());
}
}
}}
}
var sheet = e.source.getActiveSheet();
var range = e.range;
if (range.getColumn() == 9) {
var row = range.getRow();
var time = new Date();
time = Utilities.formatDate(time, "GMT+1", "dd/MM/yyyy HH:mm:ss");
sheet.getRange(row, 11).setValue(time);
}
}
En regardant rapidement, le test if (response == "Oui") {
est il passé ? sinon essaye avec YES et non pas Oui.
rebonjour,
Merci pour votre réponse rapide... Alors, pour suivre les logs, dans sheet, j'ai du mal à voir comment cela fonctionne avec le déboggage.
Par contre, je pense avoir saisi d'où venait le problème... La tache s'applique à demander d'envoyer ou non un email à la personne lorsque la liste Validé_Refus est modifiée. Ainsi, si c'est 'oui', en fonction du choix fait le message rédigé sera différent. Et c'est là que le problème doit résider. La variable 'response' est reprise alors que ce n'est pas la meme... Il faudrait que j'indique la valeur de la cellule modifiée de la colonne 9... comment puis-je changer ce paramètre?
if (columnOfCellEdited == 9) {
var body = '';
var subject = '';
if (response == 'Validé') {
Alors, plusieurs points, déjà, concernant le deboggage, il y a plusieurs moyens, personnellement je travaille surtout avec Logger.log qui permet d'afficher dans la console des éléments :
Logger.log("test réponse avec donnée : "+response)
Va afficher, si la ligne est exécutée la phrase : test réponse avec donnée + la donnée contenue dans la variable response.
Mettre des logs avant le teste, avec la valeur testée, puis un log après, permet de voir s'il y a un souci sur le test, ex :
fruit = "pomme"
Logger.log("test fruit dans plage, fruit = "+fruit)
if (plage == fruit){
Logger.log("test plage = fruit VALIDE")
Concernant votre problématique, si j'ai bien compris :
- le script s’exécute à chaque modification
- si modification dans plage Validé_Refus alors popup notification
- si réponse popup oui alors message validé
- si réponse popup non alors message refusé
Donc, oui vous pouvez stocker qu'une fois la donnée reponse et la tester 2 fois :
if (response == 'Validé') {
if (response == 'Refus') {
Bien qu'à votre place, je ferai :
if (response == 'Validé') {
....
} else if (response == 'Refus') {
....
} else {
SpreadsheetApp.getUi().alert("Réponse incorrecte en ligne " + range.getRow());
}
Rebonjour,
Vous avez bien compris le process.
Ok, j'ai modifié le script pour personnaliser la variable pour récupérer le choix de la liste déroulante... J'ai donc intégré après les 'yes' de la pop up la variable qui récupère le choix de la liste déroulante de la cellule. Je pensais avoir trouvé la solution, mais je ne reçois toujours pas d'email... Comprends pas... :(
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
var value = range.getValue();
var emailColumn = sheet.getRange(row, 2);
var datedebutColomn = sheet.getRange(row, 4);
var datefinColomn = sheet.getRange(row, 4);
var emailAddress = emailColumn.getValue();
var datedebut = datedebutColomn.getValue();
var datefin = datefinColomn.getValue();
var rowOfCellEdited = range.getRow();
if (column == 9 && row > 1 && emailAddress != "" && rowOfCellEdited == range.getRow()) {
var choix = Browser.msgBox("Voulez-vous envoyez un courriel pour prévenir "+emailAddress+ " ?", Browser.Buttons.YES_NO);
if (choix == 'yes') {
var sheet = e.source.getActiveSheet();
var range = e.range;
if (range.getColumn() == 9) {
var row = range.getRow();
var time = new Date();
time = Utilities.formatDate(time, "GMT+1", "dd/MM/yyyy HH:mm:ss");
sheet.getRange(row, 11).setValue(time);
}
if (columnOfCellEdited == 9) {
var body = '';
var subject = '';
var choixliste = sheet.getRange(row, 9);
if (choixliste == 'Validé') {
subject = "Demande d'absences validée";
body = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récap de la demande<br>" +sheet.getRange(row, 1)+ " type : " +sheet.getRange(row, 3)+ "\" du " + sheet.getRange(row, 4)+" au " + sheet.getRange(row, 5) + sheet.getRange(row, 1);
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(rowOfCellEdited, 12).setValue(new Date());
}
else if (choixliste == 'Refusé') {
subject = "Demande d'absences refusée";
body = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récap de la demande<br>" +sheet.getRange(row, 1)+ " type : " +sheet.getRange(row, 3)+ "\" du " + sheet.getRange(row, 4)+" au " + sheet.getRange(row, 5) + sheet.getRange(row, 1) + ".<br><br>Motif du refus: "+sheet.getRange(row, 11);
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(rowOfCellEdited, 12).setValue(new Date());
}
}
}
}
}
Avez vous essayer des logs ? De ce que je vois, il faudrait simplifier et remanier le script, préparez vous variables dans un premier temps, puis faites vos tests :
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
var value = range.getValue();
var emailColumn = sheet.getRange(row, 2);
var datedebutColomn = sheet.getRange(row, 4);
var datefinColomn = sheet.getRange(row, 5);
var emailAddress = emailColumn.getValue();
var datedebut = datedebutColomn.getValue();
var datefin = datefinColomn.getValue();
var time = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy HH:mm:ss");
var choixliste = sheet.getRange(row, 9).getValue();
Logger.log("test1 si( col = 9 / ligne >1 / email existe)");
if (column == 9 && row > 1 && emailAddress != "") {
Logger.log("test1 OK");
var choix = Browser.msgBox("Voulez-vous envoyer un courriel pour prévenir " + emailAddress + " ?", Browser.Buttons.YES_NO);
Logger.log("test2 prompt popup");
if (choix == 'yes') {
Logger.log("test2 Ok");
sheet.getRange(row, 11).setValue(time);
Logger.log("test 3 choix liste");
if (choixliste == 'Validé') {
Logger.log("test3 OK");
var subject = "Demande d'absences validée";
var body = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récap de la demande<br>" + sheet.getRange(row, 1).getValue() + " type : " + sheet.getRange(row, 3).getValue() + "\" du " + datedebutColomn.getDisplayValue() + " au " + datefinColomn.getDisplayValue() + sheet.getRange(row, 1).getValue();
Logger.log("envoi du mail VALIDE");
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(row, 12).setValue(new Date());
} else if (choixliste == 'Refusé') {
Logger.log("test4 OK");
var subject = "Demande d'absences refusée";
var body = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récap de la demande<br>" + sheet.getRange(row, 1).getValue() + " type : " + sheet.getRange(row, 3).getValue() + "\" du " + datedebutColomn.getDisplayValue() + " au " + datefinColomn.getDisplayValue() + sheet.getRange(row, 1).getValue() + ".<br><br>Motif du refus: " + sheet.getRange(row, 11).getValue();
Logger.log("envoi du mail REFUSE");
MailApp.sendEmail(emailAddress, subject, body);
sheet.getRange(row, 12).setValue(new Date());
}
}
}
}
+ il y a quelques petites autres erreurs, mais qui peuvent être corrigées ensuites.
Bonjour,
J'ai mis à jour la requête par rapport à ce que vous m'avez proposé, et ca fonctionne... jusqu'à l'envoi.
Je joins l'écran de l'exécution jusqu'au soucis d'accord de l'envoi:
![capture d e cran 2024 01 09 a 09 23 00](https://forum.excel-pratique.com/file/img/1/128467_659d042628eeb619315821.png)
![capture d e cran 2024 01 09 a 09 32 48](https://forum.excel-pratique.com/file/img/1/128467_659d04c2e6b4b932849460.png)
Il faut que je trouve le moyen d'accorder l'envoi d'email... si vous avez une idée...
Merci en tout cas pour le nettoyage du script, c'est bien plus propre déjà. N'hésitez à partager des astuces pour m'améliorer encore.
Cordialement,
Niko
rebonjour,
non, je n'arrive pas à avoir cette fenêtre d'autorisation google...
Niko
Tentez de faire une copie de votre fichier et du script, vous aurez à nouveau la demande d'autorisation.
Bonjour,
J'ai refait le fichier et recréé les scripts, mais ca ne résout pas le soucis.
En retestant et retestant j'ai résolu le probleme en changeant la fonction... onEdit ne fonctionnait pas et me renvoyait cette erreur :
![capture d e cran 2024 01 09 a 17 56 17](https://forum.excel-pratique.com/file/img/1/128467_659d7abb137cc667375980.png)
Du coup, j'ai changé le nom de la fonction et j'ai assigné un declencheur à modification de cellule, et ca fonctionne...
Voilà, merci j'y suis arrivé!!!
Merci encore pour votre aide.
Niko
Bonjour, votre script fonctionne chez moi avec quelque modification merci, mais il n'envoi pas quand je met refuse .
voici le fichier pour test.
https://docs.google.com/spreadsheets/d/1Rf6GsmbOHEstli4Ez1ZrdCezlvC10HqpDkbAMbk6uYM/edit?usp=sharing
Cordialement
et il y a double pop pop avant d'envoyer Voici le code :
function onEdit(e) {
// Récupérer la feuille "demande"
var feuille = e.source.getSheetByName("demande");
// Récupérer la plage de cellules modifiée
var plage = e.range;
// Récupérer la ligne et la colonne modifiées
var ligne = plage.getRow();
var colonne = plage.getColumn();
// Récupérer la valeur de la cellule modifiée
var valeur = plage.getValue();
// Colonne contenant l'adresse email
var colonneEmail = feuille.getRange(ligne, 2);
// Colonnes contenant les dates de début et de fin
var colonneDateDebut = feuille.getRange(ligne, 4);
var colonneDateFin = feuille.getRange(ligne, 5);
// Récupérer l'adresse email, les dates de début et de fin
var adresseEmail = colonneEmail.getValue();
var dateDebut = colonneDateDebut.getValue();
var dateFin = colonneDateFin.getValue();
// Ligne de la cellule modifiée
var ligneModifiee = plage.getRow();
// Si la modification se situe dans la colonne 9, après la première ligne,
// et qu'une adresse email est présente, alors on envoie un email
if (colonne == 9 && ligne > 1 && adresseEmail != "" && ligneModifiee == plage.getRow()) {
var choix = Browser.msgBox("Voulez-vous envoyer un courriel pour prévenir " + adresseEmail + " ?", Browser.Buttons.YES_NO);
if (choix == 'yes') {
// Récupérer la feuille et la plage modifiées à nouveau
var feuille = e.source.getActiveSheet();
var plage = e.range;
if (plage.getColumn() == 9) {
var ligne = plage.getRow();
// Obtenir la date et l'heure au format jj/mm/aaaa hh:mm:ss
var heure = new Date();
heure = Utilities.formatDate(heure, "GMT+1", "dd/MM/yyyy HH:mm:ss");
feuille.getRange(ligne, 11).setValue(heure);
}
if (colonne == 9) {
var corps = "";
var sujet = "";
var choixListe = feuille.getRange(ligne, 9).getValue();
if (choixListe == 'Validé') {
sujet = "Demande d'absences validée";
corps = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récapitulatif de la demande<br>" + feuille.getRange(ligne, 1) + " type : " + feuille.getRange(ligne, 3) + "\" du " + feuille.getRange(ligne, 4) + " au " + feuille.getRange(ligne, 5) + feuille.getRange(ligne, 1);
MailApp.sendEmail(adresseEmail, sujet, corps);
feuille.getRange(ligneModifiee, 12).setValue(new Date());
} else if (choixListe == 'Refusé') {
sujet = "Demande d'absences refusée";
corps = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récapitulatif de la demande<br>" + feuille.getRange(ligne, 1) + " type : " + feuille.getRange(ligne, 3) + "\" du " + feuille.getRange(ligne, 4) + " au " + feuille.getRange(ligne, 5) + feuille.getRange(ligne, 1) + ".<br><br>Motif du refus: " + feuille.getRange(ligne, 11);
MailApp.sendEmail(adresseEmail, sujet, corps);
feuille.getRange(ligneModifiee, 12).setValue(new Date());
}
}
}
}
}
function onEdit(e) {
// Récupérer la feuille "demande"
var feuille = e.source.getSheetByName("demande");
// Récupérer la plage de cellules modifiée
var plage = e.range;
// Récupérer la ligne et la colonne modifiées
var ligne = plage.getRow();
var colonne = plage.getColumn();
// Récupérer la valeur de la cellule modifiée
var valeur = plage.getValue();
// Colonne contenant l'adresse email
var colonneEmail = feuille.getRange(ligne, 2);
// Colonnes contenant les dates de début et de fin
var colonneDateDebut = feuille.getRange(ligne, 4);
var colonneDateFin = feuille.getRange(ligne, 5);
// Récupérer l'adresse email, les dates de début et de fin
var adresseEmail = colonneEmail.getValue();
var dateDebut = colonneDateDebut.getDisplayValue();
var dateFin = colonneDateFin.getDisplayValue();
var typeAbs = feuille.getRange(ligne, 3).getValue();
var choixListe = feuille.getRange(ligne, 9).getValue();
var heure = new Date();
heure = Utilities.formatDate(heure, "GMT+1", "dd/MM/yyyy HH:mm:ss");
var corps = "";
var sujet = "";
// Si la modification se situe dans la colonne 9, après la première ligne,
// et qu'une adresse email est présente, alors on envoie un email
if (colonne == 9 && ligne > 1 && adresseEmail != "") {
var choix = Browser.msgBox("Voulez-vous envoyer un courriel pour prévenir " + adresseEmail + " ?", Browser.Buttons.YES_NO);
if (choix == 'yes') {
feuille.getRange(ligne, 11).setValue(heure);
if (choixListe == 'Validé') {
sujet = "Demande d'absences validée";
corps = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récapitulatif de la demande<br> type :" + typeAbs + "\" du " + dateDebut + " au " + dateFin;
MailApp.sendEmail(adresseEmail, sujet, corps);
} else if (choixListe == 'Refus') {
sujet = "Demande d'absences refusée";
corps = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récapitulatif de la demande<br> type : " + typeAbs + "\" du " + dateDebut + " au " + dateFin + ".<br><br>Motif du refus: " + feuille.getRange(ligne, 8).getValue();
MailApp.sendEmail(adresseEmail, sujet, corps);
}
}
}
}
à choisir je séparerai en 2 fonction, onEdit qui vérifie si le trigger est correcte et si oui, lance sendMail.
Ensuite, essaye de regrouper le stockage des données en variables afin de ne pas en mettre partout.
+ attention aux erreurs de frappes, aux colonnes, lignes, etc... Je ne sais pas quel IA a généré ce script mais c'était un gros foutoir.
Voici ce que je ferai à ta place :
function onEdit(e) {
var feuille = e.source.getSheetByName("demande");
var plage = e.range;
var ligne = plage.getRow();
var colonne = plage.getColumn();
var adresseEmail = feuille.getRange(ligne, 2).getValue();
if (colonne == 9 && ligne > 1 && adresseEmail) {
sendMail(feuille,ligne, adresseEmail);
}
}
function sendMail(feuille,ligne, adresseEmail) {
var choixListe = feuille.getRange(ligne, 9).getValue();
var typeAbs = feuille.getRange(ligne, 3).getValue();
var dateDebut = feuille.getRange(ligne, 4).getDisplayValue();
var dateFin = feuille.getRange(ligne, 5).getDisplayValue();
var confirmation = Browser.msgBox("Voulez-vous envoyer un courriel pour prévenir " + adresseEmail + " ?", Browser.Buttons.YES_NO);
if (confirmation == 'yes') {
var heure = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd/MM/yyyy HH:mm:ss");
feuille.getRange(ligne, 11).setValue(heure);
var sujet, corps;
if (choixListe == 'Validé') {
sujet = "Demande d'absences validée";
corps = "Bonjour, <br><br>Votre demande d'absence vient d'être validée.<br><br>Récapitulatif de la demande<br> type : " + typeAbs + " du " + dateDebut + " au " + dateFin;
} else if (choixListe == 'Refus') {
sujet = "Demande d'absences refusée";
corps = "Bonjour, <br><br>Votre demande d'absence vient d'être refusée.<br><br>Récapitulatif de la demande<br> type : " + typeAbs + " du " + dateDebut + " au " + dateFin + ".<br><br>Motif du refus: " + feuille.getRange(ligne, 8).getValue();
}
MailApp.sendEmail(adresseEmail, sujet, corps);
}
}
bonjour,
je crois que pour supprimer le double pop-up, il faut supprimer le nom onEdit de la fonction, et créer un déclencheur sur la modification de la cellule... ca évitera le doublement de la fenêtre de confirmation...
Bonjour,
Chez moi qu'une seule popup, je t'ai fais une demande d'accès, tu as peut être un déclencheur qui double l'exécution ou bien une erreur sur un script.
J'ai réalisé une copie et ça ne fonctionne plus, même après avoir accepté d'exécuter le script : voici la copie ?? as tu une idée d'où ça peux venir ?
https://docs.google.com/spreadsheets/d/1VBPOKkVvAhC3_xSMFcVlFMf0AzeOcaDCBM8c3_P8T6g/edit?usp=sharing