Le suivi des données de base dans une feuille de calcul est assez simple – c’est se souvenir de mettre à jour vos données chaque jour, semaine ou mois qui peut être un problème. Mais pourquoi mettre à jour manuellement quand vous pouvez profiter de quelques outils d’automatisation simples ?

Voici un guide étape par étape pour créer une feuille Google mise à jour automatiquement qui récupère les données du Web et met automatiquement à jour la feuille de calcul. Nous utiliserons « température locale quotidienne à l’heure du déjeuner » dans cet exemple, mais le code peut être facilement modifié pour récupérer et stocker également d’autres données.

1. Créer une feuille de calcul Google Sheets

Se diriger vers Google Drive ou Feuilles Google et créer une nouvelle feuille de calcul vierge. (Si vous n’avez pas encore de compte Google, vous pouvez vous y inscrire.)

IDG/Sharon Machlis

Intitulez la feuille comme bon vous semble et vous êtes prêt à commencer. (La valeur par défaut sera « Feuille de calcul sans titre », mais vous pouvez cliquer dessus pour la remplacer par quelque chose qui l’identifiera mieux dans votre liste de documents.)

2. Trouvez le « XPath » pour la partie de la page Web que vous souhaitez gratter

Google Sheets a besoin d’un sélecteur « XPath » pour ne récupérer qu’une partie des données d’une page Web. Heureusement, il existe un outil pointer-cliquer facile à utiliser qui vous aide à faire exactement cela : SélecteurGadget. (Il y a une vidéo de moins de 2 minutes sur le site montrant comment cela fonctionne.)

Installez l’extension de navigateur SelectorGadget Chrome, activez-la en cliquant sur l’icône de l’outil de navigation, puis cliquez sur la section de la page que vous souhaitez gratter. Cette section deviendra verte. L’astuce consiste à s’assurer que vous êtes seul en sélectionnant cela. D’autres zones de la page peuvent également être vertes ou jaunes. Si tel est le cas, cliquez sur ceux que vous souhaitez supprimer de votre sélection.

Dans l’image ci-dessous, j’ai visité la page du National Weather Service pour Needham, MA, l’emplacement du siège social de Foundry. (Foundry est la société mère de Computerworld.)

Cette page était simple : j’ai cliqué sur la température, et c’était le seul élément sélectionné. Si d’autres étaient apparus en vert ou en jaune, j’aurais cliqué dessus pour les désactiver afin de m’assurer que mon XPath ne choisissait que ce dont j’avais besoin.

IDG/Sharon Machlis

En bas à droite, un sélecteur apparaît ; mais ça .myforecast-current-lrg est un CSS sélecteur. J’ai besoin de XPath, qui est une option tout en bas à droite. Cliquer sur XPathet la version XPath apparaîtra comme suit :

IDG/Sharon Machlis

XPath est souvent plus compliqué que CSS, mais vous n’avez pas à vous soucier de la signification de toutes les expressions. Le point clé est que le sélecteur XPath se retrouvera entouré de guillemets doubles dans votre formule Google Sheets. Malheureusement, le sélecteur lui-même comporte également des guillemets doubles. Cela causera des problèmes, donc si votre XPath inclut des guillemets doubles, remplacez-les tous manuellement par des guillemets simples.

Le sélecteur XPath que j’utiliserai dans ma feuille Google est maintenant

//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]  

3. Ajoutez votre formule

Les formules de feuille de calcul peuvent faire plus que des calculs mathématiques ; ils peuvent également extraire des données de pages Web. Pour une Google Sheet, la fonction pour lire une partie d’une page web est :

=ImportXML("URL", "XPath selector")

Pour ma page National Weather Service Needham, MA et le sélecteur de température, c’est

=IMPORTXML("https://forecast.weather.gov/MapClick.php?lat=42.2803&lon=-71.2345", "//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'myforecast-current-lrg', ' ' ))]")

Dans la première colonne de ma feuille de calcul, j’énumère l’emplacement que je suis. Cela facilitera l’ajout de lieux supplémentaires à ma feuille à l’avenir. Je vais mettre la formule dans la deuxième colonne.

IDG/Sharon Machlis

Et je veux la date et l’heure dans la colonne C. Nous nous en occuperons dans un instant.

4. Écrire une fonction pour récupérer et stocker des données

La façon dont la feuille de calcul est actuellement configurée, la température ne sera pas enregistrée ; il changera chaque fois que vous ouvrirez la feuille.

Garder historique données comme la feuille est actuellement conçue, je devrais copier et coller des valeurs manuellement dans une autre cellule chaque fois que je l’ouvrais. Ce n’est pas très évolutif ! Au lieu de cela, créons une nouvelle fonction pour 1) trouver la première ligne vide de la feuille et 2) copier la valeur de la cellule B2 dans une autre cellule vide pour le stockage.

Pour magasin données, nous devrons créer une fonction de feuille de calcul. Se diriger vers Extensions > Script d’applications pour créer des fonctions pour la feuille de calcul.

IDG/Sharon Machlis

Vous verrez une fenêtre contextuelle de fonction par défaut appelée myFunction.

IDG/Sharon Machlis

Remplacez ce nom de fonction par storeTemperature() (ou quel que soit le nom que vous lui donnerez) et utilisez le code suivant :

function storeTemperature() {
var sheet = SpreadsheetApp.getActiveSheet();
var firstEmptyRow = sheet.getLastRow() + 1;
// Get current temperature from cell B2
var currentTempCell = sheet.getRange(2, 2);
var currentTemp = currentTempCell.getValue();
// Find first empty cell in column B and add that value
var newTempCell = sheet.getRange("B" + firstEmptyRow);
newTempCell.setValue(currentTemp)


  

      
      
  

// Get the current date and time add it to same row column C
var currentDate = Date(); 
var newDateCell = sheet.getRange("C" + firstEmptyRow);
newDateCell.setValue(currentDate)
// Add place name in column A, currently hard coded
sheet.getRange("A" + firstEmptyRow).setValue("Needham, MA")
}

La première ligne de code crée une variable pour la feuille de calcul active, suivie d’une variable contenant le numéro de la première ligne vide de cette feuille.

Pour lire ou écrire des données dans une cellule Sheets, cette cellule doit être une objet de plage. Heureusement, il est facile de transformer la cellule d’une feuille de calcul emplacement dans une cellule objet de plage avec yourSheetObject.getRange(). Dans cet exemple, j’ai appelé mon objet de feuille sheet (vous pouvez l’appeler n’importe quoi), donc le code est sheet.getRange(). getRange prend plusieurs formats pour identifier un emplacement de cellule, y compris getRange(3,5) pour la ligne 3, la colonne 5 ou getRange("B " + 2) pour la syntaxe de cellule de feuille de calcul conventionnelle comme B2.

Dans cet esprit, je peux créer une variable contenant la plage de cellules pour la cellule B2, qui est la ligne 2 et la colonne 2, où je sais que ma température actuelle se trouve, en utilisant sheet.getRange(2, 2). J’ai ensuite lu cette valeur avec get.Value() comme vous pouvez le voir dans le code ci-dessus.

Ensuite, dans ce bloc de code, je trouve la première ligne vide dans la colonne B, où je veux ajouter la nouvelle température, et enregistre cette cellule en tant qu’objet de plage. Ensuite, il s’agit simplement de régler la valeur de cette cellule avec la température que je viens de stocker.

Les dernières lignes font quelque chose de similaire pour stocker la date et l’heure actuelles ainsi que l’emplacement codé en dur.

Il existe des moyens de rendre ce code plus compact, mais je partage une version détaillée pour faciliter la visualisation de ce qui se passe. Dans la dernière ligne, je l’ai un peu resserré en combinant getRange et setValue dans une seule ligne de code, au lieu de créer une variable distincte contenant la plage de cellules. Utilisez la syntaxe que vous préférez.

IDG/Sharon Machlis

Enregistrez-le en cliquant sur l’icône de la disquette au-dessus du code de fonction. Vous voudrez probablement changer le nom du projet par défaut en quelque chose d’autre. J’ai nommé le mien « Lunchtime Temps ».

Vous pouvez tester votre script en cliquant sur le Courir bouton pour voir ce qui se passe dans la feuille de calcul. Si tout se passe bien, vous aurez une nouvelle ligne de données avec la température actuelle.

5. Programmez votre fonction pour qu’elle s’exécute automatiquement

Dernière pièce : programmez votre fonction pour qu’elle s’exécute automatiquement. Pour ce faire, cliquez sur l’icône d’horloge à gauche pour ouvrir votre tableau de bord de script qui affiche les déclencheurs de votre projet en cours. (Il n’y en aura pas encore.) Vous devrez peut-être d’abord l’autoriser dans votre compte Google.

IDG/Sharon Machlis

Cliquer sur créer un nouveau déclencheuret un menu apparaît.

IDG/Sharon Machlis

Si vous souhaitez que votre feuille de calcul soit mise à jour selon un calendrier spécifique, modifiez la source de l’événement de « De la feuille de calcul » à « Déterminée par le temps », puis sélectionnez si vous souhaitez qu’elle s’exécute toutes les heures, tous les jours, toutes les semaines, tous les mois ou d’autres options.

Alors, voila ! Une feuille de calcul à mise à jour automatique qui collecte et stocke automatiquement les données.

Cet article a été initialement publié en avril 2017 et mis à jour en février 2023.