Automatisez les rapports multisites avec Google Sheets et l'API GSC

Travailler dans le référencement entraîne des défis intéressants auxquels, j'en suis sûr, vous avez tous été confrontés à un moment donné.

Vous maîtrisez la flexibilité et la gestion de tâches fastidieuses. Je me suis récemment retrouvé face à plus de 100 sites de premier plan.

En travaillant avec des entreprises mondiales, c'est tout un casse-tête de :

• Gérez les données de plus de 100 sites.
• Gardez un œil sur les performances de chaque site.

Et comme certains de ces sites se font concurrence sur la première page de Google, il est fort possible que le trafic du site 1 diminue mais que le site 2 capte la perte.

Vérifier la Google Search Console (GSC) d'un site est facile, mais c'est intense avec des centaines de sites à l'échelle mondiale.

Que pouvez-vous faire?

J'ai conçu un script Google Sheets Apps qui se connecte à l'API de GSC pour transformer le reporting global d'une tâche ardue qui peut prendre des jours – ou des semaines – en une tâche qui ne prend que quelques minutes.

Après avoir créé le script, je peux facilement définir une plage de dates et extraire les éléments suivants :

• Clics et impressions.
• Mots clés.
• Classements moyens.
• Etc.

Puisque nous gérons des centaines de sites, il n'est pas rare que des utilisateurs se retrouvent sur l'un de nos sites pour effectuer leur achat, comme mentionné ci-dessus.

Dans l’ensemble, la situation dans son ensemble est plus importante que la performance d’un site individuel.

Ce que je vais vous montrer, c'est mon Processus en 10 étapes pour créer un script qui extrait clics et impressions puis le compare toute l'année sur l'année (YoY).

Processus en 10 étapes pour créer un script d'applications Google Sheets pour créer des rapports sur des centaines de sites

Étape 1 : Création de vos feuilles Google

Votre première étape consiste à créer votre fichier Google Sheets d'origine. Vous pouvez le faire en suivant ces étapes :

• Accédez à Google Drive.
• Accédez au dossier dans lequel vous souhaitez placer les fichiers.
• Clic-droit en arrière-plan
• Sélectionnez > Google Sheets > Feuille de calcul vierge.

Vous voudrez renommer le fichier. J'ai appelé le mien « Global Search Console Reporting ».

Votre fichier est maintenant configuré et vous êtes prêt pour l'étape suivante.

Étape 2 : configuration de votre feuille Google

Une feuille vierge n'est pas utile et n'aura aucun sens pour les utilisateurs tant que vous n'aurez pas ajouté des en-têtes dans la ligne 1. Les en-têtes que je recommande d'ajouter, dans cet ordre et en gras, sont :

• Site web.
• Niche.
• Clics.
• Impressions.
• Clics annuels.
• Impressions annuelles.
• Clics % de différence.
• Différence en % d’impressions.

Votre fichier devrait maintenant ressembler à ceci :

Votre prochaine étape consiste à créer un projet Google Cloud, qui est également assez simple et direct.

Étape 3 : Créer un projet de données Google Cloud Console

La création de votre projet devrait être gratuite car Google offre un crédit de 300 $ pour tester sa plateforme. Si vous n'avez pas utilisé Google Cloud, vous pouvez le trouver sur https://console.cloud.google.com/.

Vous pouvez maintenant suivre ces étapes :

• Robinet Sélectionnez Projet > Nouveau projet.
• Entrez le nom du projet (exemple : « Mon projet de données GSC »).
• Robinet Créer.

• Cliquez sur Sélectionnez un projet.
• Sélectionnez votre projet.

• Cliquez en haut Recherche bar.
• Tapez « API de la console de recherche Google.»
• Sélectionner « API de la console de recherche Google. »
• Cliquez sur Activer.

Étape 4 : créer des scripts d'applications dans Google Sheets

Dans cette étape, nous travaillerons à l'intégration d'Apps Script dans la feuille Google que vous avez créée précédemment. Vous devrez ouvrir la feuille et suivre ces étapes :

• Robinet Rallonges > Script d'applications.

Je ne vais pas entrer dans les détails du fonctionnement du script, mais vous pouvez copier ce code :

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Search Console')
    .addItem('Fetch Data', 'menuItem1')
    .addToUi();
}

function menuItem1() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow(); // Find the last row with data in column A

  // Clear cells C2:F151 before processing data
  sheet.getRange("C2:F151").clearContent();

  for (var i = 2; i <= lastRow; i++) { var siteProperty = sheet.getRange(i, 1).getValue(); var startDateValue = sheet.getRange('M1').getValue(); var endDateValue = sheet.getRange('M2').getValue(); var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(); var format = "yyyy-MM-dd"; // Calculate dates for last year var lastYearStartDate = new Date(startDateValue); lastYearStartDate.setFullYear(lastYearStartDate.getFullYear() - 1); var lastYearEndDate = new Date(endDateValue); lastYearEndDate.setFullYear(lastYearEndDate.getFullYear() - 1); var startDate = Utilities.formatDate(lastYearStartDate, timeZone, format); var endDate = Utilities.formatDate(lastYearEndDate, timeZone, format); // Fetch data for the previous year var previousYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Fetch data for the current year (unchanged) startDate = Utilities.formatDate(new Date(startDateValue), timeZone, format); endDate = Utilities.formatDate(new Date(endDateValue), timeZone, format); var currentYearResponse = requestSearchConsoleAPI(siteProperty, startDate, endDate); // Process and write data for both years processAndWriteData(sheet, i, previousYearResponse, currentYearResponse); } } function processAndWriteData(sheet, row, previousYearResponse, currentYearResponse) { // Check if response is not defined or null and has at least one row if (previousYearResponse && previousYearResponse.length > 0) {
    var previousYearClicks = 0;
    var previousYearImpressions = 0;

    previousYearResponse.forEach(function(row) {
      previousYearClicks += row.clicks;
      previousYearImpressions += row.impressions;
    });

    sheet.getRange(row, 5).setValue(previousYearClicks); // Write to column D (index 5)
    sheet.getRange(row, 6).setValue(previousYearImpressions); // Write to column E (index 6)
  } else {
    Logger.log('No data found for previous year in row: ' + row);
  }

// Process and write data for the current year
  if (currentYearResponse && currentYearResponse.length > 0) {
    var currentYearClicks = 0;
    var currentYearImpressions = 0;

    currentYearResponse.forEach(function(row) {
      currentYearClicks += row.clicks;
      currentYearImpressions += row.impressions;
    });

    sheet.getRange(row, 3).setValue(currentYearClicks); // Write to column C (index 3)
    sheet.getRange(row, 4).setValue(currentYearImpressions); // Write to column D (index 4)
  } else {
    Logger.log('No data found for current year in row: ' + row);
  }
}



function requestSearchConsoleAPI(siteProperty, startDate, endDate) {

  try {
    const oauthToken = ScriptApp.getOAuthToken(); // Correctly call the method
    const siteUrl = siteProperty;
    const url="https://www.googleapis.com/webmasters/v3/sites/" + encodeURIComponent(siteUrl) + '/searchAnalytics/query';
    const payload = {
      startDate: startDate,
      endDate: endDate,
      type: 'web'
    };

    const headers = {
      'Authorization': 'Bearer ' + oauthToken,
      'Content-Type': 'application/json'
    };
    const options = {
      'method': 'post',
      'contentType': 'application/json', // Consistent content type
      'headers': headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };

    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const contentText = response.getContentText(); // Get response text for logging

  Logger.log('Response Code: ${responseCode}'); // Use backticks
  Logger.log('Response Content: ${contentText}'); // Use backticks


  if (responseCode === 200) {
    const json = JSON.parse(contentText);
    Logger.log(json); // This will log the actual JSON response
    return json.rows; // Adjust this line based on the actual structure of your API response
  } else {
    // Correctly use backticks here for template literals
    const errorMessage="Error fetching data: ${responseCode} - ${contentText}";
    Logger.log(errorMessage);
    throw new Error(errorMessage);
  }

  } catch (e) {
    Logger.log('Error: ${e.toString()}');
    return null;
  }
}

Revenez ensuite à votre projet Apps Script et procédez comme suit :

• Presse CTRL + A pour tout sélectionner.
• Presse CTRL + V pour coller le code que vous avez copié.
• Robinet D'ACCORD.
• Cliquez sur Sauvegarder le projet.
• Robinet Courir.

*Note: Si vous recevez une erreur de demande incorrecte de Google avec trop de redirections, c'est parce que vous avez plusieurs comptes connectés. Essayez dans un navigateur avec un seul compte Google connecté.

Il vous sera demandé de Vérifier les autorisations et devra sélectionner le compte Google associé à votre Google Search Console.

Google vous enverra un avertissement car l'application n'est pas vérifiée, alors appuyez simplement sur le bouton « Avancé » réglage puis « Allez au projet sans titre (dangereux). »

Enfin, vous pouvez terminer cette étape en appuyant ou en cliquant sur le Permettre bouton.

Étape 5 : configurer les informations d'identification d'accès

Je sais qu'il y a beaucoup de va-et-vient entre Sheets et Google Cloud Console, mais c'est une nécessité malheureuse à ce stade. Nous allons maintenant configurer les informations d'identification d'accès, ce qui vous obligera à revenir à Google Cloud Console.

Note: Vous devez avoir activé l'API Google Search Console de l'étape précédente.

Votre écran devrait ressembler à ceci :

Vous devrez :

• Robinet Informations d'identification > Créer des informations d'identification.
• Robinet ID client OAuth > Configurer l'écran de consentement.

• Cliquez sur Externe.
• Robinet Créer.
• Entrer « Mes données GSC » comme nom de l'application.
• Ajoutez votre E-mail d'assistance (votre email utilisé pour GSC).
• Ajoutez votre Dcoordonnées du développeur (l'e-mail que vous avez utilisé pour GSC).
• Robinet Sauvegarder et continuer.
• Robinet AJOUTER OU SUPPRIMER DES PORTÉES.
• Vérifiez 2 des API de la console de recherche Google étendues (peut-être à la page 2).

• Cliquez sur Mise à jour.
• Cliquez sur Sauvegarder et continuer.
• Cliquez maintenant Ajouter des utilisateurs.

• Vous pouvez ajouter plusieurs utilisateurs, de préférence ceux qui ont accès à GSC.
• Sauvegarder et continuer.

Étape 6 : Configurer le projet Google Cloud pour les données GSC

Pendant que nous sommes encore sur le projet Google Cloud, vous souhaiterez cliquer sur le bouton icône de hamburger et allez à Présentation du cloud > Tableau de bord :

Vous remarquerez qu'il est indiqué « Numéro de projet », que vous devez sélectionner et Copie en appuyant CTRL + C.

Revenez à votre onglet Apps Script et appuyez sur Paramètres du projet :

Accédez à la section intitulée Projet Google Cloud Platform (GCP)collez le numéro du projet (CTRL + V) dans la zone de texte, puis cliquez sur Définir le projet.

Étape 7 : Renommez votre script Google Apps

Vous souhaiterez maintenant renommer votre Apps Script en accédant à Historique du projet comme ça:

Vous allez alors :

• Cliquez sur Projet sans titre en haut de l'écran.
• Entrer « Mon script de projet de données GSC. »
• Cliquer sur Renommer.

Étape 8 : Modifier le fichier manifeste de Google Apps pour le script Code.gs

Vous restez toujours à l'intérieur de votre script, et nous allons y revenir Paramètres du projet tout comme nous l'avons fait auparavant.

Cette fois, vous aurez envie de cliquer Afficher le fichier manifeste « appsscript.json » dans l'éditeur pour vous assurer qu'il y a une coche à côté.

Ensuite, cliquez sur Éditeur et accédez au appsscript.jsonque vous pouvez voir ci-dessous :

Vous souhaiterez tout supprimer dans le fichier appsscript.json et coller le script suivant :

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": [
    "https://www.googleapis.com/auth/webmasters",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Une fois que vous avez ajouté le code, vous pouvez cliquer sur votre Code.gs fichier et appuyez sur Sauvegarder, et puis Courir. Vous serez invité à vérifier les autorisations et vous devrez sélectionner votre compte approprié pour continuer à l'utiliser.

Après quelques invites, il vous sera demandé d'autoriser votre application « Mes données GSC » et l'exécution commencera.

Étape 9 : Ajustez les dates pour l'analyse des données du site Web

Dans le fichier Google Sheets, vous souhaiterez ajouter les éléments suivants sous :

• L1: Date de début.
• L2: Date de fin.

Note: Les dates de début et de fin doivent être précisées dans M1 et M2. Par exemple, vous pouvez saisir :

Note: Le format de la date peut différer en fonction des paramètres de votre système et de votre emplacement.

Étape 10 : Définir la mise en forme conditionnelle pour les cellules non vides inférieures à zéro

Tout est configuré, mais vous devez ajouter une mise en forme conditionnelle pour le rendre meilleur. Nous allons nous concentrer sur les colonnes « % de différence de clics » et « % de différence d'impressions » :

Sélectionnez les lignes sous les en-têtes « Clics % de différence » et « Impressions % de différence » et cliquez sur Format > Mise en forme conditionnelle. Sous Règles de format, vous voudrez sélectionner Moins que.

Dans la zone de texte « Valeur ou formule », vous pouvez ajouter 0.

Cela fait que s'il est inférieur à 0, nous changerons la couleur en rouge car il est négatif et le trafic sera perdu. Vous pouvez le faire en cliquant sur le pot de peinture et en le changeant en rouge avant de cliquer sur Terminé.

Si vous souhaitez transformer une augmentation positive du trafic en vert, vous ajouterez une autre règle pour Plus grand que et ajoutez le 0 valeur.

Voici les formules à utiliser dans G2 et H2 (vous pouvez les reproduire pour chaque ligne ; il suffit de cliquer et de faire glisser vers le bas pour les autres lignes) :

=IFERROR(IF(AND(C2<>"",E2<>""), (C2-E2)/E2, ""),"")

=IFERROR(IF(AND(D2<>"",F2<>""), (D2-F2)/F2, ""),"")

Vous disposez désormais d’un moyen simple d’exécuter des rapports sur plusieurs sites à la fois.

Ça y est, vous avez votre rapport global

Dans la colonne A, saisissez vos propriétés Google Search Console ; s'il s'agit d'une propriété de domaine, ajoutez-la sous la forme sc-domain:example.com ou une propriété d'URL sous la forme https://example.com

Pour exécuter ou actualiser le rapport, utilisez le menu spécial Search Console > Récupérer des données :

*Note: Ce script prend en charge environ 150 domaines, mais si vous en avez besoin de plus, vous pouvez ajuster la ligne n°14 dans votre fichier AppScripts :

sheet.getRange("C2:F151").clearContent();

Grâce à ce didacticiel, vous pourrez facilement transformer des journées de collecte de données et d'exécution de rapports en quelques minutes. Vous pouvez même développer les scripts pour effectuer d'autres calculs ou collecter davantage de données pour votre rapport.

Consultez mon autre tutoriel sur l'intégration de ChatGPT avec Google Sheets.

L'automatisation de vos rapports est un excellent moyen de rationaliser les tâches fastidieuses et j'espère que cela facilitera un peu votre travail.

Davantage de ressources: