提出用フォーマット整形を1クリックで — #REF!エラーだらけを防ぐ

// コード.gs
/**
 * 「提出設定」シートで指定した対象シートから、不要列を削除した「提出用_YYYY-MM-DD_HH-mm」シートを切り出す。
 *   - 元シートには触れず、コピー後のシートだけに対して列削除＋数式→値固定を行う
 *   - 何度実行しても新しいタイムスタンプ付きシートが追加される（過去の提出版はそのまま残る）
 * メニュー「自動ボタン → 提出用シートを切り出す」から実行。
 */

const SUBMIT_CONFIG_SHEET = '提出設定';
const SUBMIT_PREFIX = '提出用_';

function makeSubmitSheet() {
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const cfg = ss.getSheetByName(SUBMIT_CONFIG_SHEET);
  if (!cfg) {
    ui.alert('「' + SUBMIT_CONFIG_SHEET + '」シートが見つかりません。');
    return;
  }

  // 提出設定の読み込み（A列キー / B列値）
  const last = cfg.getLastRow();
  let targetName = '';
  const excludeCols = [];
  if (last >= 2) {
    const cfgData = cfg.getRange(2, 1, last - 1, 2).getValues();
    for (const [key, value] of cfgData) {
      const k = String(key).trim();
      const v = String(value).trim();
      if (!k || !v) continue;
      if (k === '対象シート') targetName = v;
      else if (k === '除外列') {
        // カンマ「,」または読点「、」区切りで複数指定可（行を分けて書いてもOK）
        const parts = v.split(/[,、]/).map(function(p) { return p.trim(); }).filter(function(p) { return p; });
        for (const p of parts) excludeCols.push(p);
      }
    }
  }
  if (!targetName) {
    ui.alert('「' + SUBMIT_CONFIG_SHEET + '」のA列に「対象シート」、B列にシート名を入れてください。');
    return;
  }

  const target = ss.getSheetByName(targetName);
  if (!target) {
    ui.alert('対象シート「' + targetName + '」が見つかりません。');
    return;
  }

  // ── ① 対象シートをコピー（提出用_日付） ──
  const ts = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  let newName = SUBMIT_PREFIX + ts;
  // 同日中に再実行された場合は _2, _3, ... を付けて衝突回避
  if (ss.getSheetByName(newName)) {
    let n = 2;
    while (ss.getSheetByName(newName + '_' + n)) n++;
    newName = newName + '_' + n;
  }
  const submit = target.copyTo(ss).setName(newName);
  // 末尾タブに移動してアクティブに
  ss.setActiveSheet(submit);
  ss.moveActiveSheet(ss.getNumSheets());

  // ── ② 除外列を「右から」削除 ──
  // 左から削除すると残りの列番号がズレるので、必ず右からまとめて削除する
  const lastCol = submit.getLastColumn();
  const headers = submit.getRange(1, 1, 1, lastCol).getValues()[0]
    .map(function(h) { return String(h).trim(); });
  const removeColNumbers = [];
  for (let c = 0; c < headers.length; c++) {
    if (excludeCols.indexOf(headers[c]) >= 0) removeColNumbers.push(c + 1);
  }
  removeColNumbers.sort(function(a, b) { return b - a; });
  for (const c of removeColNumbers) submit.deleteColumn(c);

  // ── ③ 残った範囲の数式を値に固定 ──
  // getValues() は数式を「実行結果の値」で返すので、そのまま setValues() で書き戻すと
  // 数式が消えて値だけが残る（手作業の「値貼り付け」と同じ効果）
  const lastRow = submit.getLastRow();
  const remainingCol = submit.getLastColumn();
  if (lastRow >= 1 && remainingCol >= 1) {
    const range = submit.getRange(1, 1, lastRow, remainingCol);
    range.setValues(range.getValues());
  }

  SpreadsheetApp.flush();
  ui.alert(
    '「' + newName + '」を作成しました。\n' +
    '除外列: ' + removeColNumbers.length + ' 列、残り: ' + remainingCol + ' 列\n' +
    '数式は値に固定済みです。'
  );
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('自動ボタン')
    .addItem('提出用シートを切り出す', 'makeSubmitSheet')
    .addToUi();
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('自動ボタン')
    .addItem('提出用シートを切り出す', 'makeSubmitSheet')
    .addToUi();
}

SpreadsheetApp.getUi()
  .createMenu('自動ボタン')
  .addItem('提出用シートを切り出す', 'makeSubmitSheet')
  .addSeparator()
  .addItem('提出用シートを切り出す（マスク付き）', 'makeSubmitSheetWithMask')
  .addItem('提出履歴をクリア', 'clearSubmitLog')
  .addToUi();