シートの連絡先をBrevoにインポートするGoogle Apps 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;
}