Révélez la puissance des Google Spreadsheets

Introduction

Les Google Spreadsheets (feuilles de calcul) sont disponibles dans la suite professionnelle Google Apps ou gratuitement à tout un chacun dans le produit Drive. Si elles comportent de nombreuses limitations, celles-ci bénéficient d’un environnement et de caractéristiques tout à fait uniques : une part, elles peuvent être utilisées comme de véritable bases de données d’où l’on pourra facilement extraire les enregistrements correspondants à des critères de sélection multiples ; et d’autre part, on peut les manipuler grâce à des scripts simples, puissants et qui permettent, en plus, de les coupler avec tous les autres outils Google (calendrier, mail, etc.)

Aujourd'hui, je vous propose, dans cet environnement, une architecture de programmation générique qui permet de réaliser des applications métiers sur mesure faiblement ou moyennement complexes avec une étonnante facilité.

Exemple type

Prenons l’exemple hyper classique du petit commerçant avec un système tarifaire très personnel qui veut analyser ses données journalières en synthèses hebdomadaires, mensuelles et annuelles.
Les mauvaises habitudes données par Microsoft et les largesses d’Excel nous auraient inciter à créer un énorme tableau regroupant toutes les données (jours, semaines, mois, années), très lourd car bourré de formules et délicat à manipuler et qui doit être renouveler tous les exercices par une procédure très complexe.
Le principe, pour simplifier et réduire drastiquement de poids de cette application, consiste à séparer les groupes de données logiquement dans des bases de données distinctes, simplement en créant des onglets (feuilles) supplémentaires et à faire réaliser le traitement régulier de ces bases par des scripts. Ainsi, pour l’exemple évoqué, nous aurons la base de saisie des données journalières, plus deux bases pour les synthèses hebdomadaires et mensuelles.
Pour finir, les données synthétisées sont extraites et re-synthétisées dans une dernière feuille qui sert à l’affichage d’un tableau de bord personnalisé et paramétrable.
Je n’évoquerais pas ici la problématique de la saisie qui mériterait un (long) article à elle toute seule, mais uniquement les aspects de traitement et d’affichage.

Structure des bases et traitements

Une base de donnée, dans les feuilles de calcul Google, c’est tout simplement les noms des champs dans la première ligne (commençant à A et sans interruption) et les enregistrements dans les lignes suivantes (sans lignes vides). Dans notre cas, pour plus de clarté et pour simplifier le code des scripts, il est recommandé d’avoir un enregistrement par jour (365 lignes donc) dans l’ordre chronologique et avec plusieurs index comme la date, l’exercice, le jour de la semaine, le mois, etc.
Les  bases de données des synthèses hebdomadaires et mensuelles ont la même construction, avec les champs correspondants aux besoins spécifiques d’analyse de l’utilisateur et les index correspondants : dans notre cas, seuls le numéro d’exercice et de semaine sont nécessaires pour la synthèse hebdomadaire, par exemple.

Maintenant, il faut réaliser le traitement à l’aide de Google Apps Script, l’outil de scripting interne complètement “Cloud” de Google. Basé sur le langage JavaScript, et étant capable d’accéder très facilement à tous vos documents de votre compte Google ou à des documents publiques, cet environnement de programmation simplifié vous permet de concevoir des prouesses de simplifications tout en s’adaptant totalement aux spécificités du projet.
Dans notre exemple, nous pouvons tout à fait concevoir qu’un script unique puisse mettre à jour une base de donnée de synthèse automatiquement toute les semaines (pour l’hebdomadaire), ou recréer une base complètement ou partiellement effacée, ou créer une synthèse partielle en cours de semaine ou même une synthèse globale de la semaine passée avant la mise à jour automatique.
Le déclenchement manuel de ceux-ci peut être réalisé grâce à un menu supplémentaire personnalisable ou à des boutons dans des cellules de la feuille de synthèse globale.

Afficher les résultats

Maintenant que nous avons toutes les données, il est extrêmement simple de les afficher et les mettre en forme dans cette feuille de synthèse globale.
Cette feuille doit être découpée en trois zones logiques dont une a une position définie.
Les variables et les constantes sont stockées dans des cellules quelconques des colonnes visibles et sont les seules cellules éditables. Les variables seraient, pour notre exemple, l’année et la semaine dont on veut la synthèse et les constantes sont des valeurs qui sont rarement changées et qui servent dans les scripts.
La zone des tableaux de sélection sont des colonnes cachées, à droite de la feuille, où sont stockés les tableaux de valeurs calculées en fonction des variables et qui servent à sélectionner les enregistrements des bases de données. Dans notre cas, nous créerons deux tableaux (de 2 par 2) avec en première ligne le même libellé que le libellé de la première ligne de la base de sonnée (“mois” et “exercice”), et en seconde ligne les valeurs de ces critères de sélection (même mois et année n et n-1) pour comparer un mois au même de l’année précédente.
Le reste sert à l’affichage, où, avec quelques DGET, DSUM ou DAVERAGE, les tableaux de sélections et des formules très simples, nous pouvons mettre en page une présentation claire, complète, paramétrable et personnalisée de vos données synthétisées.

Conclusion

En utilisant les feuilles de calcul Google comme des bases de donnée, nous pouvons créer simplement des applications métiers sur mesure complexes et puissantes qui n’ont besoin de pratiquement aucune maintenance. La simplicité des structures de données et des scripts procurent un sentiment de maîtrise chez l’utilisateur.
En plus, l’environnement “Cloud” de Google Drive permet à plusieurs personnes d’utiliser un document unique : plus de soucis de validations et de transmissions ; la sécurité est excellente : qualité, performance et sûreté de la structure Google et un historique sur tous les documents ; ainsi que l’accès ou la saisie des données à partir du simple navigateur ou d’un iPad. Sans oublier que les documents Google Apps natifs ne sont pas déduits de l’espace de stockage de l’utilisateur.
Le document qui pesait 5Mo avec Excel et qui engorgeait les messageries de tout le monde et les sauvegardes, ne pèse plus rien et ne génère plus aucun stress. Avec un impact minimum sur les habitudes prises, une présentation similaire et aucun nouvel outil à apprendre, le reporting et l’analyse sont devenus de vrais plaisirs.