Exemple de reporting agrégé avec ImportRange

Google Docs : importer des données d’une autre feuille de calcul

Introduction

Google Docs permet de partager des documents entre plusieurs personnes. Les avantages sont nombreux:

  • pas de problème de synchronisation avec des répertoires partagés (du style « arf, il y a un conflit dans mes fichiers, lequel est la dernière version? » ou « t’as modifié quoi dans le document? il faut qu’on le resynchronise »)
  • tout le monde voit les modifications se faire en « live »
  • un historique permet de revenir en arrière (il manque un peu de souplesse cependant)
  • accès aux fichiers depuis tout ordinateur ayant accès à Internet et un navigateur web

Il y a aussi quelques désavantages:

  • pas d’accès au document sans connexion internet
  • lenteur relative inhérent aux « applications web » (lors de clicks dans l’interface, par comparaison à LibreOffice par exemple)
  • sauvegarde des données (je n’ai pas encore trouvé de solution à ce sujet)
  • sécurité des données (thème très à la mode dans le « cloud ») : ma solution est de faire attention aux données que j’y stocke :-).

La partie « Feuille de Calcul » de Google Docs est la plus intéressante pour moi. Voici une partie de mon utilisation de ces feuilles de calcul : l’import de données d’une feuille de calcul dans une autre feuille de calcul.

Import de données d’une feuille de calcul Google Docs depuis un autre document

L’import de données se fait à l’aide de la fonction « Importange » :

ImportRange(clé du document; Feuille!plage)

Ici, nous avons:

  • clé du document : la valeur du paramètre « key » de l’URL. Cette clé identifie de manière unique un document.
  • Feuille : le nom de la « feuille », ce qui correspond au nom de l’onglet dans lequel se trouve les données (« Feuille1″/ »Feuille2 » dans le bas du document)
  • Plage : la plage de données de type. Par exemple : « A1:B5 » correspond à la plage qui va de la case A1 à la case B5.

Exemple d’utilisation :

=importrange("abcdjdgkdelkgeiudneleodjeqkjfie";"Feuille1!A1:AK1")

Dès lors, dans le document courant, toutes les données de cette plage A1->AK1 de la feuille « Feuille1 » seront récupérées et stockées dans la feuille courante.

Quelques informations à connaître :

  1. pour identifier la clé du document, il est nécessaire de regarder l’URL du document. Je n’ai pas trouvé de fonction permettant de récupérer cette information. Un copier/coller est nécessaire.
  2. une fois cette clé de document identifiée, elle peut être placée dans une autre cellule et la fonction « importrange » peut utiliser la valeur de cette cellule. Admettons que cette clé soit placée dans la cellule « A3″, la fonction devient : importrange(A3; »Feuille1!A1:AK1 »)
  3. la plage doit obligatoirement être placée entre double quotes (le caractère « ) sinon la fonction tente de récupérer une information indiquée dans cette plage du document en cours
  4. un document est limité à 50 importrange
  5. le rafraîchissement n’est pas réalisé dès qu’une information est modifié mais toutes les N minutes, N étant un paramètre que je ne connais pas. Je n’ai pas trouvé comment forcer ce rafraîchissement.
  6. les styles (couleurs, polices et surtout « formatage conditionnel ») ne sont pas récupérés
  7. l’accès en lecture du document source est obligatoire

 

Mon utilisation

J’utilise cette fonction pour agréger les données et limiter la visibilité sur ces données. J’ai le mode de fonctionnement suivant:

  1. plusieurs documents différents, construits exactement pareil. Ces documents contiennent les informations précises d’un sujet très particulier. Ces documents constituent mon premier niveau de la pyramide. Les personnes qui remplissent ces documents ont chacune un accès à un document.
  2. importrange de tous ces documents dans un second niveau : le document contient l’agrégation brute de toutes les données de tous ces documents. Des calculs sont faits sur ces données brutes. Une partie de ce document est dédié au reporting. Je suis seul à avoir accès en écriture à ce document.
  3. un document fait office de 3e niveau de ma pyramide et est à destination du reporting final. Ce document se veut une présentation simple de toutes les données.

Grâce à cela, je simplifie l’utilisation (pas de nombreux onglets dans un seul document), je limite les accès (seules les informations courantes d’un sujet sont connues) et j’effectue un reporting global.

Exemple de reporting agrégé avec ImportRange
Exemple de reporting agrégé avec ImportRange

27 thoughts on “Google Docs : importer des données d’une autre feuille de calcul”

  1. Bonjour,

    est-il possible d’effectuer la formule en prenant par exemple la colonne A de toutes les feuilles d’un autre document ?

    Si oui comment ?

    Merci d’avance !

  2. Bonjour,

    J’utilise un simple formulaire google form pour effectuer des audit.
    les résultats sont stockés dans une feuille de calcul.

    j’ai créé un fichier excel qui synthétise les résultats pour l’envoyer .
    et j’importe les résultats avec un import range.
    voir exemple ci dessous
    =ImportRange (« 1aS4fw9zBYn3Al8uKD2ZRG-EEHHzWf9LweyWArZfBO7w »; »Réponses!d83″)

    la cellule B83 correspond au champ date de mon formulaire
    et j’ai des réponses allant de C à BW
    ma question est ( car j’ai des notions de développement assez faible)
    est-t’il possible de faire une formule ou script sur mon tableau qui afficherais les résultats
    d’un audit en sélectionnant la date ?

    1. Bonjour,

      Sur la formule ci dessous

      =ImportRange (« 1aS4fw9zBYn3Al8uKD2ZRG-EEHHzWf9LweyWArZfBO7w »; »Réponses!d83″)

      ma question est : est-il possible de mettre la valeur contenu dans une cellule en remplacement de la valeur « 83 »
      ex : dans la cellule A1 la valeur est 122 la formule serait

      =ImportRange (« 1aS4fw9zBYn3Al8uKD2ZRG-EEHHzWf9LweyWArZfBO7w »; »Réponses!d »contenu de la cellule A1″ »)

      soit la valeur 122 à la place de 83.

      merci pourl’aide

  3. Bonjour Cédric,

    IMPORTRANGE ne fonctionne pas correctement et je ne vois pas de solution pour l’instant.
    Peut-être pourriez-vous m’éclairer?

    Voici ma structure:
    Un fichier avec un seul onglet dans lequel un vendeur notera ses ventes. Ce fichier sera multiplié par autant de vendeurs que nécessaire. Dans ce cas ci il y en aura 15.

    Un fichier principal, utilisé par mon supérieur. Ce fichier aura 15 onglets qui devront récupérer les données des 15 fichiers vendeurs. Un 16ème onglet totalise les données pour un aperçu global des ventes.

    Petite précision concernant l’onglet de vente: les premières colonnes font références à une liste de produit à vendre avec un montant de commissions pour chaque produit. Les fichiers vendeurs vont récupérer ces données sur le principal. Ainsi si un produit change ou si la commissions change, les autres fichiers se mettent a jours.
    Quand au fichier principal, il récupère uniquement les chiffres de ventes à la journée de chaque vendeur sur leur onglet respectif.

    Chaque fichier dispose de la même mise en page (couleur,cellules fusionnées,titre,etc) préalablement créé sur pc avant d’être postes sur le drive.

    Mon soucis est que la fonction IMPORTRANGE ne fonctionne pas si je donne une plage de données (B1:C4) par exemple.
    En revanche si je fait un import uniquement pour une cellule ça marche (B1) par exemple
    Ça ne marche pas non plus si je fait un copier/coller de la formule en modifiant par après la cellule. Je dois retaper la formule en entier pour que ça fonctionne.

    Voyez vous ce qui pourrait clocher?
    J’espère avoir été suffisamment clair.
    D’avance merci de votre aide

    Joffrey

    1. Bonjour,
      Lorsque vous importez plusieurs sheets en une vous devez faire en sorte de laisser assez de place libre pour que les sheets n’empiètent pas les unes sur les autres, sinon l’import ne fonctionne tout bonnement pas.
      C’est peut être pour ça que vous y arrivez avec une seule cellule et pas avec plusieurs
      Je veux bien vous vérifier si vous m’envoyez une copie de ce que vous faites.

  4. Bonjour,

    Excellent article sur la fonction Importrange !!!

    Je cherche un script Google me permettant de comparer 2 Google Sheets afin de découvrir les valeurs uniques.
    Auriez-vous une solution ?

    Merci d’avance

    1. salut : oui je peux t’aider sans souci . sans script et assez facile a comprendre.
      mais combien de colonnes à comparer : 1 , 2 ??
      suffit de compter combien tu as de X par exemple en colonne A sheet 1) dans la colonne Y en colonne A de la sheet 2
      par exemple.
      ainsi de suite. …..
      On fait avec fonction Countif et fonction Unique.
      le résultat sur une autre sheet nommée sheet 3 ou alors 2 ?

      envoi un email sur ocetessa@gmail.com et ouvre moi un fichier google sheet /autorisation de partage avec ocetessa@gmail.com en exemple et je te montrerai les formules et resulats.
      😉

  5. Bonjour,

    Cette outil marche très bien pour mon cas mais j’ai cependant une petite question.
    Dans le cas où j’ai un tableau de base avec de nombreuses données et que je ne souhaite pas communiquer à tout un groupe j’ai créé un document plus léger avec quelques lignes de ce tableau afin de pouvoir les partager et qu’elle s’actualisent en temps réel. Cependant l’adresse de la source est visible et donc accessible.
    Je cherche à savoir si il est possible de cacher la source et son accès au utilisateur ayant accès au fichier de données importées.

    Si vous avez une solution, ce la me serait très utile. Merci!

    1. Bonjour,

      en utilisant le principe à trois niveaux et en ne donnant l’accès qu’au niveau 3, vous pourrez masquer le niveau 1 puisque que seul le niveau 2 est visible depuis le 3.

  6. Bonsoir,

    Comment faites vous pour centraliser les données sans que celle-ci ne se chevauchent?
    Ex d’utilisation:
    Je cherche à rassembler dans une feuille unique plusieurs feuilles de calcul dans lesquelles sont saisies des lignes de dépenses afin de pouvoir utiliser la fonction de tableaux croisés dynamiques. Or mon problème est que le nombre de lignes renseignées dans chaque feuille de budget n’est pas déterminée. La solution pour l’instant est de prévoir « large » mais je me retrouve avec de nombreuses cellules vides.
    Merci pour votre aide,

    Bonne soirée,

    1. J’avais le même problème.
      Je l’ai solutionné en utilisant des plages nommées sur une feuille que j’ai nommée « paramètres »
      Sur cette feuille de paramètres j’ai intégré mes catégories de dépenses
      Sur chaque feuille où j’ai mes dépense, je liste en début de feuille dans une zone cachée l’ensemble des catégories de la feuille paramètres pour lesquelles je fais la somme de toutes les dépenses d’une catégorie de la feuille (sumif et sumifs)
      Sur chaque feuille, pour chaque dépense j’ai ajouté une « Nature », cette nature étant une donnée de validation dont les valeurs sont celles de la plage nommée de la feuille paramètres. C’est ce critère qui me permet de faire des sommes conditionnelles avec sumif.
      Sur ma feuille d’analyse, je n’ai plus qu’à sommer les sous-totaux de chaque feuille de dépense par cétgorie.

  7. Bonjour,

    J’utilise depuis quelques temps importrange et ceci marche parfaitement et est en effet très pratique.

    Je cherche maintenant à pouvoir reproduire une possibilité microsoft office qui est le « copier avec liaison », d’un tableau excel dans word. Ceci se passerait donc sous google apps entre Google sheets et google docs. Mais, je ne trouve pas. On peut juste faire du copier/coller traditionnel. J’ai essayé de trouver des modules complémentaires, mais aucun ne propose cette faculté.
    Quelqu’un pourrait-il m’aider ?
    D’avance merci
    Xavier

  8. Bonjour,

    je suis avec beaucoup d’intérêt votre poste.

    auriez vous la solution qui m’occupe depuis quelques jours?

    J’ai créé dans office une feuille de calculs qui me sert de data base(fichier nommé DataBase), (différents fournisseurs avec les références et autres informations nécessaire. )
    Dans une autre feuille de calcul (fichier nommé : Offre) je vais rechercher les valeurs dans cette data base, (dans un onglet nommé lui-même DataBase et qui a pour formules, exemple: A1: ='[DataBase.xlsx]Tartampion’!$A1)
    pour faire des cellules avec validations de données.
    Dans l’onglet ‘Offre’, sur la cellule A1 j’applique une validation de données avec comme comme source:

    =DataBase!$A1

    Ce qui me permet d’avoir des cellules avec menu déroulant et avec l’utilisation de la fonction SI, d' »entraîner » le reste des infos de chaque produits après une formule dans la cellule d’à coté. exemple :

    A1= liste déroulante fournisseurs
    A2= liste avec comme champs : =INDIRECT($A1)

    ce qui a pour effet d’ouvrir une liste liée à la cellule A1 donc si A1= Tartampion B1 donne accès aux produits de Tartampion.

    Je cherche à recréer cela dans Google drive.

    Auriez-vous une idée de génie pour y arriver?
    J’avoue que cela m’aiderait grandement.

    En sachant que le nombre d’importrange est limité par feuille dans drive je ne vois pas trop de solution à mon problème.
    Merci d’avance de votre intérêt

    1. Bonjour,

      En sachant que le nombre d’importrange est limité par feuille dans drive je ne vois pas trop de solution à mon problème.

      Non, c’était mon plus gros problème. Les importrange sont bien mais en petite quantité. Dès que l’on dépasse un certain chiffre, les feuilles se ralentissent très fortement. À mon avis, Google Drive n’est pas la bonne solution à votre problème.

  9. Bonjour,
    Bonen présentation merci!
    Je suis intéressé par la façon dont vous avez procédé, cependant, comme « manu » dit ci-haut, j’ai du mal à voir comment vous arrivez à collecter les données de plusieurs spreadsheet (ayant le même format) et assembler tout dans une seule sheet…
    Votre expérience me serait très utile 🙂
    Merci d’avance

    1. Bonjour Mergeai,

      Comme indiqué dans l’article, j’utilise la fonction ImportRange qui prend en argument une clé de document. Cette clé de document se trouve dans l’URL du document au paramètre « key ». Ouvrer le document que vous souhaitez importer et regarder son adresse. Il y a un paramètre « key=sdhfsjdkfhsdjfh ». Il faut utiliser ce qu’il y a à la droite de key.

  10. bjr

    Importrange marchait bien avec l’ancienne version.
    Comment importez-vous des lignes d’un fichier vers un autre ?

    Je fonctionne comme vous sur le 1er point

    les 2 fichiers sont construit sur le mm format mais la réception se fait sur une ligne différente
    (l’info sur fichier A en ligne 45, sur fichier B elle doit figurer sur ligne 24)

    j’ai uniquement des lignes à récupérer : ça devrait simplifier ?

    cordialement

  11. Bonjour,
    Concernant le point 2 : importrange de tous ces documents dans un second niveau : ……

    Quelle solution avez-vous utilisé ?

    Je tente de le faire avec =QUERY(importRange(importRange(« Idclef »; »range1″); range2) mais cela ne sélectionne que mon premier fichier de la liste des clefs.
    Dans le fichier Idclef j’ai la liste des clefs des fichiers qui m’interesse pour l’importrange global !

    D’avance merci.

    1. Bonjour, je cherche également une solution à ce pbm.
      J’ai plusieurs équipes qui travaille chacune sur un tableur, dont les format sont identiques.
      Pour faciliter la visualisation globale de tous ces tableaux aux 2 coordinateurs, je souhaite les fusionner en un seul tableau (lui aussi avec les mêmes en-têtes de colonne).
      Cependant je ne réussi pas à empiler plusieurs importrange dans une même colonne.
      Y-a-t-il une solution à cela ?

  12. Bonjour,
    Je travaille sur des dashboards et votre visuel avec des compteurs donne une bonne visibilité aux résultats.
    Comment les mettez vous en oeuvre?
    Merci pour cette page très instructive.
    Cordialement.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *