Google Script - Remplissage et Création Quittances puis envoie par mail

Question

Je pensais être capable de créer ce modèle mais je m'aperçois que ce n'est toujours pas le cas ! Serait-il possible que vous m'aidiez à élaborer un script pour effectuer les 2 opérations suivantes: 1- Créer Quittances en les sauvegardant dans mon Drive en pdf 2- Envoyer par mail les quittances à mes locataires Pour vous donner toutes les infos utiles pour chacune de ces étapes, voici le détail de comment je souhaiterais que cela fonctionne: 1re étape: - Via l'onglet "Gestion Quittances", je gère quel locataire doit recevoir sa quittance de loyer, via les cases à cocher - Via le menu horizontal et en cliquant sur le bouton "Créer Quittances", je souhaiterais que le script créer autant de quittances qu'il y a de cases à cocher, en fonction du lieu de résidence du locataire. Je m'explique: Si le locataire Mme CHARLES LÉONIE habitant dans l'appartement de MONTAUBAN doit recevoir ses quittances de loyer de Octobre, Novembre et Janvier (cases cocher), alors lorsque je clique sur le bouton du menu, les quittances d'Octobre à Janvier doivent être créer selon le modèle s'appelant "Quittance MONTAUBAN", transformer en fichier pdf (avec le nom de fichier "Nom Locataire - Date Quittance") et stocké dans mon drive dans un dossier qui s'appellera certainement "Quittances MONTAUBAN". Idem, pour les autres locataires, vivant dans les autres appartement. Bien sur, les quittances devront se remplir automatiquement avec les infos liées au locataire et au Bailleur 2e étape: - Via le menu horizontal et en cliquant sur le bouton "Envoyer Mail", je souhaiterais que le script envoie un mail par locataire (mail bailleur ajouté en copie du message) avec toutes les quittances en pièces jointes qui ont été créer au préalables et qui concerne uniquement le locataire ! Une fois les quittances envoyées, les dossiers de stockage des quittances devront être purgés et ces quittances devront être archivé dans un dossier qui s'appellera "Dossier Archivage Quittances". Pour effectuer le script, voici le lien pour accéder au sheet + script dans le quel j'ai créé les variables des sheets : https://docs.google.com/spreadsheets/d/1WPKyG_xA6gwQf9n3ytd2NwzpGj9eYfM3SYavtww1HSU/edit?usp=sharing Je vous remercie énormément pour votre aide et le temps que vous passerez à la création de ce modèle! Bonnes fetes de fin d'années. Cordialement. Vincent...

сталиевич · Accepted Answer

1- fait, mais pas testé (je pense que cela fonctionne) 2- idem, a priori les noms es fichiers acceptent les / 3- ok, fait pour Montauban, je te laisse reproduire pour les autres (j'ai vu qu'il y avait quelques différences) A noter que j'ai créé une feuille spécifique d paramètres pour mettre à jour les quittances, ce sera plus net ...

сталиевич · Answer

Voici une maquette qui pourrait servir de base, sachant que pour simplifier, je ferai la quittance et l'envoi simplement en cliquant sur la case à cocher (donc sans menu). Mais il faut effectivement renseigner au préalable le modèle de quittance. function envoiCopieFeuillePDF() { DocumentApp.getActiveDocument(); DriveApp.getFiles(); // variables const doc = SpreadsheetApp.getActive(); const docID = '________mettre_ici_l_ID___du___fichier_____________'; const feuilleID = '__ID_feuille__'; const email = '___email_destinataire____; const dossier = DriveApp.getFolderById('__________ID_du_dossier______________'); const d = Utilities.formatDate(new Date(), "GMT+1", "yyyyMMdd") const fichier = '____nom___du___fichier____suivi_ici_de_sa_date____________' + "_" + d + ".pdf" const objet = 'Test pdf'; const corps = "Veuillez trouver ci-joint ..."; // Création du fichier pdf const url = 'https://docs.google.com/spreadsheets/d/' + docID + '/export?'; const exportOptions = 'exportFormat=pdf&format=pdf' + '&size=A4' + '&portrait=true' + // orientation portrait, false pour paysage '&fitw=false' + // pas d'ajustement en largeur '&sheetnames=false&printtitle=false' + // pas de nom ni de titre à l'impression '&pagenumbers=false&gridlines=false' + // pas de numérotation, pas de grille '&fzr=false' + // frozen rows = pas de répétition de l'en-tête '&gid=' + feuilleID; var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}}; var reponse = UrlFetchApp.fetch(url + exportOptions, params).getBlob(); // Envoi email avec fichier attaché GmailApp.sendEmail(email, objet, corps, { htmlBody: corps, attachments: &#91;{ fileName: fichier, content: reponse.getBytes(), mimeType: "application/pdf" }&#93; }); // Sauvegarde du fichier. dossier.createFile(reponse.setName(fichier)); } Je regarderai si besoin plus en détail dans la journée...

сталиевич · Answer

Pour débuter ...

Tu peux exécuter une première fois la fonction activate afin d'autoriser google à exécuter le script

Ensuite, j'ai programmé une première fonction qui donnera les informations essentielles quand tu cliques sur une case à cocher ...

function activate(){
  SpreadsheetApp.getActive().toast('Le script est maintenant actif !')
}
function onEdit(event){
  var f = event.source.getActiveSheet();
  var cel = event.source.getActiveRange();
  if (cel.getValue()==true && f.getName() == 'Gestion Quittances'){
    var quand = f.getRange(1,cel.getColumn()).getDisplayValue()
    var ou = f.getRange(cel.getRow(),1).getValue()
    var qui = f.getRange(cel.getRow(),2).getValue()
    SpreadsheetApp.getActive().toast(quand+' '+ou+' '+qui)

  }
}

À partir de là, je vais maintenant renseigner la quittance ...

сталиевич · Answer

...pour renseigner la quittance, nous allons chercher la ligne où se situe les infos du locataires.

Pour cela, j'ai ajouté cette partie dans le script

    var ligne = -1
    var doc = SpreadsheetApp.getActiveSpreadsheet()
    var dataLocataires = doc.getSheetByName("Infos Locataires").getDataRange().getValues()
    for (var i=0;i<dataLocataires.length;i++){
      if (dataLocataires[i][1] == qui){
        ligne = i
      }
    }
    if (ligne==-1){
      Browser.msgBox('Locataire non trouvé !')
      return
    }
    f.getRange('C1').setValue(ligne)

j'ai profité que C1 était libre pour y mettre le n° trouvé

A partir de là, il faudrait reprendre les formules du type

='Infos Locataires'!B2

et mettre

=OFFSET('Infos Locataires'!B1;'Gestion Quittances'!C1;)

dans les onglets quittance (que je ne peux pas faire sauf à perturber les protections mises ne place)

On terminera ensuite par l'enregistrement et l'envoi du mail.

сталиевич · Answer

Ok, je vais donc poursuivre ... oui, tu as raison il y a 2 grandes possibilités soit tu le fais via le script, avec un inconvénient est que l'écriture dans la feuille peut prendre du temps dès lors que l'on effectue plusieurs setValue() puisque les données sont disséminées; mais comme j'ai vu que tu utilisais un lien vers la feuille Gestion Quittances, j'ai trouvé cela intéressant et plus souple car tu peux modifier la mise en forme sans intervenir sur le script (celui-ci se contenant de rechercher le n° de la ligne concernée); la seule obligation sera de faire un flush()...

сталиевич · Answer

Il faudra aussi le faire pour les autres zones comme l'adresse, les prix, etc. ... je te laisse faire car je suis toujours bloqué sur les protections...

сталиевич · Answer

Peut-être si en effet il n'y a pas de spécificité entre les différentes villes !

J'ai mis en pause car j'ai un blocage sur un script qui pourtant fonctionnait. J'ai pis une copie car il se peut que ce soit pace que tu es propriétaire du script et je ne suis que le modificateur ... je verrai cela dans la journée.

сталиевич · Answer

Voilà ...

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('** ACTIVER **')
  .addItem('👉 Activer', 'activate')
  .addToUi();
}
function activate(){
  if(!isTrigger('onSpeEdit')) {
    ScriptApp.newTrigger('onSpeEdit')
      .forSpreadsheet(SpreadsheetApp.getActive())
      .onEdit()
      .create();
  }
  SpreadsheetApp.getActive().toast('Le script est maintenant actif !')
}
function quota(){
  Logger.log(MailApp.getRemainingDailyQuota())
}
function onSpeEdit(event){
  var f = event.source.getActiveSheet();
  var cel = event.source.getActiveRange();
  if (cel.getValue()==true && f.getName() == 'Gestion Quittances'){
    var quand = f.getRange(1,cel.getColumn()).getDisplayValue()
    var ou = f.getRange(cel.getRow(),1).getValue()
    var qui = f.getRange(cel.getRow(),2).getValue()

    var ligne = -1
    var doc = SpreadsheetApp.getActiveSpreadsheet()
    var dataLocataires = doc.getSheetByName("Infos Locataires").getDataRange().getValues()
    for (var i=0;i<dataLocataires.length;i++){
      if (dataLocataires[i][1] == qui){
        ligne = i
      }
    }
    if (ligne==-1){
      Browser.msgBox('Locataire non trouvé !')
      return
    }
    f.getRange('C1').setValue(ligne)
    var email=dataLocataires[ligne][7]
    SpreadsheetApp.flush(); // permet une mise à jour de la feuille
    Utilities.sleep(200);

    envoiQuittance(email,qui,quand,ou,doc.getId(),doc.getSheetByName('Quittance '+ou).getSheetId().toString())

  }
}

function envoiQuittance(email,qui,quand,ou,docID,feuilleID) {

  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  const dossier = DriveApp.getFolderById('___________________________');

  const d = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd")
  const fichier = "Quittance_" + qui + '_' + d + ".pdf"
  const objet = 'Quittance du mois '+quand.substring(3);
  const corps = 'Veuillez trouver ci-joint votre quittance du mois '+quand.substring(3);

  // Création du fichier pdf
  const url = 'https://docs.google.com/spreadsheets/d/' + docID + '/export?';
  const exportOptions =
    'exportFormat=pdf&format=pdf' + 
    '&size=A4' + 
    '&portrait=true' +                     
    '&fitw=true' +                        
    '&sheetnames=false&printtitle=false' + // pas de nom ni de titre à l'impression
    '&pagenumbers=false&gridlines=false' + // pas de numérotation, pas de grille
    '&fzr=false' +                         // frozen rows = pas de répétition de l'en-tête
    '&gid=' + feuilleID;
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  var reponse = UrlFetchApp.fetch(url + exportOptions, params).getBlob();

  // Envoi email avec fichier attaché 
    GmailApp.sendEmail(email, objet, corps, {
      htmlBody: corps,
      attachments: [{
            fileName: fichier,
            content: reponse.getBytes(),
            mimeType: "application/pdf"
        }]
    });

  // Sauvegarde du fichier. 
  dossier.createFile(reponse.setName(fichier));

  SpreadsheetApp.getActive().toast('Quittance envoyée et enregistrée !')
}

// trigger management
function myTriggerSetup(nom) {
  if(!isTrigger(nom)) {
    ScriptApp.newTrigger(nom)
      .timeBased()
      .everyMinutes(1)
      .create();  
  }
}
function myTriggerDelete(nom){
     deleteTriggersByName(nom);
}
function deleteTriggersByName(name){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++){ 
    if (triggers[i].getHandlerFunction().indexOf(name) != -1) 
    {
      ScriptApp.deleteTrigger(triggers[i]);
    }
}}
function isTrigger(funcName) {
     var r=false;
     if(funcName) {
       var allTriggers=ScriptApp.getProjectTriggers();
       var allHandlers=[];
       for(var i=0;i<allTriggers.length;i++) {
         allHandlers.push(allTriggers[i].getHandlerFunction());
       }
       if(allHandlers.indexOf(funcName)>-1) {
         r=true;
       }
     }
     return r;
}

Il faut que tu mettes à jour l'ID du dossier de sauvegarde

const dossier = DriveApp.getFolderById('___________________________');

ensuite, il faut aller dans le menu personnalisé et activer (attention, pas compatible avec ton script qu'il faudrait mettre en veilleuse).

Enfin, on pourra voir ensemble comment actualiser les dates inscrites sur les quittances.

сталиевич · Answer

Ok, j'allais te mettre des commentaires sur la façon de faire ... ensuite je verrai pour modifier. Le cœur du projet est onEdit ... cette fonction réagit à une modification manuelle d'une cellule. En testant la feuille et la cellule concernées var f = event.source.getActiveSheet(); var cel = event.source.getActiveRange(); on peut alors rechercher les informations sur la même ligne et la même colonne en utilisant getRow() et getColumn(). var quand = f.getRange(1,cel.getColumn()).getDisplayValue() var ou = f.getRange(cel.getRow(),1).getValue() var qui = f.getRange(cel.getRow(),2).getValue() Ceci a permis de déterminer ou, quand et qui dans le script. Nota : la notion de date est hyper bien traitée dans GSheets, mais un peu déstabilisante car elle tient compte du fuseau horaire. Du coup, pour faire simple, j'emploie plutôt getDisplayValue() que getValue() pour avoir la date telle qu'elle est affichée (mais en texte). Pour rechercher l'email, il faut aller chercher une correspondance dans une autre feuille. Pour éviter un balayage ligne à ligne qui pourrait être long, j'absorbe les données dans un tableau (toujours à 2 dimensions) var dataLocataires = doc.getSheetByName("Infos Locataires").getDataRange().getValues() que je vais balayer (attention, les indices d'un tableau commencent à 0 alors que les lignes et colonnes d'une feuille commencent à 1). Je trouve (ou pas) la ligne concernée que je stocke dans une cellule afin de m'en servir dans les fonctions DECALER/OFFSET des quittances. Et du coup j'obtiens aussi l'email 8ème colonne donc indice 7 dans le tableau var email=dataLocataires&#91;ligne&#93;&#91;7&#93; Quand j'ai tout ceci et que la feuille de quittance est mise à jour (attention, il faut donc forcer la mise à jour de la feuille en cours de script en utilisant flush() sinon le script s'appuierait sur la feuille telle qu'elle était au début du script !) on peut envoyer par mail. Sauf que !! onEdit qui est un déclencheur simple https://developers.google.com/apps-script/guides/triggers ne peut pas faire certaines tâches pour des questions de sécurité, notamment lancer un email : il faut que tu donnes ta validation et que tu déclares formellement le déclencheur. Ici j'ai donc d'une part (et par habitude) changé le nom de onEdit en onSpeEdit (on aurait pu mettre autre chose) et j'ai facilité la tâche en mettant dans un script qui fera cette opération (que l'on réalise d'habitude à la main via l'horloge à gauche) ScriptApp.newTrigger('onSpeEdit') .forSpreadsheet(SpreadsheetApp.getActive()) .onEdit() .create(); avec en plus des fonctions de gestion des déclencheurs que j'ai trouvées intéressantes sur le net pour rendre plus "friendly" Pour le mail lui-même et l'enregistrement dans un dossier, il ne faut pas trop réfléchir ... j'ai pris ce que l'on trouve sur le net. Pas la peine de réinventer l'eau chaude. Du reste, j'ai tout un tas de fonctions que je me suis emmagasinées et que j'utilise comme des modules que j'assemble en leur passant les paramètres...

сталиевич · Answer

On peut s'en passer totalement, il suffit de déclarer manuellement le déclencheur sur onSpeEdit en cliquant sur l'horloge. On supprime aussi dans ce cas les fonctions de management des triggers (=déclencheurs). C'est juste une aide à la mise en œuvre. N'hésite pas à simplifier. supprime aussi cette fonction, comme j'étais bloqué je me demandais si cela ne venait pas de là ... aucun intérêt tant que tu n'as pas de centaines de mails à envoyer un jour donné c'est juste une garantie que les fonctions natives de GSheets aient pu traiter les formules les fonctions myTriggerDelete et deleteTriggersByName font partie de mon package de fonctions, donc je les ai collées ici mais elles ne sont pas utiles dans ton cas, et comme vu plus haut autant tout supprimer dès lors que le déclencheur est mis manuellement isTrigger permet de savoir s'il n'y a pas déjà un déclencheur sur onSpeEdit ... sinon on double la fonctions et on y met le bazar en doublent les mails Conclusion, pour simplifier, garde uniquement les fonctions function onSpeEdit(event){ } -et- function envoiQuittance(email,qui,quand,ou,docID,feuilleID) { } j'ai effacé le reste, mais tu peux les retrouver dans le post précédent ... https://forum.excel-pratique.com/sheets/google-script-remplissage-et-creation-quittances-puis-envoie.....

сталиевич · Answer

Je ne sais pas ! par contre il y a la possibilité de publier une application générique comme ici https://www.sheets-pratique.com/fr/telechargements/auteur-900...

сталиевич · Answer

Petite explication sur les dates ... Ta feuille est bien en timezone GMT+1/Paris = ok mais ton projet lui est en timezone America/New_York !! c'était il y a peu encore le timezone par défaut quand on ouvrait un nouveau fichier et un éditeur de script que l'on peut voir en cochant cette 3ème case du coup ... utiliser .getValue() sur une date qui par défaut est à 0h00 te renverrait alors à la veille (18h), et comme cette donnée de timezone n'est pas forcément visible, cela peut mettre un schmilblick dans la restitution des dates. C'est pourquoi, pour plus d'assurance, il vaut mieux utiliser .getDisplayValue()...

Gilbert_RGI · Answer

Bonjour et bonne Année En fouillant un peu ;-)) getDisplayValue() : getValue() : getDisplayValue renvoie la valeur telle que vous la voyez à l'écran, donc toujours une chaîne, tandis que getValue renvoie la valeur en dessous, donc un objet. Ce qui peut être une chaîne si la plage contient du texte...