Google Sheets : traiter, utiliser, partager les données efficacement : 5/5 – centraliser et partager les données

2

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.


Dans ce dernier article de la série, je vous propose d’aborder la question de l’organisation générale des informations dans votre entreprise. L’idée est d’optimiser la gestion des données en différenciant clairement la question de :

  • la saisie, mise à jour et stockage des données d’une part ;
  • l’utilisation de ces données d’autre part ;

ce qui nous amène à identifier les feuilles de calcul qui serviront de tables de données, et celles qui permettront de construire des vues.

Pour créer des vues, nous avons vu dans l’article 4 qu’il fallait importer les données nécessaires dans la feuille qui permettrait de construire les fameuses vues.

Voici un cas concret :

Une agence de communication gère des clients, des projets et des salariés. Elle met en place trois feuilles de calcul servant de table de données pour chaque secteur :

Table CLIENTS, gérée par le service commercial

table PROJETS, gérée par le service graphistes

table SALARIES, gérée par la direction

Ces trois tables doivent communiquer entre elles, pour de nombreux besoins du type :

  • Les commerciaux : « Nous aimerions connaître les projets réalisés par les graphistes pour chaque client quand nous les relançons. »
  • Les graphistes : « Quand on entre un nouveau projet, il faudrait qu’on récupère la liste des clients gérés par les commerciaux pour en associer un au projet, et du coup récupérer l’adresse mail du contact client, et aussi associer le chef de projet et les exécutants en utilisant une liste à jour des salariés. »
  • La direction : « Je veux avoir un aperçu des projets par client et par salarié, avec projection des temps d’occupation de ces derniers. »

Chaque service a donc

  • la responsabilité de gérer certaines données ;
  • le droit de récupérer certaines données des autres services.

Voici une procédure que j’ai mise en place dans les entreprises que j’accompagne pour documenter les données disponibles et les importer rapidement dans une feuille de vue.

Identifier les données grâce aux plages nommées

L’importation d’un tableau entier via la fonction IMPORTRANGE() dans une feuille destinée à être une vue pose trois problèmes :

  • lourdeur inutile de l’importation : pourquoi importer une vingtaine de colonnes d’une table alors que je n’ai besoin que de deux ?
  • faille de confidentialité : toutes les données d’une table ne sont pas communicables à tout le monde, par exemple les informations personnelles de chaque salarié, comme le numéro de sécurité sociale ou le téléphone, ne doivent pas être communiquées aux graphistes ;
  • dépendance au nombre de colonnes et à leurs places dans la feuille de destination. IMPORTRANGE() spécifie les noms des colonnes à importer. Exemple : Feuille1!A:D récupère les colonnes A à D de « Feuille1 ». Si la personne en charge de cette feuille rajoute une colonne pour insérer une information manquante, cela provoque un décalage pour la feuille de destination. Bref, ingérable.

Pour palier ces problèmes, je propose d’utiliser les plages nommées dans les tables de données pour nommer chaque colonne grâce à un nom clairement identifiable. Encore une habitude héritée de l’utilisation de SQL !

  • sélectionnez toute une colonne (et une seule) ;
  • cliquez sur « Données / Plages Nommées » ;
  • nommez la colonne (préconisation : TABLE_COLONNE) les majuscules c’est juste une question d’habitude :), le « _ » permet de séparer les données (le « – » n’est pas utilisable) ;
  • cliquer sur « OK » pour enregistrer la plage.
Ajouter des plages nommées dans la table CLIENTS

Répétez la procédure pour chaque colonne.

C’est un peu laborieux, mais les données seront ainsi clairement identifiées, indépendantes les unes des autres et « cerise on the cake », en cas d’insertion de colonne, la plage nommée se met à jour !

Voici un aperçu de la table CLIENT contenant toutes les plages nommées.

plages nommées de la table CLIENTS

Par exemple, vous pourrez à présent importer uniquement le prénom et le nom d’un client dans une feuille externe, en créant un IMPORTRANGE() pour chaque plage nommée.

Voyons comment créer ces IMPORTRANGE() et les récupérer rapidement 🙂

Lister et localiser les données disponibles

Rappel : Lister = créer une table de données ! Youpi : une table de données des données de toutes les tables de données ! (Hommage à Raymond Devos)

Créez un Google Sheets comprenant deux feuilles :

  • SHEETS : permet de lister les Sheets contenant des données :
    • ID (récupérable dans l’URL du SHEET) ;
    • nom de la table ;
  • DONNEES : permet de lister les plages nommées et de créer automatiquement l’IMPORTRANGE correspondant :
    • Nom de la table (liste déroulante issue de la feuille SHEETS),
    • Nom de la plage nommée,
    • récupération de l’ID de la table
=ArrayFormula(SI(B:B<>"";SI(B:B=B1;"ID TABLE";RECHERCHEV(A:A;SHEETS!A:B;2;FAUX));""))
    • création de l’IMPORTRANGE
=ArrayFormula(SI(B:B<>"";"=IMPORTRANGE("""&C:C&""";"""&B:B&""")";""))

Cette table de données hautement stratégique, permet de venir piocher les IMPORTRANGE() utiles pour les différents utilisateurs. L’administrateur des données doit être le seul à avoir accès à cette table, il pourra y copier les formules IMPORTRANGE() nécessaires à la construction d’une vue pour un service, et les coller dans le fichier de destination.


Collez la formule IMPORTRANGE() générée dans la feuille CARTOGRAPHIE dans la cellule A1 pour récupérer l’ID du client :

Collez la formule IMPORTRANGE() générée dans CARTOGRAPHIE de l’e-mail dans la cellule B1 :


Cet article vient clôturer la série dédiée à l’utilisation de Google Sheets pour en faire un système d’information du type MVC (Model / Vue / Contrôleur). Il reste beaucoup de choses à dire et à explorer mais j’espère que ces approches vous ont permis d’appréhender les points d’attention dans la gestion et le partage des données au sein d’une entreprise ou d’un groupe. Nous pourrions évoquer les limites d’une telle pratique, surtout en terme de rapidité de calcul, et envisager des solutions plus avancées pour partager les données, en particulier avec des scripts. Peut-être dans la suite ?

Merci pour vos commentaires constructifs et vos témoignages sur l’utilisation de Google Sheets en tant que système d’information.


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 .

2 Commentaires

  1. Bonjour Punk_sportif (comme quoi tout est possible ! ça me rappelle une chanson des VRP).
    Effectivement Sheets reste un tableur, loin de moi l’idée de palier des outils de gestion de bases de données. L’idée de l’article est d’optimiser l’utilisation du tableur en différenciant les tables de données et les vues.

    pour autant je travaille sur un système d’information d’une entreprise avec des tables assez conséquentes sous Sheets. ça reste une solution souple et efficace pour gérer un grand nombre d’informations.
    A bientôt !

  2. Bonjour et merci pour cette suite d’article.
    Je pense qu’il faut voir dans la pratique cette feuille de cartographie des données, mais j’ai comme l’impression que c’est relativement laborieux et que je ne connais pas encore les subtilités de protections nécessaires aux partages des données.

    Quel est le risque d’utiliser directement une requête du genre =query(importrange »adresse_feuille »;plage_bdd); »select ID_client, MAIL_CLIENT ») ?

    Google sheets n’est-il pas encore un peu trop « jeune » pour gérer une BDD?
    J’ai commencé à réfléchir / tester une application de gestion horaire de salariés, mais je vois que bien dans mes tests de quelques lignes, ça rame un peu… Alors j’imagine que quand il y aura 10000 enregistrements d’horaires dans la base, ça sera très très laborieux…

    Sinon, ben… Vivement la suite 🙂 🙂

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.