Comment détecter automatiquement le fournisseur de messagerie d’une adresse e-mail dans Google Sheets ?

Publié le 11 novembre 20246 décembre 2024 par L'atelier informatique

Avez-vous déjà eu le besoin d’identifier rapidement le fournisseur de messagerie de vos contacts afin de mieux segmenter vos listes, analyser vos campagnes marketing ou tout simplement organiser votre base d’adresses ? Saviez-vous qu’il est possible d’automatiser ce processus directement au sein de Google Sheets grâce à une fonction personnalisée ?

Dans cet article, je vais vous montrer comment créer une fonction capable de déterminer le fournisseur d’une adresse e-mail en analysant les enregistrements MX du domaine associé. En quelques minutes, vous disposerez d’un outil pratique pour mieux comprendre et gérer vos données e-mail.

Pourquoi détecter le fournisseur de messagerie ?

Connaître le fournisseur (Gmail, Yahoo, Outlook, OVH, etc.) offre plusieurs avantages :

Segmentation marketing : Personnaliser vos campagnes en fonction du fournisseur permet d’ajuster votre ton ou vos offres.
Analyse de performance : Évaluer si vos e-mails sont plus ouverts chez certains fournisseurs pour améliorer votre stratégie.
Organisation des données : Maintenir une base de contacts propre et identifier rapidement les adresses potentiellement inactives.
Support technique : Diagnostiquer plus facilement les problèmes de réception en identifiant les spécificités propres à chaque fournisseur.

Ce dont vous avez besoin

Un compte Google et accès à Google Sheets.
La possibilité d’accéder à l’éditeur de script (Apps Script) intégré dans Google Sheets.
Quelques minutes pour copier et coller la fonction personnalisée.

Étape 1 : Accéder à l’éditeur de script

Ouvrez votre feuille de calcul Google Sheets.
Dans le menu, cliquez sur Extensions > Apps Script. Une nouvelle fenêtre s’ouvrira, affichant l’éditeur de script.

Étape 2 : Ajouter la fonction personnalisée

Copiez le code ci-dessous et collez-le dans l’éditeur. Cette fonction, nommée DETECTER_FOURNISSEUR_EMAIL, va interroger les enregistrements MX d’un domaine, puis retourner le nom du fournisseur correspondant.

/**
 * Fonction personnalisée pour identifier le fournisseur de messagerie à partir des enregistrements MX.
 * Utilisation dans Google Sheets :
 * - Une seule cellule : =DETECTER_FOURNISSEUR_EMAIL(A1)
 * - Une plage de cellules : =DETECTER_FOURNISSEUR_EMAIL(A1:A10)
 *
 * @param {string|string[][]} courriel L'adresse ou les adresses e-mail à analyser
 * @customfunction
 */
function DETECTER_FOURNISSEUR_EMAIL(courriel) {
  // Gère la saisie sous forme de tableau (plage) via récursion
  if (Array.isArray(courriel)) {
    return courriel.map(ligne => {
      if (Array.isArray(ligne)) {
        return ligne.map(cel => DETECTER_FOURNISSEUR_EMAIL(cel));
      }
      return DETECTER_FOURNISSEUR_EMAIL(ligne);
    });
  }

  // Vérification de l'entrée
  if (!courriel || typeof courriel !== 'string') {
    return 'Saisie invalide';
  }

  const domaine = courriel.split('@')[1];
  if (!domaine) {
    return 'Adresse e-mail invalide';
  }

  // Appel de l'API DNS de Google pour récupérer les enregistrements MX
  const url = `https://dns.google/resolve?name=${domaine}&type=MX`;

  let reponse;
  try {
    reponse = UrlFetchApp.fetch(url);
  } catch (err) {
    return 'Échec de la requête';
  }

  let resultat;
  try {
    resultat = JSON.parse(reponse.getContentText());
  } catch (err) {
    return 'Échec de la requête';
  }

  if (!resultat.Answer) {
    return 'Aucun enregistrement MX';
  }

  // Concatene tous les enregistrements MX en une seule chaine
  const mxString = resultat.Answer
    .map(enregistrement => enregistrement.data.toLowerCase())
    .join(' ');

  // Tableau des fournisseurs
  const fournisseurs = [
    { nom: 'Gmail', motsCles: ['google', 'googlemail'], condition: domaine === 'gmail.com' },
    { nom: 'Google Workspace', motsCles: ['google', 'googlemail'] },
    { nom: 'Microsoft 365/Outlook', motsCles: ['outlook', 'hotmail', 'microsoft'] },
    { nom: 'Yahoo', motsCles: ['yahoodns'] },
    { nom: 'iCloud', motsCles: ['mail.icloud'] },
    { nom: 'AOL', motsCles: ['aol.com'] },
    { nom: 'Zoho', motsCles: ['zoho'] },
    { nom: 'ProtonMail', motsCles: ['proton'] },
    { nom: 'FastMail', motsCles: ['messagingengine'] },
    { nom: 'Rackspace', motsCles: ['emailsrvr'] },
    { nom: 'Yandex', motsCles: ['mx.yandex'] },
    { nom: 'GoDaddy', motsCles: ['secureserver.net'] },
    { nom: 'Hostinger', motsCles: ['hostinger'] },
    { nom: 'OVH', motsCles: ['ovh.net'] },
    { nom: 'IONOS', motsCles: ['ionos'] },
    { nom: 'Namecheap', motsCles: ['privateemail'] },
    { nom: 'Amazon WorkMail', motsCles: ['amazonaws.com'] },
    { nom: 'Mailgun', motsCles: ['mailgun'] },
    { nom: 'SendGrid', motsCles: ['sendgrid'] },
    { nom: 'Hetzner', motsCles: ['your-server.de'] },
    { nom: 'DreamHost', motsCles: ['dreamhost'] },
    { nom: 'Mail.ru', motsCles: ['mail.ru'] },
    { nom: 'GMX', motsCles: ['gmx.net'] },
    { nom: 'Postmark', motsCles: ['postmarkapp'] },
    { nom: 'Mailjet', motsCles: ['mailjet'] },
    { nom: 'Hey', motsCles: ['hey.com'] },
    { nom: 'Tencent', motsCles: ['qq.com'] },
    { nom: 'Bluehost', motsCles: ['bluehost'] },
    { nom: 'HostGator', motsCles: ['hostgator'] },
    { nom: 'Gandi', motsCles: ['mail.gandi.net'] },
    { nom: 'Infomaniak', motsCles: ['infomaniak'] },
    { nom: 'Orange', motsCles: ['orange.fr', 'orange.net'] },
    { nom: 'La Poste', motsCles: ['laposte.net'] }
  ];

  // Fonction pour déterminer le fournisseur
  const detecterFournisseur = (chaine, domaine) => {
    for (const fournisseur of fournisseurs) {
      // Si une condition est définie sur le fournisseur
      if (typeof fournisseur.condition !== 'undefined') {
        // Ne renvoyer le fournisseur que si la condition est vraie
        if (fournisseur.condition && fournisseur.motsCles.some(motCle => chaine.includes(motCle))) {
          return fournisseur.nom;
        }
      } else {
        // Pas de condition, on se base uniquement sur les mots-clés
        if (fournisseur.motsCles.some(motCle => chaine.includes(motCle))) {
          return fournisseur.nom;
        }
      }
    }
    return 'Autre';
  };

  try {
    return detecterFournisseur(mxString, domaine);
  } catch (error) {
    return 'Échec de la requête';
  }
}

Cliquez sur la disquette pour enregistrer le script, puis retournez sur votre feuille de calcul.

Étape 3 : Utiliser la fonction dans votre feuille

Pour utiliser la fonction, il vous suffit d’entrer une formule dans une cellule, par exemple :

=DETECTER_FOURNISSEUR_EMAIL(A1)

Ici, A1 est la cellule contenant l’adresse e-mail à analyser. Si vous souhaitez l’appliquer à une plage, par exemple de A1 à A10 :

=DETECTER_FOURNISSEUR_EMAIL(A1:A10)

La fonction retournera le fournisseur correspondant (par exemple “Google Workspace”, “OVH”, “Yahoo”, etc.) ou “Autre” si elle ne trouve pas de correspondance.

Grâce à cette fonction personnalisée, vous pouvez désormais analyser rapidement les adresses e-mail dans Google Sheets et identifier leur fournisseur. Cet outil s’intègre parfaitement à des processus plus larges d’analyse marketing, de segmentation de contacts ou de maintenance de bases de données.

En quelques étapes simples, vous avez mis en place une solution pratique qui vous évite des recherches manuelles fastidieuses, tout en vous permettant de mieux comprendre et exploiter vos données e-mail. N’hésitez pas à personnaliser la liste des fournisseurs ou à adapter la fonction à vos besoins spécifiques.