Google Sheets : comment réparer l’erreur “dans la fonction ARRAY_LITERAL, il manque des valeurs…”

La fonction matricielle ou ARRAY_LITERAL sous Google Sheets, permet d’afficher plusieurs colonnes les unes en dessous des autres. C’est très pratique pour consolider des données issues de tables différentes.

Voici l’article récent de Thierry pour en savoir plus sur cette fonctionnalité de cumul des tables.

Prenons l’exemple d’une équipe dont chaque collaborateur doit renseigner le temps passé sur différentes tâches de la journée. Chacun remplit ses informations sur son onglet et la direction les rassemble dans une seule table pour faire ses analyses globales. 

Afficher toutes les données en un seul endroit avec les accolades.

={Annabelle!A1:F9;Bastien!A2:F9;Claudia!A2:F9}

Chaque table peut être une plage du même SpreadSheet, comme dans l’exemple, mais aussi une formule de tri, de filtres (FILTER() ou QUERY()) ou encore des IMPORTRANGE() ou IMPORTDATA() IMPORTHTML()… 

Comment gérer les erreurs de la fonction matricielle ?

Si vous essayez des combinaisons un peu rock’n’roll, vous risquez forcément de tomber sur l’erreur suivante : 

« Dans la fonction ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d’un littéral de tableau”. 

Personnellement, la formulation de l’erreur m’a toujours laissé perplexe. 

Autant les autres messages d’erreurs sont précis et détaillés (cf. encore un article de Thierry), autant celui-ci brille par son manque d’explications ! Après plusieurs heures de recherches sur des cas précis, de tests et de quêtes dans les documentations et forums, j’ai pu repérer plusieurs explications différentes pour le même message.

Cet article vous présente le résultat de mes observations. Si vous en connaissez d’autres, n’hésitez pas à partager dans les commentaires.

Les formules ou les plages internes ne renvoient pas le même nombre de colonnes 

Exemple :

={Annabelle!A1:E9;Bastien!A2:B9;Claudia!A2:F9}

ici la plage A1:E9 a cinq colonnes, A2:B9 seulement deux et A2:F9, six colonnes.

={query(Annabelle!A1:F;"SELECT A,B,C,E WHERE A is not null");Bastien!A2:E9;Claudia!A2:E9}

Le Query() renvoie quatre colonnes, alors que les autres plages en ont cinq.

Google Sheets ne peut pas cumuler des plages si elles n’ont pas le même nombre de colonnes.

Résolution :

Vérifiez le nombre de colonnes renvoyées par chaque plage ou chaque formule. Dans le premier exemple, c’est relativement simple car il s’agit de trois plages explicites. Mais cela se complexifie si vous cumulez un grand nombre de tables, ou si vous utilisez des plages nommées, qu’il faut prospecter une à une, et s’assurer qu’elles recouvrent le même nombre de colonnes, ou si vous utilisez des formules (Filter() ou Query() par exemple) où il est plus difficile de gérer le nombre de colonnes.

Une des formules qui compose la fonction matricielle renvoie une erreur 

Exemple :

J’utilise des Query() pour afficher les informations filtrées (et ainsi éviter les lignes vides avec la condition « where B Is Not Null » bonne pratique plutôt que de limiter le nombre de lignes en dur comme dans l’exemple précédent (en savoir plus sur QUERY)).

Résolution :

Si la première hypothèse a été vérifiée et validée (est-ce que toutes les formules renvoient le même nombre de colonnes ?), il faut maintenant se demander si chaque formule est correcte. L’erreur globale #VALEUR! ne précise pas si il y a une erreur, et surtout dans quelle formule elle se situe. Il faut donc prospecter chaque formule et y déceler la potentielle erreur. Une seule requête Query() est parfois complexe à réparer, alors plusieurs ensemble … ça devient ubuesque.

Quelques astuces pour déceler l’erreur :

Séparer l’affichage des requêtes :

Optimisez l’affichage en chassant à la ligne chaque formule (ALT+ ENTREE) ;

La lecture de chaque requête est plus facile et peut faire apparaître l’erreur. Vous avez trouvé ? Sinon, passez à la suite.

Recopier chaque formule indépendamment :

Les deux premiers Query() ont renvoyé chacun un tableau correct, mais le dernier m’a permis de repérer une erreur de syntaxe : la source « Claudia!A:E » ne possède pas de colonne « F » qui est pourtant appelée dans la requête. L’erreur « NO_COLUMN: F » est plus précise. Je peux donc réparer et injecter la réparation dans la vue matricielle.

Une des formules renvoie un nombre de ligne nul

Extrêmement déroutant : une requête vide dont la source n’inclut pas la ligne d’en-tête renvoie une erreur ! De quoi s’arracher le peu de cheveux qui me restent !

Exemple

La requête 1 n’a aucune réponse, et pourtant la vue matricielle fonctionne.

La requête 2 ne renvoie aucune réponse, et la vue bogue. 🙁

La différence entre les deux : la source commence à la ligne 2 (Bastien!A2:F) et non (Bastien!A:F) ; en fait, ici, la réponse est vraiment vide car A2:F ne contient pas la ligne d’en-tête.

Résolution

Si vous utilisez des Query(), assurez-vous de prendre l’intégralité des lignes dans la source, si votre requête peut renvoyer un nombre de lignes nul. Car la vue matricielle n’accepte pas qu’un de ses composant soit une plage vide. Du coup, vous risquez de voir plusieurs fois la ligne d’en-tête dans la consolidation des données.

Un ou plusieurs Importrange() n’a pas été validé(s)

Dernier cas possible que j’ai recensé : l’utilisation de fonctions Importrange() non associées à l’intérieur d’une vue matricielle.

Chaque collaborateur possède son propre SpreadSheet dans lequel il complète ses heures individuellement, un autre SpreadSheet centralise les heures de travail et les compile dans un seul tableau. J’ai placé les URLs de chaque collaborateur dans l’onglet « Collaborateurs ».

L’onglet « Collaborateurs » recense les URL de leurs SpreadSheets respectifs.

Le recap affiche les Importrange() filtrés par un Query().

L’erreur vient du fait qu’un des Importrange() n’a pas été associé au SpreadSheet. Dans la colonne qui suit les URLs des SpreadSheets individuels, je place un Importrange() sur une seule cellule. Cela permet de visualiser si l’association des feuilles indispensables pour l’import des données fonctionne. Ici, on voit qu’une des trois associations n’a pas été autorisée (#REF!).

Un simple clic sur « Autoriser l’accès » et c’est validé :

Valider l’association, et la vue matricielle fonctionnera !

Chaque élément de la vue matricielle est un Importrange() qui a été associé à sa feuille source. Pensez à faire des tests en amont en important uniquement la première cellule du tableau externe, afin de valider le cas échéant l’association des tables.

Ce tour d’horizon, loin d’être exhaustif, permet d’évoquer plusieurs hypothèses dans la recherche de remédiation de l’erreur « ARRAY_LITERAL ». En espérant que ce questionnement et ces pistes de résolution vous permettent de sortir de l’impasse ! Si vous avez décelé d’autres explications au même message d’erreur, merci de partager dans les commentaires.