Envoyer un e-mail automatique depuis Google Sheets avec Apps Script

Relancer des clients, confirmer des inscriptions, prévenir un collègue dès qu'une ligne change de statut : tant que votre liste tient dans une feuille Google Sheets, un petit script Apps Script peut envoyer les e-mails à votre place. Voici un script complet, à copier-coller, qui n'envoie jamais deux fois le même message.

Ce que fait le script

Il parcourt l'onglet Contacts et envoie un e-mail à chaque ligne qui contient une adresse et dont la colonne « Statut » est vide, puis écrit « Envoyé » et la date dans la feuille. Aux passages suivants, ces lignes sont ignorées : la colonne Statut sert de mémoire. Un déclencheur peut lancer le script chaque jour, sans y penser.

Prérequis

• Un compte Google (Gmail ou Google Workspace) : les messages partiront depuis cette adresse.
• Une feuille dont l'onglet s'appelle exactement Contacts, avec cinq colonnes et des intitulés en ligne 1 : A « Nom », B « E-mail », C « Message », D « Statut », E « Date d'envoi ».
• Un script créé depuis la feuille elle-même, par le menu Extensions > Apps Script.

Le code

Étape 1 : les réglages

Tout ce qui dépend de votre feuille est regroupé en haut du script : le nom de l'onglet, l'objet du message et la position des colonnes. Si votre tableau est organisé autrement, c'est ici qu'il faut intervenir.

// ---- Réglages à adapter ----
const NOM_ONGLET = 'Contacts';           // nom exact de l'onglet à traiter
const SUJET = 'Suite à votre demande';   // objet du message

// Numéros de colonnes (A = 1, B = 2, C = 3...)
const COL_NOM = 1;
const COL_EMAIL = 2;
const COL_MESSAGE = 3;
const COL_STATUT = 4;
const COL_DATE_ENVOI = 5;

Étape 2 : l'envoi

Le script lit toutes les lignes d'un coup (bien plus rapide que cellule par cellule) puis les traite une à une. Le statut est écrit immédiatement après chaque envoi : si le script est interrompu en route, les lignes déjà envoyées ne repartiront pas. Une erreur sur une ligne est notée dans la feuille au lieu d'arrêter le traitement. Enfin, MailApp.getRemainingDailyQuota() indique ce qu'il reste de quota d'envoi, et le script s'arrête proprement quand il est épuisé.

// Vérification volontairement simple : une arobase, un point, pas d'espace.
function adresseValide(adresse) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(adresse);
}

function envoyerEmailsEnAttente() {
  const feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(NOM_ONGLET);
  if (!feuille) {
    throw new Error('Onglet introuvable : ' + NOM_ONGLET);
  }

  const derniereLigne = feuille.getLastRow();
  if (derniereLigne < 2) {
    return 0; // seulement l'en-tête : rien à faire
  }

  // Lecture de toutes les lignes en une seule fois (en-tête exclu)
  const lignes = feuille.getRange(2, 1, derniereLigne - 1, COL_DATE_ENVOI).getValues();
  let envoyes = 0;

  for (let i = 0; i < lignes.length; i++) {
    const nom = String(lignes[i][COL_NOM - 1]).trim();
    const adresse = String(lignes[i][COL_EMAIL - 1]).trim();
    const message = String(lignes[i][COL_MESSAGE - 1]).trim();
    const statut = String(lignes[i][COL_STATUT - 1]).trim();
    const numeroLigne = i + 2; // +1 pour l'en-tête, +1 car la numérotation commence à 1

    // On saute les lignes vides et celles déjà traitées
    if (adresse === '' || statut !== '') {
      continue;
    }
    if (!adresseValide(adresse)) {
      feuille.getRange(numeroLigne, COL_STATUT).setValue('Adresse invalide');
      continue;
    }
    // Quota du jour épuisé : on s'arrête, la suite partira au prochain passage
    if (MailApp.getRemainingDailyQuota() < 1) {
      break;
    }

    try {
      MailApp.sendEmail({
        to: adresse,
        subject: SUJET,
        body: 'Bonjour' + (nom ? ' ' + nom : '') + ',\n\n' + message
      });
      // On note l'envoi tout de suite : si le script s'interrompt plus loin,
      // cette ligne ne sera pas renvoyée au prochain passage.
      feuille.getRange(numeroLigne, COL_STATUT).setValue('Envoyé');
      feuille.getRange(numeroLigne, COL_DATE_ENVOI).setValue(new Date());
      envoyes++;
    } catch (erreur) {
      feuille.getRange(numeroLigne, COL_STATUT).setValue('Erreur : ' + erreur.message);
    }
  }
  return envoyes;
}

Le script utilise MailApp plutôt que GmailApp : il ne sait qu'envoyer des messages, alors que GmailApp demande l'accès à toute la boîte Gmail. Mieux vaut n'accorder que l'autorisation nécessaire.

Étape 3 : le déclencheur

Un déclencheur est une programmation : Google exécute la fonction à l'heure voulue, même si la feuille est fermée. La fonction ci-dessous est à lancer une seule fois, à la main ; elle supprime d'abord d'éventuels anciens déclencheurs identiques pour éviter les doublons.

// À lancer UNE seule fois, à la main : programme l'envoi chaque jour vers 8 h.
function creerDeclencheurQuotidien() {
  // On supprime d'abord d'éventuels déclencheurs identiques pour éviter les doublons
  ScriptApp.getProjectTriggers().forEach(function (declencheur) {
    if (declencheur.getHandlerFunction() === 'envoyerEmailsEnAttente') {
      ScriptApp.deleteTrigger(declencheur);
    }
  });

  ScriptApp.newTrigger('envoyerEmailsEnAttente')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

Installer le script pas à pas

1. Dans votre feuille, ouvrez Extensions > Apps Script.
2. Videz le fichier Code.gs, collez les trois blocs de code à la suite les uns des autres, puis enregistrez.
3. Remplissez deux ou trois lignes de test avec votre propre adresse comme destinataire.
4. Choisissez la fonction envoyerEmailsEnAttente dans la barre d'outils et cliquez sur Exécuter. Au premier lancement, Google demande d'autoriser le script. Comme il s'agit d'un script personnel non validé par Google, un avertissement s'affiche : cliquez sur « Paramètres avancés », puis sur l'accès au projet.
5. Vérifiez que les messages sont arrivés et que les colonnes Statut et Date d'envoi se sont remplies.
6. Quand tout est correct, exécutez une fois creerDeclencheurQuotidien. Le script se lancera désormais chaque jour, aux alentours de 8 h.

Quotas : à connaître avant d'envoyer en nombre

Google limite le nombre d'e-mails qu'un script peut envoyer par jour, et cette limite dépend du type de compte (compte Google personnel ou Google Workspace). Ne vous fiez pas à un chiffre lu sur un forum : il évolue. La valeur à jour figure dans la documentation de Google (page « Quotas for Google Services »), et le script interroge lui-même ce qu'il reste. Les lignes non traitées faute de quota partent au passage suivant.

Erreurs fréquentes

• « Onglet introuvable » : le nom saisi dans NOM_ONGLET doit être identique à celui de l'onglet, espaces et majuscules compris.
• Rien ne part, sans message d'erreur : la colonne Statut contient déjà quelque chose sur ces lignes, même un simple espace ou le résultat d'une formule. Le script les considère comme traitées.
• « Erreur : … » dans la colonne Statut : l'envoi a échoué pour cette ligne et le script ne la retentera pas seul. Corrigez la cause, puis effacez la cellule Statut pour la relancer.
• Le déclencheur ne s'exécute pas : il tourne avec les droits du compte qui l'a créé. Si ce compte perd l'accès à la feuille ou si l'autorisation est révoquée, les envois s'arrêtent.
• Les messages arrivent en spam : la délivrabilité dépend du contenu du message et de la configuration de votre domaine. Testez avec de vrais destinataires avant un envoi large.

Enfin, si ce script sert à de la prospection commerciale, les règles sur le consentement et le désabonnement (RGPD) s'appliquent comme pour tout autre envoi.

Quand faire appel à un développeur ?

Un script de ce type convient à un usage interne, à un volume modeste et à une feuille dont la structure ne change pas. Il montre ses limites quand plusieurs personnes modifient la feuille en même temps, quand il faut un message mis en forme avec pièces jointes, un lien de désinscription ou un suivi des réponses, ou quand les volumes approchent des quotas.

La maintenance est l'autre point à regarder : un script rattaché au compte d'une seule personne s'arrête le jour où celle-ci quitte l'entreprise, et plus personne ne sait où il se trouve. Si la feuille contient des données sensibles (coordonnées de clients, informations de santé…), il faut aussi se demander si elles doivent vivre dans un tableur partagé.

Si vous préférez déléguer, ou faire cadrer votre besoin, nous développons des scripts Google Sheets et Apps Script sur mesure, dans le cadre plus large de l'automatisation sur mesure de vos tâches répétitives.