Êtes-vous un utilisateur expérimenté de Google Sheets ? Alors vous connaissez probablement les options dans Google Sheets pour référencer une autre feuille et utiliser la fonction IMPORTRANGE pour cela. Cependant, vous pourriez devenir un expert et faire mieux. Les professionnels combinent IMPORTRANGE avec QUERY et manipulent les données importées en même temps. Si vous souhaitez rejoindre les experts et améliorer vos compétences sur Google Sheets, bienvenue dans ce billet de blog.
Si vous préférez regarder plutôt que de lire, consultez notre guide sur QUERY & IMPORTRANGE dans Google Sheets sur la chaîne YouTube de Coupler.io Academy.
Fonction IMPORTRANGE expliquée
La fonction IMPORTRANGE vous permet d’importer une plage de données à partir d’une feuille de calcul spécifiée.
Syntaxe IMPORTRANGE
=IMPORTRANGE(« URL_de_la_feuille_de_calcul », »plage_de_données »)
- URL_de_la_feuille_de_calcul : insérez l’URL de la feuille de calcul à partir de laquelle vous souhaitez importer les données.
- plage_de_données : insérez une chaîne qui spécifie la plage de données à importer.
Exemple de formule IMPORTRANGE
Nous avons une base de données dans la feuille de calcul appelée « Orders from Airtable ». Importons toutes les valeurs des colonnes E à I. Voici la formule pour cela :
=IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!E1:I21″)
Vous pouvez consulter l’exemple de formule dans cet onglet.
Fonction QUERY expliquée
La fonction QUERY vous permet de manipuler les données tout en les important à partir d’une autre feuille. Vous pouvez sélectionner, filtrer, trier et effectuer d’autres manipulations.
Syntaxe QUERY
=QUERY(plage_de_données, »chaîne_de_requête »)
-
plage_de_données : insérez une plage de cellules à interroger. La plage_de_données peut contenir des colonnes avec des valeurs booléennes, numériques ou alphabétiques. Si une colonne contient différents types de données, QUERY choisira le type de données majoritaire comme type de données de la colonne.
-
chaîne_de_requête : insérez une chaîne construite à l’aide de clauses du langage de requête de l’API Google. Vous pouvez également vous référer à une cellule qui contient la chaîne_de_requête (dans ce cas, évitez les guillemets doubles à la fois dans la formule QUERY et dans la chaîne_de_requête).
Facultativement, vous pouvez améliorer la formule QUERY avec une partie qui définit le nombre d’en-têtes dans votre plage_de_données. Dans ce cas, la syntaxe de QUERY sera la suivante :
=QUERY(plage_de_données, »chaîne_de_requête »,nombre_d’en-têtes)
Par défaut, une rangée d’en-têtes est utilisée.
Exemple de formule QUERY
Utilisons la plage de données que nous avons importée à l’aide d’IMPORTRANGE dans l’exemple précédent et effectuons les manipulations suivantes :
- Sélectionnez les colonnes A, D et E.
- Filtrez les produits dont le prix total est supérieur à 50 $.
- Triez le résultat par quantité par ordre croissant.
- Changez l’en-tête de la colonne A de « Produit » en « Nom du sandwich ».
Voici la formule QUERY pour cela :
=QUERY(‘IMPORTRANGE example’!A:E, »select A,D,E where E>50 order by D label A ‘Nom du sandwich' »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
Bien que QUERY soit une fonction puissante, elle présente un inconvénient : elle ne fonctionne que dans une feuille de calcul. Ainsi, vous pouvez récupérer des données d’une feuille à une autre, mais vous ne pouvez pas interroger une autre feuille de calcul. La combinaison de QUERY+IMPORTRANGE est destinée à résoudre ce problème.
Nous avons écrit un article sur cette fonction dans « Google Sheets Query ».
Comment combiner QUERY et IMPORTRANGE dans Google Sheets
Lorsque vous combinez QUERY et IMPORTRANGE, vous pouvez interroger une plage de données dans une autre feuille de calcul (ou plusieurs feuilles de calcul).
Si vous avez besoin d’un aperçu rapide de l’utilisation de QUERY+IMPORTRANGE, consultez cette partie du tutoriel vidéo IMPORTRANGE de Railsware Product Academy. Le timestamp nécessaire est déjà configuré :)
Syntaxe QUERY+IMPORTRANGE
=QUERY(IMPORTRANGE(« URL_de_la_feuille_de_calcul », « plage_de_données »), « chaîne_de_requête »)
- URL_de_la_feuille_de_calcul : insérez l’URL de la feuille de calcul à partir de laquelle vous souhaitez importer les données.
- plage_de_données : insérez une plage de cellules à interroger.
- chaîne_de_requête : insérez une chaîne construite à l’aide des clauses du langage de requête de l’API Google.
Facultativement, vous pouvez améliorer la formule avec une partie qui définit le nombre d’en-têtes dans votre plage_de_données. Dans ce cas, la syntaxe sera la suivante :
=QUERY(IMPORTRANGE(« URL_de_la_feuille_de_calcul », « plage_de_données »), « chaîne_de_requête », [en-têtes])
Par défaut, une rangée d’en-têtes est utilisée.
Exemple de formule QUERY+IMPORTRANGE
Répétons les exemples ci-dessus, mais en n’utilisant qu’une seule formule. Nous devons donc :
- importer un ensemble de données de la feuille de calcul appelée « Orders from Airtable ».
- effectuer quelques manipulations avec ces données :
- Sélectionnez les colonnes A, D et E.
- Filtrez les produits dont le prix total est supérieur à 50 $.
- Triez le résultat par quantité par ordre croissant.
- Changez l’en-tête de la colonne A de « Produit » en « Nom du sandwich ».
Voici la formule :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!E1:I21″), « select Col1,Col4,Col5 where Col5>50 order by Col4 label Col1 ‘Nom du sandwich' »)
« All orders!E1:I21 » est notre plage_de_données cible. Pendant l’importation, la référence des colonnes changera comme suit :
- E en A, ou simplement Colonne #1 – Col1
- F en B, ou simplement Colonne #2 – Col2
- G en C, ou simplement Colonne #3 – Col3
- H en D, ou simplement Colonne #4 – Col4
- I en E, ou simplement Colonne #5 – Col5
C’est pourquoi nous vous recommandons d’appliquer une référence Col dans vos formules QUERY pour éviter les erreurs.
Vous pouvez consulter l’exemple de formule dans cet onglet.
Comment interroger des données provenant d’autres feuilles de calcul sans formules
Utiliser des formules complexes QUERY et IMPORTRANGE peut prendre trop de temps, sans parler des erreurs de syntaxe, combinées aux problèmes du côté de Google.
Heureusement, il existe une solution alternative. Vous pouvez interroger votre plage de données à partir d’autres feuilles de calcul ou même de sources de données externes sans aucune formule complexe avec Coupler.io. De plus, cela vous permet d’automatiser l’importation de données à partir de plusieurs feuilles de calcul vers un seul document.
De cette façon, vous pouvez gagner des heures de votre temps chaque mois, éviter les erreurs potentielles et avoir vos données constamment mises à jour.
Utilisez le formulaire ci-dessous pour voir par vous-même. Nous avons présélectionné Google Sheets comme source, mais vous êtes libre de choisir votre option. Cliquez sur Continuer et inscrivez-vous gratuitement avec votre compte Google.
Voyons comment vous pouvez interroger les données de la feuille de calcul appelée « Orders from Airtable » exactement comme nous l’avons fait dans l’exemple ci-dessus.
Choisissez « Orders from Airtable » comme fichier source. Ensuite, spécifiez votre feuille – « All orders » – pour le transfert de données. Facultativement, vous pouvez sélectionner une plage particulière que vous souhaitez exporter.
Passez à l’étape suivante, où vous pouvez transformer ou interroger vos données. Cliquez sur Gestion des colonnes et décochez les colonnes à l’exception de A, D et E. De cette façon, vous masquez les colonnes inutiles en cours de route.
Vous pouvez utiliser des options de filtrage et d’autres options de requête. Cliquez sur Filtrer pour filtrer les produits dont le prix total est supérieur à 50 $. Sélectionnez la colonne nommée Total price, spécifiez la condition « supérieur à » et saisissez 50 dans le champ de valeur.
Cliquez sur le bouton Trier, sélectionnez la colonne Quantity et choisissez l’ordre croissant.
Pour changer l’en-tête de la colonne A de « Product » à « Sandwich name », cliquez sur les trois lignes à côté du nom de la colonne et sélectionnez Modifier la colonne.
Une fois vos données transformées, passez aux paramètres de destination pour vos données. Vous devrez choisir le fichier et la feuille où importer les données interrogées. Enfin, cliquez sur Exécuter l’importation.
Maintenant, vous avez vos données disponibles dans la feuille de calcul de destination.
Facultativement, activez la Mise à jour automatique des données et définissez votre calendrier de mises à jour.
Ce que vous pouvez faire avec les fonctions IMPORTRANGE+QUERY (exemples de formules réelles)
Maintenant, découvrons ce que la combinaison de IMPORTRANGE et QUERY peut offrir. « Orders from Airtable » sera notre feuille de calcul source. Le reste relève de la magie des clauses du langage de requête.
1. Importer une plage de données spécifique avec la clause de requête SELECT
La clause de requête SELECT vous permet de choisir exactement les colonnes que vous souhaitez extraire. Pour en savoir plus sur Google Sheets Query: Select.
Tâche : Importer les colonnes B, E, H et I de la feuille de calcul « Orders from Airtable ».
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« 1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g », »All orders!A:I »), « select Col2,Col5,Col8,Col9 »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
2. Combiner des données provenant de plusieurs feuilles avec QUERY et IMPORTRANGE
Il se peut que vous ayez des données dans plusieurs feuilles et que vous souhaitiez fusionner certaines informations en une seule feuille. Pas de problème. Vous pouvez également utiliser QUERY et IMPORTRANGE avec plusieurs feuilles.
Pour cela, utilisez la même structure que celle que nous avons utilisée précédemment. Cependant, vous devrez envelopper plusieurs fonctions IMPORTRANGE entre des accolades {} et les séparer par des virgules (pour fusionner les données horizontalement) ou des point-virgules (pour fusionner les données verticalement).
Syntaxe de la formule QUERY+IMPORTRANGE pour fusionner les feuilles de calcul horizontalement :
=QUERY({IMPORTRANGE(« URL_de_la_feuille_de_calcul1 », « plage_de_données »),IMPORTRANGE(« URL_de_la_feuille_de_calcul2 », « plage_de_données »),IMPORTRANGE(« URL_de_la_feuille_de_calcul3 », « plage_de_données »),..}, « chaîne_de_requête »)
Syntaxe de la formule QUERY+IMPORTRANGE pour fusionner les feuilles de calcul verticalement :
=QUERY({IMPORTRANGE(« URL_de_la_feuille_de_calcul1 »; « plage_de_données »),IMPORTRANGE(« URL_de_la_feuille_de_calcul2 »; « plage_de_données »),IMPORTRANGE(« URL_de_la_feuille_de_calcul3 »; « plage_de_données »);..}, « chaîne_de_requête »)
La plage_de_données doit contenir la même plage de colonnes pour chaque URL_de_la_feuille_de_calcul, tandis que le nombre de lignes peut varier.
IMPORTANT : Avant d’importer des données à partir de plusieurs feuilles de calcul avec QUERY et IMPORTRANGE, vous devez effectuer des importations de données à partir de chaque feuille de calcul séparément pour permettre l’accès. Sinon, vous obtiendrez une erreur #VALUE! : dans ARRAY_LITERAL, une liste littérale d’array manquait de valeurs pour une ou plusieurs lignes.
La raison est assez simple : lorsque vous importez des données IMPORTRANGE d’une feuille de calcul particulière pour la première fois, la formule demande l’autorisation d’accéder : vous devez cliquer sur un petit bouton qui apparaît à partir de la cellule.
Cependant, il n’y a pas de tel bouton lorsque vous le faites pour plusieurs sources. Consultez notre article de blog « Pourquoi IMPORTRANGE ne fonctionne pas » pour gérer d’autres erreurs.
Tâche :
- Importer les colonnes B, E, H et I de la feuille de calcul #1, « Orders from Airtable ».
- Importer les colonnes B, E, H et I de la feuille de calcul #2, « Orders from Airtable #2 ».
- Importer les colonnes B, E, H et I de la feuille de calcul #3, « Orders from Airtable #3 ».
- Ignorer la première ligne lors de l’exportation des données des feuilles de calcul #2 et #3.
Exemple de formule QUERY+IMPORTRANGE :
=QUERY({IMPORTRANGE(« 1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g », »All orders!A1:I21″); IMPORTRANGE(« 1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4″, »All orders!A2:I21 »); IMPORTRANGE(« 1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk », »All orders!A2:I21″)}, « select Col2,Col5,Col8,Col9 »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
3. Couper les lignes inutiles des données importées avec les clauses limit et offset de QUERY
La clause limit de QUERY vous permet de définir le nombre total de lignes (à l’exclusion de l’en-tête) à importer. La clause offset de QUERY vous permet de définir combien de lignes couper en haut de la plage de données importée. Pour en savoir plus sur Google Sheets Query: Limit et Offset.
Tâche 1 :
- Importer les colonnes B, E, H et I de la feuille de calcul, « Orders from Airtable ».
- Réduire le nombre de lignes importées à 10 (à l’exclusion de l’en-tête).
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col2,Col5,Col8,Col9 Limit 10 »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
Tâche 2 :
- Importer les colonnes B, E, H et I de la feuille de calcul « Orders from Airtable ».
- Réduire le nombre de lignes importées à 10 (à l’exclusion de l’en-tête).
- Ignorer cinq lignes en haut de la feuille de calcul à partir de laquelle nous exportons les données (à l’exclusion de l’en-tête).
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col2,Col5,Col8,Col9 Limit 10 offset 5 »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
4. Changer les noms des colonnes importées avec la clause label de QUERY
La clause label de QUERY vous permet de changer les noms des colonnes (en-têtes). Pour en savoir plus sur Google Sheets Query: Label.
Tâche :
- Importer les colonnes B, E, H et I de la feuille de calcul « Orders from Airtable ».
- Réduire le nombre de lignes importées à 10 (à l’exclusion de l’en-tête).
- Renommer les en-têtes des colonnes importées :
- « Date of order » => « Date et heure »
- « Product » => « Sandwich »
- « Quantity » => « Combien »
- « Total price » => « Combien ça coûte »
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col2,Col5,Col8,Col9 Limit 10 label Col2 ‘Date et heure’, Col5 ‘Sandwich’, Col8 ‘Combien’, Col9 ‘Combien ça coûte' »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
5. Formater les valeurs des colonnes importées avec la clause format de QUERY
La clause format de QUERY vous permet d’appliquer des formats spécifiques aux colonnes importées. Pour en savoir plus sur Google Sheets Query: Format.
Tâche :
- Importer les colonnes B, E, H et I de la feuille de calcul « Orders from Airtable ».
- Réduire le nombre de lignes importées à 10 (à l’exclusion de l’en-tête).
- Formater les valeurs de la colonne B pour n’afficher que le mois et l’année.
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col2,Col5,Col8,Col9 Limit 10 format Col2 ‘mmm-yyyy' »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
6. Importer les données filtrées avec la clause where de QUERY
La clause where de QUERY vous permet de filtrer les lignes des colonnes importées en fonction de conditions définies. Pour en savoir plus sur Google Sheets Query: Where.
Tâche :
- Importer les colonnes B, E, H et I de la feuille de calcul « Orders from Airtable ».
- Filtrez les données importées en fonction de la condition suivante : la valeur de la colonne I doit être supérieure ou égale à 50.
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col2,Col5,Col8,Col9 where Col9>=50 »)
7. Appliquer plusieurs critères pour QUERY et IMPORTRANGE
En poursuivant avec l’exemple précédent – lorsque vous utilisez l’instruction where, vous n’avez pas besoin de vous limiter à un seul critère. À l’aide des opérateurs and ou or, vous pouvez filtrer plusieurs critères en utilisant QUERY et IMPORTRANGE.
Tâche :
- Filtrer la formule précédente pour également récupérer les commandes dont la valeur de la colonne E commence par la lettre « S ».
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), »select Col2,Col5,Col8,Col9 where Col5 starts with ‘S’ and Col9>=50″)
Vous pouvez consulter l’exemple de formule dans cet onglet.
Remarque : Vous pouvez également utiliser QUERY et IMPORTRANGE avec plusieurs critères et un opérateur or pour sélectionner les résultats qui répondent à l’un des critères. Par exemple :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), »select Col2,Col5,Col8,Col9 where Col5 starts with ‘S’ or Col9<30″)
8. Trouver des données contenant une certaine phrase à l’aide de QUERY et IMPORTRANGE
Vous pouvez également utiliser la clause where pour spécifier ce qu’une certaine colonne doit contenir pour répondre à vos critères ou quelle phrase éviter lors de la restitution des résultats. Pour cela, utilisez un opérateur de type contains.
Tâche :
- Filtrer les données des commandes pour ne récupérer que les commandes dont les produits ne contiennent pas le mot « sandwich ».
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), »select Col2,Col5,Col8,Col9 where not Col5 contains ‘sandwich' »)
Pour trouver des données contenant une certaine phrase avec QUERY + IMPORTRANGE, omettez le mot clé not.
Notez que la chaîne que vous utilisez avec contains est sensible à la casse. Si vous avez, par exemple, à la fois « Sandwich » et « sandwich » dans le tableau, vous voudrez utiliser les opérateurs or ou and pour choisir l’une ou l’autre de ces versions, en répétant la totalité de l’instruction ‘contains’. Par exemple :
where not Col5 contains ‘sandwich’ and not Col5 contains ‘Sandwich’
where Col5 contains ‘sandwich’ or Col5 contains ‘Sandwich’
9. Trier les données importées avec la clause order by de QUERY
La clause order by de QUERY vous permet de trier les données en fonction de la colonne choisie par ordre croissant/décroissant. Pour en savoir plus sur Google Sheets Query: Order By.
Tâche :
- Importer les colonnes B, E, H et I de la feuille de calcul « Orders from Airtable ».
- Filtrer les données importées par la colonne I où les valeurs doivent être supérieures ou égales à 50.
- Trier les données importées par la date de commande (colonne B) par ordre décroissant.
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col2,Col5,Col8,Col9 where Col9>=50 order by Col2 DESC »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
10. Opérations arithmétiques avec les données importées
Vous pouvez ajouter (+), soustraire (-), multiplier (*) et diviser (/) des colonnes d’une feuille de calcul et importer le résultat dans une colonne distincte. Pour en savoir plus sur les opérateurs arithmétiques de Google Sheets Query.
Tâche :
- Importer les colonnes E et I de la feuille de calcul « Orders from Airtable ».
- Multipliez le prix (colonne G) par la quantité (colonne H) et importez le résultat dans une colonne distincte nommée « Prix total calculé ».
Exemple de formule QUERY+IMPORTRANGE :
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select Col5,Col7Col8,Col9 label Col7Col8 ‘Prix total calculé' »)
Vous pouvez consulter l’exemple de formule dans cet onglet.
11. Opérations d’agrégation avec les données importées
Dans les clauses select, order by, label et format de QUERY, vous pouvez appliquer les fonctions d’agrégation suivantes aux colonnes importées :
- avg() : fournit la moyenne de tous les nombres d’une colonne.
- sum() : fournit la somme de tous les nombres d’une colonne.
- count() : fournit la quantité d’éléments dans une colonne (les lignes avec des cellules vides ne sont pas prises en compte).
- max() : fournit la valeur maximale d’une colonne.
- min() : fournit la valeur minimale d’une colonne.
Les fonctions d’agrégation ne s’appliquent pas aux clauses where, group by, pivot, limit et offset de QUERY.
For more information about Google Sheets Query: Aggregation Functions.
Task:
- Import columns B, E, H, and I from the spreadsheet, Orders from Airtable.
- Provide the minimum value in column B.
- Count the items in column E.
- Provide the sum of all numbers in column H.
- Provide the average of all values in column I.
Example QUERY+IMPORTRANGE formula:
=QUERY(IMPORTRANGE(« https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing », »All orders!A:I »), « select min(Col2),count(Col5),sum(Col8),avg(Col9) »)
