Google Apps Script : créer un Google Docs à partir d’informations contenues dans un Google Sheets

1

Olivier travaille dans une usine de production d’arbres à came et vilebrequins pour moteurs de camions. Il est en charge de la qualité et donc de la gestion des rebuts. Pour traiter ces rebuts, Olivier affecte à chacun des collaborateurs de son équipe un ou plusieurs rebuts à réparer (voir article Comment partager des informations à ces collaborateurs). Il effectue cette opération chaque lundi, pour la semaine, avec son document Google Sheets (Cliquez sur ce lien pour obtenir une copie) comprenant deux onglets : 

GESTION REBUTS
  • GESTION FINANCIÈRE : cette onglet regroupe des informations financières issues de l’onglet LISTE REBUTS 

Les collaborateurs d’Olivier : Julie, Paul et Sophie, ne doivent pas avoir accès à cette feuille. Pour respecter cette dernière contrainte, Olivier ne partage donc pas son Google Sheets avec ses collaborateurs (Voir article sur les paramètres de partages). 

Pour que son équipe puisse voir les différents rebuts qu’ils ont à traiter, chaque personne possède un Google Sheets copié à partir d’un modèle (cliquez sur ce lien pour obtenir une copie). Olivier propage alors les informations (liste rebuts à traiter) vers le Google Sheets de chaque collaborateur.

Lorsqu’il a traité un rebut, chaque collaborateur doit créer une fiche d’information et la transmettre à Olivier. Voici les différentes étapes : 

  • Passage du statut du rebut à “Terminé” dans son Google Sheets. Ce statut signifie que les rebuts peuvent être renvoyés dans la chaîne de production. 
Statut Terminé
  • Saisie des informations du rebuts dans les champs identifiées par des accolades comme ci dessous. Les informations proviendront de son Google Sheets : 
    • N° rebut (format code barre et texte)
    • Date de retour 
    • Personne ayant réparé la pièce/le rebut
    • N° rebut au format texte
    • Cause rebut
  • Impression de la fiche retour 
  • Enregistrement du Google Docs / de la fiche retour dans un dossier drive intitulé “RETOURS”
  • Récupération du lien du Google Docs et copie de ce lien dans son Google Sheet 

Des étapes chronophages et sources d’erreurs : mauvais lien copié, oubli de mettre la fiche retour dans le dossier drive, …

L’objectif de cet article est de montrer comment automatiser la création, l’enregistrement et le partage d’un Google Doc contenant des champs de fusion en lien avec des informations stockées dans une feuille de calcul. 


Copie de la fiche retour dans un dossier drive spécifique

Tout d’abord créons un script à partir du Google Sheets utilisé par Olivier (Voir cet article sur la mise en place d’un script)

Puis déclarons notre première fonction 

function copieFicheRetour(idRebut) 
  • Cette fonction crée une copie du modèle de la fiche retour, change son nom et la place dans un dossier Drive choisi par Olivier. Le paramètre idRebut sera l’identifiant du rebut et servira à renommer le Google Docs copié; 

*ASTUCE* : Pour créer un modèle Google Docs, il faut identifier le texte statique et le texte dynamique représenté par des champs de fusion. Pour ces champs de fusion, utilisez des symboles facilement repérables dans un texte et n’étant pas utilisés dans vos données. Ensuite, une fois votre symbole choisi, par exemple les accolades, servez vous en comme délimiteur au texte qui sera remplacé.

Choisissez un texte avec du sens comme « DateRetour » (qui correspond au nom d’une colonne de votre table de données). Enfin vérifiez que ce texte ne soit pas identique au nom d’une variable récurrente de votre script afin d’éviter toute confusion.

Exemple : Le rebut doit être retourné le : {DateRetour}

var idModele="zefozhefana8192Edak"

  • La variable idModele contiendra l’identifiant de la fiche retour modèle. Vous pouvez retrouver cet identifiant dans l’URL de votre Google Docs
var dossierDestination=DriveApp.getFolderById("1Q-sBnha_n0x7vlmu6HR5Mxm9THwUpRq5");
  • DriveApp : cette classe permet de manipuler les documents et dossiers du Drive
  • getFolderById(id) : cette fonction permet de récupérer un dossier drive grâce à son identifiant. Ici nous allons récupérer le dossier où seront stockées les fiches retours. Vous pouvez récupérer cet identifiant dans l’URL de votre dossier. 
  • La variable dossierDestination contiendra notre dossier drive
return DriveApp.getFileById(idModele).makeCopy(idRebut,dossierDestination).getUrl()
  • getFileById(id) : permet de récupérer un document du Google Drive par son identifiant. Ici nous utilisons l’identifiant du Google Docs déclaré dans la variable idModele
  • makeCopy(nom,dossier) : créé une copie du document, la nomme avec la valeur de idRebut et place cette copie dans un dossier Drive prédéfini :
    • nom : le nom qui sera défini pour la copie du document. Ici nous utiliserons l’identifiant du rebut
    • dossier : le dossier de destination de la copie. Ici nous utiliserons le dossier récupéré dans la variable dossierDestination
  • getUrl() : permet de retrouver l’URL du document créé. Ici nous accédons à l’URL de la nouvelle fiche retour créée. 
  • return : renvoie l’URL de la nouvelle fiche retour 

Le code de notre première fonction :

function copieFicheRetour(idRebut) {
  
  var idModele="1GxjXsOyozwvM5JeeLKl2yGuJY_XdPZnZw_CFRg8ltNw";
  
  var dossierDestination=DriveApp.getFolderById("1Q-sBnha_n0x7vlmu6HR5Mxm9THwUpRq5");
  
  return DriveApp.getFileById(idModele).makeCopy(idRebut,dossierDestination).getUrl();
  
}

Création d’une fonction de remplacement de texte dans un Google Docs

La fiche retour pour le rebut est créée mais les informations présentes sont toujours celles de la fiche modèle. Créons une fonction qui remplace un texte (ou une chaîne de caractères) par un autre et le met en forme.

function remplacementTexte(texteARemplacer, texteRemplacement, urlDocs, policeEcriture, tailleTexte)
  • La fonction comprend quatre paramètres : 
    • texteARemplacer : le texte qui devra être remplacé dans le Google Docs
    • texteRemplacement : le texte qui remplacera texteARemplacer 
    • urlDocs : l’URL du document Google Docs
    • policeEcriture : la police d’écriture du texte une fois celui remplacé
    • tailleTexte : la taille du texte une fois celui remplacé

Définition du style de texte

var styleTexte = {};
  • Création d’un objet javascript vide. Cet objet contiendra le paramètre de style du texte
if(policeEcriture !=null && tailleTexte !=null ){
  • Si le paramètre de la police d’écriture et la taille du texte ne sont pas renseignés, le style sera défini avec des valeurs par défaut. Sinon le style sera défini avec la police et la taille passées en paramètre
style[DocumentApp.Attribute.FONT_FAMILY] = policeEcriture ;
  • DocumentApp : cette classe permet d’accéder aux fonctions et paramètres de Google Docs
  • Attribute : permet de retrouver les attributs généraux du Google Docs
  • FONT_FAMILY : l’attribut responsable de la police d’écriture
  • styleTextet[element]=valeur : définit un élément pour l’objet. Ici nous définissons que l’élément DocumentApp.Attribute.FONT_FAMILY dans l’objet style a pour valeur celle du paramètre policeEcriture
styleTexte[DocumentApp.Attribute.FONT_SIZE] = tailleTexte;
  • Le principe est le même que la ligne au-dessus. Ici au lieu de définir la police d’écriture, nous renseignons la taille du texte avec le paramètre tailleText
else{
  • La taille du texte ou la police d’écriture n’ont pas été renseignées, des valeurs par défaut vont être définies
styleTexte[DocumentApp.Attribute.FONT_FAMILY] ="Arial" ;   
  • Police d’écriture par défaut : Arial
styleTexte[DocumentApp.Attribute.FONT_SIZE] = 20 ;
  • Taille de texte par défaut : 20

Remplacement du texte 

DocumentApp.openByUrl(urlDocs).getBody().findText(texteARemplacer).getElement().asText().setAttributes(style);
  • openByUrl(url) : fonction permettant de récupérer le document Google Docs depuis l’URL, qui est en paramètre de la fonction 
  • getBody() : renvoie le corps du texte du Google Docs
  • findText(txt) : permet de rechercher un texte dans le Google Docs. Ici nous voulons retrouver le texte à remplacer comme par exemple {idRebut} 
  • getElement() : si la recherche ci-dessus n’est pas vide, getElement permet d’avoir le texte à remplacer en tant qu’élément du Google Docs
  • asText() : transforme l’élément en variable de type texte 
  • setAttribute(style) : définit le style pour le texte récupéré avec asText(). Dans notre cas, nous appliquons le style grâce à notre objet styleTexte
DocumentApp.openByUrl(urlDocs).getBody().replaceText(texteARemplacer,texteRemplacement);
  • replaceText(texte1,texte2) : remplace le texte1 par le texte2. La modification du style du texte effectuée au-dessus sera conservée et le texte entre accolade ({idRebut} par exemple) sera alors remplacé

Le code complet de la fonction :

function remplacementTexte(texteARemplacer, texteRemplacement, urlDocs, policeEcriture,tailleTexte){
  
  var styleTexte = {};
  
  if(policeEcriture!=null){
    
    styleTexte[DocumentApp.Attribute.FONT_FAMILY] =policeEcriture ;
    
    styleTexte[DocumentApp.Attribute.FONT_SIZE] = tailleTexte ;
  }
  
  else{
    
    styleTexte[DocumentApp.Attribute.FONT_FAMILY] ="Arial" ;
    
    styleTexte[DocumentApp.Attribute.FONT_SIZE] = 20 ;
    
  }
  
  DocumentApp.openByUrl(urlDocs).getBody().findText(texteARemplacer).getElement().asText().setAttributes(styleTexte);
  
  DocumentApp.openByUrl(urlDocs).getBody().replaceText(texteARemplacer,texteRemplacement);
  
}

Remplacement des données du Google Docs par les informations du rebuts du Google Sheets 

Nous avons à notre disposition une fonction de copie de la fiche retour et une fonction de remplacement du texte dans un Google Docs. Créons maintenant la fonction qui va placer les données du Google Sheets du collaborateur dans la fiche retour.

function remplacementDonnees(urlDocs,idRebut,dateRetour,collaborateur,causeRebut,typePiece)
  • Cette fonction comprend six paramètres : 
    • urlDocs : l’URL du Google Docs correspondant à la fiche retour qui va récupérer les données du rebut
    • ligne : la ligne sur laquelle est positionné le curseur dans le Google Sheets 
    • idRebut : l’identifiant du rebut
    • dateRetour :  la date du retour en production pour le rebut. Par défaut ce paramètre sera égal à la date du jour
    • collaborateur : le collaborateur en charge du rebut
    • causeRebut : la cause de la mise en rebut de la pièce
    • typeRebut : le type de pièce (vilebrequin, arbre à came,…)
remplacementTexte("{idRebutCodeBarre}",idRebut,urlDocs, 'Libre Barcode 128');
  • Nous appelons la fonction remplacementTexte avec les paramètres suivants : 
    • "{idRebutCodeBarre}" : la fonction va rechercher cette chaîne de caractères pour la remplacer. Ici nous remplaçons la partie du Google Docs qui va accueillir le code barre de l’identifiant du rebut
    • idRebut : la valeur de ce paramètre remplacera "{idRebutCodeBarre}"
    • urlDocs : l’url du document Google Docs où sera effectué le remplacement
    • 'Libre Barcode 128' : la police d’écriture permettant d’avoir un code barre
remplacementTexte("{idRebut}",idRebut,urlDocs);
  • Nous appelons la fonction remplacementTexte pour remplacer la chaîne {idRebut} par la valeur de idRebut sur le Google Docs correspondant à l’URL du paramètre urlDocs

Pour la suite nous effectuons la même opération pour des chaînes de caractères différentes : 

  • Date retour
remplacementTexte("{dateRetour}",dateRetour,urlDocs);
  • Cause rebut 

remplacementTexte("{causeRebut}",causeRebut,urlDocs);

  •   Type pièce
remplacementTexte("{typePiece}",typePiece,urlDocs);
  •   Collaborateur
 remplacementTexte("{collaborateur}",collaborateur,urlDocs);
return urlDocs;
  • renvoie l’url du Google Docs. Cette URL nous sera utile pour faire le lien entre le Google Sheets et la fiche retour.

Voilà la fonction :

function remplacementDonnees(urlDocs, idRebut,dateRetour,collaborateur,causeRebut,typePiece){  
  
  remplacementTexte("{idRebutCodeBarre}",idRebut,urlDocs, 'Libre Barcode 128');
  
  remplacementTexte("{idRebut}",idRebut,urlDocs);
  
  remplacementTexte("{dateRetour}",dateRetour,urlDocs);
  
  remplacementTexte("{causeRebut}",causeRebut,urlDocs);
  
  remplacementTexte("{typePiece}",typePiece,urlDocs);
  
  remplacementTexte("{collaborateur}",collaborateur,urlDocs);
  
  return urlDocs;
  
}

Appel des trois fonctions et lien entre le fiche retour et la liste des rebuts

Les trois fonctions précédentes sont nos outils pour créer une fiche retour et remplacer du texte dans cette fiche. Il faut maintenant utiliser ces outils en leur donnant les bons paramètres. Pour cela, créons un script regroupant tout le travail effectué .

var infos=SpreadsheetApp.getActive().getSheetByName("INFORMATIONS PERSONNELLES"); 
  • La classe SpreadsheetApp permet d’intervenir sur le fichier Google Sheets. 
  • getActive()  : permet de récupérer le Google Sheets à partir duquel est exécuté le script
  • getSheetByName("INFORMATIONS PERSONNELLES")  : permet de récupérer un onglet du Google Sheets par son nom. Ici nous supposons que l’onglet concerné est intitulé “INFORMATIONS PERSONNELLES” ( ATTENTION : les majuscules et minuscules sont importantes dans la recherche de l’onglet)
  • var infos : La variable infos est l’onglet du Google Sheets à partir duquel nous récupérons le nom et prénom du collaborateur
var rebuts=SpreadsheetApp.getActive().getSheetByName("LISTE REBUTS");
  • La variable rebuts est l’onglet du Google Sheets à partir duquel nous récupérons le informations du rebut
var ligne = SpreadsheetApp.getActiveRange().getRow()
  • getActiveRange() : renvoie la plage de cellules sélectionnée dans le Google Sheets. L’utilisation de la plage de cellule sélectionnée permet à l’utilisateur de se placer sur la ligne du rebut pour créer la fiche retour
  • getRow() : à partir de la plage de cellule, nous récupérons le numéro de de la première de la plage
  • var ligne : contient le numéro de la ligne du rebut
var idRebut = rebuts.getRange(ligne, 1).getValue()
  • getRange(ligne, colonne) : renvoie la cellule de l’onglet rebuts au coordonnées définies par les valeur de ligne et cellule. Dans notre cas la cellule à récupérer est celle contenant l’identifiant du rebut. C’est à dire celle à la ligne de la cellule sélectionnée et à la colonne 1
  • getValue() : renvoie la valeur de la cellule
  • var idRebut : récupère la valeur de la cellule. Dans notre cas l’identifiant du rebut
var dateRetour= Utilities.formatDate(new Date(),'GMT+2','dd/MM/YYYY');
  • Utilities : cette classe permet de manipuler des dates
  • formatDate(date,fuseau,format) : fonction transformant une date dans un format et fuseau horaire défini. Ici notre fuseau horaire sera celui de Paris (GMT+2) et le format sera similaire à 30/01/2020 correspondant au 30 janvier 2020. Nous définirons également la date à celle du jour avec new Date() qui renvoie la date courante
  • var dateRetour : contient la date au format texte
var collaborateur=infos.getRange(1,2).getValue()+' ' + infos.getRange(2,2).getValue();
  • infos.getRange(1,2).getValue() : récupère le nom du collaborateur contenu dans la cellule à la ligne 1 et colonne 2 dans l’onglet INFORMATIONS PERSONNELLES
  • infos.getRange(2,2).getValue() : récupère le prénom du collaborateur contenu dans la cellule à la ligne 2 et colonne 2 dans l’onglet INFORMATIONS PERSONNELLES
  • var collaborateur : contient la concaténation du nom et prénom avec un espace entre les deux
var causeRebut= rebuts.getRange(ligne, 3).getValue();
  • var causeRebut : contient la valeur de la cellule indiquant la cause du rebut, c’est à dire la cellule dans la colonne 3 de l’onglet REBUTS
var typePiece= rebuts.getRange(ligne, 2).getValue();
  • var typePiece : contient la valeur de la cellule indiquant le type de pièce du rebut
if(rebuts.getRange(ligne, 4).getValue()=="Terminé" ){
  • rebuts.getRange(ligne, 4).getValue() : récupération de la valeur dans la colonne 4, c’est à dire le statut du rebut
  • =="Terminé" : vérification que le statut du rebut de la ligne soit égal à “Terminé”
  • if(condition) : opérateur nous permettant de tester la condition ci-dessus. Dans notre cas si le statut du rebut est égal à Terminé alors nous procédons aux différentes actions de création de la fiche retour
var url=remplacementDonnees(copieFicheRetour(idRebut),ligne, idRebut);
  • copieFicheRetour(idRebut) : appel de la fonction créant une nouvelle fiche retour avec l’identifiant du rebut. Cette fonction nous renverra l’url de la fiche retour créée
  • remplacementDonnees(copieFicheRetour(idRebut),ligne, id)  : appel de la fonction qui remplacera les données dans la nouvelle fiche retour. Ici l’url de la fiche retour sera donnée par copieFicheRetour(idRebut)
  • var url : contient l’url de la nouvelle fiche retour
rebuts.getRange(ligne, 5).setValue(url);
  • getRange(ligne, 5) : récupère la cellule à la colonne 5 de la ligne du rebut
  • setValue(url) : insère l’url de la fiche retour dans la cellule à la colonne 5
rebuts.getRange(ligne, 4).setValue("Fiche retour imprimée");
  • getRange(ligne, 5) : récupère la cellule à la colonne 4 de la ligne du rebut
  • setValue("Fiche retour imprimée") : insère le nouveau statut du rebut dans la cellule à la colonne 4

 else{Browser.msgBox("Statut incorrect") }

  • Si le statut du rebut n’est pas à “Terminé” alors un message s’affiche avec Browser.msgBox() pour indiquer que le statut est incorrect

Voici donc le code pour cette fonction :

function main(){
  
  var infos=SpreadsheetApp.getActive().getSheetByName("INFORMATIONS PERSONNELLES");
  
  var rebuts=SpreadsheetApp.getActive().getSheetByName("LISTE REBUTS");
  
  var ligne = SpreadsheetApp.getActiveRange().getRow()
  
  var idRebut = rebuts.getRange(ligne, 1).getValue()
  
  var dateRetour= Utilities.formatDate(new Date(),'GMT+2','dd/MM/YYYY');
  
  var collaborateur= infos.getRange(1,2).getValue()+' ' + INFOS.getRange(2,2).getValue();
  
  var causeRebut= rebuts.getRange(ligne, 3).getValue();
  
  var typePiece= rebuts.getRange(ligne, 2).getValue();
  
  if(rebuts.getRange(ligne, 4).getValue()=="Terminé" ){
    
    var url=remplacementDonnees(copieFicheRetour(idRebut),ligne, idRebut);
    
    rebuts.getRange(ligne, 5).setValue(url);
    
    rebuts.getRange(ligne, 4).setValue("Fiche retour imprimée");
    
  }
  
  else{
    
    Browser.msgBox("Statut incorrect")
  }
}

Un partie des opérations de création de la fiche retour pour rebut est automatisée et limite les sources d’erreur.

Voici donc le code complet 

function copieFicheRetour(idRebut) {
  
  var idModele="1GxjXsOyozwvM5JeeLKl2yGuJY_XdPZnZw_CFRg8ltNw"
  
  var dossierDestination=DriveApp.getFolderById("1Q-sBnha_n0x7vlmu6HR5Mxm9THwUpRq5");
  
  return DriveApp.getFileById(idModele).makeCopy(idRebut,dossierDestination).getUrl()
  
}

function remplacementTexte(texteARemplacer, texteRemplacement, urlDocs, policeEcriture,tailleTexte){
  
  var styleTexte = {};
  
  if(policeEcriture!=null){
    
    styleTexte[DocumentApp.Attribute.FONT_FAMILY] =policeEcriture ;
    
    styleTexte[DocumentApp.Attribute.FONT_SIZE] = tailleTexte ;
  }
  
  else{
    
    styleTexte[DocumentApp.Attribute.FONT_FAMILY] ="Arial" ;
    
    styleTexte[DocumentApp.Attribute.FONT_SIZE] = 20 ;
    
  }
  
  DocumentApp.openByUrl(urlDocs).getBody().findText(texteARemplacer).getElement().asText().setAttributes(styleTexte);
  
  DocumentApp.openByUrl(urlDocs).getBody().replaceText(texteARemplacer,texteRemplacement);
  
}



function remplacementDonnees(urlDocs, idRebut,dateRetour,collaborateur,causeRebut,typePiece){  
  
  remplacementTexte("{idRebutCodeBarre}",idRebut,urlDocs, 'Libre Barcode 128');
  
  remplacementTexte("{idRebut}",idRebut,urlDocs);
  
  remplacementTexte("{dateRetour}",dateRetour,urlDocs);
  
  remplacementTexte("{causeRebut}",causeRebut,urlDocs);
  
  remplacementTexte("{typePiece}",typePiece,urlDocs);
  
  remplacementTexte("{collaborateur}",collaborateur,urlDocs);
  
  return urlDocs;
  
}

function main(){
  
  var infos=SpreadsheetApp.getActive().getSheetByName("INFORMATIONS PERSONNELLES");
  
  var rebuts=SpreadsheetApp.getActive().getSheetByName("LISTE REBUTS");
  
  var ligne = SpreadsheetApp.getActiveRange().getRow()
  
  var idRebut = rebuts.getRange(ligne, 1).getValue()
  
  var dateRetour= Utilities.formatDate(new Date(),'GMT+2','dd/MM/YYYY');
  
  var collaborateur=infos.getRange(1,2).getValue()+' ' + INFOS.getRange(2,2).getValue();
  
  var causeRebut= rebuts.getRange(ligne, 3).getValue();
  
  var typePiece= rebuts.getRange(ligne, 2).getValue();
  
  if(rebuts.getRange(ligne, 4).getValue()=="Terminé" ){
    
    var url=remplacementDonnees(copieFicheRetour(idRebut),ligne, idRebut);
    
    rebuts.getRange(ligne, 5).setValue(url);
    
    rebuts.getRange(ligne, 4).setValue("Fiche retour imprimée");
    
  }
  
  else{
    
    Browser.msgBox("Statut incorrect")
  }
}

Un envoi par e-mail de fiche retour en pdf à la production ou une vérification que la sélection de la cellule soit bien dans l’onglet LISTE REBUT pourraient perfectionner ce script… mais peut-être dans un prochain épisode ! 

Avez-vous un cas d’automatisation auquel Google Apps Script pourrait répondre ? Merci pour vos commentaires.


Vous avez un projet autour de G Suite
et vous souhaitez nous solliciter :

formation | développement | paramétrage G Suite | interventions | …


Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

1 COMMENTAIRE

  1. Formidable cet article ! Des explications TRÈS détaillées pour chaque ligne, sur chaque script ! QUEL TRAVAIL ! Comprehensible pour tous les niveaux .. MERCI.

LAISSER UN COMMENTAIRE

S'il vous plaît entrez votre commentaire!
S'il vous plaît entrez votre nom ici

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.