Google Sheets est un puissant tableur basé sur le cloud qui vous permet de faire presque tout ce que vous pouvez faire dans Microsoft Excel. Mais la véritable puissance de Google Sheets est la fonctionnalité de script Google qui l'accompagne.
Le script Google Apps est un outil de script en arrière-plan qui fonctionne non seulement dans Google Sheets mais aussi 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 à démarrer avec les scripts Google Apps, à créer un script de base dans Google Sheets pour lire et écrire des données de cellule et les feuilles de calcul Google avancées les plus efficaces. fonctions de script.
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 Outilsdans le menu, puis Éditeur de script.
Cela ouvre la fenêtre de l'éditeur de script et utilise par défaut une fonction appelée myfunction (). C'est ici que vous pouvez créer et tester votre script Google.
In_content_1 all: [300x250] / dfp: [640x360]->Pour lui donner un aperçu, essayez de créer une fonction de script Google Sheets qui lira les données d'une cellule, effectuera un calcul sur celle-ci et exportera le volume de données dans une autre cellule.
La fonction permettant d'obtenir des données à partir d'une cellule est les fonctions getRange ()et getValue (). 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();
}
Ceci stocke la valeur de cette dans la variable données. Vous pouvez effectuer un calcul sur les données, puis écrire ces données dans une autre cellule. La dernière partie de cette fonction sera donc:
var results = data * 100;sheet.getRange(row, col+1).setValue(results); }
Lorsque vous aurez fini d'écrire votre fonction, sélectionnez l'icône du disque à 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 qu'il a écrit les résultats du calcul dans la cellule cible.
Maintenant que vous savez comment écrire une fonction de script de base de Google Apps, jetons un œil à quelques fonctions plus avancées.
Utiliser getValues pour charger des tableaux
Vous pouvez prendre 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 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 data = sheet.getDataRange().getValues();
La variable de données est une multidimensionnelle tableau qui contient toutes les données de la feuille. Pour effectuer un calcul sur les données, vous utilisez une boucle pour. Le compteur de la boucle for fonctionnera sur chaque ligne et la colonne restera constante, en fonction de la colonne où vous souhaitez extraire les données.
Dans notre exemple de feuille de calcul, vous pouvez effectuer des calculs sur les trois lignes des 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 inscrits 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 de celui 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 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 les résultats
Et si vous avez une feuille de calcul où vous voulez écrire des données dans un nouveau ligne au lieu d'une nouvelle colonne?
Ceci est facile à faire avec la fonction appendRow. Cette fonction ne dérange aucune donnée existante dans la feuille. Il suffit d'ajouter une nouvelle ligne à la feuille existante.
Par exemple, créez une fonction qui comptera de 1 à 10 et affichera un compteur avec des multiples de 2 dans un compteur.
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 URLFetchApppour 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 ce faire 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.parseextrait chaque élément du flux RSS et sépare chaque ligne dans le titre, le lien, la date et la description.
En utilisant la fonction appendRow, vous pouvez placer ces éléments dans les colonnes appropriées pour chaque élément du flux RSS.
La sortie de votre feuille se présentera quelque chose comme ceci:
d'incorporer l'URL du 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.
Concaténer des chaînes et ajouter un retour chariot
Vous pouvez aller plus loin dans la feuille de calcul RSS en ajoutant des fonctions de manipulation de texte, puis utiliser les 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 un script qui extraira toutes les informations de la feuille de calcul.
Vous souhaiterez 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 "items" For.
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
Puis, à la fin de les «éléments» pour la boucle (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 "body" contient la totalité de la chaîne d'e-mail. 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 sera d'envoyer le «sujet» et le «corps» par e-mail. Faire cela dans Google Script est très facile.
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
La 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 courriels. Grâce à cela, la seule ligne avec la fonction sendEmail vous permet envoyer un e-mail avec seulement l'adresse e-mail, la ligne d'objet et le corps du texte.
Voici à quoi ressemblera l'e-mail résultant .
Combinant la capacité d'extraire le flux RSS d'un site Web, le stocker dans une feuille Google et vous l'envoyer avec les liens URL inclus, il est très pratique de suivre le 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.