5 fonctions de script de Google Sheets à connaître

Google Sheets est un puissant outil de feuille de calcul basé sur le cloud qui vous permet de faire presque tout ce que vous pouvez faire dans Microsoft Excel. Mais la vraie puissance de Google Sheets réside dans la fonctionnalité de script Google qui l’accompagne.

La création de scripts Google Apps est un outil de création de scripts en arrière-plan qui fonctionne non seulement dans Google Sheets, mais également dans Google Docs, Gmail, Google Analytics et presque tous les autres services cloud de Google. Il vous permet d’automatiser ces applications individuelles et d’intégrer chacune de ces applications les unes aux autres.

Dans cet article, vous apprendrez à vous familiariser avec la création de scripts Google Apps, à créer un script de base dans Google Sheets pour lire et écrire des données de cellule, et à utiliser les fonctions de script avancées les plus efficaces de Google Sheets.

Comment créer un script Google Apps

Vous pouvez commencer dès maintenant à créer votre premier script Google Apps à partir de Google Sheets.

Pour ce faire, sélectionnez Outils du menu, puis Éditeur de script.

Cela ouvre la fenêtre de l’éditeur de script et utilise par défaut une fonction appelée ma fonction (). C’est ici que vous pouvez créer et tester votre script Google.

Pour tenter votre chance, essayez de créer une fonction de script Google Sheets qui lira les données d’une cellule, effectuera un calcul dessus et affichera la quantité de données dans une autre cellule.

La fonction pour obtenir des données d’une cellule est la getRange () et getValue () les fonctions. Vous pouvez identifier la cellule par ligne et colonne. Donc, si vous avez une valeur dans la ligne 2 et la colonne 1 (la colonne A), la première partie de votre script ressemblera à ceci:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

Cela stocke la valeur de cette cellule dans le Les données variable. Vous pouvez effectuer un calcul sur les données, puis écrire ces données dans une autre cellule. Donc, la dernière partie de cette fonction sera:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

Lorsque vous avez terminé d’écrire votre fonction, sélectionnez l’icône du disque pour enregistrer.

La première fois que vous exécutez une nouvelle fonction de script Google Sheets comme celle-ci (en sélectionnant l’icône d’exécution), vous devrez fournir une autorisation pour que le script s’exécute sur votre compte Google.

Autorisez les autorisations à continuer. Une fois votre script exécuté, vous verrez que le script a écrit les résultats du calcul dans la cellule cible.

Maintenant que vous savez comment écrire une fonction de script Google Apps de base, examinons quelques fonctions plus avancées.

Utilisez getValues ​​pour charger des tableaux

Vous pouvez amener le concept de faire des calculs sur les données de votre feuille de calcul avec des scripts à un nouveau niveau en utilisant des tableaux. Si vous chargez une variable dans un script Google Apps à l’aide de getValues, la variable sera un tableau qui peut charger plusieurs valeurs à partir de la feuille.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

La variable de données est un tableau multidimensionnel qui contient toutes les données de la feuille. Pour effectuer un calcul sur les données, vous utilisez un pour boucle. Le compteur de la boucle for fonctionnera sur chaque ligne et la colonne reste constante, en fonction de la colonne dans laquelle vous souhaitez extraire les données.

Dans notre exemple de feuille de calcul, vous pouvez effectuer des calculs sur les trois lignes de données comme suit.

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

Enregistrez et exécutez ce script comme vous l’avez fait ci-dessus. Vous verrez que tous les résultats sont remplis dans la colonne 2 de votre feuille de calcul.

Vous remarquerez que référencer une cellule et une ligne dans une variable de tableau est différent d’une fonction getRange.

Les données[i][0] fait référence aux dimensions du tableau où la première dimension est la ligne et la seconde est la colonne. Les deux commencent à zéro.

getRange (i + 1, 2) fait référence à la deuxième ligne lorsque i = 1 (puisque la ligne 1 est l’en-tête), et 2 est la deuxième colonne où les résultats sont stockés.

Utilisez appendRow pour écrire des résultats

Que faire si vous avez une feuille de calcul dans laquelle vous souhaitez écrire des données dans une nouvelle ligne au lieu d’une nouvelle colonne?

C’est facile à faire avec le appendRow fonction. Cette fonction ne dérangera aucune donnée existante dans la feuille. Il ajoutera simplement une nouvelle ligne à la feuille existante.

À titre d’exemple, créez une fonction qui comptera de 1 à 10 et afficher un compteur avec des multiples de 2 dans un Compteur colonne.

Cette fonction ressemblerait à ceci:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Voici les résultats lorsque vous exécutez cette fonction.

Traiter les flux RSS avec URLFetchApp

Vous pouvez combiner la fonction de script Google Sheets précédente et le URLFetchApp pour extraire le flux RSS de n’importe quel site Web et écrire une ligne dans une feuille de calcul pour chaque article récemment publié sur ce site Web.

Il s’agit essentiellement d’une méthode de bricolage pour créer votre propre feuille de calcul de lecteur de flux RSS!

Le script pour faire cela n’est pas trop compliqué non plus.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

Comme vous pouvez le voir, Xml.parse extrait chaque élément du flux RSS et sépare chaque ligne en titre, lien, date et description.

En utilisant le appendRow , vous pouvez placer ces éléments dans des colonnes appropriées pour chaque élément du flux RSS.

La sortie de votre feuille ressemblera à ceci:

Au lieu d’incorporer l’URL du flux RSS dans le script, vous pouvez avoir un champ dans votre feuille avec l’URL, puis plusieurs feuilles – une pour chaque site Web que vous souhaitez surveiller.

Concaténer des chaînes et ajouter un retour chariot

Vous pouvez pousser la feuille de calcul RSS un peu plus loin en ajoutant des fonctions de manipulation de texte, puis utiliser les fonctions de courrier électronique pour vous envoyer un courrier électronique avec un résumé de tous les nouveaux messages dans le flux RSS du site.

Pour ce faire, sous le script que vous avez créé dans la section précédente, vous voudrez ajouter un script qui extraira toutes les informations de la feuille de calcul.

Vous voudrez créer la ligne d’objet et le corps du texte de l’e-mail en analysant ensemble toutes les informations du même tableau «éléments» que vous avez utilisé pour écrire les données RSS dans la feuille de calcul.

Pour ce faire, initialisez l’objet et le message en plaçant les lignes suivantes avant la boucle For «items».

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Ensuite, à la fin de la boucle «items» for (juste après la fonction appendRow), ajoutez la ligne suivante.

message = message + title + 'n' + link + 'n' + date + 'n' + desc + 'n' + 'n n';

Le symbole «+» concaténera les quatre éléments ensemble suivi de « n» pour un retour chariot après chaque ligne. À la fin de chaque bloc de données de titre, vous aurez besoin de deux retours chariot pour un corps d’e-mail bien formaté.

Une fois que toutes les lignes sont traitées, la variable «corps» contient toute la chaîne du message électronique. Vous êtes maintenant prêt à envoyer l’e-mail!

Comment envoyer un e-mail dans le script Google Apps

La prochaine section de votre script Google consistera à envoyer le «sujet» et le «corps» par e-mail. Faire cela dans Google Script est très simple.

var emailAddress = myemail@gmail.com;
MailApp.sendEmail(emailAddress, subject, message);

MailApp est une classe très pratique dans les scripts Google Apps qui vous donne accès au service de messagerie de votre compte Google pour envoyer ou recevoir des e-mails. Grâce à cela, la seule ligne avec la fonction sendEmail vous permet d’envoyer n’importe quel e-mail avec uniquement l’adresse e-mail, la ligne d’objet et le corps du texte.

Voici à quoi ressemblera l’e-mail résultant.

La combinaison de la possibilité d’extraire le flux RSS d’un site Web, de le stocker dans une feuille Google et de vous l’envoyer avec des liens URL inclus, rend très pratique le suivi du dernier contenu de n’importe quel site Web.

Ce n’est qu’un exemple de la puissance disponible dans les scripts Google Apps pour automatiser les actions et intégrer plusieurs services cloud.