How to Build a Google Apps Script to Import a Sheet's Contacts Into Brevo

If your team already lives in a Google Sheet — sales leads, event signups, a partner contact list — getting that data into Brevo doesn’t need to involve exporting CSVs and re-importing them by hand. Google Apps Script lets you wire the Sheet directly to Brevo’s API. The script runs inside Google’s infrastructure, so there’s nothing to host, deploy, or babysit.

This guide walks through a working script: a custom Sync to Brevo menu item in your Sheet, an automatic hourly trigger, safe API key storage, batch handling, and a small bit of structured logging so you can tell what happened.

What you need

• A Google Sheet with contacts (one row per contact, header row first)
• A Brevo account and an API key (Settings → SMTP & API → API Keys)
• The numeric ID of the Brevo list you want contacts added to

That’s it. No npm, no Python, no server.

Sheet layout

The script in this guide expects a header row followed by one contact per row. Columns are mapped by header name to Brevo attributes:

email is mandatory and is matched case-insensitively. Everything else gets sent to Brevo as a contact attribute. Custom attributes (anything beyond the standard ones like FIRSTNAME, LASTNAME) need to exist in your Brevo account first — define them under Contacts → Settings → Contact attributes, or via the Brevo API.

Open the Apps Script editor

In your Sheet: Extensions → Apps Script. A new tab opens with a blank Code.gs. Replace the contents with the script below.

The full script

const BREVO_API_BASE = 'https://api.brevo.com/v3';
const BREVO_LIST_ID = 42;        // <- the Brevo list to import contacts into
const SHEET_NAME = 'Contacts';   // <- name of the sheet tab to read
const BATCH_SIZE = 1000;         // contacts per import call

/**
 * Adds a "Brevo" menu to the Sheet so users can run the sync from the UI.
 * Triggered automatically when the Sheet opens.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Brevo')
    .addItem('Sync sheet to Brevo', 'syncSheetToBrevo')
    .addItem('Configure API key', 'configureApiKey')
    .addToUi();
}

/**
 * Reads every contact row from the sheet, batches them, and sends each batch
 * to Brevo's import endpoint. Returns a summary string for logging.
 */
function syncSheetToBrevo() {
  const apiKey = getApiKey_();
  if (!apiKey) {
    SpreadsheetApp.getUi().alert(
      'No Brevo API key configured. Run "Configure API key" first.'
    );
    return;
  }

  const contacts = readContactsFromSheet_();
  if (contacts.length === 0) {
    SpreadsheetApp.getUi().alert('No contacts found in the sheet.');
    return;
  }

  const batches = chunk_(contacts, BATCH_SIZE);
  const results = [];

  for (let i = 0; i < batches.length; i++) {
    const result = importBatchToBrevo_(apiKey, batches[i]);
    results.push(result);
    Logger.log(
      `Batch ${i + 1}/${batches.length}: ${result.ok ? 'ok' : 'FAILED'} ` +
      `(processId=${result.processId || '-'}, status=${result.status})`
    );
  }

  const summary =
    `Sent ${contacts.length} contacts in ${batches.length} batch(es). ` +
    `Successful: ${results.filter(r => r.ok).length}/${results.length}.`;
  Logger.log(summary);
  SpreadsheetApp.getActiveSpreadsheet().toast(summary, 'Brevo sync', 5);
  return summary;
}

/**
 * Reads the active spreadsheet's "Contacts" tab into an array of
 * { email, attributes } objects shaped for Brevo's jsonBody.
 */
function readContactsFromSheet_() {
  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error(`Sheet tab "${SHEET_NAME}" not found`);
  }

  const range = sheet.getDataRange().getValues();
  if (range.length < 2) return [];

  const headers = range[0].map(String);
  const emailColumn = headers.findIndex(h => h.toLowerCase() === 'email');
  if (emailColumn === -1) {
    throw new Error('Sheet must have an "email" column');
  }

  const contacts = [];
  for (let i = 1; i < range.length; i++) {
    const row = range[i];
    const email = String(row[emailColumn] || '').trim().toLowerCase();
    if (!email || !email.includes('@')) continue;  // skip invalid

    const attributes = {};
    for (let c = 0; c < headers.length; c++) {
      if (c === emailColumn) continue;
      const value = row[c];
      if (value === '' || value === null) continue;
      // Brevo convention: ATTRIBUTES ARE UPPERCASE
      attributes[headers[c].toUpperCase()] = value;
    }
    contacts.push({ email, attributes });
  }
  return contacts;
}

/**
 * POSTs a batch of contacts to Brevo's import endpoint.
 * Returns { ok, status, processId, error }.
 */
function importBatchToBrevo_(apiKey, contacts) {
  const payload = {
    jsonBody: contacts,
    listIds: [BREVO_LIST_ID],
    updateExistingContacts: true,
    emptyContactsAttributes: false,
  };

  const response = UrlFetchApp.fetch(`${BREVO_API_BASE}/contacts/import`, {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'api-key': apiKey,
      'accept': 'application/json',
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,  // we'll inspect status ourselves
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status === 202) {
    const json = JSON.parse(body);
    return { ok: true, status, processId: json.processId };
  }
  return { ok: false, status, error: body };
}

/**
 * Stores the Brevo API key in script properties — encrypted at rest by Google
 * and not visible in the source code or to viewers of the sheet.
 */
function configureApiKey() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'Brevo API key',
    'Paste your Brevo API key (xkeysib-...). It will be stored in Script Properties.',
    ui.ButtonSet.OK_CANCEL
  );
  if (response.getSelectedButton() !== ui.Button.OK) return;
  const key = response.getResponseText().trim();
  if (!key.startsWith('xkeysib-')) {
    ui.alert('That doesn\'t look like a Brevo API key (should start with xkeysib-).');
    return;
  }
  PropertiesService.getScriptProperties().setProperty('BREVO_API_KEY', key);
  ui.alert('API key saved.');
}

function getApiKey_() {
  return PropertiesService.getScriptProperties().getProperty('BREVO_API_KEY');
}

function chunk_(arr, size) {
  const out = [];
  for (let i = 0; i < arr.length; i += size) out.push(arr.slice(i, i + size));
  return out;
}

That’s the whole thing. Save it (⌘S / Ctrl+S), name the project something like “Brevo sync”, and head back to your Sheet.

First run

Reload the Sheet — the new Brevo menu appears at the top.

1. Click Brevo → Configure API key, paste your xkeysib-... key, click OK.
2. Click Brevo → Sync sheet to Brevo. Google will ask for permissions the first time:
   • “View and manage your spreadsheets” — needed to read the rows
   • “Connect to an external service” — needed to call api.brevo.com
3. Approve. The script runs. A green toast in the bottom-right tells you how many contacts went out.

If it fails, click Extensions → Apps Script → View → Logs to see the per-batch status code from Brevo. The most common failure is a 400 because of a missing custom attribute — see the troubleshooting section below.

Run it on a schedule

In the Apps Script editor: Triggers (the clock icon in the left sidebar) → Add Trigger.

• Choose function: syncSheetToBrevo
• Event source: Time-driven
• Type: Hour timer (or Day timer for a once-a-day sync)
• Interval: every hour (or whatever fits)

Save. Google will run the function on that cadence forever, with no server, no cron, no maintenance.

You can also use From spreadsheet → On edit if you want every cell change to trigger a sync. Be careful with that — even cosmetic edits will fire the trigger, which can hit Apps Script’s daily quota fast on busy sheets. The hourly time trigger is almost always the right answer.

Apps Script quotas to know about

The free Apps Script tier has limits worth respecting:

For a typical contact sync (a few thousand contacts, hourly), you’re nowhere near any of these. The only one to watch is 6-minute single execution — if you ever sync hundreds of thousands of contacts in one go, batch them into smaller chunks (the script above already does this via BATCH_SIZE).

Handling the import asynchronously

Brevo’s import endpoint is asynchronous: you get a processId back immediately, and the actual import runs server-side. For most sheet syncs this is fine — fire and forget, Brevo will email a summary when each batch finishes.

If you want to block until the import is really done, poll the process status endpoint:

function waitForImport_(apiKey, processId, timeoutMs = 5 * 60 * 1000) {
  const deadline = Date.now() + timeoutMs;
  while (Date.now() < deadline) {
    const resp = UrlFetchApp.fetch(`${BREVO_API_BASE}/processes/${processId}`, {
      headers: { 'api-key': apiKey },
      muteHttpExceptions: true,
    });
    if (resp.getResponseCode() === 200) {
      const status = JSON.parse(resp.getContentText()).status;
      if (status === 'completed' || status === 'failed') return status;
    }
    Utilities.sleep(5000);  // 5s between checks
  }
  return 'timeout';
}

Utilities.sleep is the Apps Script equivalent of a blocking wait. Don’t sleep too long — you’ve got 6 minutes total per execution.

Adding a notify webhook

A cleaner pattern than polling: deploy your Apps Script as a Web App and pass its URL as notifyUrl. Brevo will POST to it when the import finishes.

// Add to Code.gs
function doPost(e) {
  const payload = JSON.parse(e.postData.contents);
  Logger.log(`Brevo import ${payload.processId} finished: ${payload.status}`);
  // optionally: write the result back to a "Sync log" tab in the sheet
  return ContentService.createTextOutput('ok');
}

Deploy: Deploy → New deployment → Web app, set “Who has access” to Anyone, copy the resulting URL, and pass it as notifyUrl in your import payload:

payload.notifyUrl = 'https://script.google.com/macros/s/AKfy.../exec';

Now Brevo posts the result back to your Sheet’s own script — closing the loop without external infrastructure.

Troubleshooting

400 Bad Request with error: "Attribute X not found" — A column in your Sheet maps to an attribute Brevo doesn’t know about. Either rename the Sheet column to match an existing attribute, or create the attribute in Brevo (Contacts → Settings → Contact attributes).

401 Unauthorized — API key is wrong or expired. Re-run Configure API key, paste a fresh key from Brevo’s dashboard.

429 Too Many Requests — You’re hitting Brevo’s rate limit. The import endpoint allows around 30 calls per minute. If you’re batching aggressively, add Utilities.sleep(2000) between batches in the loop.

Script silently doesn’t run on schedule — Check Triggers in the Apps Script editor. If a trigger is failing repeatedly, Google disables it. Click into the trigger to see the failure reason — usually a permissions issue you can re-authorize.

The Brevo menu didn’t appear — onOpen only runs when you (re)open the Sheet from scratch. Reload the browser tab.

Permissions popup keeps coming back — You probably edited the script’s scopes (added a new Google service). Apps Script re-prompts for authorization any time the required permissions change. Run any function once from the editor to trigger the prompt and approve.

Why this beats Zapier and friends

Apps Script is free, lives inside Google’s infra, and has direct access to the Sheet’s data — no row-by-row event firing, no per-task pricing, no rate limits other than Google’s quota (which is generous for this kind of job). The flip side: you’re committing to writing and maintaining a small piece of code. For a contact sync, that’s about 100 lines and basically zero ongoing maintenance.

Pair this with a daily trigger and a sheet your sales team is already updating, and you’ve got a contact pipeline into Brevo with zero recurring work.

Further reading

• Brevo API: Import contacts reference
• Apps Script: UrlFetchApp
• Apps Script: PropertiesService
• Apps Script: Time-driven triggers