qiscus-omniform-to-google-sheet.gs

function doPost(e) {
  // Daftar field yang ingin di-exclude (bisa ditaruh di atas sheet baris 1 kolom A misalnya "exclude: room_id, user id")
  let excludes = [];

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = JSON.parse(e.postData.contents);
  const fields = data.fields;
  const author = data.author || {};
  const roomInfo = data.room_info || {};

  const excludeNote = sheet.getRange(1, 1).getValue();
  if (typeof excludeNote === 'string' && excludeNote.toLowerCase().startsWith('exclude:')) {
    excludes = excludeNote.substring(8).split(',').map(f => normalizeHeader(f.trim()));
  }

  // Ambil header (dari baris 1 sekarang)
  let headers = [];
  if (sheet.getLastColumn() === 0 || sheet.getLastRow() === 0) {
    headers = [];
  } else {
    headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  }

  const headerMap = headers.map(h => normalizeHeader(h));
  let newRow = [];
  let fieldMap = {}; // key: normalized header, value: cell value
  let originalLabelMap = {}; // key: normalized header, value: original label (camel/spaced)

  // Tambahkan data author
  if (author.name && !excludes.includes(normalizeHeader("author_name"))) {
    fieldMap["author_name"] = author.name;
    originalLabelMap["author_name"] = "Author Name";
  }
  if (author.email && !excludes.includes(normalizeHeader("author_email"))) {
    fieldMap["author_email"] = author.email;
    originalLabelMap["author_email"] = "Author Email";
  }

  // Tambahkan data room_info
  for (const key in roomInfo) {
    const normKey = normalizeHeader(key);
    if (!excludes.includes(normKey)) {
      fieldMap[normKey] = roomInfo[key];
      originalLabelMap[normKey] = toTitleCase(key);
    }
  }

  // Loop data fields
  fields.forEach(field => {
    const label = field.label;
    const value = field.payload?.value || "";
    const normLabel = normalizeHeader(label);

    if (!excludes.includes(normLabel)) {
      fieldMap[normLabel] = value;
      originalLabelMap[normLabel] = label;
    }
  });

  // Pastikan header lengkap & sinkron
  for (const normKey in fieldMap) {
    let index = headerMap.indexOf(normKey);
    if (index === -1) {
      const originalLabel = originalLabelMap[normKey] || normKey;
      headers.push(originalLabel);
      headerMap.push(normKey);
      index = headers.length - 1;
      sheet.getRange(1, index + 1).setValue(originalLabel);
    }
  }

  // Susun data untuk baris baru
  for (let i = 0; i < headers.length; i++) {
    const normKey = headerMap[i];
    newRow[i] = fieldMap[normKey] || "";
  }

  sheet.appendRow(newRow);

  return ContentService.createTextOutput(JSON.stringify({ status: "success" }))
    .setMimeType(ContentService.MimeType.JSON);
}

function normalizeHeader(text) {
  return String(text || "")
    .toLowerCase()
    .replace(/[^a-z0-9]/g, ""); // Hapus spasi, underscore, simbol dll
}

function toTitleCase(text) {
  return String(text || "")
    .replace(/[_-]/g, ' ') // underscore dan dash jadi spasi
    .replace(/\w\S*/g, function(txt) {
      return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
    });
}