[Google Sheets] QUERY avec noms de colonne (no script)

Bonjour à tous,

Aujourd'hui je vais vous présenter une petite astuce pour "améliorer" la fonction QUERY de Google Sheets. L'idée c'est de pouvoir utiliser les en-têtes de colonne dans la query afin d'avoir une syntaxe de 1 beaucoup plus lisible, et de 2 plus adaptable puisque l'on n'a plus à mémoriser l'emplacement des colonnes.

Comme vous pouvez le constater dans l'exemple ci-dessus, cela permet d'écrire une QUERY sur la plage A:F, en récupérant (ici) les colonnes "first name" et "age".

Alors je n'invente rien, c'est une astuce que j'ai trouvée ici et que je voulais partager.

Pour faire cela il y a plusieurs solutions, mais souvent elles dépendent d'un script qui va lire la formule et la "corriger", ou de formules assez lourdes. Dans cette astuce en revanche on va rester en no script et juste utiliser une formule assez courte. On verra comment créer une fonctions nommées afin de pouvoir utiliser la formule plus facilement.

Nous allons maintenant voir comment créer la fonction nommée dans le but de simplifier l'expression ci-après (qui permet de tester la formule sans le gestionnaire de noms) par la suivante :

=QUERY_TITRE(A:F; "select `first name`, `age` where `age` = 24")

=LAMBDA(data; query_text;
  QUERY({data}; LAMBDA(text; columns;
    REDUCE(text; FILTER(columns; NOT(ISBLANK(columns))); LAMBDA(res; col;
      REGEXREPLACE(res; "`" & col & "`"; "Col" & MATCH(col; columns; 0))))
    )(query_text; ARRAY_CONSTRAIN(data; 1; COLUMNS(data)));
  1)
)(A:F; "select `first name`, `age` where `age` = 24")

Info : sur clavier français AZERTY, vous pouvez accéder au caractère "back quote" ` en appuyant sur ALTGR + 7.

Création de la fonction nommée

Menu Données > fonctions nommées > ajouter une nouvelle fonction

• nom de la fonction : (mettez le nom que vous voulez, moi je propose) QUERY_TITRE
• description : QUERY sur les titres de colonnes
• arguments : data [Entrée] query_text [Entrée]
• définition de la formule :

=QUERY({data}; LAMBDA(text; columns; REDUCE(text; FILTER(columns; NOT(ISBLANK(columns))); LAMBDA(res; col; REGEXREPLACE(res; "`" & col & "`"; "Col" & MATCH(col; columns; 0)))))(query_text; ARRAY_CONSTRAIN(data; 1; COLUMNS(data))); 1)

Puis [Suivant]

• Insérer les détails supplémentaires

Et [Valider].

Test de la formule

Maintenant si vous copiez/collez le petit tableau suivant dans votre classeur en A1 :

Puis que vous écrivez en E1 :

=QUERY_TITRE(A1:C7;"select `first name`, `age` where `age` = 24")

Vous obtiendrez bien les colonnes "first name" et "age" de la plage A1:C7 (avec les en-têtes conservés).

Vous pouvez maintenant utiliser cette formule comme la QUERY habituelle, simplement en encapsulant les en-tete de colonnes dans des `.

Explication de la formule

En fait cette formule utilise une combinaison très intéressante de REDUCEet REGEXREPLACEet MATCH. L'idée c'est d'utiliser REDUCE pour parcourir le "texte" de notre query, et de remplacer tous les mots entre "back quote" (`) par leurs numéros de colonne relative.

MATCH permet de trouver le numéro de colonne (X), et REGEXREPLACE remplace toutes les occurrences du mot dans la QUERY par "Col X". Et ensuite la query "corrigée" est envoyée normalement à Google Sheets qui fait son calcul.

Informations complémentaires

Je vous conseille de lire la discussion complète sur stackoverflow pour voir les alternatives, de plus l'auteur propose une alternative pour gérer des colonnes de titres (affichées ou non).

Si vous n'aimez pas les back quote ` vous pouvez les remplacer par d'autres caractères (par exemple { }) mais gardez bien en tête que ce sont les caractères délimitant, donc ils ne doivent pas apparaitre ailleurs dans la query.

// exemple d'utilisation des { }

=LAMBDA(data; query_text;
  QUERY({data}; LAMBDA(text; columns;
    REDUCE(text; FILTER(columns; NOT(ISBLANK(columns))); LAMBDA(res; col;
      REGEXREPLACE(res; "{" & col & "}"; "Col" & MATCH(col; columns; 0))))
    )(query_text; ARRAY_CONSTRAIN(data; 1; COLUMNS(data)));
  1)
)(A:F; "select {first name}, {age}")

Vous pourriez même dans l'absolu vous en passer, mais il faudrait être absolument certain de 2 choses :

1. les en-têtes sont toujours un mot unique (sans espace)
2. ces mots n'empiètent jamais sur les mots clés réservés de la query. C'est pourquoi je le déconseille.