Aller au contenu

Comment extraire automatiquement les mots-clés de Google Suggest dans Google Sheets ?

Antoine Brisset
Antoine Brisset
Temps de lecture : 3 min
Comment extraire automatiquement les mots-clés de Google Suggest dans Google Sheets ?
Photo by Lucia Macedo / Unsplash

Table des matières

Google Suggest est une fonctionnalité très intéressante pour le SEO. Le problème, c'est que sans outil pour récupérer facilement les suggestions, on peut vite y passer énormément de temps.

Il faut en effet se rendre sur Google, saisir un mot-clé, ajouter "a" et relever les suggestions, puis ajouter "b" et relever les nouvelles suggestions... et ainsi de suite jusque "z".

Fastidieux, non ?

Voyons plutôt comment faire ça en un clic dans Google Sheets.

Créer une colonne pour chaque lettre

L'idée, c'est de récupérer les suggestions de mots-clés pour chaque lettre suivant un mot-clé donné, c'est-à-dire mot-clé + a, mot-clé + b, mot-clé +c, et ainsi de suite.

Créons donc les colonnes A à Z.

Pour éviter de nommer chaque colonne une par une, manuellement, voici une petite astuce :

D'abord, je crée une colonne en C contenant les 26 lettres de l'alphabet avec la formule suivante =ARRAYFORMULA(CHAR(96+(ROW(C1:C26)))):

  • la formule CHAR suivi d'un nombre permet d'afficher le caractère alphanumérique correspondant à un caractère Unicode : ici je démarre au n°97 (96 + 1) qui correspond dans la table Unicode à la première lettre minuscule de l'alphabet latin, autrement dit le "a"
  • la valeur du "+" est obtenue avec la fonction ROW qui retourne simplement le numéro de ligne de la celulle (donc 1, 2, 3, etc. jusqu'à 26)
  • la fonction ARRAYFORMULA permet de jouer la formule sur l'ensemble de la plage C1 à C26, de manière à afficher automatiquement toutes les lettres de a à z
Génération des lettres de l'alphabet

Ensuite, c'est assez simple.

Pour transformer les lignes en colonne et ainsi avoir un en-tête de colonne contenant chaque lettre de l'alphabet, je vais utiliser la fonction TRANSPOSE.

Création des en-têtes de colonne

Récupérer les mots-clés Google Suggest pour chaque lettre

Passons au cœur du sujet : le scrape des suggestions Google.

Ici, j'aurai besoin de deux ingrédients : la fonction IMPORTXML et l'API Google Autocomplete, disponible à cette adresse https://www.google.com/complete/search?output=toolbar&q=mot-clé.

Il suffit d'ajouter un espace et une lettre à la fin de l'URL ci-dessus pour obtenir, à partir d'un mot-clé, les suggestions de mots-clés correspondantes.

Plaçons le mot-clé de départ en A2.

La formule sera donc la suivante :

=IMPORTXML("https://www.google.com/complete/search?output=toolbar&q="&$A$2&"%20"&D1&")";"//suggestion/@data")
Extraction des mots-clés

Regrouper tous les mots-clés dans une seule colonne

Dernière étape : regrouper l'ensemble des mots-clés dans une seule et unique colonne, en prenant soin de filtrer les résultats vides ou #N/A.

Pour ce faire, je vais utiliser la fonction QUERY qui est absolument géniale. En effet, elle permet de réaliser des opérations sur une plage de données à l'aide d'une syntaxe très proche du SQL.

Voici la formule :

=QUERY(FLATTEN(D2:AC11);"WHERE Col1 IS NOT NULL AND NOT Col1 CONTAINS '#N/A'")));"")

Si tu te demandes à quoi sert FLATTEN, il permet d'agréger toutes les valeurs d'une plage en une seule colonne, afin de pouvoir utiliser correctement la fonction QUERY.

Il ne me reste plus qu'à dédoublonner les mots-clés (au cas où) avec UNIQUE, puis à les trier par ordre alphabétique avec SORT.

=SORT(UNIQUE(QUERY(FLATTEN(D2:AC11);"WHERE Col1 IS NOT NULL AND NOT Col1 CONTAINS '#N/A'")))

Création de la liste finale

Pour récupérer le template, c'est par ici : https://docs.google.com/spreadsheets/d/1GsPy1Py0Okho-L3uMbt1_A4l4EbwvpKAQJujGxuyqW4/copy

Amuse-toi bien !

Google Sheets

Antoine Brisset Twitter

Consultant SEO depuis 2010. Je traque les clics inutiles et automatise les tâches répétitives pour gagner du temps dans mon quotidien de travailleur du web.

Commentaires


Articles Similaires

Membres Public

Comment vérifier gratuitement si une URL est indexée avec Google Docs ?

La semaine dernière, j'ai partagé sur Youtube une petite astuce qui permet de savoir si une URL est indexée en utilisant Google Docs. C'est rapide, c'est gratuit et ça ne nécessite aucune compétence technique. Je t'explique. Quand tu colles une URL dans un document Google Docs (ça marrche aussi dans

Comment vérifier gratuitement si une URL est indexée avec Google Docs ?
Membres Public

Comment trouver l'IP d'un site web avec une simple formule Google Sheets ?

Aujourd'hui, je vais te montrer comment convertir un domaine en adresse IP directement dans Google Sheets. Et oui, c'est possible ! Personnellement, je m'en sers surtout pour analyser la qualité de mes backlinks : ça me permet de vérifier que j'ai suffisamment d'IP différentes parmi mes domaines référents. 1ère étape : récupérer la

Comment trouver l'IP d'un site web avec une simple formule Google Sheets ?
Membres Public

Fonctions nommées : le secret pour travailler encore plus vite avec Google Sheets

As-tu déjà entendu parler des fonctions nommées ("named functions"), la nouveauté disponible dans Google Sheets depuis cet été ? Personnellement, je l'utilise depuis quelques semaines et je trouve ça absolument génial. Les fonctions nommées, c'est quoi ? Une fonction nommée, c'est une fonction personnalisée qu'on peut construire à partir des fonctions "natives"

Fonctions nommées : le secret pour travailler encore plus vite avec Google Sheets