Excel a bien sûr ses aficionados pour mettre en place un certain nombre de fonctionnalités avancées et automatisées en termes de SEO. Mais savez-vous que Google Sheets, le tableau de la firme de Mountain View, a également un certain nombre d'avantages à faire valoir si vous désirez traiter certaines données pour suivre votre référencement naturel ? Voici un aperçu de ses principales possibilités...

Par Aymeric Bouillat

 

L’outil Google Sheets proposé par Google (équivalent à une version d’Excel en ligne) peut être utilisé pour travailler son référencement naturel, et ce de multiples façons. L’avantage de cet outil est qu’il est disponibleen ligne, où que l’on se trouve, et qu’il est gratuit. Bien que les feuilles de calcul puissent montrer leurs limites sur de gros fichiers, cet outil permet malgré tout de traiter les données de sites pas trop volumineux.

Nous allons passer en revue les différentes possibilités offertes par Google Sheets, et voir comment en tirer profit pour le SEO : extraction d’éléments en ligne, monitoring d’URL, test de redirection, ou encore connexion à des API pour analyser et traiter des KPI SEO.

Google Spreadsheet, des fonctions et un langage de script

Tout d’abord, il faut savoir que les fonctions intégrées dans Google Sheets sont en partie des variantes d’Excel, mais certaines d’entre elles ne sont pas disponibles sur Excel (ou alors via l’outil SEOTools for Excel), et permettent de se connecter directement à des URL externes pour récupérer des éléments.

Parmi ces fonctions, on peut citer IMPORTHTML, IMPORTXML pour le scraping, mais également d’autres fonctions qui peuvent aider au traitement de données et d’URL comme REGEXEXTRACT qui permet de manipuler une chaîne de caractères via les expressions régulières.


Fig. 1. Fonction Google Sheets pour extraire des éléments HTML sur des pages Web..

Pour aller plus loin, des scripts peuvent être intégrés dans les Google Sheets pour accéder à des fonctions avancées, et permettre de se connecter à des API.

C’est via un langage spécifique mais relativement proche du Javascript dans sa syntaxe que l’on va pouvoir mettre en place des outils SEO à part entière. Voici un exemple de script Google Sheets permettant de récupérer le statut http d’une page Web :


Fig. 2. Fonction permettant de récupérer le statut HTTP pour une URL donnée.

 

A noter que des possibilités équivalentes sont également disponibles sur la plateforme de Microsoft (Office Live), mais n’étant pas familier avec cette solution, nous allons nous concentrer sur Google Sheets ce mois-ci.

Des fonctions pour le SEO

Fonction IMPORTHTML

La fonction IMPORTHTML permet de rapatrier des tableau ou listes HTML directement dans une feuille de calcul. Supposons que nous souhaitions créer du contenu, et l’agrémenter de données externes, comme des informations sur les principales villes de France (avec pour objectif de créer du contenu géolocalisé). Il sera alors très simple de récupérer l’intégralité d’un tableau HTML issu de Wikipedia  dans Google Sheets :

Fonction utilisée dans une cellule sur Google Sheets :

=IMPORTHTML("https://fr.wikipedia.org/wiki/Liste_des_communes_de_France_les_plus_peupl%C3%A9es";"table";1)

Soit =IMPORTHTML(URL ;type d’élément HTML ;Occurrence à récupérer à 1 = premier tableau trouvé dans le code)


Fig. 3. Extraction d'un tableau HTML..

Cette fonction étant assez limitée (mais pratique quand les copier/coller créent des décalages ou importent des caractères spéciaux), nous allons lui préférer IMPORTXML.

Fonction IMPORTXML

Cette fonction se base sur le langage Xpath pour parcourir l’arbre d’un document HTML/XML et en extraire des données. L’avantage d’Xpath est qu’il permet de cibler des éléments de façon très précise dans le code (balise div liée à une classe ou avec Id spécifique entre autre), et qu’il fait également appel à des fonctions étendues (ex : lien contenant un mot spécifique).

Supposons que nous souhaitions extraire pour toutes les pages produits d’un site, le nombre d’avis postés par les utilisateurs pour chaque produit, afin d’analyser la visibilité des produits ayant peu d’avis (maillage, opti on-page, etc.).

Nous allons créer une colonne dans une nouvelle feuille de calcul avec la liste de URL produit. Ensuite, il sera nécessaire de détecter l’élément HTML lié au nombre d’avis, en l’occurrence pour une page produit du site test https://www.amazon.fr/Game-Thrones-Tr%C3%B4ne-Fer-Saison/dp/B07RXK29NY/, le nombre d’avis est relatif à la balise <span> contenant l’id « acrCustomerReviewText » comme le montre la capture ci-dessous :


Fig. 4. Nombre d'avis dans la balise <span>.

On pourra donc cibler cet élément via la syntaxe Xpath suivante : //span[@id=’acrCustomerReviewText’]

 

Explication :  depuis la racine du document HTML (« // »), récupérer le contenu de la balise <span> ayant pour id "acrCustomerReviewText" (soit <span id="acrCustomerReviewText" class="a-size-base">606 évaluations</span>)

ce qui nous donnera dans Google Sheets la formule suivante : =IMPORTXML(A2;"//span[@id=’acrCustomerReviewText’]")


Fig. 5. Résultat pouvant être obtenu pour récupérer un élément sur une page à partir d'une URL..

Cet exemple ne fonctionne que sur le site pris en exemple et est donné à titre indicatif, Amazon étant protégé contre la récupération de contenu.

Il est également possible de trouver le nœud Xpath d’un élément à partir de votre navigateur via l’inspecteur d’éléments :


Fig. 6. Fonction "Copy Xpath" de l'inspecteur d'éléments.

 

La fonction IMPORTXML peut nous aider à faire des vérifications en tout genre, comme par exemple sur ce site https://www.yapasdequoi.com/ :

  • Extraction de la balise title :
    =IMPORTXML(A2;"//title")
  • Extraction de la meta description :
    =IMPORTXML(A2;"//meta[@name='description']/@content")
  • Extraction du fil d’Ariane (pour classer/qualifier des URL) :
    =IMPORTXML(A2;"//p[@id='breadcrumbs']")
  • Extraction de la date des articles et de l’auteur (nombre d’article publié par mois pour chaque rédacteur)
  • Etc.


Fig. 7. Récupération d'éléments à partir d'un ensemble d'URL via Xpath.

Ce qui donnera comme résultat après avoir appliqué les formules sur chaque colonne :


Fig. 8. Résultat après tri.

Voici une liste des principaux éléments SEO pouvant être récupérés pour une URL renseignée (en cellule A2 dans cet exemple) via Google Sheets :

  • Title :
    =IMPORTXML(A2;"//title")
  • Meta description :
    =IMPORTXML(A2;"//meta[@name=’description’]/@content")
  • Meta robots :
    =IMPORTXML(A2;"//meta[@name=’robots’]/@content")
  • Balise Canonical :
    =IMPORTXML(A2;"//link[@rel=’canonical’]/@href")
  • Balises HREFLANG :
    =IMPORTXML(A2;"//link[@hreflang]/@href")

Il est donc très simple de réaliser un « mini-crawler » pour récupérer rapidement des éléments en cas de traitement d’URL par lot.

Voici d’autres exemples d’utilisation de la fonction IMPORTXML et les objectifs SEO associés à partir d’une liste d’URL :

  • Extraction du nombre de produits par catégorie (pour identifier les catégories pauvres en contenu) ;
  • Extraction des descriptifs Produit (pour identifier les pages avec peu de texte, contenus à optimiser) ;
  • Extraction du nombre d’images par produit (pour identifier les produits sans images) ;
  • Pages qui ne contiennent pas de produits/articles (pour les sortir du maillage interne) ;
  • Produits en « rupture de stock » ;
  • etc.

La syntaxe d’Xpath permettant aussi de rechercher des éléments de texte en complément des balises, vous pourrez aisément extraire tous les liens externes d’une page, en ciblant les liens qui ne contiennent pas le domaine en cours :

=IMPORTXML("https://www.yapasdequoi.com/";"//a[not(contains(@href,'yapasdequoi.com'))]/@href") renverra tous les liens externes de la page https://www.yapasdequoi.com :


Fig. 9. Extraction des liens sortants externes d'une page.

Cette utilisation peut aider à détecter des partenaires potentiels en analysant les sites concurrents.

Initialement restreinte à 50 appels par feuille de calcul, IMPORTXML a été légèrement débridée. Attention toutefois aux appels trop massifs. A noter une URL utile si vous débutez avec Xpath : https://devhints.io/xpath.

Exemple d’utilisation pour le suivi de partenariats

A partir de la fonction ci-dessous couplée au potentiel de Xpath, divers suivis peuvent être mis en place, comme le fait de s’assurer de la présence de liens vers un site dans une page (dans le cadre d’une campagne de netlinking). En couplant la fonction IF et IMPORTXML, nous allons pouvoir détecter la présence de l’URL mais également de l’ancre de lien pour monitorer ses échanges de netlinking :


Fig. 10. Extraction des URL et des ancres des liens.

Contenu de la cellule D :

=IF(ISBLANK(A2);"-"; IF(ISBLANK(B2);"-"; IF(ISBLANK(C2);"-"; IF( ISERROR(importxml(A2;"//a[text()='" & C2 & "']/@href")); "Texte de lien non trouvé"; IF(importxml(A2;"//a[text()='" & C2 & "']/@href")=B2; "OK"; "Lien non trouvé")))))

On pourra ensuite adapter ces enchaînements de fonctions pour s’assurer que les liens ne sont pas en nofollow par exemple.

Fonction DETECTLANGAGE

La fonction DETECTLANGAGE, quant à elle, peut s’avérer utile dans le cas où vous souhaitez identifier la langue d’un élément sur un ensemble d’URL pour un site multilingue. Cela peut être utile pour analyser les balises <title> non traduites à afin de les optimiser dans la langue concernée :

=DETECTLANGUAGE(IMPORTXML(A2;"//title"))


Fig. 11. Extraction de la langue des balises Title.

Il suffira ensuite de mettre en correspondance cette donnée avec la langue de la page pour envoyer les éléments manquants en traduction ! Si le répertoire langue est présent dans l’URL, l’utilisation de la fonction REGXEXEXTRACT permettra d’isoler la langue dans l’URL via des expressions régulières.

Exemple :

=REGEXEXTRACT("http://www.monsite.com/en/category/url";"\.com\/([a-z]{2})\/")

renverra la valeur « en »

.

Pour aller plus loin, et avoir une utilisation plus avancée de Google Sheets pour son SEO, il sera ensuite nécessaire de passer par le langage de script de Google Sheets.

Des scripts pour le SEO

Via l’éditeur de script de Google Sheets, il va être possible de créer des fonctions avancées et d’avoir des interactions avec des éléments extérieurs comme :

  • Appeler des URL avec des requêtes de type GET, POST ;
  • Traiter divers types de fichier pour dialoguer avec des API ;
  • Interagir avec les différents produits Google ;
  • Archiver des données de façon quotidienne ;
  • Envoyer des e-mails ;
  • Créer des menus spécifiques afin de déclencher des actions dans vos feuilles de calcul.
  • (liste non exhaustive)

C’est la Class URLFetchApp qui va nous permettre d’exécuter des requêtes sur des URL  https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app

Pour ajouter des scripts à une feuille de calcul, voici comment accéder à l’éditeur :


Fig. 12. Editeur de scripts de Google Sheets.

L’une des premières utilisations pourrait être de mettre en place un tableau de bord SEO pour suivre un ensemble de sites Web avec différents KPI :  statut HTTP, présence d’un fichier robots.txt, score PageSpeedInsights, temps de réponse de l’URL, TrustFlow (Majestic), etc. Il nous faudra dans ce cas créer une fonction par KPI dans l’éditeur de script (ou une fonction globale avec la possibilité de spécifier le KPI à récupérer via un paramètre:

Voici une fonction permettant de récupérer le status HTTP d’une page, à copier /coller dans l’éditeur de script (Ctrl+S pour sauvegarder) :

function StatutHTTP(url){
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
var url_trimmed = url.trim();
var response = UrlFetchApp.fetch(url_trimmed, options);
return response.getResponseCode();
}

Fonctionnement :
1/ On déclare la fonction « StatutHTTP » ;
2/ On nettoie l’URL (ex : suppression des espaces) ;
3/ On appelle l’URL avec les options renseignées (suivre les redirections, et masquer les erreurs) ;
4/ On récupère le statut HTTP.

Cette fonction est utile dans le cadre de la mise en place d’un plan de redirection pour s’assurer d’une bonne implémentation des 301. Pour l’utiliser, il suffit de l’appeler dans une cellule Google Sheets :

=StatutHTTP("URL à tester")

En adaptant cette fonction, il sera possible d’appeler des URL externes, comme pour récupérer le score PageSpeedInsight (via l’API V5) d’une URL sur mobile, desktop :

function pagespeed(url,device){
var key = "clé d’API à copier-coller ici";
//Obtenir une clé d’API : https://developers.google.com/speed/docs/insights/v5/get-started
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
var url_trimmed = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + url.trim() + "&strategy=" + device + "&key=" + key;
var response = UrlFetchApp.fetch(url_trimmed, options);
var result = JSON.parse(response.getContentText());
scoring = result.lighthouseResult.categories.performance.score;
return(scoring);
}

On pourra ensuite appeler la fonction dans une cellule Google Sheets :

=pagespeed("https://www.yapasdequoi.com/";"mobile")


Fig. 13. Résultat obtenu via la fonction décrite ci-dessus.

En complément, il est possible de lancer des déclenchements de fonctions de façon automatisé :


Fig. 14. Déclenchement de fonctions.

Vous pourrez également en fonction de vos besoins mettre en place des programmations sur vos diverses fonctions afin d’avoir une bonne vision de l’évolution de certains KPI. En voici un bon exemple via un script qui analyse le code réponse et le temps de chargement d’une page dans le temps :

function check_website(url) {
var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
return response.getResponseCode();
}

function doLog(timestamp, responseCode, timeDiff) {
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/URL-Feuille-Google-Sheet/sedit');
SpreadsheetApp.setActiveSpreadsheet(ss);
SpreadsheetApp.setActiveSheet(ss.getSheetByName("Log"));
var activeSheet=ss.getActiveSheet();
activeSheet.appendRow([timestamp, responseCode, timeDiff]);
}

function getTime() {
var startTime = new Date() ;
var responseCode=check_website("https://www.yapasdequoi.com");
var endTime = new Date() ;
var timeDiff = endTime-startTime;
doLog(Utilities.formatDate(new Date(), "GMT+7", "yyyyMMdd-HHmmss") , responseCode , timeDiff);
}

Il faudra ensuite mettre en place un déclencheur toutes les X minutes en fonction de vos besoins :


Fig. 15. Ajout de déclencheur.

On retrouvera ensuite dans la feuille Google Sheets correspondant à l’URL renseignée dans le script, une ligne pour chaque déclenchement effectué (onglet nommé « Log »). Il faudra préalablement nommer les colonnes auxquelles le script fait référence (activeSheet.appendRow([timestamp, responseCode, timeDiff]);))  :


Fig. 16. Une ligne par déclenchement effectué.

 De multiples possibilités s’offrent à vous via les déclencheurs, libre recours à votre imagination !

Les scripts « clés en main »

Différents développeurs et référenceurs se sont déjà penché sur les utilisations possibles de Google Apps Scripts. Voici les outils les plus remarquables :

Des add-ons pour le SEO

Pour aller plus loin, divers modules et add-ons Chrome orientés SEO sont mis à votre disposition afin de connecter des API à Google Sheets :

Données issues de Google Search Console : https://searchanalyticsforsheets.com/
Données issues de Google Analytics : https://gsuite.google.com/marketplace/app/google_analytics/477988381226
Données issues de SuperMetrics : https://gsuite.google.com/marketplace/app/supermetrics/523876908005
Donénes issues de SEMRush : https://opensourceseo.org/semrush-api-library-google-sheets-google-scripts/

Et enfin, quelques sources pour Apps Script utilisées dans cet article :
https://developers.google.com/apps-script/
https://www.labnol.org/

Avec tout cela, vous êtes paré pour adapter Google Sheets à votre SEO (ou le contraire)...

 

Aymeric Bouillat, Consultant SEO senior chez Novalem (https://www.novalem.fr/)

 

Articles similaires :