Google Sheets-contacten met Apps Script in Brevo importeren

Een sheet met twaalf aanmeldingen voor een evenement exporteer je gemakkelijk één keer. Bij de tweede import staat een adres er dubbel in en heeft een ander contact zich in Brevo afgemeld. Een herhaalbare import moet beide gevallen herkennen.

Dit script gebruikt Brevo’s eindpunten om contacten te lezen, aan te maken en bij te werken. Het is bedoeld voor kleine geplande imports, niet voor grote uploads of als ingebouwde Google Sheets-connector. Het verstuurt geen e-mail. Jij beheert het toestemmingsbewijs en het script.

Bereid het sheet en de lijst voor

Maak een tabblad Contacts met precies deze kolomnamen. Gebruik selectievakjes voor consent en zet alleen TRUE als je toestemming hebt voor het beoogde marketinggebruik.

Maak of kies een Brevo-lijst en vul de numerieke ID in bij LIST_ID in de code. Controleer of de contactattributen FNAME en LNAME in Brevo bestaan. Alleen deze velden worden gekoppeld. Bewaar apart wanneer en hoe toestemming is verkregen als het sheet dat niet kan aantonen. Een TRUE-cel is geen bewijs.

Voeg in Extensies → Apps Script → Projectinstellingen → Scripteigenschappen BREVO_API_KEY toe. Scripteigenschappen worden gedeeld met gebruikers van het script. Beperk daarom wie het project mag bewerken. Zet de sleutel nooit in een cel of bronbestand. Lees de toegangsregels van Google.

Het script

Vervang Code.gs door dit voorbeeld. Vul LIST_ID in voordat je het voor het eerst uitvoert.

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);
  }
}

Sla op, kies syncSheetToBrevo en klik op Uitvoeren. Google vraagt toegang tot Sheets en externe verzoeken. Onder Uitvoeringen vind je het eindlog {written, skipped}. Een leesantwoord 404 leidt tot aanmaken; 200 leidt tot bijwerken. Andere leesfouten stoppen de run. Een geslaagde schrijfbewerking bevestigt de contactaanvraag, niet het versturen van een campagne.

Het script verwijdert dubbele e-mailadressen binnen één run en bevraagt Brevo opnieuw in de volgende run. Het zet emailBlacklisted nooit op false en verandert geen e-mailadres. Als Brevo een algemene blokkade of afmelding voor deze lijst meldt, wordt de rij overgeslagen, ook wanneer het sheet TRUE toont. Gebruik dit niet om een afmelding terug te draaien. Brevo waarschuwt dat het wijzigen van een geblokkeerd e-mailadres de blokkade kan opheffen.

Controleer één run voordat je plant

Gebruik een testlijst en contacten die je zelf beheert. Voer het script uit met een TRUE-rij, een FALSE-rij en een dubbel adres. Verwacht één schrijfbewerking en twee overgeslagen rijen. Meld het testcontact in Brevo af en voer opnieuw uit: voor dat contact mag niets geschreven worden. Zet voor een fouttest LIST_ID tijdelijk op een ongeldige waarde, controleer de HTTP-fout in Uitvoeringen, herstel de ID en probeer opnieuw. Test niet met echte klantgegevens. De code is ook lokaal getest met gesimuleerde Brevo-antwoorden, zonder echte schrijfbewerking.

Open voor een schema Apps Script → Triggers → Trigger toevoegen, kies syncSheetToBrevo en een tijdsinterval. Installeerbare triggers draaien onder het account dat ze aanmaakt. Dat account moet toegang tot het sheet houden. Begin dagelijks voor een klein sheet en controleer mislukkingen. Google beperkt uitvoertijd en URL Fetch-aanroepen. Verdeel grotere bronnen in begrensde taken.

Bij 429 leest het script x-sib-ratelimit-reset, probeert het maximaal twee keer opnieuw en geeft het 429 terug voor een latere run als de nodige wachttijd boven 60 seconden ligt. Andere fouten stoppen de run; de volgende run begint weer bovenaan. Een contact kan zich tussen lezen en schrijven afmelden. Gebruik dit voorbeeld alleen als dat korte tijdvenster acceptabel is. Voor strengere garanties zijn afstemming via gebeurtenissen, een gezaghebbende toestemmingsbron en een laatste controle vóór verzending nodig.

Voor grote sheets geeft Brevo’s asynchrone import-eindpunt een processId terug. Dat bevestigt dat de taak is aangenomen, niet dat hij klaar is. Controleer de uitkomst voor je een grote import geslaagd noemt. Bij de route per contact is een fout zichtbaar op de betreffende rij.