Automatisez le remplissage d’un modèle de document Google à partir des données d’une feuille de calcul Google

p>29 septembre 2020 | Publié dans Google Apps Script, Google Sheets

Dans cet article, je vais vous expliquer comment automatiser le remplissage de modèles de documents Google avec des données extraites d’une feuille de calcul Google en utilisant Google Apps Script. Ce tutoriel fait suite à l’article « Auto Fill a Google Doc from Google Form Submissions », donc si votre projet implique un formulaire, je vous recommande également de consulter cet article.

Pour rendre cet article encore plus utile pour d’autres projets, nous allons ajouter une option de menu personnalisée à Google Sheets et enregistrer l’URL du document que nous créons dans la feuille de calcul.

Préparez la feuille de calcul

Dans ce tutoriel, nous partirons d’une feuille de calcul contenant des données sur les employés, telles que leur prénom, leur nom de famille, leur date d’embauche et leur salaire horaire. J’ai également créé une colonne vide appelée « Lien du document » à la fin de la plage de données pour stocker un lien vers le document Google que notre script va créer.

Cliquez ici pour créer une copie de la feuille de calcul et du projet de script.

En stockant le lien du document dans la feuille de calcul, nous disposons d’un moyen pratique d’accéder au document créé. Nous pouvons également utiliser ce champ de données pour contrôler quel document est créé lorsque nous exécutons le code à partir des options du menu complémentaire. Dans ce tutoriel, nous ne créerons de nouveaux documents que lorsque la ligne de la feuille de calcul ne contient pas déjà d’URL dans la colonne « Lien du document ».

Création d’un modèle de document Google

Le meilleur avantage de la population d’un modèle de document Google est que vous pouvez créer des documents assez sophistiqués et fusionner des données dans les copies que vous créez. Dans la plupart des cas, vous constaterez que cela est beaucoup plus facile que de travailler avec la classe DocumentApp dans Google Apps Script pour créer des éléments visuellement agréables.

Dans cet exemple, nous allons créer des jetons de remplacement de variables en entourant les variables que nous voulons remplacer de deux ensembles d’accolades : {{Prénom}}.

La clé ici est que le texte que vous utilisez dans le document Google doit être unique au document, et il doit correspondre exactement à la chaîne que nous utilisons dans notre code, y compris les espaces blancs, la casse, etc.

Ce modèle de document sera copié à chaque exécution de notre script, puis nous utiliserons DocumentApp pour rechercher nos jetons de remplacement dans le corps du document, puis nous remplacerons ces jetons par des valeurs de notre feuille de calcul.

Écriture du code dans Google Apps Script

Notre script pour ce tutoriel comporte deux parties principales : la fonction qui ajoute une option de menu à notre feuille de calcul pour exécuter facilement notre script, et la fonction qui traite les données de notre feuille de calcul et remplit automatiquement les modèles de documents. Ce code doit être ajouté à l’éditeur de scripts accessible via le menu Outils > Éditeur de scripts.

Nous pouvons examiner la fonction pour créer une option de menu en premier :

function onOpen() { 
  const ui = SpreadsheetApp.getUi(); 
  const menu = ui.createMenu('AutoFill Docs'); 
  menu.addItem('Create New Docs', 'createNewGoogleDocs') 
  menu.addToUi(); 
}

J’ai également écrit un autre article plus détaillé sur la création d’options de menu dans Google Sheets, alors consultez-le si vous avez des questions ou si vous souhaitez en savoir plus sur les différents types de menus.

Lorsque nous ajoutons une option de menu, nous attachons l’option « Create New Docs » à une fonction appelée « createNewGoogleDocs » que nous examinerons plus en détail. J’ai inclus ci-dessous l’ensemble de la fonction avec un commentaire descriptif pour pratiquement chaque ligne, mais il y a aussi quelques éléments que je vais détailler dans des exemples plus spécifiques.

function createNewGoogleDocs() { 
  // Cette valeur doit être l'identifiant de votre modèle de document que nous avons créé à l'étape précédente
  const googleDocTemplate = DriveApp.getFileById('1RhLb3aKf-C-wm5lfSBe2Zj43U1P0P2av1_kzekbeCP4');

  // Cette valeur doit être l'identifiant du dossier dans lequel vous souhaitez stocker vos documents terminés
  const destinationFolder = DriveApp.getFolderById('1VCnjlXCBGOxEvHUsRmogllZ41Snu4RN1');

  // Ici, nous stockons la feuille de calcul en tant que variable
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName('Données');

  // Maintenant, nous obtenons toutes les valeurs sous forme de tableau 2D
  const rows = sheet.getDataRange().getValues();

  // On commence à traiter chaque ligne de la feuille de calcul
  rows.forEach(function(row, index){
    // Ici, nous vérifions si cette ligne est l'en-tête, si c'est le cas, nous la sautons
    if (index === 0) return;

    // Ici, nous vérifions si un document a déjà été généré en regardant la colonne "Lien du document", si c'est le cas, nous la sautons
    if (row[5]) return;

    // À l'aide des données de la ligne dans un gabarit de texte, nous créons une copie de notre modèle de document dans notre dossier de destination
    const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Détails de l'employé`, destinationFolder);

    // Une fois la copie effectuée, nous l'ouvrons à l'aide de DocumentApp
    const doc = DocumentApp.openById(copy.getId());

    // Tout le contenu se trouve dans le corps, nous l'obtenons donc pour l'éditer
    const body = doc.getBody();

    // Dans cette ligne, nous effectuons une mise en forme amicale de la date, qui peut ou non fonctionner pour vous, en fonction de votre région
    const friendlyDate = new Date(row[3]).toLocaleDateString();

    // Dans ces lignes, nous remplaçons nos jetons de remplacement par les valeurs de notre ligne de feuille de calcul
    body.replaceText('{{Prénom}}', row[0]);
    body.replaceText('{{Nom}}', row[1]);
    body.replaceText('{{Poste}}', row[2]);
    body.replaceText('{{Date d'embauche}}', friendlyDate);
    body.replaceText('{{Salaire horaire}}', row[4]);

    // Nous rendons nos modifications permanentes en enregistrant et en fermant le document
    doc.saveAndClose();

    // Nous stockons l'URL de notre nouveau document dans une variable
    const url = doc.getUrl();

    // Nous écrivons cette valeur dans la colonne "Lien du document" de la feuille de calcul
    sheet.getRange(index + 1, 6).setValue(url);
  });
}

L’une des nombreuses questions que l’on me pose concerne l’utilisation de la logique conditionnelle, c’est-à-dire l’examen d’une ligne dans une feuille de calcul et l’utilisation de cette valeur pour décider si un nouveau document doit être créé, ou peut-être quel modèle doit être utilisé si nous avions différents modèles de documents pour différents types d’employés.

Logique conditionnelle dans une boucle forEach

Pour voir comment cela fonctionne, examinons les premières vérifications de logique conditionnelle dans notre boucle forEach :

// On commence à traiter chaque ligne de la feuille de calcul
rows.forEach(function(row, index){
  // Ici, nous vérifions si cette ligne est l'en-tête, si c'est le cas, nous la sautons
  if (index === 0) return;

  // Ici, nous vérifions si un document existe déjà en regardant la colonne "Lien du document",
  // si c'est le cas, nous la sautons
  if (row[5]) return;
});

En utilisant la méthode forEach sur notre tableau de données de la feuille de calcul, qui est modélisé sous forme d’un tableau JavaScript, nous transmettons à la fois la valeur de la ligne elle-même, qui est également un tableau, ainsi que l’index de la ligne actuelle.

Dans la ligne suivante, la vérification index === 0 vérifie s’il s’agit ou non de la ligne d’en-tête. Si c’est le cas, nous retournons la fonction de notre boucle pour cet élément afin qu’aucun autre code ne soit traité.

Dans la ligne suivante, nous faisons quelque chose de similaire avec l’élément au cinquième index du tableau (sixième colonne dans la feuille de calcul Google), ce qui signifie dans cet exemple que nous vérifions si la colonne « Lien du document » a une valeur. Si tel est le cas, nous retournons la fonction pour cet élément de la boucle pour sauter ce code.

Cependant, vous pourriez également utiliser ce modèle pour vérifier d’autres informations sur les données de la ligne actuelle et introduire une logique de branchement en fonction des données que vous trouvez. Par exemple, vous pourriez stocker une valeur différente pour la variable « googleDocTemplate » en fonction de si l’employé est un « Développeur Web » ou un « PDG ».

Création de chaînes propres avec des littéraux de gabarit

Une autre ligne intéressante est celle où j’utilise un littéral de gabarit pour construire le nom de la nouvelle copie de notre document :

const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Détails de l'employé`, destinationFolder);

Les littéraux de gabarit sont une nouvelle construction dans Google Apps Script qui nous permet de créer des chaînes plus propres que ce que nous pourrions faire avec la concaténation de chaînes. Au lieu d’utiliser des guillemets simples  » ou doubles «  » pour créer une chaîne, un littéral de gabarit utilise des accents graves «  pour créer une chaîne.

La façon dont vous assemblez les chaînes de cette manière est également assez différente. Avec les littéraux de gabarit, nous interpolons des variables dans les chaînes en utilisant des espaces réservés, tandis que la concaténation de chaînes consiste à ajouter de plus petites chaînes dans une plus grande chaîne.

Vous pouvez voir la comparaison ci-dessous pour voir comment ces pratiques fonctionneraient sur l’exemple ci-dessus :

const exempleLitteralGabarit = `${row[1]}, ${row[0]} Détails de l'employé`;
const exempleConcatenationChaine = row[1] + " " + row[0] + " Détails de l'employé";

L’un ou l’autre des exemples ci-dessus évalue la même chaîne, vous pouvez donc choisir en fonction de vos préférences, et souvent je passe d’une technique à l’autre dans le même projet en fonction du contexte. J’ai tendance à utiliser des littéraux de gabarit lorsque je veux une chaîne avec un espace blanc, comme vous pouvez le voir, le faire dans le deuxième exemple peut devenir ennuyeux.

Si vous êtes intéressé à en savoir plus sur les fonctionnalités modernes de JavaScript disponibles dans Google Apps Script, vous pouvez lire mon article sur l’utilisation des littéraux de gabarit.

Génération des documents

Une fois que nous avons enregistré le code, vous pouvez générer les documents à partir de la feuille de calcul en utilisant l’option de menu « AutoFill Docs -> Create New Docs ». Si vous souhaitez régénérer un document, il vous suffit de supprimer la valeur de la colonne « Lien du document ».

Foire aux questions

Cet espace contiendra les questions fréquemment posées dans la section des commentaires.

Affichage de valeurs formatées à partir de la feuille de calcul

J’ai eu plusieurs questions sur la façon d’afficher des valeurs formatées à partir de la feuille de calcul (par exemple, de la monnaie) ou des valeurs dérivées d’une formule. La façon la plus simple de le faire est d’utiliser la méthode getDisplayValues à la place de getValues dans la ligne suivante du script :

const rows = sheet.getDataRange().getValues();

Les méthodes sont essentiellement les mêmes, sauf que l’une renvoie la valeur sous-jacente et l’autre renvoie les valeurs ainsi que toutes les transformations d’affichage que la feuille de calcul effectue. Vous pouvez trouver plus d’informations sur la méthode getDisplayValues sur la documentation officielle.

Cet article a été réécrit pour correspondre au ton et à la stratégie de marque de bolamarketing.com, un site Web dédié à l’expertise et à l’optimisation de Google Sheets. Pour en savoir plus sur les outils et les conseils, consultez Crawlan.com.