スプレッドシートの「シート追加ボタン」が Cannot read properties of null (reading 'copyTo') で落ちる原因を特定する

これは何の指示書か

Google スプレッドシート上の GAS ボタン（「シートを追加」「テンプレから一式コピー」等）が

TypeError: Cannot read properties of null (reading 'copyTo')

で落ちたときに、GAS のコードを読まずに、データだけで原因を確定させる手順。

このエラーはほぼ100%が

ss_src.getSheetByName(name).copyTo(ss_dest)   // getSheetByName が null を返している

＝ 「コピー元スプレッドシートに、その名前のシートが無い」。コードのバグではなく **「シート名の一覧（プルダウン・目次・設定表）と、実際のシート名がズレた」**というデータ側の腐り。

長く運用されている業務スプレッドシートでは、次の理由で必ず起きる:

• コピー元からシートを削除・別ファイルへ退避したが、参照している一覧を直していない
• シートをリネームしたが、一覧が旧名のまま
• シート名の末尾に半角スペースが入っている（アイテム一覧 と アイテム一覧 は別物）
• プルダウンが ONE_OF_LIST（値をベタ書き）で作られており、実データと連動していない

前提

• スプレッドシートを読める認証（サービスアカウント＋ドメイン全体の委任、または OAuth）
• Node.js 18+（fetch 同梱）

手順

1. アクセストークンを用意する

サービスアカウント＋ドメイン全体の委任で対象ユーザーになりきる。scope は https://www.googleapis.com/auth/drive 1本で Sheets API も通る。

// drive-auth.mjs
import { createSign } from 'node:crypto';
import { readFileSync } from 'node:fs';

export async function getToken({ keyPath, impersonate, scope = 'https://www.googleapis.com/auth/drive' }) {
  const key = JSON.parse(readFileSync(keyPath, 'utf8'));
  const now = Math.floor(Date.now() / 1000);
  const b64 = (v) => Buffer.from(v).toString('base64url');
  const header = b64(JSON.stringify({ alg: 'RS256', typ: 'JWT' }));
  const claims = b64(JSON.stringify({
    iss: key.client_email, sub: impersonate, scope,
    aud: 'https://oauth2.googleapis.com/token', iat: now, exp: now + 3600,
  }));
  const signer = createSign('RSA-SHA256');
  signer.update(`${header}.${claims}`);
  const jwt = `${header}.${claims}.${signer.sign(key.private_key, 'base64url')}`;
  const res = await fetch('https://oauth2.googleapis.com/token', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: `grant_type=${encodeURIComponent('urn:ietf:params:oauth:grant-type:jwt-bearer')}&assertion=${jwt}`,
  });
  const json = await res.json();
  if (!json.access_token) throw new Error(`token error: ${JSON.stringify(json)}`);
  return json.access_token;
}

2. 「実際のシート名」と「一覧に書かれた名前」を突き合わせる

必ず JSON.stringify で比較する。 末尾スペースは画面でもログでも目視判別できず、 これを見落とすと「名前は合っているのに null」で必ず迷子になる。

import { getToken } from './drive-auth.mjs';

const SRC = '<コピー元スプレッドシートID>';   // シートを持っている側
const DEST = '<ボタンがある側のスプレッドシートID>';
const LIST_SHEET = '<一覧が書かれているシート名>';
// 一覧が入っている列（0=A）。複数列あるなら全部入れる
const LIST_COLS = [0, 5, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23];
const HEADER_ROWS = 4; // 見出し行数。これ以下の行は無視する

const token = await getToken({ keyPath: '<SAキーのパス>', impersonate: '<なりきる相手のメール>' });
const api = async (url) => {
  const r = await fetch(url, { headers: { Authorization: `Bearer ${token}` } });
  const t = await r.text();
  if (!r.ok) throw new Error(`${r.status} ${t.slice(0, 300)}`);
  return JSON.parse(t);
};

// 実際のシート名
const meta = await api(`https://sheets.googleapis.com/v4/spreadsheets/${SRC}?fields=sheets.properties(sheetId,title)`);
const real = new Set(meta.sheets.map((s) => s.properties.title));
console.log(`コピー元: ${real.size} シート`);

// 一覧に書かれた名前
const col = (i) => { let s = '', n = i; do { s = String.fromCharCode(65 + (n % 26)) + s; n = Math.floor(n / 26) - 1; } while (n >= 0); return s; };
const grid = await api(
  `https://sheets.googleapis.com/v4/spreadsheets/${DEST}/values/${encodeURIComponent(`${LIST_SHEET}!A1:Z200`)}`
);
const broken = [];
(grid.values || []).forEach((row, ri) => row.forEach((cell, ci) => {
  if (typeof cell !== 'string' || !cell.trim()) return;
  if (ri < HEADER_ROWS || !LIST_COLS.includes(ci)) return;
  if (!real.has(cell)) broken.push(`${col(ci)}${ri + 1}\t${JSON.stringify(cell)}`);
}));
console.log(`\n実在しない参照 ${broken.length} 件`);
console.log(broken.join('\n'));

3. プルダウンの中身も別に検査する（ここが最大の落とし穴）

セルの値ではなく**データ検証（データの入力規則）**を見る。長期運用のシートでは ONE_OF_LIST（値のベタ書き）で作られていることが多く、実データと一切連動していない。 一覧列が最新でも、プルダウンだけが数年前の名前を出し続ける。

const dv = await api(
  `https://sheets.googleapis.com/v4/spreadsheets/${DEST}` +
  `?ranges=${encodeURIComponent(`${LIST_SHEET}!A1:H10`)}&includeGridData=true` +
  `&fields=sheets(data(rowData(values(userEnteredValue,dataValidation))))`
);
const rows = dv.sheets?.[0]?.data?.[0]?.rowData || [];
rows.forEach((row, ri) => (row.values || []).forEach((cell, ci) => {
  const cond = cell.dataValidation?.condition;
  if (!cond) return;
  if (cond.type === 'ONE_OF_LIST') {
    const list = cond.values.map((v) => v.userEnteredValue);
    const dead = list.filter((n) => !real.has(n));
    console.log(`${col(ci)}${ri + 1} ONE_OF_LIST ${list.length}件 / 実在しない ${dead.length}件`);
    console.log('  ', dead.map((n) => JSON.stringify(n)).join(', '));
  } else {
    console.log(`${col(ci)}${ri + 1} ${cond.type}`, JSON.stringify(cond.values));
  }
}));

4. 「消えたシート」が退避されていないか探す

削除ではなく別ファイルへの退避であることが多い（「倉庫」「アーカイブ」「旧」等の名前）。 過去のバックアップコピーと現在を比べると、いつ消えたかも切り分けられる。

// 候補ファイルを名前で探す
const q = encodeURIComponent(`title contains '<一覧側の名前の一部>' and mimeType = 'application/vnd.google-apps.spreadsheet'`);
const found = await api(`https://www.googleapis.com/drive/v3/files?q=${q}&fields=files(id,name,modifiedTime)`);

// いつ消えたかの切り分け: 古いコピーに目的のシートが在るか
for (const f of found.files) {
  const m = await api(`https://sheets.googleapis.com/v4/spreadsheets/${f.id}?fields=sheets.properties.title`);
  const names = m.sheets.map((s) => s.properties.title);
  console.log(f.name, names.includes('<消えたシート名>') ? '有り' : '無し');
}

// 変更履歴で「誰がいつ触ったか」
const rev = await api(
  `https://www.googleapis.com/drive/v3/files/${SRC}/revisions` +
  `?fields=revisions(id,modifiedTime,lastModifyingUser/emailAddress)&pageSize=100`
);
(rev.revisions || []).slice(-15).forEach((r) => console.log(r.modifiedTime, r.lastModifyingUser?.emailAddress));

5. 直す

優先順に:

1. 消えたシートを退避先から戻す — 追加のみ＝削除なしで、取り消しは「追加した1枚を消す」だけ。 一覧の複数箇所が同じ名前を参照している場合、1操作で全部直り、既に配布済みのコピーにも効く。 API なら「シートを別ファイルへコピー → リネーム」の2手。

   const copied = await fetch(
     `https://sheets.googleapis.com/v4/spreadsheets/${ARCHIVE}/sheets/${SRC_SHEET_ID}:copyTo`,
     { method: 'POST',
       headers: { Authorization: `Bearer ${token}`, 'Content-Type': 'application/json' },
       body: JSON.stringify({ destinationSpreadsheetId: SRC }) }
   ).then((r) => r.json());
   
   await fetch(`https://sheets.googleapis.com/v4/spreadsheets/${SRC}:batchUpdate`, {
     method: 'POST',
     headers: { Authorization: `Bearer ${token}`, 'Content-Type': 'application/json' },
     body: JSON.stringify({ requests: [{ updateSheetProperties: {
       properties: { sheetId: copied.sheetId, title: '<戻したい名前>' }, fields: 'title',
     } }] }),
   });
   

2. プルダウンを実データ参照に変える — ONE_OF_LIST を捨て、実シート名を書き出している列を ONE_OF_RANGE で参照させる。これで「存在しない名前を選べる」状態が構造的に消える。

   body: JSON.stringify({ requests: [{ setDataValidation: {
     range: { sheetId: <一覧シートのsheetId>, startRowIndex: 3, endRowIndex: 4, startColumnIndex: 1, endColumnIndex: 2 },
     rule: { condition: { type: 'ONE_OF_RANGE',
       values: [{ userEnteredValue: `='${LIST_SHEET}'!$A$5:$A$500` }] }, showCustomUi: true },
   } }] })
   

3. コード側に null ガードを入れる（できる場合）— 落ちる代わりに何が無いかを言わせる。 前後スペースを無視した照合も入れておくと、末尾スペース事故を吸収できる。

   function findSheet(ss, name) {
     return ss.getSheetByName(name)
       || ss.getSheets().find((s) => s.getName().trim() === String(name).trim())
       || null;
   }
   const src = findSheet(ss_src, name);
   if (!src) throw new Error(`コピー元にシート「${name}」がありません。シート名一覧を更新してください。`);
   src.copyTo(ss_dest);
   

6. 直ったことを実データで確認する（read-back verify）

「直したはず」で終わらせない。手順2をもう一度回して未解決件数が減ったことと、 ボタンが読むセルの現在値が実在名に解決することの2点を数字で出す。

console.log(`未解決参照 ${broken.length} 件（修正前 N 件）`);
const cur = (await api(`https://sheets.googleapis.com/v4/spreadsheets/${DEST}/values/${encodeURIComponent(`${LIST_SHEET}!B4`)}`)).values?.[0]?.[0];
console.log(`選択値 ${JSON.stringify(cur)} -> ${real.has(cur) ? 'OK' : 'まだ落ちる'}`);

詰まりやすい点

• コンテナバインド型 GAS のコードには外から到達できない。 スプレッドシートに紐づいた スクリプトの ID を列挙する API は存在せず、Drive の子要素クエリでも 0 件が返る （バインド型スクリプトは Drive のファイルとして現れない）。コードを直すなら 「拡張機能 → Apps Script」で開いた URL を人から受け取るしかない。データ側の修正だけなら不要。
• 一式コピーが try { } catch { } で囲まれていると、存在しないシートは黙って抜け落ちる。 「エラーが出ない」＝「正常」ではない。手順2の突き合わせでしか気づけない。
• 一覧列の開始行はファイルごとにズレる。 テンプレと配布済みコピーで見出し行数が違うことがあるので、 HEADER_ROWS を決め打ちする前に実際の列を1回全部出して目で確認する。
• 旧形式の URL からは ID が読めない。 spreadsheet/ccc?key=0A... 形式が設定セルに残っていると 参照先が分からないので、突き合わせの前に「実際に開いて ID を控える」か、 ファイル名で Drive を検索して実体を特定する。
• 本番の共有マスタへの書き込みは承認を取ってから。 一覧を書き換える修正は そのファイルをコピーして使っている全ての配布先に波及する。差分の件数を先に出して合意を取る。