Import Google Sheets Contacts to Brevo With Apps Script

A Sheet with 12 event signups is easy to export once. The trouble starts on the second import: one address appears twice, and another person has unsubscribed in Brevo since the first run. Sending every row again without checking those two facts is a bad sync.

The script below uses Brevo’s contact read, create, and update endpoints. It is for a small, scheduled import, not a native Google Sheets connector or a bulk loader. It does not send email. You own the consent evidence and the script’s operation.

Prepare the Sheet and list

Create a Contacts tab with these exact headers. Use checkboxes for consent and enter TRUE only when you have permission for the intended marketing use.

Create or choose a Brevo list and copy its numeric ID into LIST_ID below. Check that the FNAME and LNAME contact attributes exist in Brevo. These are the only two mapped attributes. Keep your source consent record outside this Sheet if the Sheet cannot prove when and how consent was given. A TRUE cell alone is not proof.

In Extensions → Apps Script, open Project Settings → Script Properties and add BREVO_API_KEY. Script properties are shared with users of the script; restrict who can edit this project. Do not place the key in a cell or in source code. See Google’s property-store access rules.

The script

Replace Code.gs with this example. Set LIST_ID before the first run.

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

Save, choose syncSheetToBrevo, and click Run. Google asks for Sheets and external-request permissions. Inspect Executions for the final {written, skipped} log. A contact read returning 404 leads to a create; a read returning 200 leads to an update. Other read failures stop the run. A successful write means the contact request completed, not that a campaign was sent.

The script compares email addresses within one run, and Brevo is queried again on the next run. It never sets emailBlacklisted to false or changes the email attribute. If Brevo reports a global email block or an unsubscribe from this list, the row is skipped even when the Sheet still says TRUE. Do not use this script to reverse an unsubscribe. Brevo warns that changing a blocked contact’s email address can remove its block, so the script keeps that field fixed.

Check one run before scheduling

Use a test list and test contacts you control. Run once with one TRUE row, one FALSE row, and a duplicate email; expect one write and two skips. Mark the contact unsubscribed in Brevo, then run again; expect zero writes for that contact. For a failure check, temporarily set LIST_ID to an invalid value, run, confirm the execution shows the HTTP error, restore the ID, and rerun. Do not test against real customer records. These are manual checks in your own account; the code in this article was also exercised locally with mocked Brevo responses, not a live write.

To schedule it, open Apps Script → Triggers → Add Trigger, choose syncSheetToBrevo, then choose a time-driven interval. Installable triggers run under the account that created them, so that account needs ongoing Sheet access. Start daily for a small Sheet and inspect failed executions. Google limits execution time and URL Fetch usage; split larger sources into bounded jobs instead of assuming a long run will finish.

A 429 uses Brevo’s x-sib-ratelimit-reset header, with at most two retries. If the required wait exceeds 60 seconds, it returns 429 for a later run instead of retrying early. Other failures stop the run, so the next scheduled or manual run repeats from the top. Since a contact may have unsubscribed between the read and write, this example is suitable only where that narrow race is acceptable. For stricter suppression guarantees, use an event-driven reconciliation process with an authoritative consent store and a final pre-send check.

If the Sheet is large enough to need bulk imports, Brevo’s asynchronous import endpoint returns a processId. That confirms job acceptance, not completion. Record the process outcome before calling a bulk load successful. This article’s per-contact path makes small-run failures visible at the row that caused them.