Google Sheets : traiter, utiliser, partager les données efficacement : 1/5 – construire des tables de données avec Google Sheets

8

Google Sheets est un tableur aux possibilités de calcul, de stockage et de collaboration indéniables. Son utilisation, couplée à celle de toutes les applications de la galaxie Google, s’impose dans de nombreuses entreprises. Il atteint parfois un niveau d’exploitation semblable à celui d’un système d’information complet, à l’instar d’un ERP, un CRM, une GED… Dans cette série d’articles, fort de mon expérience de consultant et développeur d’applications web, je vous propose d’identifier quelques pratiques indispensables à la mise en place d’un système d’information pérenne avec Google Sheets.


Avant-propos

Google Sheets est souvent utilisé en entreprise pour réaliser des tableaux de bord qui répondent à un besoin précis.

Je veux un tableau des ventes de plusieurs produits sur l’année.

L’utilisateur construit directement le résultat final qu’il attend, avec de jolies couleurs, des croisements d’informations, des graphiques, des lignes regroupées par année ou par types de produits… Et c’est bien normal ! Quelle satisfaction de voir directement la concrétisation de son besoin.

Si le besoin initial est atteint dans cet exemple, d’autres besoins secondaires surgiront forcément : connaître le chiffre d’affaires de chaque produit en fonction du prix de vente réel, ou encore afficher une répartition par marque de produits, etc.

Et quid de la mise à jour des données qui viendront perturber la jolie mise en page ? Quid du partage de l’information avec d’autres personnes ou d’autres services ? Quid de l’exploitation de ces données dans un Google Agenda ou un Google Docs ?

Ce questionnement sur la circulation des données et l’automatisation de tâches implique de considérer Google Sheets comme un véritable système d’information, au même titre qu’un ERP (Enterprise Ressource Planning) ou d’un CRM (Customer Relationship Management) ou encore d’une GED (Gestion Electronique des Documents) ou encore de CMS (Content Management System)…

Utiliser Google Sheets pour créer un système d’information efficace requiert de différencier :

  • Le traitement des données (recueil, ajout, modification) dans une base de données structurée et exploitable ;
  • L’utilisation des données (affichage, analyse, production… dans des vues, des tableaux de bord, des graphiques, des applications tierces etc.)
A gauche les tables de données à droite les vues des exploitations des tables de données.

L’objectif de cet article est de sensibiliser aux notions essentielles de structuration d’une base de données dans le cadre d’un véritable protocole (SQL) et de mettre ces notions en application dans la construction de tables des données sur Google Sheets. Il ne requiert pas de compétences techniques particulières, il propose une approche globale et, à mon sens, efficiente de l’utilisation de Google Sheets.

Système d’information

Un système d’information est un programme qui généralement exploite une base de données pour créer des affichages compréhensibles par un humain grâce à un ensemble de fonctions.

Cette architecture s’appelle MVC (Modèle – Vue – Contrôleur).

(PS : Le terme « Modèle » était source de confusion pour moi, il désigne la base de données, et non un template ou un exemple. Et perso j’aurai mis « Vue » à la fin car à mon sens c’est le résultat final. Je préfère le terme Data – Contrôleur – Vue.)

Par exemple, la page du site Internet que vous êtes en train de consulter est une vue au format HTML compilant des informations récupérées grâce à des requêtes et utilisées par à un moteur de création.

Le système d’information qui génère cette page est WordPress, le CMS (Content Management System) mondialement connu.

Une base de données, c’est quoi ?

Définition :

Une base de données (data base) est un ensemble d’informations organisées par tables de données. Une base est donc constituée de plusieurs tables.

DemOwa

WordPress par exemple exploite de nombreuses tables de données pour fabriquer la page du site. Il y a une table « articles » qui contient a minima : le titre de l’article, la date de publication, l’auteur… puis une table « blocs » rassemblant les différents éléments de cette page, une table « auteurs » qui contient le prénom, nom, description de l’auteur affichés en bas de page etc.

Les bases de données sont gérées par des langages spécifiques. Dans le cas de WordPress, il s’agit de SQL. Voici à quoi ressemble une plateforme d’administration des bases de données SQL.

Exemple de plateforme de gestion de base de données au format SQL,

Google Sheets n’a pas pour ambition de suppléer SQL. Pour autant, voici quelques recommandations pour utiliser au mieux l’environnement Sheets afin d’organiser les informations comme dans une vraie base de données.

Une feuille par table, une table par feuille

Sous Sheets je conseille de créer une feuille de calcul pour chaque table de données. Plusieurs tables peuvent être sur le même SpreadSheets, et la base de données peut rassembler plusieurs SpreadSheets.

Point vocabulaire : dans cet article je ferai la distinction entre « Base de données » et « Table de données » : une base de données est un ensemble de tables de données.

Voyons en détail comment construire une table :

Une seule entrée dans les colonnes svp !

Une table de données est un tableau à une seule entrée : les colonnes représentent les « champs ». Chaque champ est une information qui caractérise les données. Prenons par exemple une table de données CLIENTS :

CivilitéPrénomNomEmailDate d’entréeTéléphone

La table est construite sous la forme d’un tableau à une seule entrée par colonnes. Chaque colonne doit avoir un titre, pas de syntaxe particulière ici, contrairement à SQL qui refuse tout accent ou caractères spéciaux.

Chaque colonne doit avoir un format précis parmi :

  • Texte (ou chaîne de caractères ou VarChar en BDD) ;
  • Nombre ;
  • Booléenne (VRAI ou FAUX) ;
  • Date.

Avec SQL il faut définir très précisément le type de données : si c’est une chaîne de caractères, combien de caractères maximum ? Quel format de nombres, entre quelle et quelle valeurs, est-ce un nombre décimal, avec quelle précision ? etc. Aucune donnée hors format ne peut être saisie sous peine d’erreur fatale (Fatal Error) ! Ce qui est très contraignant, mais indispensable à une bonne optimisation de la table.

Sheets est moins strict (car rappelons-le le rôle d’organisateur des données n’est pas sa priorité), ce qui implique qu’il faut anticiper l’utilisation de la donnée pour choisir le meilleur format et respecter ensuite ce format. Donc, par exemple, si la colonne attend un nombre, il ne faut pas écrire : « entre 5 et 10 ». :).

TP : Créer la table de données « CLIENTS »

  • Créez un SpreadSheet « CLIENTS » puis une feuille « DONNÉES CLIENTS » ;
  • figez la première ligne (aucune obligation, mais bien pratique pour la lisibilité) ;
  • nommez les différentes colonnes : Civilité, Prénom, Nom, E-mail, Date d’entrée, téléphone
  • appliquez un format couleurs alternées pour la lisibilité.
première table de données

Points de vigilance :

  • Pas de cellules fusionnées ou de doubles lignes de titres ;
  • pas de titre de feuille dans la feuille ;
  • pas de lignes ou colonnes vides « pour aérer » pour faire des marges ;
  • pas de chichi, pas de Blabla.

Une ligne est un individu : unique et identifiable.

Chaque ligne (appelée « entrée » en SQL) représente un individu de la table. Il faut une ligne par individu et un individu par ligne.

Il est essentiel d’identifier chaque ligne à l’aide d’un code normalisé : une référence unique, comme un numéro de sécurité social, qui permettra de repérer la ligne et donc l’individu plus tard.

Pour faire court nous emploierons le terme « ID » (prononcer « AieDi » 🙂 )

Ajoutez donc systématiquement une colonne « ID » au début de votre table de données. au début par habitude et pour montrer l’importance de cette information.

SQL crée automatiquement un ID, sous forme numérique à chaque création de ligne (auto-incrémentation). Cet ID est aussi nommée « Clef » (Key) et jouera un rôle essentiel dans l’exploitation et la mise en relation des données.

Sur Sheets, je préconise de former les ID avec un groupe de deux ou trois lettres identifiant la table, puis un nombre à trois ou quatre chiffres qui devrait s’incrémenter à chaque création de ligne (tâche automatisable avec un petit script). Donc mes « clients » de la table « DONNEES CLIENTS » auront un ID sous la forme CL-0000. J’envisage pour l’instant de lister 999 clients. Au 1000ème , j’ouvrirai le champagne et passerai sur cinq chiffres, pas de souci.

TP : Ajoutez une première colonne « ID CLIENT », créez l’ID « CL-001 » puis étirez la cellule pour créer les 100 premiers IDs de votre table. remplissez les premières lignes :

Points de vigilance :

  • Attribuer un seul ID au même individu ;
  • attribuer un seul individu à un ID ;
  • en cas de suppression d’une ligne, ne pas réattribuer l’ID à un nouvel individu ;
  • ne pas changer l’ID d’un individu !
  • pas de fusion de cellule ;
  • pas de groupement ou titres de groupement !

Qu’est-ce qui détermine qu’un ensemble de données doit faire l’objet d’une table propre ?

Il arrive que de nouvelles informations doivent être gérées par l’organisation. La question que vous devez vous poser est de savoir si cette nouvelle information peut se satisfaire d’une ou plusieurs colonnes d’une table existante ou bien si elle mérite une table de données dédiée.

TP : Marie-Sophie de la gestion client vous informe que les clients ont deux numéros de téléphone.

Renommez la colonne « Téléphone » en « Téléphone 1 » et ajoutez une colonne « Téléphone 2 »:

un nouveau numéro de téléphone

A priori nous ne devrions pas avoir besoin d’un troisième numéro de téléphone. Cette solution peut convenir.

TP : A présent, Régis de la compta a besoin de répertorier l’adresse postale de facturation pour chaque client.

Ajoutez une série de colonnes à notre table de données « Adresse ligne 1 », « Adresse ligne 2 », « Code postal », « Ville », « Pays » :

Une adresse postale

TP : Ursulla du service expédition a besoin de, je cite « au moins deux adresses de livraison ».

L’ajout de plusieurs groupes de cinq colonnes (avec l’information « Facturation », « Livraison 1 », « Livraison 2 » en plus dans le titre de chaque colonne) va rapidement s’avérer laborieuse et consommateur de place… et tiens, il faut dix nouvelles adresses de livraison pour un client (un grand-père qui envoie des cadeaux à chacun de ses petits-enfants:) ).

La solution d’ajouter cinq colonnes par adresse n’est plus gérable.

Il est temps pour cette accumulation d’informations de créer une nouvelle table de données « ADRESSES ».

TP : Créer la table de données des adresses postales des clients.

Ouvrez dans le même SpreadSheets une feuille de calcul « DONNEES ADRESSES ».

  • première colonne : « ID ADRESSE », je vous propose le nommage AD-000 ;
  • deuxième colonne : « CLIENT », pour indiquer à qui appartient l’adresse (appliquez une validation des données dans cette colonne qui permet de choisir parmi les ID CLIENTS disponibles) ;
  • colonnes suivantes : les informations postales classiques (adresse ligne 1, adresse ligne 2, code postal, ville, Pays) ;
  • ajoutons une nouvelle colonne « Type », pour caractériser l’adresse (« Facturation » ou « Livraison » ?) ;
  • … libre à vous d’imaginer d’autres champs… interphone ? étage ? informations pour le livreur ?…

Les adresses sont stockées dans une table de données dédiée. Le champ client stocke l’ID CLIENT permet de créer la première relation entre les deux tables.


Ce premier article avait pour objectif d’identifier les caractéristiques techniques d’une table de données.

Dans l’article suivant (en cours), nous verrons comment mettre en relation ces tables, afin de récupérer des informations d’une table dans une autre par exemple.


Vous avez un projet autour de G Suite
et vous souhaitez nous solliciter :

formation | développement | paramétrage G Suite | interventions | …


Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

8 Commentaires

  1. Merci pour votre article, cela m’aide beaucoup.
    Néanmoins, dans le TP « Créer la table d’adresses… » vous proposer de créer une colonne « type » pour y caractériser l’adresse (facturation / livraison…).
    Je ne comprends pas comment créer une liste de « caractérisation d’adresse ».
    Avez-vous un article dédié à ce sujet?
    Merci

    • Bonjour, vous pouvez par exemple créer une autre feuille et lister tous les types d’adresse que vous souhaitez référencer.

      type adresse
      facturation
      devis
      livraison

      Dans la base de données, affichez cette liste en utilisant la validation de données et en vous servant de cette liste pour la validation.

      Est-ce que cela répond à votre demande ?

LAISSER UN COMMENTAIRE

S'il vous plaît entrez votre commentaire!
S'il vous plaît entrez votre nom ici

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.