Googleフォームの内容を自動でNotionのDBに登録する方法

/*************************************************
 * Googleフォーム → スプレッドシート → Notion 連携
 * “実行前入力”版（ノープロンプト・インストーラー付き）
 * - 先頭2行にトークン/DB IDを入れて保存 → installNotionSyncPreset() を実行
 * - タイトル列（左端）が「件名」のDBに自動対応
 * - 日時の安全パース / 手動実行フォールバック / ヘッダー保護
 * - 機密は Script Properties に保存（以後はコード外参照）
 **************************************************/

/*** ▼ 実行前にここを入力（コピペ読者はこの2行だけ触ればOK） ▼ ***/
const PRESET_NOTION_API_KEY     = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; // 例: secret_xxx または ntn_xxx
const PRESET_NOTION_DATABASE_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; // 
const NOTION_VERSION            = '2022-06-28';

/** インストール（ノープロンプト：先頭の PRESET_* を入力してから実行） */
function installNotionSyncPreset() {
  if (!PRESET_NOTION_API_KEY || !PRESET_NOTION_DATABASE_ID) {
    throw new Error('PRESET_NOTION_API_KEY / PRESET_NOTION_DATABASE_ID を先に入力してください。');
  }
  // 保存（Script Properties）
  const props = PropertiesService.getScriptProperties();
  props.setProperty('NOTION_API_KEY', PRESET_NOTION_API_KEY.trim());
  props.setProperty('NOTION_DATABASE_ID', PRESET_NOTION_DATABASE_ID.trim());
  props.setProperty('NOTION_VERSION', NOTION_VERSION);

  // 正しいトリガーを作成（From spreadsheet / On form submit）
  setupOnSubmitTrigger();

  // 接続テスト
  testNotionConnection(false);

  // スキーマキャッシュをクリア
  clearNotionSchemaCache();

  Logger.log('セットアップ完了。フォーム送信で自動連携します。必要なら sendLastRowToNotion() で即テスト可。');
}

/** アンインストール（トリガー＆機密の削除） */
function uninstallNotionSync() {
  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() === 'onFormSubmit') ScriptApp.deleteTrigger(t);
  });
  const props = PropertiesService.getScriptProperties();
  props.deleteProperty('NOTION_API_KEY');
  props.deleteProperty('NOTION_DATABASE_ID');
  props.deleteProperty('NOTION_VERSION');
  clearNotionSchemaCache();
  Logger.log('Uninstalled: triggers & secrets removed.');
}

/** 正しいトリガーを作成（From spreadsheet / On form submit） */
function setupOnSubmitTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.getProjectTriggers().forEach(t => {
    if (t.getHandlerFunction() === 'onFormSubmit') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('onFormSubmit').forSpreadsheet(ss).onFormSubmit().create();
  Logger.log('Trigger created for spreadsheet: ' + ss.getName());
}

/** トリガー一覧（確認用） */
function listTriggers() {
  ScriptApp.getProjectTriggers().forEach(t => {
    Logger.log(`${t.getHandlerFunction()} / ${t.getEventType()} / ${t.getTriggerSource()}`);
  });
}

/** Secrets を取得（未設定ならエラー） */
function getSecrets_() {
  const props = PropertiesService.getScriptProperties();
  const NOTION_API_KEY     = props.getProperty('NOTION_API_KEY');
  const NOTION_DATABASE_ID = props.getProperty('NOTION_DATABASE_ID');
  const NOTION_VERSION_    = props.getProperty('NOTION_VERSION') || NOTION_VERSION;
  if (!NOTION_API_KEY || !NOTION_DATABASE_ID) {
    throw new Error('Notionのトークン/DB ID が未設定です。installNotionSyncPreset() を先に実行してください。');
  }
  return { NOTION_API_KEY, NOTION_DATABASE_ID, NOTION_VERSION: NOTION_VERSION_ };
}

/** DBスキーマキャッシュをクリア（列を調整後に実行） */
function clearNotionSchemaCache() {
  CacheService.getScriptCache().remove('notion_db_schema');
}

/** Notion接続テスト：エラーなら throw */
function testNotionConnection(showLog) {
  const sec = getSecrets_();
  const url = 'https://api.notion.com/v1/databases/' + sec.NOTION_DATABASE_ID;
  const options = {
    method: 'get',
    headers: {
      'Authorization': 'Bearer ' + sec.NOTION_API_KEY,
      'Notion-Version': sec.NOTION_VERSION
    },
    muteHttpExceptions: true
  };
  const res = UrlFetchApp.fetch(url, options);
  const code = res.getResponseCode();
  if (showLog !== false) Logger.log('Notion DB GET → HTTP ' + code + ' ' + res.getContentText());
  if (code >= 200 && code < 300) return true;
  const body = res.getContentText() || '';
  if (body.indexOf('Unauthorized') !== -1 || code === 401 || code === 403) {
    throw new Error('権限エラー。NotionのDB画面右上「… → Add connections」でIntegrationを接続してください。');
  }
  throw new Error('DB取得エラー: HTTP ' + code + ' ' + body);
}

/** ▼ ここが自動実行される：フォーム送信時 */
function onFormSubmit(e) {
  try {
    let named, timestamp;
    if (e && e.namedValues) {
      named = e.namedValues;
      timestamp = (e.values && e.values[0]) ? toDateSafe_(e.values[0]) : new Date();
    } else {
      // 手動フォールバック：最終行を送る
      const sh = SpreadsheetApp.getActiveSheet();
      const lastRow = sh.getLastRow();
      if (lastRow <= 1) throw new Error('送信データ行がありません（見出しのみ）');
      const header = sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
      const values = sh.getRange(lastRow, 1, 1, sh.getLastColumn()).getValues()[0];
      named = {};
      header.forEach((h, i) => { if (h) named[h] = [ String(values[i] == null ? '' : values[i]) ]; });
      timestamp = toDateSafe_(values[0] || new Date());
    }

    const properties = buildPropertiesAuto_(named, timestamp); // 自動スキーマ対応
    const res = notionCreatePage_(properties);
    Logger.log('Notion登録 OK: ' + JSON.stringify(res));
  } catch (err) {
    Logger.log('Notion登録エラー: ' + err.message);
    throw err;
  }
}

/** 手動テスト：選択中の行を送る（1行目=ヘッダーは最終行に置換） */
function sendActiveRowToNotion() {
  const sheet = SpreadsheetApp.getActiveSheet();
  let row = sheet.getActiveRange().getRow();
  if (row === 1) row = sheet.getLastRow();
  const header = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const values = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const named = {};
  header.forEach((h, i) => { if (h) named[h] = [ String(values[i] == null ? '' : values[i]) ]; });
  const timestamp = toDateSafe_(values[0] || new Date());

  const properties = buildPropertiesAuto_(named, timestamp);
  const res = notionCreatePage_(properties);
  Logger.log('手動送信 OK: ' + JSON.stringify(res));
}

/** 手動テスト：常に最終行を送る */
function sendLastRowToNotion() {
  const sh = SpreadsheetApp.getActiveSheet();
  let row = sh.getLastRow();
  if (row <= 1) throw new Error('送信データ行がありません（見出しのみ）');
  sh.getRange(row, 1).activate();
  sendActiveRowToNotion();
}

/** === 核心：DBスキーマを見て自動でプロパティを組み立てる ===
 *  左端のタイトル列（今回は「件名」）へ「[お問い合わせ] 氏名 - 日時」を入れる
 *  さらに“テキストの件名列”が別に存在する場合は同じ値を複写（空問題の解消）
 *  氏名/詳細=テキスト, 種別=セレクト, 希望サービス（複数選択可）=マルチセレクト, 送信日時=日付
 */
function buildPropertiesAuto_(namedValues, timestamp) {
  const schema = getDbSchema_(); // { props: {name: {type}}, titleName: '件名' }
  const props = {};

  // 1) タイトル列（件名）に件名テキスト
  const titleName = schema.titleName; // 例: '件名'
  const nameFromForm = getFirst_(namedValues['氏名']) || '無名';
  const titleText = '[お問い合わせ] ' + nameFromForm + ' - ' + formatDateTime_(timestamp);
  props[titleName] = { title: [ { type: 'text', text: { content: titleText } } ] };

  // 1.5) “テキストの件名列”が別に存在すれば複写（rich_text優先）
  const subjectDupName = ['件名','タイトル','題名'].find(n => schema.props[n] && n !== titleName);
  if (subjectDupName) {
    const ptype = schema.props[subjectDupName].type;
    if (ptype === 'rich_text') {
      props[subjectDupName] = { rich_text: [ { type: 'text', text: { content: titleText } } ] };
    } else if (ptype === 'title' && subjectDupName !== titleName) {
      // Notion上は Title は1つ想定だが、安全のため条件分岐
      props[subjectDupName] = { title: [ { type: 'text', text: { content: titleText } } ] };
    }
  }

  // 2) 論理フィールドをDBスキーマに合わせて投入
  const LOGICAL_FIELDS = [
    { form: '氏名',                      logical: 'name'     },
    { form: 'お問い合わせ種別',          logical: 'type'     },
    { form: '詳細',                      logical: 'detail'   },
    { form: '希望サービス（複数選択可）', logical: 'services' }
  ];

  for (let i = 0; i < LOGICAL_FIELDS.length; i++) {
    const lf = LOGICAL_FIELDS[i];
    const answerArr = namedValues[lf.form];
    const answerStr = Array.isArray(answerArr) ? answerArr.join(', ') : String(answerArr || '');

    // 候補名：フォーム名と同名 > よくある日本語カラム名（短縮名にも対応）
    const candidateNames = [
      lf.form,
      lf.logical === 'type'     ? 'お問い合わせ種別' : null,
      lf.logical === 'detail'   ? '詳細' : null,
      lf.logical === 'services' ? '希望サービス（複数選択可）' : null,
      lf.logical === 'services' ? '希望サービス' : null,
      lf.logical === 'name'     ? '氏名' : null
    ].filter(Boolean);

    let propName = null, propInfo = null;
    for (let j = 0; j < candidateNames.length; j++) {
      const cand = candidateNames[j];
      if (schema.props[cand]) { propName = cand; propInfo = schema.props[cand]; break; }
    }
    if (!propName) continue; // DBに列が無ければ送らない

    const type = propInfo.type; // 'title' | 'rich_text' | 'select' | 'multi_select' | 'checkbox' | 'date' ...
    if (type === 'title' && propName === titleName) continue; // タイトル列は件名で埋め済み

    if (type === 'title') {
      props[propName] = { title: [ { type: 'text', text: { content: answerStr || nameFromForm } } ] };
    } else if (type === 'rich_text') {
      props[propName] = { rich_text: [ { type: 'text', text: { content: answerStr } } ] };
    } else if (type === 'select') {
      props[propName] = answerStr ? { select: { name: answerStr } } : { select: null };
    } else if (type === 'multi_select') {
      const items = splitMulti_(answerStr).map(p => ({ name: p.trim() })).filter(o => o.name);
      props[propName] = { multi_select: items };
    } else if (type === 'checkbox') {
      const val = getFirst_(answerArr || '').toString().toLowerCase();
      props[propName] = { checkbox: looksTrue_(val) };
    } else if (type === 'date') {
      props[propName] = { date: { start: toISO_(getFirst_(answerArr) || timestamp) } };
    }
  }

  // 3) 送信日時（Date）がDBにあれば入れる
  if (schema.props['送信日時'] && schema.props['送信日時'].type === 'date') {
    props['送信日時'] = { date: { start: toISO_(timestamp) } };
  }

  return props;
}

/** DBスキーマを取得（キャッシュ10分） */
function getDbSchema_() {
  const cache = CacheService.getScriptCache();
  const cached = cache.get('notion_db_schema');
  if (cached) return JSON.parse(cached);

  const sec = getSecrets_();
  const url = 'https://api.notion.com/v1/databases/' + sec.NOTION_DATABASE_ID;
  const options = {
    method: 'get',
    headers: {
      'Authorization': 'Bearer ' + sec.NOTION_API_KEY,
      'Notion-Version': sec.NOTION_VERSION
    },
    muteHttpExceptions: true
  };
  const response = UrlFetchApp.fetch(url, options);
  const code = response.getResponseCode();
  if (code < 200 || code >= 300) throw new Error('DBスキーマ取得失敗: HTTP ' + code + ' ' + response.getContentText());

  const json = JSON.parse(response.getContentText());
  const props = {};
  Object.keys(json.properties || {}).forEach(name => {
    props[name] = { type: json.properties[name].type };
  });

  // Title列名を検出（Notionは常に1つ）
  const titleName = Object.keys(json.properties).find(name => json.properties[name].type === 'title');

  const schema = { props, titleName };
  cache.put('notion_db_schema', JSON.stringify(schema), 600); // 10分
  return schema;
}

/** Notionへページ作成 */
function notionCreatePage_(properties) {
  const sec = getSecrets_();
  const url = 'https://api.notion.com/v1/pages';
  const payload = {
    parent: { database_id: sec.NOTION_DATABASE_ID },
    properties: properties
  };
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + sec.NOTION_API_KEY,
      'Notion-Version': sec.NOTION_VERSION
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  const response = UrlFetchApp.fetch(url, options);
  const code = response.getResponseCode();
  const body = response.getContentText();
  if (code >= 200 && code < 300) return JSON.parse(body);
  throw new Error('HTTP ' + code + ' ' + body);
}

/***************** ユーティリティ *****************/
function getFirst_(v) {
  if (!v) return '';
  if (Array.isArray(v)) return (v[0] == null) ? '' : String(v[0]);
  return String(v);
}

/** どんな入力でもDateに直す安全パーサ */
function toDateSafe_(v) {
  if (v instanceof Date && !isNaN(v)) return v;
  if (typeof v === 'number') {
    const dNum = new Date(v);
    if (!isNaN(dNum)) return dNum;
  }
  if (typeof v === 'string') {
    const s = v.trim();
    if (s) {
      const normalized = s.replace(/[年月]/g, '/').replace(/日/g, '').replace(/\./g, '/');
      const d1 = new Date(normalized);
      if (!isNaN(d1)) return d1;
      const d2 = new Date(s);
      if (!isNaN(d2)) return d2;
    }
  }
  return new Date(); // 最後の保険
}

function toISO_(d) {
  const date = toDateSafe_(d);
  return date.toISOString(); // UTC
}

function formatDateTime_(d) {
  const date = toDateSafe_(d);
  const pad = n => ('0' + n).slice(-2);
  return `${date.getFullYear()}-${pad(date.getMonth()+1)}-${pad(date.getDate())} ${pad(date.getHours())}:${pad(date.getMinutes())}`;
}

function looksTrue_(s) {
  const list = ['はい','true','有','オン','チェック','yes','y','1','ok'];
  for (let i=0;i<list.length;i++) if (s.indexOf(list[i]) !== -1) return true;
  return false;
}

function splitMulti_(s) {
  if (!s) return [];
  // カンマ（日/全角/半角）・読点・改行・タブで分割
  return s.split(/[,、，\n\r\t]+/);
}