Importer des contacts Google Sheets dans Brevo avec Apps Script

Une feuille de douze inscriptions à un événement s’exporte facilement une fois. À la deuxième importation, une adresse est en double et une autre personne s’est désabonnée dans Brevo. Un import reproductible doit tenir compte de ces deux faits.

Le script ci-dessous utilise les endpoints Brevo de lecture, création et mise à jour des contacts. Il vise une petite importation planifiée, pas un chargement massif ni un connecteur Google Sheets natif. Il n’envoie aucun e-mail. Vous êtes responsable des preuves de consentement et de l’exploitation du script.

Préparer la feuille et la liste

Créez un onglet Contacts avec exactement ces en-têtes. Utilisez des cases à cocher pour consent et réservez TRUE aux personnes ayant autorisé l’usage marketing prévu.

Créez ou choisissez une liste Brevo et placez son identifiant numérique dans LIST_ID ci-dessous. Vérifiez que les attributs de contact FNAME et LNAME existent dans Brevo. Ce sont les seuls champs associés. Conservez ailleurs la preuve de la date et du mode de recueil du consentement si la feuille ne le permet pas. Une cellule TRUE ne constitue pas une preuve.

Dans Extensions → Apps Script → Paramètres du projet → Propriétés du script, ajoutez BREVO_API_KEY. Les propriétés du script sont partagées avec les utilisateurs du script ; limitez les droits de modification. Ne placez jamais la clé dans une cellule ni dans le code source. Consultez les règles d’accès de Google.

Le script

Remplacez Code.gs par cet exemple. Définissez LIST_ID avant la première exécution.

const LIST_ID = 42; // Replace with your existing Brevo list ID.
const TAB = 'Contacts';
const BASE = 'https://api.brevo.com/v3';

function syncSheetToBrevo() {
  const key = PropertiesService.getScriptProperties().getProperty('BREVO_API_KEY');
  if (!key) throw new Error('Set BREVO_API_KEY in Script Properties first');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(TAB);
  if (!sheet) throw new Error('Contacts tab missing');
  const values = sheet.getDataRange().getValues();
  const headers = values.shift().map(h => String(h).trim());
  const columns = ['email', 'firstName', 'lastName', 'consent']
    .map(name => headers.indexOf(name));
  if (columns.includes(-1)) throw new Error('Required header missing');
  const [emailCol, firstCol, lastCol, consentCol] = columns;
  const seen = new Set();
  const result = { written: 0, skipped: 0 };

  for (const row of values) {
    const email = String(row[emailCol] || '').trim().toLowerCase();
    if (row[consentCol] !== true || !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email) || seen.has(email)) {
      result.skipped++;
      continue;
    }
    seen.add(email);
    const path = '/contacts/' + encodeURIComponent(email);
    const current = brevo_(key, path, 'get');
    if (current.code !== 200 && current.code !== 404) {
      throw new Error(`Contact read failed (${current.code}) for row ${result.written + result.skipped + 2}`);
    }
    if (current.code === 200) {
      const contact = JSON.parse(current.text);
      if (contact.emailBlacklisted || (contact.listUnsubscribed || []).includes(LIST_ID)) {
        result.skipped++;
        continue;
      }
    }
    const attributes = {};
    if (row[firstCol] !== '') attributes.FNAME = String(row[firstCol]);
    if (row[lastCol] !== '') attributes.LNAME = String(row[lastCol]);
    const body = { attributes, listIds: [LIST_ID] };
    const saved = current.code === 404
      ? brevo_(key, '/contacts', 'post', { email, ...body })
      : brevo_(key, path, 'put', body);
    if (![201, 204].includes(saved.code)) {
      throw new Error(`Contact write failed (${saved.code}) for ${email}: ${saved.text.slice(0, 200)}`);
    }
    result.written++;
  }
  Logger.log(JSON.stringify(result));
  return result;
}

function brevo_(key, path, method, body) {
  for (let attempt = 0; attempt < 3; attempt++) {
    const response = UrlFetchApp.fetch(BASE + path, {
      method,
      contentType: 'application/json',
      headers: { 'api-key': key },
      payload: body ? JSON.stringify(body) : undefined,
      muteHttpExceptions: true,
    });
    const code = response.getResponseCode();
    if (code !== 429) return { code, text: response.getContentText() };
    if (attempt === 2) return { code, text: response.getContentText() };
    const reset = Object.entries(response.getHeaders()).find(
      ([name]) => name.toLowerCase() === 'x-sib-ratelimit-reset'
    );
    const raw = Number(reset && reset[1]);
    const seconds = Number.isFinite(raw) && raw > 0 ? raw : 2 ** attempt;
    const waitSeconds = seconds + 2 ** attempt;
    if (waitSeconds > 60) return { code, text: response.getContentText() };
    Utilities.sleep(waitSeconds * 1000);
  }
}

Enregistrez, sélectionnez syncSheetToBrevo, puis cliquez sur Exécuter. Google demande les autorisations d’accès à Sheets et aux requêtes externes. Dans Exécutions, consultez le journal final {written, skipped}. Une lecture qui renvoie 404 entraîne une création ; une réponse 200 entraîne une mise à jour. Les autres erreurs de lecture arrêtent l’exécution. Une écriture réussie confirme la requête de contact, pas l’envoi d’une campagne.

Le script élimine les adresses en double pendant une exécution et interroge à nouveau Brevo lors de la suivante. Il ne définit jamais emailBlacklisted à false et ne change pas l’adresse e-mail. Si Brevo signale un blocage global ou un désabonnement de cette liste, la ligne est ignorée même si la feuille indique TRUE. N’utilisez pas le script pour annuler un désabonnement. Brevo prévient que le changement d’adresse d’un contact bloqué peut lever ce blocage.

Vérifier une exécution avant la planification

Utilisez une liste de test et des contacts de test sous votre contrôle. Exécutez le script avec une ligne TRUE, une ligne FALSE et une adresse en double : attendez-vous à une écriture et deux lignes ignorées. Désabonnez le contact de test dans Brevo, puis relancez : aucune écriture ne doit le concerner. Pour vérifier un échec, définissez provisoirement un LIST_ID invalide, contrôlez l’erreur HTTP dans Exécutions, restaurez l’identifiant et relancez. Ne faites pas ces essais sur des données clientes réelles. Le code de cet article a aussi été vérifié localement avec des réponses Brevo simulées, sans écriture réelle.

Pour la planification, ouvrez Apps Script → Déclencheurs → Ajouter un déclencheur, choisissez syncSheetToBrevo, puis un intervalle temporel. Un déclencheur installable s’exécute sous le compte qui l’a créé ; ce compte doit conserver l’accès à la feuille. Commencez par un lancement quotidien et contrôlez les échecs. Google limite la durée des exécutions et les appels URL Fetch. Divisez les sources plus volumineuses en tâches bornées.

Pour un code 429, le script lit x-sib-ratelimit-reset, réessaie au plus deux fois et renvoie 429 pour une exécution ultérieure si l’attente nécessaire dépasse 60 secondes. Les autres erreurs arrêtent l’exécution. Le prochain lancement manuel ou planifié reprend depuis le début. Un contact peut se désabonner entre la lecture et l’écriture : cet exemple convient seulement si ce bref intervalle est acceptable. Pour une garantie plus stricte, prévoyez une réconciliation par événements, une source de consentement faisant autorité et une dernière vérification avant l’envoi.

Pour les feuilles plus grandes, l’endpoint d’import asynchrone de Brevo renvoie un processId. Celui-ci confirme l’acceptation d’une tâche, pas son achèvement. Vérifiez le résultat du processus avant d’annoncer un import massif réussi. La voie par contact présentée ici rend l’erreur visible sur la ligne concernée.