5 fonctions de script Google Sheets que vous devez savoir

Google Sheets est un puissant outil de feuille de calcul basé sur le cloud qui vous permet de faire presque tout ce que vous pourriez faire dans Microsoft Excel. Mais la véritable puissance de Google Sheets est la fonction de script Google qui l'accompagne.

Google Apps Scripting est un outil de script de fond qui fonctionne non seulement dans Google Sheets, mais aussi sur Google Docs, Gmail, Google Analytics et presque tous les autres services Google Cloud. Il vous permet d'automatiser ces applications individuelles et d'intégrer chacune de ces applications entre elles.

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

Pour vous donner une chance, essayez de créer une fonction de script Google Sheets qui lira les données d'une cellule, effectuera un calcul dessus et sortira le montant des données à 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 Données = feuille.getRange (ligne, col).getValue (); 

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

 VAR RÉSULTATS = données * 100; feuille.getRange (ligne, col + 1).setValue (résultats); 

Lorsque vous avez terminé d'écrire votre fonction, sélectionnez l'icône de 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 Run), vous devrez fournir une autorisation au script pour exécuter sur votre compte Google.

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

Maintenant que vous savez comment rédiger une fonction de script Google Apps de base, jetons un coup d'œil à des fonctions plus avancées.

Utilisez GetValues ​​pour charger les tableaux

Vous pouvez prendre le concept de faire des calculs sur les données de votre feuille de calcul avec le script à un nouveau niveau en utilisant des tableaux. Si vous chargez une variable dans le 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 Données = feuille.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 à travers chaque ligne, et la colonne reste constante, basée sur la colonne où vous souhaitez retirer les données.

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

pour (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 dans votre feuille de calcul.

Vous remarquerez que référencer une cellule et une ligne dans une variable de tableau est différente de celle d'une fonction GetRange. 

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. Ces 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 les résultats

Et si vous avez une feuille de calcul où 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 va simplement ajouter une nouvelle ligne à la feuille existante.

Par exemple, faites une fonction qui comptera de 1 à 10 et montrera un compteur avec des multiples de 2 dans un Comptoir colonne.

Cette fonction ressemblerait à ceci:

function myFunction () var sheet = SpreadsheetApp.getActiveSheet (); pour (var i = 1; i<11; i++)  var result = i * 2; sheet.appendRow([i,result]);  

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

Le processus RSS alimente avec URLFetchApp

Vous pouvez combiner la fonction de script Google Sheets précédente et le Urlfetchapp Pour tirer le flux RSS de n'importe quel site Web, et écrire une ligne sur 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 RSS Feed Reader!

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

function myFunction () var sheet = SpreadsheetApp.getActiveSheet (); VAR Élément, date, titre, lien, desc; var txt = urlfetchApp.fetch ("https: // www.topsecretwriters.com / rss ").getContentText (); var doc = xml.Parse (txt, false); titre = doc.getElement ().getElement ("canal").getElement ("titre").getText (); var items = doc.getElement ().getElement ("canal").getElements ("item"); // analyse des éléments uniques dans le flux RSS pour (var i en éléments) item = items [i]; titre = article.getElement ("titre").getText (); lien = élément.getElement ("lien").getText (); date = article.getElement ("Pubdate").getText (); desc = article.getElement ("Description").getText (); feuille.APPENDROW ([Titre, Link, Date, DESC]); 

Comme tu peux le voir, Xml.analyse Sorte chaque élément du flux RSS et sépare chaque ligne dans le titre, le lien, la date et la description. 

En utilisant le appendrow fonction, vous pouvez mettre 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 de flux RSS dans le script, vous pouvez avoir un champ dans votre feuille avec l'URL, puis avoir plusieurs feuilles - une pour chaque site Web que vous souhaitez surveiller.

CONCATENER les chaînes et ajouter un retour en chariot

Vous pouvez faire plus loin la feuille de calcul RSS en ajoutant des fonctions de manipulation de texte, puis utiliser des fonctions de messagerie pour vous envoyer un e-mail 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 des scripts qui extraire toutes les informations de la feuille de calcul. 

Vous voudrez construire la ligne d'objet et le corps de texte de messagerie en analysant toutes les informations du même tableau «éléments» que vous avez utilisé pour écrire les données RSS à la feuille de calcul. 

Pour ce faire, initialisez le sujet et le message en plaçant les lignes suivantes avant les «éléments» pour la boucle.

var sujet = '10 derniers articles publiés sur mysite.com '
var message = "

Ensuite, à la fin des «éléments» pour Loop (juste après la fonction APPENDROW), ajoutez la ligne suivante.

Message = message + title + '\ n' + lien + '\ n' + date + '\ n' + desc + '\ n' + '\ n \ n';

Le symbole «+» concaténera les quatre éléments ensemble suivis par «\ n» pour un retour en chariot après chaque ligne. À la fin de chaque bloc de données de titre, vous voudrez deux retours de chariot pour un corps de messagerie bien formaté.

Une fois toutes les lignes traitées, la variable «corps» contient la chaîne de messagerie électronique entière. Maintenant, vous êtes prêt à envoyer l'e-mail!

Comment envoyer un e-mail dans le script Google Apps

La prochaine section de votre script Google sera d'envoyer le «sujet» et le «corps» par e-mail. Faire cela dans Google Script est très facile.

var emailaddress = myemail @ gmail.com;
MailApp.SendEmail (EmailAddress, Sujet, Message);

Le MailApp est une classe très pratique à l'intérieur des 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 ligne unique avec la fonction SendEmail vous permet d'envoyer n'importe quel e-mail avec seulement l'adresse e-mail, la ligne d'objet et le texte du corps.

C'est à quoi ressemblera l'e-mail qui en résulte. 

En combinant la possibilité d'extraire le flux RSS d'un site Web, de le stocker dans une feuille Google et de l'envoyer avec des liens URL inclus, rend très pratique de suivre le dernier contenu pour n'importe quel site Web.

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