Si vous êtes comme moi un expert SEO qui travaille sur de nombreux sites, vous vous êtes sûrement rendu compte que vérifier régulièrement l’évolution des KPIs Google Search Console de tous vos sites peut être une tâche assez fastidieuse.
Alors, dans cet article, je vous propose de vous montrer comment créer un dashboard SEO dans une Google Sheets qui récupérera automatiquement les valeurs de vos KPIs pour toutes les propriétés qui sont associées à votre compte GSC.
Pour ce faire, on utilisera Google Apps Script, un langage de programmation propre à Google qui permet de réaliser des scripts à l’intérieur de certains services de Google, comme Google Sheets, Google Data Studio, Gmail, etc…
Comme toujours, lorsque je crée un nouveau dashboard, j’utilise la méthode que je transmets dans la formation Construire des Dashboards SEO automatisés avec Google Apps Script :
- Design du dashboard
- Récupération des données via APIs
- Mise en forme des données
- Affichage des données
Étape 1 : Design du dashboard
La première étape consiste à ouvrir une Google Sheets vierge et à créer tout simplement le dashboard de nos rêves avec des données d’exemple. Cela nous permettra, d’une part, d’avoir une idée précise de ce qu’on cherche à construire, et, d’autre part, à connaître précisemment quelles seront les plages de la Google Sheets qu’il faudra alimenter avec les données de la GSC.
En ce qui me concerne, je voudrais obtenir un dashboard qui ressemblera à ça :
Vous noterez ici qu’on peut utiliser à notre avantage le format « date » des cellules d’une Google Sheets pour afficher un calendrier de sélection du jour souhaité.
On a aussi la possibilité de créer un bouton personnalisé (« METTRE À JOUR » dans cet exemple) pour lancer l’exécution du script qui récupérera les données du dashboard. Pour ce faire, il suffit de cliquer sur les éléments Insertion >> Dessin du menu principal de la Google Sheets.
L’interface suivante apparaîtra pour vous permettra de dessiner votre bouton :
Étape 2 : Récupération des données via APIs
On passe maintenant à la récupération des données via l’API de la Google Search Console. Comme la connexion à la GSC nécessite de passer par le protocole d’autorisation OAuth 2.0, on va avoir un petit peu plus de paramétrages à effectuer que pour la plupart des autres outils SEO, mais en suivant les instructions suivantes, ça ne devrait pas prendre plus de 5 minutes.
Connexion du projet Apps Script à la GSC
1. On commence par ouvrir un nouveau projet Google Apps Script depuis la Google Sheets qu’on a utilisé pour créer notre dashboard, comme ceci :
2. Ensuite, vous pouvez cliquer sur le lien suivant pour créer un nouveau projet sur Google Cloud qui vous permettra d’accéder à l’API de la Search Console via le protocol OAuth 2.0 : https://console.cloud.google.com/projectcreate
Donnez un nom au projet, et cliquez sur le bouton « CRÉER ».
3. Depuis la barre de recherche qui se trouve tout en haut de votre tableau de bord, recherchez « Google Search Console API », puis cliquez sur le bouton pour l’activer :
4. On va maintenant configurer l’écran de consentement OAuth 2.0.
Depuis le tableau de bord de votre projet, ouvrez le panneau sur la gauche de l’écran et sélectionnnez API et services >> Écran de consentement OAuth :
Sélectionnez ensuite le type d’utilisateurs que souhaitez pour votre dashboard GSC :
Dans la première étape, renseignez le nom de l’application et votre email pour l’assitance utilisateur et aussi en tant que développeur.
La deuxième étape de la configuration (« Niveaux d’accès ») peut être passée pour aller directement à la troisième étape « Utilisateurs tests », et là il vous faudra ajouter votre email comme utilisateur de test :
5. Enregistrez. Maintenant il ne nous reste plus qu’à récupérer le numéro de votre projet avant de quitter Google Cloud. Pour ce faire, il suffit de revenir à l’accueil de la console où vous pourrez copier le numéro du projet :
6. On va maintenant quitter la console Google Cloud pour revenir à notre projet Apps Script (celui qui est attaché à notre Google Sheets), et on va ouvrir les paramètres du projet en cliquant sur la roue dentée. Il y aura deux choses à faire dans les paramètres :
- Cocher «Afficher le fichier manifeste « appsscript.json » dans l’éditeur»,
- Cliquer sur le bouton «Changer de projet» et coller le numéro du projet récupéré depuis Google Cloud.
7. Dans le fichier « appsscript.json », copiez le code suivant :
Et voilà, vous avez dorénavant accès à l’API de la Google Search Console depuis votre projet Google Apps Script !
Récupération des données de la GSC
La première chose que nous devons récupérer est la liste des propriétés que se trouvent sur votre compte GSC afin de pouvoir mettre à jour notre dashboard automatiquement si des propriétés sont ajoutées ou supprimées de votre compte.
Pour ce faire, tapez le code suivant dans un script de votre de projet Apps Script :
function get_GSC_All_Sites() {
const oauthToken = ScriptApp.getOAuthToken();
const url = 'https://www.googleapis.com/webmasters/v3/sites';
const headers = {
'Authorization': 'Bearer ' + oauthToken,
'Content-Type': 'application/json'
}
const options = {
headers: headers,
method: 'GET',
}
const response = UrlFetchApp.fetch(url, options).getContentText()
const json = JSON.parse(response);
var results = [];
json.siteEntry.forEach(elem => results.push([elem.siteUrl]));
Logger.log(results.sort());
return results.sort();
}
L’instruction Logger.log(results.sort()) devra normalement vous permettre de voir apparaître la liste de vos propriétés GSC triées par ordre alphabétique dans la console de Google Apps Script.
Ensuite, nous allons écrire la fonction qui va permettre récupérer les KPIs (Clicks, Impressions, CTR, Position moyenne), avec comme paramètres la propriété GSC, la date de début et la date de fin (au format « 2022-05-24 ») :
function requestSearchConsoleAPI(property, startDate, endDate) {
const oauthToken = ScriptApp.getOAuthToken();
const siteUrl = encodeURIComponent(property);
const url = 'https://www.googleapis.com/webmasters/v3/sites/'+ siteUrl + '/searchAnalytics/query';
const payload = {
startDate: startDate,
endDate: endDate,
dimensions: []
}
const headers = {
'Authorization': 'Bearer ' + oauthToken,
'Content-Type': 'application/json'
}
const options = {
headers: headers,
method: 'POST',
payload: JSON.stringify(payload)
}
const response = UrlFetchApp.fetch(url, options).getContentText()
const json = JSON.parse(response)
Logger.log(json);
return json
}
Étapes 3 : Mise en forme des données
La prochaine étape va être de mettre en forme les données GSC dans le format qu’utilise Apps Script pour remplir les cellules d’une Google Sheets.
Il s’agit d’un tableau de tableaux (array of arrays), par exemple : [ [‘Nom’, ‘Clicks’, ‘Impressions’], [‘Site 1’, 12678, 34679], [‘Site 2’, 12678, 34679] ].
On va donc écrire le code suivant pour mettre dans un tableau tous nos KPIs avec, pour chacun d’entre eux, le calcul du pourcentage d’évolution par rapport à la période précédente :
function shape_GSC_Data (properties, current_startDate, current_endDate, previous_startDate, previous_endDate) {
var all_results = [];
function pct (a,b) { return (a - b)/a; }
for (var i = 0; i < properties.length; i++) {
try {
var curData = requestSearchConsoleAPI(properties[i][0], current_startDate, current_endDate).rows[0];
var prevData = requestSearchConsoleAPI(properties[i][0], previous_startDate, previous_endDate).rows[0];
all_results.push([
curData.clicks,
pct(curData.clicks, prevData.clicks),
curData.impressions,
pct(curData.impressions, prevData.impressions),
curData.ctr,
pct(curData.ctr, prevData.ctr),
curData.position,
pct(curData.position, prevData.position),
]);
}
catch(e) {
all_results.push(["-", "-", "-", "-", "-", "-", "-", "-",]);
}
}
Logger.log(all_results);
return all_results;
}
Étape 4 : Affichage des données
Pour terminer, on va créer une fonction qui va faire trois choses :
- Afficher la liste des propriétés dans notre dashboard
- Récupérer les paramètres de notre dashboard (c’est-à-dire les dates d’analyse et de comparaison des données)
- Afficher tous les KPIs avec leur évolution par rapport à la période précédente dans le dashboard
(À noter que la variable spreadsheetID correspond à l’ID de votre Google Sheets qui se trouve dans son URL.)
function update_GSC_Dashboard() {
var sheet = SpreadsheetApp.openById(spreadsheetID).getSheetByName('Dashboard GSC');
var properties = get_GSC_All_Sites();
Logger.log(properties);
sheet.getRange('C11:M200').clearContent();
sheet.getRange('C11:C' + (10 + properties.length)).setValues(properties);
function get_Text_Date(sheet, cell) {
var text_date = sheet.getRange(cell).getValues()[0][0];
text_date = text_date.getFullYear()
+"-"+((text_date.getMonth()+'').length == 2 ? text_date.getMonth() : "0" + text_date.getMonth())
+"-"+((text_date.getDate()+'').length == 2 ? text_date.getDate() : "0" + text_date.getDate());
return text_date;
}
var current_startDate = get_Text_Date(sheet, 'H6');
var current_endDate = get_Text_Date(sheet, 'H7');
var previous_startDate = get_Text_Date(sheet, 'L6');
var previous_endDate = get_Text_Date(sheet, 'L7');
var all_results = shape_GSC_Data (properties, current_startDate, current_endDate, previous_startDate, previous_endDate);
sheet.getRange('F11:M' + (10 + all_results.length)).setValues(all_results);
}
La création du dashboard est maintenant terminée !
Pour lancer l’exécution du script qui mettra à jour les données du dashboard, il ne reste plus qu’à assigner la fonction écrite ci-dessus au bouton « MISE À JOUR » que nous avons créé dans l’étape n°1.
Pour ce faire :
- Faites un clic droit sur le bouton,
- Cliquez sur les « trois petits points » en haut à droite du bouton,
- Sélectionnez « Assigner un script »
- Renseignez le nom de la fonction à exécuter :
Et voilà ! Le dashboard est terminé !
Pour aller plus loin…
Pour aller plus loin, on pourrait éventuellement afficher des charts avec l’évolution journalière des KPIs pour chaque propriété, ou bien combiner ces KPIs avec les données d’autres outils SEO (SEMRush, Botify, OnCrawl, aHref, etc…), mais ce sera peut-être l’objet d’un prochain article 😉
Si le sujet des dashboards créés avec Google Apps Script vous intéresse ou pourrait intéresser les développeurs qui travaillent avec vous, je vous invite à jeter un œil à ma formation : Construire des Dashboards SEO automatisés avec Google Apps Script.
Et si vous souhaitez m’engager pour designer et construire vos dashboards personnalisés, n’hésitez pas à me contacter.
À très vite !
