【完全版】プロラインのフォーム回答をスプレッドシートに自動反映！事務作業から解放される温かな自動化ガイド

下記のボタンを押してコードをコピーし、Google Apps Scriptの画面に貼り付けてください。
※貼り付け前に、既存のコードはすべて削除してくださいね！

function doGet() {
  return ContentService.createTextOutput("OK");
}

function doPost(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var debug = ss.getSheetByName('_debug') || ss.insertSheet('_debug');

  try {
    var p = (e && e.parameter) ? e.parameter : {};
    var raw = (e && e.postData && e.postData.contents) ? e.postData.contents : "";

    // 固定列（先頭3列）
    var sentAt = p.date
      ? p.date
      : (p.unixtime ? fmt_(new Date(Number(p.unixtime) * 1000)) : fmt_(new Date()));
    var uid = p.uid || "";
    var snsname = p["user_data[snsname]"] || "";

    // シート（form_nameベース）
    var sheet = getOrCreateSheet_(ss, sheetNameFromFormName_(p.form_name || "unknown"));

    // form_data（送信順）
    var parsed = parseOrderedFormData_(raw);
    var keysOrder = parsed.keysOrder;   // 送信順のキー配列
    var valuesMap = parsed.valuesMap;   // { key: [v1,v2...] }

    // ヘッダー準備（初回のみ作成。以後は上書きしない）
    ensureHeaderOnceAndExpand_(sheet, keysOrder);

    // データ行を「列の位置」で作る
    var baseLen = 3; // date, uid, snsname
    var lastCol = sheet.getLastColumn();
    var row = [];
    for (var i = 0; i < lastCol; i++) row.push("");

    row[0] = sentAt;
    row[1] = uid;
    row[2] = snsname;

    // form_data は「送信順に」3列目以降へ入れる
    for (var j = 0; j < keysOrder.length; j++) {
      var k = keysOrder[j];
      var arr = valuesMap[k] || [];
      var col = baseLen + j; // 0-based
      if (col >= row.length) break;
      row[col] = arr.join("\n");
    }

    sheet.appendRow(row);
    return ContentService.createTextOutput("OK");

  } catch (err) {
    debug.appendRow([new Date(), "ERROR", String(err), raw]);
    return ContentService.createTextOutput("NG");
  }
}

function ensureHeaderOnceAndExpand_(sheet, keysOrder) {
  var base = ["date", "uid", "snsname"];
  var baseLen = base.length;
  var lastCol = sheet.getLastColumn();
  var header = [];
  if (lastCol > 0) {
    header = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
  }
  var hasHeader = false;
  for (var i = 0; i < header.length; i++) {
    if (header[i] !== "") { hasHeader = true; break; }
  }
  if (!hasHeader) {
    var newHeader = base.concat(keysOrder);
    sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
    return;
  }
  var currentFormCols = Math.max(0, lastCol - baseLen);
  if (keysOrder.length <= currentFormCols) return;
  for (var j = currentFormCols; j < keysOrder.length; j++) {
    var keyName = keysOrder[j] || ("form_add_" + (j + 1));
    sheet.getRange(1, baseLen + j + 1).setValue(keyName);
  }
}

function parseOrderedFormData_(raw) {
  var keysOrder = [];
  var valuesMap = {};
  if (!raw) return { keysOrder: keysOrder, valuesMap: valuesMap };
  var parts = raw.split("&");
  for (var i = 0; i < parts.length; i++) {
    var part = parts[i];
    if (!part) continue;
    var eq = part.indexOf("=");
    var kEnc = (eq >= 0) ? part.slice(0, eq) : part;
    var vEnc = (eq >= 0) ? part.slice(eq + 1) : "";
    var key = decodeURIComponent(kEnc.replace(/\+/g, " "));
    var val = decodeURIComponent(vEnc.replace(/\+/g, " "));
    var m = key.match(/^form_data\[(.+)\]$/);
    if (!m) continue;
    var innerKey = m[1].replace(/\]\[\d+\]?$/g, "");
    if (!valuesMap[innerKey]) valuesMap[innerKey] = [];
    if (val !== "") valuesMap[innerKey].push(val);
    if (keysOrder.indexOf(innerKey) === -1) keysOrder.push(innerKey);
  }
  return { keysOrder: keysOrder, valuesMap: valuesMap };
}

function sheetNameFromFormName_(formName) {
  var prefix = "form_";
  var maxLen = 100;
  var name = String(formName).replace(/:/g, "：").replace(/[\\\/\?\*\[\]]/g, " ").replace(/\s+/g, " ").trim();
  if (!name) name = "unknown";
  var allow = maxLen - prefix.length;
  if (name.length > allow) name = name.slice(0, allow).trim();
  return prefix + name;
}

function getOrCreateSheet_(ss, name) {
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

function fmt_(d) {
  return Utilities.formatDate(d, "Asia/Tokyo", "yyyy-MM-dd HH:mm:ss");
}