วิธีสร้าง Google Apps Script เพื่อนำเข้ารายชื่อจาก Sheet ไปยัง Brevo

ถ้าทีมของคุณใช้ Google Sheet อยู่แล้ว (lead จากฝ่ายขาย, การลงทะเบียนงาน, รายการ contact ของพาร์ตเนอร์) การนำข้อมูลเหล่านี้เข้า Brevo ไม่จำเป็นต้องผ่านการ export CSV แล้ว re-import ด้วยมือ Google Apps Script ให้คุณเชื่อม Sheet ตรงเข้ากับ Brevo API สคริปต์รันอยู่ใน infrastructure ของ Google จึงไม่มีอะไรต้อง host, deploy หรือดูแล

คู่มือนี้จะพาทำสคริปต์ที่ใช้งานได้จริง: เมนู Sync to Brevo ใน Sheet ของคุณ, trigger รายชั่วโมงอัตโนมัติ, การเก็บ API key อย่างปลอดภัย, การจัดการ batch และ logging แบบมีโครงสร้างเล็กน้อยให้รู้ว่าเกิดอะไรขึ้น

สิ่งที่ต้องเตรียม

• Google Sheet ที่มีรายชื่อ (หนึ่งแถวต่อหนึ่งราย แถว header ก่อน)
• บัญชี Brevo และ API key (Settings → SMTP & API → API Keys)
• ID เชิงตัวเลขของ list ใน Brevo ที่ต้องการเพิ่มรายชื่อเข้าไป

แค่นี้ ไม่ต้องใช้ npm, Python หรือ server

โครง Sheet

สคริปต์ในคู่มือนี้คาดว่าจะมีแถว header ตามด้วยรายชื่อหนึ่งแถวต่อหนึ่งราย คอลัมน์จะถูกแมปด้วยชื่อ header ไปที่ attribute ของ Brevo:

email เป็นฟิลด์บังคับและจับคู่แบบไม่สนใจตัวพิมพ์ คอลัมน์อื่นทุกคอลัมน์จะถูกส่งไปที่ Brevo เป็น contact attribute Custom attribute (นอกเหนือจากชุดมาตรฐานเช่น FIRSTNAME, LASTNAME) ต้องมีในบัญชี Brevo ก่อน นิยามได้ที่ Contacts → Settings → Contact attributes หรือผ่าน Brevo API

เปิด Apps Script editor

ใน 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 วาง 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 เพื่อดู status code ต่อ batch จาก Brevo จุดล้มเหลวที่พบบ่อยที่สุดคือ 400 เพราะ custom attribute หาย ดูหัวข้อ troubleshooting ด้านล่าง

รันตามตารางเวลา

ใน Apps Script editor: Triggers (ไอคอนนาฬิกาในแถบซ้าย) → Add Trigger

• Choose function: syncSheetToBrevo
• Event source: Time-driven
• Type: Hour timer (หรือ Day timer สำหรับซิงค์วันละครั้ง)
• Interval: ทุกหนึ่งชั่วโมง (หรือตามต้องการ)

บันทึก Google จะรันฟังก์ชันตามจังหวะนี้ตลอดไป โดยไม่ต้องมี server, ไม่มี cron, ไม่มีการบำรุงรักษา

จะใช้ From spreadsheet → On edit เพื่อให้ทุกการเปลี่ยนเซลล์ทริกเกอร์ซิงค์ก็ได้ แต่ต้องระวัง การแก้ไขเชิงเครื่องสำอางก็จะจุดทริกเกอร์ ซึ่งกินโควต้ารายวันของ Apps Script ได้เร็วบน sheet ที่ใช้งานหนัก trigger รายชั่วโมงเป็นคำตอบที่ใช่เกือบทุกครั้ง

โควต้า Apps Script ที่ควรรู้

แพลนฟรีของ Apps Script มีลิมิตที่ควรเคารพ:

สำหรับซิงค์รายชื่อทั่วไป (ไม่กี่พันรายชื่อ รายชั่วโมง) คุณห่างไกลจากลิมิตทุกตัว ที่ต้องเฝ้าระวังคือ 6 นาทีต่อการรันหนึ่งครั้ง ถ้าซิงค์หลายแสนรายชื่อในรอบเดียว ให้แบ่งเป็นชิ้นเล็กลง (สคริปต์ข้างต้นทำให้แล้วผ่าน BATCH_SIZE)

จัดการ import แบบ asynchronous

Endpoint นำเข้าของ Brevo เป็น asynchronous: คุณได้ processId กลับมาทันที ส่วน import จริงทำงานฝั่ง server สำหรับซิงค์ Sheet ส่วนใหญ่ก็เพียงพอ ส่งแล้วปล่อย Brevo จะส่งสรุปทางอีเมลเมื่อ batch แต่ละก้อนเสร็จ

ถ้าต้องการ block จนกว่า import จะเสร็จจริง ให้ poll endpoint สถานะ process:

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 คือเวอร์ชัน blocking wait ของ Apps Script อย่า sleep นานเกินไป มีเวลารวม 6 นาทีต่อการรอบ

เพิ่ม notify webhook

แพตเทิร์นที่สะอาดกว่า poll: deploy Apps Script เป็น Web App แล้วส่ง URL เป็น notifyUrl Brevo จะ POST กลับมาเมื่อ import เสร็จ

// 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 ตั้งค่า “Who has access” เป็น Anyone ก็อป URL ที่ได้ แล้วส่งเป็น notifyUrl ใน payload ของ import:

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

ตอนนี้ Brevo โพสต์ผลลัพธ์กลับไปที่สคริปต์ของ Sheet เอง ปิดวงจรโดยไม่มี infrastructure ภายนอก

Troubleshooting

400 Bad Request พร้อม error: "Attribute X not found" คอลัมน์ใน Sheet ของคุณแมปไปที่ attribute ที่ Brevo ไม่รู้จัก ให้เปลี่ยนชื่อคอลัมน์ใน Sheet ให้ตรงกับ attribute ที่มีอยู่ หรือสร้าง attribute ใน Brevo (Contacts → Settings → Contact attributes)

401 Unauthorized API key ผิดหรือหมดอายุ รัน Configure API key ใหม่ วาง key สดจาก dashboard ของ Brevo

429 Too Many Requests คุณชน rate limit ของ Brevo Endpoint นำเข้ารองรับราว 30 ครั้งต่อนาที ถ้าตี batch รัวๆ ให้เพิ่ม Utilities.sleep(2000) ระหว่าง batch ใน loop

สคริปต์ไม่รันตามตารางเวลาแบบเงียบๆ ตรวจ Triggers ใน Apps Script editor ถ้า trigger ล้มเหลวซ้ำๆ Google จะปิดให้เอง คลิกเข้า trigger เพื่อดูเหตุผลที่ล้มเหลว มักเป็นปัญหาสิทธิ์ที่ต้อง re-authorize

เมนู Brevo ไม่ปรากฏ onOpen ทำงานเมื่อคุณ (re)open Sheet ใหม่เท่านั้น โหลดแท็บเบราว์เซอร์ใหม่

popup ขอสิทธิ์โผล่ซ้ำ อาจแก้ scope ของสคริปต์ (เพิ่ม Google service ใหม่) Apps Script จะขอ authorization ใหม่เมื่อสิทธิ์ที่ต้องการเปลี่ยน รันฟังก์ชันใดก็ได้จาก editor หนึ่งครั้งเพื่อกระตุ้น prompt แล้วอนุมัติ

ทำไมวิธีนี้ดีกว่า Zapier และพวกพ้อง

Apps Script ฟรี อยู่ใน infrastructure ของ Google และเข้าถึงข้อมูลของ Sheet ได้ตรง ไม่มี event ยิงทีละแถว ไม่มีการคิดเงินรายงาน ไม่มี rate limit นอกจากโควต้า Google (ซึ่งใจกว้างสำหรับงานแบบนี้) ข้อแลกคือคุณต้องเขียนและบำรุงรักษาโค้ดเล็กๆ ก้อนหนึ่ง สำหรับซิงค์รายชื่อ ราว 100 บรรทัด และแทบไม่มีงานบำรุงรักษาต่อเนื่อง

จับคู่กับ trigger รายวันและ Sheet ที่ฝ่ายขายอัปเดตอยู่แล้ว ก็ได้ pipeline รายชื่อเข้า Brevo โดยไม่มีงานซ้ำๆ ที่ต้องทำ

อ่านเพิ่มเติม

• Brevo API: เอกสารอ้างอิง Import contacts
• Apps Script: UrlFetchApp
• Apps Script: PropertiesService
• Apps Script: Time-driven triggers