كيفية بناء Google Apps Script لاستيراد جهات اتصال Sheet إلى Brevo

إذا كان فريقك يعيش بالفعل في Google Sheet (سالس ليدز، تسجيلات أحداث، قائمة جهات اتصال شركاء)، فإن إيصال تلك البيانات إلى Brevo لا يحتاج إلى تصدير CSV وإعادة استيرادها يدوياً. يتيح لك Google Apps Script ربط Sheet مباشرة بـ Brevo API. يعمل السكريبت داخل بنية Google التحتية، لذا لا يوجد شيء لاستضافته أو نشره أو الإشراف عليه.

يسير هذا الدليل خلال سكريبت عامل: عنصر قائمة مخصص Sync to Brevo في Sheet الخاص بك، مشغّل تلقائي كل ساعة، تخزين آمن لمفتاح API، معالجة الدفعات، وقليل من التسجيل المنظم حتى تتمكن من معرفة ما حدث.

ما تحتاجه

• Google Sheet مع جهات الاتصال (صف واحد لكل جهة اتصال، صف الرأس أولاً)
• حساب Brevo ومفتاح API (Settings → SMTP & API → API Keys)
• المعرّف الرقمي لقائمة Brevo التي تريد إضافة جهات الاتصال إليها

هذا كل شيء. بدون npm، بدون Python، بدون خادم.

تخطيط الورقة

يتوقع السكريبت في هذا الدليل صف رأس متبوعاً بجهة اتصال واحدة لكل صف. تُربط الأعمدة باسم الرأس إلى سمات Brevo:

email إلزامي ويتم مطابقته بصرف النظر عن حالة الأحرف. كل شيء آخر يُرسل إلى Brevo كسمة جهة اتصال. السمات المخصصة (أي شيء خارج المعيار مثل FIRSTNAME، LASTNAME) تحتاج إلى أن توجد في حسابك Brevo أولاً. عرّفها تحت Contacts → Settings → Contact attributes، أو عبر Brevo API.

افتح محرر Apps Script

في Sheet الخاص بك: Extensions → Apps Script. تفتح علامة تبويب جديدة بـ Code.gs فارغ. استبدل المحتوى بالسكريبت أدناه.

السكريبت الكامل

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

هذا هو الشيء كله. احفظه (⌘S / Ctrl+S)، سمّ المشروع شيئاً مثل “Brevo sync”، وعد إلى Sheet الخاص بك.

التشغيل الأول

أعد تحميل Sheet. تظهر قائمة Brevo الجديدة في الأعلى.

1. انقر على Brevo → Configure API key، الصق مفتاحك xkeysib-...، انقر OK.
2. انقر على Brevo → Sync sheet to Brevo. ستطلب Google الأذونات في المرة الأولى:
   • “View and manage your spreadsheets”، مطلوب لقراءة الصفوف
   • “Connect to an external service”، مطلوب لاستدعاء api.brevo.com
3. اعتمد. يعمل السكريبت. يخبرك toast أخضر في الأسفل اليمين بعدد جهات الاتصال التي تم إرسالها.

إذا فشل، انقر على Extensions → Apps Script → View → Logs لرؤية كود الحالة لكل دفعة من Brevo. أكثر فشل شائع هو 400 بسبب سمة مخصصة مفقودة، انظر قسم استكشاف الأخطاء وإصلاحها أدناه.

شغّله وفق جدول

في محرر Apps Script: Triggers (أيقونة الساعة في الشريط الجانبي الأيسر) → Add Trigger.

• Choose function: syncSheetToBrevo
• Event source: Time-driven
• Type: Hour timer (أو Day timer لمزامنة مرة واحدة في اليوم)
• Interval: كل ساعة (أو ما يناسب)

احفظ. ستشغّل Google الدالة على هذا الإيقاع للأبد، بدون خادم، بدون cron، بدون صيانة.

يمكنك أيضاً استخدام From spreadsheet → On edit إذا أردت أن يؤدي كل تغيير في خلية إلى مزامنة. كن حذراً مع ذلك. حتى التعديلات التجميلية ستطلق المشغّل، مما قد يصل إلى الحصة اليومية لـ Apps Script بسرعة على الأوراق المزدحمة. المشغّل الزمني كل ساعة هو دائماً تقريباً الإجابة الصحيحة.

حصص Apps Script التي يجب معرفتها

المستوى المجاني من Apps Script لديه حدود تستحق الاحترام:

لمزامنة جهات اتصال نموذجية (بضعة آلاف من جهات الاتصال، كل ساعة)، لست قريباً من أي من هذه. الوحيد الذي يجب مراقبته هو 6 دقائق لتنفيذ واحد. إذا قمت في أي وقت بمزامنة مئات الآلاف من جهات الاتصال دفعة واحدة، قسّمها إلى أجزاء أصغر (السكريبت أعلاه يفعل هذا بالفعل عبر BATCH_SIZE).

التعامل مع الاستيراد بشكل غير متزامن

نقطة نهاية استيراد Brevo غير متزامنة: تحصل على processId فوراً، والاستيراد الفعلي يعمل من جانب الخادم. لمعظم مزامنات الأوراق، هذا جيد، fire-and-forget، سيرسل Brevo بريداً إلكترونياً بالملخص عند انتهاء كل دفعة.

إذا أردت الحجب حتى يتم الاستيراد فعلاً، استطلع نقطة نهاية حالة العملية:

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 هو مكافئ Apps Script لانتظار حاجب. لا تنم لفترة طويلة. لديك إجمالي 6 دقائق لكل تنفيذ.

إضافة webhook للإشعار

نمط أنظف من الاستطلاع: انشر Apps Script الخاص بك كـ Web App ومرر URL الخاص به كـ notifyUrl. سيقوم Brevo بـ POST إليه عند انتهاء الاستيراد.

// 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 → New deployment → Web app، اضبط “Who has access” على Anyone، انسخ URL الناتج، ومرره كـ notifyUrl في payload الاستيراد:

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

الآن يقوم Brevo بإرسال النتيجة إلى سكريبت Sheet الخاص بك، مغلقاً الحلقة دون بنية تحتية خارجية.

استكشاف الأخطاء وإصلاحها

400 Bad Request مع error: "Attribute X not found". عمود في Sheet الخاص بك يربط بسمة لا يعرفها Brevo. إما أعد تسمية عمود Sheet ليطابق سمة موجودة، أو أنشئ السمة في Brevo (Contacts → Settings → Contact attributes).

401 Unauthorized. مفتاح API خاطئ أو منتهي الصلاحية. أعد تشغيل Configure API key، الصق مفتاحاً جديداً من dashboard Brevo.

429 Too Many Requests. أنت تصل إلى rate limit الخاص بـ Brevo. تسمح نقطة نهاية الاستيراد بحوالي 30 استدعاء في الدقيقة. إذا كنت تقسّم بشدة، أضف Utilities.sleep(2000) بين الدفعات في الحلقة.

السكريبت لا يعمل بصمت وفق الجدول. تحقق من Triggers في محرر Apps Script. إذا فشل المشغّل بشكل متكرر، تعطّله Google. انقر على المشغّل لرؤية سبب الفشل، عادة مشكلة إذن يمكنك إعادة التفويض لها.

قائمة Brevo لم تظهر. يعمل onOpen فقط عند (إعادة) فتح Sheet من الصفر. أعد تحميل علامة تبويب المتصفح.

نافذة الأذونات تستمر في العودة. ربما قمت بتعديل نطاقات السكريبت (أضفت خدمة Google جديدة). يطلب Apps Script إعادة الترخيص في أي وقت تتغير فيه الأذونات المطلوبة. شغّل أي دالة مرة من المحرر لإطلاق النافذة وقبولها.

لماذا يتفوق هذا على Zapier وأصدقائه

Apps Script مجاني، يعيش داخل بنية Google التحتية، وله وصول مباشر لبيانات Sheet. بدون إطلاق أحداث صف بصف، بدون pricing لكل مهمة، بدون rate limits غير حصة Google (التي سخية لهذا النوع من العمل). الجانب الآخر: أنت تلتزم بكتابة وصيانة قطعة صغيرة من الكود. لمزامنة جهات الاتصال، هذا حوالي 100 سطر وأساساً صفر صيانة جارية.

اقرن هذا بمشغّل يومي وورقة يحدّثها فريق المبيعات الخاص بك بالفعل، ولديك خط أنابيب لجهات الاتصال إلى Brevo بصفر عمل متكرر.

قراءات إضافية

• Brevo API: مرجع استيراد جهات الاتصال
• Apps Script: UrlFetchApp
• Apps Script: PropertiesService
• Apps Script: Time-driven triggers