Google Sheets : générez vos devis avec des feuilles de calcul

Dans cet article, nous nous pencherons sur la question de la construction et de la génération des devis à l’aide de plusieurs feuilles de calcul Google Sheets.

Nous évoquerons ainsi de façon transversale :

• la différence entre une base de données et une vue d’informations (à ce propos lire cet article);
• les fonctionnalités de partage et d’affichage d’informations (#validation des données, #plages nommées, #importrange(), #filter(), #query()) ;
• l’organisation de feuilles de calculs au sein d’une équipe ;
• la génération de PDF à partir de Google Sheets.

Contexte

Organigramme des données

La création d’un devis simple demande la mise en commun de plusieurs données qui proviennent chacune d’une table différente. Pour faire simple, voici un organigramme des tables de données et de leurs interactions :

• Une table DEVIS centralise les informations de base d’un devis : La référence, la date, le titre (qui pourrait reprendre le titre du projet), le client (à aller chercher dans la table « clients » externalisée) et la somme totale du devis (calculée grâce à la table « LIGNES ») ;
• Une table LIGNES DE DEVIS détaille les tâches devisées, relatives à un devis (référence à aller chercher dans « DEVIS »), l’intitulé de la tâche, le prix unitaire, la quantité et le prix total de la ligne ;
• Une table CLIENTS : un annuaire indispensable à toute organisation.

Voyons comment organiser ces tables et comment en extraire un devis propre et communicable à vos clients !

Créez la table DEVIS

Créer une nouveau fichier Google Sheets que vous pouvez appeler GESTION COMMERCIALE séparée de celle des CLIENTS.

En effet, la base clients peut être utilisée par des collaborateurs de votre agence qui ne doivent pas avoir accès aux informations commerciales des devis. Il vaut mieux ne pas tout mélanger.

Créez dans GESTION COMMERCIALE une feuille DEVIS : que vous pouvez remplir avec des données fictives. Laissez la colonne CLIENT vide pour l’instant.

Importez les informations « CLIENTS »

Préparez la table « clients »

Ouvrez votre fichier CLIENTS (pour créer ce fichier voir : l’article suivant)

Il est possible d’importer la totalité de la feuille mais je préconise de n’importer que les colonnes qui nous intéressent.

Nous allons pour cela identifier clairement chaque colonne du fichier « clients » en utilisant les plages nommées.

Les plages nommées dans ce cas précis permettent

• de n’importer que certaines colonnes
• de ne pas être tributaire des manipulations de colonnes effectuées sur la base de données clients.

Importez les données

Dans le fichier SUIVI COMMERCIAL, créez une nouvelle feuille. J’ai l’habitude de nommer les feuilles dans lesquelles j’importe des données externes avec le préfixe « IMPORT… ». Nommons donc cette nouvelle feuille IMPORT CLIENTS.

Dans la cellule A1, nous allons importer avec la fonction importrange() la plage nommée CLIENT_NOM venant du fichier CLIENTS.

La fonction IMPORTRANGE() contient deux arguments obligatoires :

• l’URL du fichier de base, à récupérer dans la fonction « partager » du fichier :

• la plage nommée à importer
  (ou la plage classique de type ‘NOMDEFEUILLE’!A:F)

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/15wAIwAmv8ddyn1vY6Z2RpC-WuzX-aIQKNjASbu5JcIQ/edit?usp=sharing";"CLIENTS_RAISON_SOCIALE")

Vous pouvez utiliser l’URL de mon fichier pour l’exercice, pensez à adapter l’URL de votre fichier ensuite.

Recopiez la formule dans les cellules suivantes en changeant le nom de la plage nommée désirée. Je vais prendre uniquement la civilité, le nom de famille du client et l’adresse postale. Nous en aurons besoin pour éditer le devis.

Astuce : Je vous conseille de protéger et masquer la feuille, elle ne sert que de sources de données pour alimenter les autres tableaux.

Vous possédez maintenant dans le fichier GESTION COMMERCIALE une feuille contenant les informations de base des clients, synchronisée avec la base CLIENTS.

Toute modification apportée sur la base est automatiquement reportée dans l’import. Cette synchronisation des données permet d’ajouter ou modifier une information client à un seul endroit !

Par contre aucune modification n’est possible dans la feuille IMPORT CLIENTS. Essayez, cela entraînera une erreur.

Associez la table DEVIS et la table IMPORT CLIENTS

Dans la feuille DEVIS, ajoutez la validation des données en utilisant la colonne « raison sociale » de la feuille CLIENTS.

Créez la table LIGNES DE DEVIS

Cette nouvelle feuille, dans le fichier GESTION COMMERCIALE va permettre de lister toutes les lignes du devis. Le principe est d’ « appeler » le numéro du devis en utilisant une liste déroulante, et de renseigner les informations propres à chaque ligne.

Indiquez pour chaque ligne l’intitulé de la tâche, le prix unitaire, la quantité et le total de la ligne par un arrayformula simple :

=arrayformula(C2:C*D2:D)

Créez le générateur de devis

Enfin, sur une dernière feuille, créez un devis. Cette feuille est destinée à être imprimée et communiquée au client. Soignez la présentation !

Voir cet article pour automatiser le téléchargement, nommage, stockage et envoi du PDF.

La seule cellule modifiable est le numéro du devis. Toutes les autres informations sont soit fixes, soit en lien avec le numéro du devis.

---

---

---

Affichage des lignes du devis :

vous pouvez utiliser FILTER :

=FILTER('LIGNES DEVIS'!B:E;'LIGNES DEVIS'!A:A=B2)

ou QUERY :

 =QUERY('LIGNES DEVIS'!A:E;"SELECT B,C,D,E WHERE A = '"&B2&"' ") 

pour afficher les lignes correspondant au devis affiché dans la cellule B2.

---

Affichage du total HT, taxes et total TTC :

Il faut laisser un certain nombre de lignes en prévision d’un gros devis. Pour améliorer l’affichage, il suffira de regrouper les lignes vides.

---

Exemples de devis édités :

Notez que vous pouvez mélanger les lignes, en ajouter ou en retirer dans la feuille LIGNES DEVIS. Exemple avec trois devis détaillés :

Résultats des devis dans la feuille GENERATEUR DEVIS

Téléchargez le devis au format PDF

Il ne vous reste « plus qu’à » télécharger la feuille au format PDF en paramétrant correctement l’impression du fichier. Cet article décrit une automatisation de ces tâches répétitives.

Stockez votre devis et envoyez-le à votre client.

Bien sûr, il existe des techniques pour automatiser l’envoi et assurer le suivi commercial du devis, gérer les différentes versions, relancer le client… mais c’est une autre histoire 🙂 !

N’hésitez pas à nous contacter pour être accompagné dans votre gestion des devis au sein de votre entreprise, ou pour d’autres demandes.