const SHEET_ID = 'スプレッドシートID';
const SHEET_NAME = 'シート1';
const ID_COL = 'id'; // A列の見出し

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
    .setTitle('CRUD')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

function list() {
  const [header, ...rows] = sheet_().getDataRange().getDisplayValues();
  const items = rows
    .filter(r => r[0] !== '')
    .map(r => Object.fromEntries(header.map((h, i) => [h, r[i]])));
  return { header, items };
}

function create(item) {
  return withLock_(() => {
    const sh = sheet_();
    const id = Utilities.getUuid();
    sh.appendRow(header_(sh).map(h => (h === ID_COL ? id : item[h] ?? '')));
    return id;
  });
}

function update(item) {
  withLock_(() => {
    const sh = sheet_();
    const header = header_(sh);
    const row = findRow_(sh, item[ID_COL]);
    sh.getRange(row, 1, 1, header.length).setValues([header.map(h => item[h] ?? '')]);
  });
}

function remove(id) {
  withLock_(() => {
    const sh = sheet_();
    sh.deleteRow(findRow_(sh, id));
  });
}

// 既存データの空IDを埋める（エディタから一度だけ手動実行）
function fillMissingIds() {
  const sh = sheet_();
  const n = sh.getLastRow() - 1;
  if (n < 1) return;
  const range = sh.getRange(2, 1, n, 1);
  range.setValues(range.getValues().map(([v]) => [v || Utilities.getUuid()]));
}

// ---- 末尾 _ の関数はフロントから呼べない ----

function sheet_() {
  return SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
}

function header_(sh) {
  return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}

function findRow_(sh, id) {
  const cell = sh.getRange('A2:A').createTextFinder(String(id)).matchEntireCell(true).findNext();
  if (!cell) throw new Error('対象が見つかりません: ' + id);
  return cell.getRow();
}

function withLock_(fn) {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try { return fn(); } finally { lock.releaseLock(); }
}

<!DOCTYPE html>
<html lang="ja">
<head>
  <style>
    body { font-family: system-ui, sans-serif; max-width: 40rem; margin: auto; padding: 1rem; }
    .item { display: grid; gap: .5rem; padding: 1rem 0; border-bottom: 1px solid #ddd; }
    .item p { margin: 0; white-space: pre-wrap; word-break: break-word; }
    textarea { width: 100%; box-sizing: border-box; }
  </style>
</head>
<body>
  <section id="listView">
    <h1>一覧 <button onclick="show('createView')">＋ 新規</button></h1>
    <p id="status"></p>
    <div id="list"></div>
  </section>

  <section id="createView" hidden>
    <h1><button onclick="show('listView')">←</button> 新規作成</h1>
    <form id="createForm" class="item"></form>
  </section>

  <script>
    const ID = 'id';
    let header = [], items = [];
    const $ = id => document.getElementById(id);
    const esc = s => String(s ?? '').replace(/[&<>"]/g, c => `&#${c.charCodeAt(0)};`);
    const fields = () => header.filter(h => h !== ID);

    // google.script.run を Promise 化
    const api = new Proxy({}, { get: (_, fn) => (...args) => new Promise((ok, ng) =>
      google.script.run.withSuccessHandler(ok).withFailureHandler(ng)[fn](...args)) });

    function show(view) {
      for (const v of ['listView', 'createView']) $(v).hidden = v !== view;
    }

    // 見出しから入力欄を生成
    const inputs = (item = {}) => fields().map(h =>
      `<label>${esc(h)}<textarea name="${esc(h)}">${esc(item[h])}</textarea></label>`).join('');

    // 表示用
    const viewHtml = it => `
      <div class="item" data-id="${esc(it[ID])}">
        ${fields().map(h => `<p><b>${esc(h)}</b><br>${esc(it[h])}</p>`).join('')}
        <div><button data-act="edit">編集</button> <button data-act="del">削除</button></div>
      </div>`;

    // 編集用
    const editHtml = it => `
      <form class="item" data-id="${esc(it[ID])}">${inputs(it)}
        <div><button>保存</button> <button type="button" data-act="cancel">キャンセル</button></div>
      </form>`;

    async function load() {
      ({ header, items } = await api.list());
      $('list').innerHTML = items.map(viewHtml).join('') || 'データがありません';
      $('createForm').innerHTML = inputs() + '<button>追加</button>';
    }

    // 書き込み → 再読み込み（処理中は label を表示）
    const run = (p, label) => {
      $('status').textContent = label;
      return p.then(load)
        .then(() => { $('status').textContent = ''; })
        .catch(e => { $('status').textContent = ''; alert(e.message); });
    };

    // 編集・キャンセル・削除
    $('list').addEventListener('click', e => {
      const act = e.target.dataset.act;
      if (!act) return;
      const el = e.target.closest('.item');
      const it = items.find(x => x[ID] === el.dataset.id);
      if (act === 'edit') el.outerHTML = editHtml(it);
      if (act === 'cancel') el.outerHTML = viewHtml(it);
      if (act === 'del' && confirm('削除しますか？')) run(api.remove(it[ID]), '削除中…');
    });

    // 保存（Update）
    $('list').addEventListener('submit', e => {
      e.preventDefault();
      const data = Object.fromEntries(new FormData(e.target));
      run(api.update({ ...data, [ID]: e.target.dataset.id }), '保存中…');
    });

    // 追加（Create）
    $('createForm').addEventListener('submit', e => {
      e.preventDefault();
      show('listView');
      run(api.create(Object.fromEntries(new FormData(e.target))), '追加中…');
    });

    run(Promise.resolve(), '読み込み中…');
  </script>
</body>
</html>