# スプレッドシートの「シート追加ボタン」が `Cannot read properties of null (reading 'copyTo')` で落ちる原因を特定する ## これは何の指示書か Google スプレッドシート上の GAS ボタン（「シートを追加」「テンプレから一式コピー」等）が ``` TypeError: Cannot read properties of null (reading 'copyTo') ``` で落ちたときに、**GAS のコードを読まずに、データだけで原因を確定させる**手順。 このエラーはほぼ100%が ```js 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 も通る。 ```js // 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」で必ず迷子になる。 ```js 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: '', 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`（値のベタ書き）で作られていることが多く、**実データと一切連動していない**。 一覧列が最新でも、プルダウンだけが数年前の名前を出し続ける。 ```js 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. 「消えたシート」が退避されていないか探す 削除ではなく**別ファイルへの退避**であることが多い（「倉庫」「アーカイブ」「旧」等の名前）。 過去のバックアップコピーと現在を比べると、いつ消えたかも切り分けられる。 ```js // 候補ファイルを名前で探す 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手。 ```js 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` で参照させる。これで「存在しない名前を選べる」状態が構造的に消える。 ```js 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 ガードを入れる**（できる場合）— 落ちる代わりに何が無いかを言わせる。 前後スペースを無視した照合も入れておくと、末尾スペース事故を吸収できる。 ```js 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点を数字で出す。 ```js 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 を検索して実体を特定する。 - **本番の共有マスタへの書き込みは承認を取ってから。** 一覧を書き換える修正は そのファイルをコピーして使っている全ての配布先に波及する。差分の件数を先に出して合意を取る。 ---