Google Sheets : traiter, utiliser, partager les données efficacement : 3/5 – créer, modifier, mettre à jour une entrée dans une table

5

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.


Les deux premiers articles de cette série traitaient de la structure d’une table de données et de la mise en relation des tables entre elles. Abordons dans ce nouvel article la question du traitement des informations d’une table de données.

Le traitement de l’information en SQL

En langage SQL il existe quatre types de traitement :

  • Sélectionner (select) : recherche des lignes de la table en fonction d’un ou plusieurs critères ;
  • Création (insert) : cette action génère une nouvelle ligne dans la table, les champs obligatoires sont remplis, un ID est créé automatiquement (si la table a bien été configurée) ;
  • Modification (update) : cette action permet de modifier un ou plusieurs champs d’une ligne en fonction de son ID ;
  • Suppression (delete) : permet de détruire une ligne répondant à un ID (logiquement l’ID n’est plus disponible pour un nouvel enregistrement).

Ces actions peuvent être commandées directement dans la console SQL, mais il faut connaître le langage des requêtes SQL.

Tiens, une « requête » se traduit « query » en anglais, ça ne vous rappelle rien ?…

Eh oui, la fonction query() de Sheets s’inspire de ce langage SQL. Pour l’instant, seule l’action « select » est disponible sur Sheets, mais permet déjà de réaliser des recherches complexes.

Alors si ce n’est pas directement dans la console SQL, où et comment les bases de données SQL sont-elles modifiées ?

Les applications web sont les interfaces conçues pour que les utilisateurs exercent une de ces actions sur la base de données. Cf. le système Modèle / Vue / Contrôleur. évoqué dans le premier article.

Avec le « Web 2.0 », l’internaute est devenu non seulement spectateur d’un énorme flux d’informations mais aussi acteur de ce flux : il peut sélectionner, créer, modifier et supprimer des informations.

Par exemple, sur un site marchand, vous avez l’habitude de créer un compte en complétant un formulaire. Vous validez le formulaire ce qui provoque une requête de création (insert) d’une ligne sur la table « CLIENT ». Une fois connecté (select) grâce à votre login et votre mot de passe, vous pouvez modifier (update) ou même supprimer (delete) votre compte. Vous avez réalisé toutes ces actions sans le savoir, par le biais de pages web, de formulaires, de boutons d’action, etc. à aucun moment vous n’êtes intervenu directement dans la base de données, et heureusement !

La pratique sur Sheets

Sheets peut être utilisé pour gérer des bases de données relationnelles, mais contrairement à SQL, Sheets est aussi utilisé pour rechercher, ajouter, modifier ou supprimer des informations directement dans la base, l’appli est également utilisée pour créer des vues, des tableaux de bord, de analyses, des graphiques… Cela apporte plus de souplesse à son utilisation certes, mais aussi plus de fragilité, surtout dans une situation de partage de fichiers. Voici trois procédures d’intervention sur une table de données stockée sur Sheets :

L’intervention directe sur la table de données

C’est la pratique la plus courante et la plus naturelle. La table « CLIENTS » est accessible et modifiable par plusieurs personnes. Chacun peut intervenir sur toutes les cellules. La collaboration c’est formidable, mais peut aussi entraîner des problèmes !

Voici quelques exemples de mauvaises manipulations :

  • un filtre appliqué sur une partie des colonnes : cela m’est déjà arrivé et je ne dois pas être le seul ! cette manipulation sur une table contenant un grand nombre de colonnes est très facile à faire, sans s’en rendre compte, et mélange complètement les informations.

Exemple d’un filtre appliqué sur les trois premières colonnes de notre table « CLIENTS » :

  • une personne sans mauvaise intention, mais pas beaucoup de jugeote qui n’a pas lu le premier article de cette série, réattribue les identifiants… parce que « Il y avait un trou (sic) » et « Je voulais garder l’ordre (resic) »… (c’est du vécu:))
  • Le format du champ n’est pas respecté : (voir le premier article sur la structure d’une table de données.)

Ces manipulations ont des conséquences plus ou moins graves sur l’utilisation des données. Plusieurs solutions sont envisageables :

  • former les utilisateurs des tables de données aux bonne pratiques ;
  • limiter l’accès en édition aux tables de données aux seules personnes habilités à les modifier ;
  • mettre en place des validations de données si la liste est très fermée, du type « vrai » « faux » ;
  • protéger certaines cellules de votre table…

Une solution un peu plus avancée est d’utiliser des formulaires :

Intervention dans les tables par formulaire

Comme je l’ai déjà évoqué dans cette série d’articles, Google Forms et Google Sheets communiquent très facilement entre eux. Un formulaire permet d’ajouter de nouvelles lignes à une table de données en toute sécurité. Le choix de types de questions, les validations de réponse par expressions régulières permettent de maîtriser les informations. Le formulaire est plus convivial, plus simple d’utilisation, et cerise sur le gâteau peut créer un ID directement, grâce à l’horodateur (certes un peu long…).

Une petite formule pour transformer la date envoyée par Forms en nombre :

=ARRAYFORMULA(TEXTE(A:A*100;"#"))

Qu’en est-il de la modification d’une ligne avec un formulaire ?

Si l’option est activée, il est possible de modifier les réponses d’un formulaire après l’avoir envoyé.

A la fin du formulaire, un lien permet d’afficher le formulaire pré-rempli et de modifier les informations sur la même ligne dans la feuille de réponse (la valeur modifiée est même commentée pour attirer votre attention !). Malheureusement, à ma connaissance, il n’est pas possible de mémoriser l’URL de modification du formulaire pour chaque réponse (Si quelqu’un connait la méthode, je suis preneur !). Un lien de modification propre à chaque ligne permettrait de modifier les informations dans un formulaire.

Je vous propose une solution intermédiaire : le formulaire pré-rempli. Cette option, formidablement décrite dans cet article, permet d’afficher le formulaire contenant les informations déjà saisies. Cela facilite la saisie des informations, vous pouvez aussi n’afficher que ce lien et permettre aux utilisateurs autorisés à modifier les données.

Attention ! la validation ne modifie pas la ligne du sheets, mais entraîne la création d’une nouvelle ligne ! N’oubliez pas d’envoyer également l’ID de la ligne modifiée…

Il faut alors filtrer les réponses envoyées par le formulaire en ne gardant que la dernière réponse concernant le même ID.

Intervention par script

Pour les besoins un peu plus complexes, il est possible de créer une feuille Sheets interactive comme un formulaire, dans laquelle l’utilisateur sélectionne et modifie des informations. Un script, appelé par un bouton de validation (voir cet article), enregistre les données dans la feuille de calcul de son choix.

Par exemple : Voici un formulaire qui permet de contrôler les mesures d’une pièce produite par une machine. L’utilisateur sélectionne une pièce, les cotes à mesurer et le plan de la pièce s’affichent, il doit saisir des informations dans les cases noires, il clique sur le bouton « enregistrer », les données alimentent une table externe.

Cette procédure fera l’objet d’un article à part, si vous insistez lourdement dans les commentaires 🙂

Pour conclure…

Il existe d’autres solutions de gestion de bases de données hébergées sur Sheets, ou ailleurs ! Il est possible par exemple avec l’environnement Google Apps Script de développer une application web semblable à un système d’information.

Ce tour d’horizon de la question des mises à jour d’une table de données dans l’environnement Sheets nous a permis d’aborder quelques points d’attention à connaitre, quelque soit la procédure que vous choisissez, pour la gestion de vos données. Dans le prochaine article, je vous proposerai une approche de partage d’informations… Laissez un commentaire !


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 .

5 Commentaires

  1. Bonjour Gaétan,
    Merci pour votre message ! votre besoin à l’air très spécifique, n’hésitez pas à nous contacter directement pour évoquer ce projet ensemble https://thierryvanoffe.com/contact/ . Numericoach propose des forfaits « accompagnement développement et formation » souple pour aborder ce type de besoins.
    Pour le script je ne peux pas le communiquer en l’état car il contient des informations « client ».
    Mais je prévois un prochain article sur une script permettant d’alimenter une base de données à partir d’une saisie sur une feuille, dans lequel je publierai un script semblable…
    Bonne journée !

    • Bonjour et merci pour votre retour.
      C’est vrai qu’il tombe à pique votre « dossier » sur les BDD google sheets. J’attends la suite avec impatience.
      Pour le forfait, je suis désolé, mais je n’ai aucun budget. C’est du professionnel, mais ça part d’une initiative personnelle et je suis « juste » secrétaire.
      J’ai suivi vos conseils et ai posé ma question sur le groupe facebook.
      Bonne journée et vivement la suite !

  2. Bonjour,
    J’insiste lourdement, ca m’intéresse beaucoup tout ça.

    J’aimerais construire un système de saisi des horaires par les salariés, validation par les cadres, traitement des données par le service paie.

    Merci beaucoup pour le partage de vos connaissances.
    Bonne continuation.
    Gaetan

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.