Automatiser la mise à jour des données dans Google Sheets grâce à Big Query et Google App Script

Le besoin de suivi en temps réel d’indicateurs issus de données ouvertes n’est pas nouveau, mais se fait sentir de façon plus aigüe en cette période de crise sanitaire. Aujourd’hui de nombreux projets permettent de visualiser l’évolution de la propagation du Covid-19 en temps réel, mais ne permettent pas forcément de faire toutes les opérations que l’on souhaiterait sur les données brutes. Pour autant, développer son propre dashboard de A à Z, à partir d’open data, peut être très chronophage. Dans cet article, je vais vous montrer comment créer son propre dashboard de suivi de l’évolution du coronavirus sur des Google sheets, avec une mise à jour automatique des données quotidiennes. Vous pouvez visualiser le résultat dans ce dashboard de suivi de l’évolution du coronavirus en France. Il me permet notamment de comparer les courbes de la France avec celles des autres pays, avec la possibilité de définir un ratio de mise à l’échelle pour chaque pays ainsi qu’un nombre de jours de décalage.

Disclaimer: L’objet de cet article n’est pas de commenter les courbes dont l’interprétation est rendue très difficile par les différences entre les pays et la complexité du phénomène, mais de vous donner les outils permettant de réaliser vos propres dashboards très facilement. Vous pourrez notamment faire une copie du dashboard pour l’adapter à vos propres besoins.

À noter que le dashboard partagé dans cet article ne se met pas à jour automatiquement pour la partie Big Query. J’ai en effet fait une copie avec mon compte Google personnel d’un dashboard développé à l’origine sur mon compte Google professionnel afin de pouvoir partager le dashboard sans contrainte d’appartenance à Publicis Sapient Engineering. Comme il se trouve que le connecteur Big Query / Google Sheets n’est pas disponible pour les comptes personnels, les données Big Query ne se mettent pas à jour mais vous pourrez tout de même voir la requête utilisée.

Les 2 outils abordés dans cet article sont:

• Le connecteur Big Query / Google Sheets disponible uniquement pour les comptes pro.
• La fonction IMPORTDATA et la façon de l’automatiser.

Importer des données depuis Big Query grâce au connecteur Big Query / Google Sheets

Les ensembles de données publics de Big Query

Depuis le 30 mars 2020, Google a mis à disposition des données sur la propagation du Covid-19 dans la liste de ses datasets publics Big Query. Pour les consulter, accédez à https://console.cloud.google.com avec votre compte Google, sélectionnez « Big Query » dans le menu principal (barres horizontales en haut à gauche de l’écran d’accueil) dans la section « Big Data », et cliquez sur « Accéder à des ensembles de données publiques ». Si vous ne l’avez pas encore fait, vous devrez renseigner une carte bancaire pour démarrer un essai gratuit, sachant que vous ne serez pas débité de façon automatique à la fin de l’essai sans l’avoir explicitement autorisé.

Une fois le dataset qui vous intéresse trouvé, vous pouvez l’explorer via l’interface Big Query pour construire la requête dont vous voudrez importer les résultats dans Google Sheets.

Activer le connecteur Big Query / Google Sheets

Le connecteur est très facilement accessible dans Données → Connecteur de données:

Rappel: cette fonctionnalité n’est malheureusement pas disponible pour les comptes personnels.

Après avoir choisi le projet de facturation, vous n’aurez plus qu’à écrire la requête que vous souhaitez importer dans la feuille sélectionnée.

Quelques limitations à prendre en compte:

• L’extraction de données est limitée à 10000 lignes.
• Les requêtes sont limitées à une durée de 4 minutes 30 secondes.

Ça y est ! L’output de la requête est disponible au traitement dans la feuille de calcul:

Un bouton « Actualiser » permet de rejouer la requête très facilement, avec l’information correspondant au volume de données traitées (entouré en rouge sur la capture d’écran). Dans le cas du dataset Covid-19 cela n’a pas d’importance car Google a décidé de ne pas le facturer, mais c’est un élément important à surveiller d’une manière générale. Google ne facture pas le premier téraoctet de traitement par mois, puis facture 5$ le téraoctet. Avec notre requête de 1,72MB on reste très large dans tous les cas .
Si vous rentrez dans le détail de la requête (accessible en cliquant « Afficher la requête » dans la Google Sheet partagée en début d’article), vous verrez que j’ai sélectionné uniquement les pays avec lesquels je voulais comparer la France, et que j’ai calculé l’agrégation mondiale directement dans la requête Big Query. Cela me permet d’être sûr de rester durablement en-dessous de la limite des 10000 lignes d’output.

Automatiser le lancement quotidien de la requête grâce à Google App Script

Le bouton « Actualiser » permet de relancer facilement la requête à la main et c’est très bien, mais notre besoin est d’automatiser complètement la mise à jour quotidienne des données. Il suffit pour cela de créer la fonction suivante dans l’éditeur de scripts accessible dans Outils → Éditeur de scripts:

L’automatisation de la requête se fait alors de façon très intuitive, en cliquant sur l’icône d’horloge situé à côté du bouton d’exécution:

Vous pourrez décider d’exécuter la requête une fois par jour ou à intervalle de temps régulier. Ça y est, vous avez un dashboard avec mise à jour automatique des données !

Solution de contournement pour les particuliers

La solution décrite ci-dessus ne fonctionne qu’avec les comptes pro. Il est néanmoins possible de lancer des requêtes Big Query directement depuis App Script, sans passer par le connecteur Big Query. Cette solution un peu plus complexe est très bien décrite dans cet article (en Anglais).

Importer des données depuis le web avec la fonction IMPORTDATA

Les données publiques Big Query ne sont pas forcément les seules que l’on peut avoir envie d’inclure dans son dashboard. Dans le cas de notre dashboard de suivi du coronavirus, l’open data disponible sur data.gouv.fr peut nous permettre d’inclure des métriques plus précises pour la France, comme le suivi du nombre de personnes hospitalisées.

L’utilisation de la fonction est on ne peut plus simple:

J’ai rajouté dans mon dashboard un filtre sur les lignes pour lesquelles la somme du nombre de cas et de décès est strictement supérieure à 0. Cela me permet de souligner que les noms de colonnes issues de la fonction IMPORTDATA sont Col1, Col2, etc.

Pour automatiser cette fonction, le menu de Fichiers → Paramètres de la feuille de calcul propose une option bien pratique: nouveau calcul « Après une modification et toutes les heures ».

Et c’est aussi simple que ça ! À noter que malheureusement le volume de données importables de cette façon est assez limité: autour de 40000 à 50000 lignes de mon expérience, même si je n’ai pas trouvé de chiffre dans la documentation officielle.

Conclusion

Les Google Sheets disposent de fonctionnalités très pratiques pour développer des dashboards de suivi connectés à des données dont on veut suivre l’évolution au cours du temps. Leur grande force réside dans la rapidité de développement des connexions aux sources de données externes, la facilité de l’automatisation des mises à jour et la facilité à les partager. Et vous, quel dashboard temps réel allez-vous construire et partager en quelques heures grâce à cet exemple ?