【第463回】 データエクステンションを Google スプレッドシートへ連携する

// ===== Global settings (declare ONCE) =====
const MC_SUBDOMAIN = "mcxkknz3m0zj3jqsxn4hm3dp3q-4";
const MC_CLIENT_ID = "t1onsb1ax48kuv7sc2kvie6s";
const MC_CLIENT_SECRET = "1HwUFfftSNa6SfTIjyWaq3ow";

const PAGE_SIZE = 2500;
const MAX_RECORDS_100K = 100000;       // 100,000 records target
const WRITE_CHUNK_ROWS = 5000;         // write to sheet in chunks

// ===== 5 patterns (edit here) =====
const PATTERNS_100K = {
  P1: { deKey: "57EFBC00-1564-409F-8979-6A9F878169E2", sheetName: "OrderDetail_1K" },
  P2: { deKey: "800851CB-9CE8-44F4-856C-16587AACA5C0", sheetName: "OrderDetail_100K" },
  P3: { deKey: "YOUR_DE_KEY_3", sheetName: "Sheet_P3" },
  P4: { deKey: "YOUR_DE_KEY_4", sheetName: "Sheet_P4" },
  P5: { deKey: "YOUR_DE_KEY_5", sheetName: "Sheet_P5" }
};

// ===== Core functions =====
function getAccessToken_() {
  const url = `https://${MC_SUBDOMAIN}.auth.marketingcloudapis.com/v2/token`;
  const payload = {
    grant_type: "client_credentials",
    client_id: MC_CLIENT_ID,
    client_secret: MC_CLIENT_SECRET
  };

  const response = UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const body = response.getContentText();
  if (response.getResponseCode() >= 300) {
    throw new Error(`Token request failed: ${response.getResponseCode()} / ${body}`);
  }
  return JSON.parse(body).access_token;
}

function fetchRowsetPage_(token, deKey, page) {
  const url =
    `https://${MC_SUBDOMAIN}.rest.marketingcloudapis.com/data/v1/customobjectdata/key/` +
    `${encodeURIComponent(deKey)}/rowset?$page=${page}&$pagesize=${PAGE_SIZE}`;

  const response = UrlFetchApp.fetch(url, {
    method: "get",
    headers: { Authorization: `Bearer ${token}` },
    muteHttpExceptions: true
  });

  const body = response.getContentText();
  if (response.getResponseCode() >= 300) {
    throw new Error(`DE retrieval failed (deKey=${deKey}, page=${page}): ${response.getResponseCode()} / ${body}`);
  }

  const json = JSON.parse(body);
  return json.items || [];
}

function writeRows_(sheet, headers, rowsObj, alreadyWritten) {
  if (!rowsObj || rowsObj.length === 0) return 0;

  const values = rowsObj.map(obj => headers.map(h => (obj[h] ?? "")));
  const startRow = 2 + alreadyWritten; // row 1 is header
  sheet.getRange(startRow, 1, values.length, headers.length).setValues(values);
  return values.length;
}

// patternKey: "P1" | "P2" | ...
function exportPattern100K_(patternKey) {
  const lock = LockService.getScriptLock();
  // Wait up to 30 seconds for other executions to finish
  if (!lock.tryLock(30000)) {
    throw new Error("Another export is currently running. Try again later or stagger triggers.");
  }

  try {
    const pattern = PATTERNS_100K[patternKey];
    if (!pattern) throw new Error(`Unknown patternKey: ${patternKey}`);

    const token = getAccessToken_();

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(pattern.sheetName) || ss.insertSheet(pattern.sheetName);
    sheet.clearContents();

    const maxPages = Math.ceil(MAX_RECORDS_100K / PAGE_SIZE); // 100,000 => 40 pages

    let headers = null;
    let buffer = [];
    let writtenRows = 0;

    for (let page = 1; page <= maxPages; page++) {
      const items = fetchRowsetPage_(token, pattern.deKey, page);
      if (items.length === 0) break;

      for (const item of items) buffer.push(item.values || {});

      if (!headers && buffer.length > 0) {
        headers = Object.keys(buffer[0]);
        sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
      }

      if (buffer.length >= WRITE_CHUNK_ROWS) {
        writtenRows += writeRows_(sheet, headers, buffer, writtenRows);
        buffer = [];
      }

      if (writtenRows >= MAX_RECORDS_100K) {
        buffer = [];
        break;
      }
    }

    if (buffer.length > 0) {
      if (!headers) {
        headers = Object.keys(buffer[0]);
        sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
      }
      writtenRows += writeRows_(sheet, headers, buffer, writtenRows);
    }

    Logger.log(`Export completed: pattern=${patternKey}, rows=${writtenRows}, sheet=${pattern.sheetName}`);
  } finally {
    lock.releaseLock();
  }
}

// ===== Wrapper functions for each pattern (easy to set triggers) =====
function export_P1_100K() { exportPattern100K_("P1"); }
function export_P2_100K() { exportPattern100K_("P2"); }
function export_P3_100K() { exportPattern100K_("P3"); }
function export_P4_100K() { exportPattern100K_("P4"); }
function export_P5_100K() { exportPattern100K_("P5"); }

// ===== Global settings (declare ONCE) =====
const MC_SUBDOMAIN = "mcxkknz3m0zj3jqsxn4hm3dp3q-4";
const MC_CLIENT_ID = "t1onsb1ax48kuv7sc2kvie6s";
const MC_CLIENT_SECRET = "1HwUFfftSNa6SfTIjyWaq3ow";

// ===== 5 patterns (edit here) =====
const PATTERNS_100K = {
  P1: { deKey: "57EFBC00-1564-409F-8979-6A9F878169E2", sheetName: "OrderDetail_1K" },
  P2: { deKey: "800851CB-9CE8-44F4-856C-16587AACA5C0", sheetName: "OrderDetail_100K" },
  P3: { deKey: "YOUR_DE_KEY_3", sheetName: "Sheet_P3" },
  P4: { deKey: "YOUR_DE_KEY_4", sheetName: "Sheet_P4" },
  P5: { deKey: "YOUR_DE_KEY_5", sheetName: "Sheet_P5" }
};