Apps Scriptで未入力行を一覧化する方法｜元表を修正するためのコード

管理表の空欄を毎回目で探しているなら、修正が残っている行を別シートへ出す方法があります。ただし、空欄をすべて拾うと、まだ使っていないひな形まで一覧へ並んでしまいます。Apps Scriptを使う前に、実データを見分ける基準列と、埋めるべき必須列を分けましょう。元行番号、管理番号、未入力項目名を残せば、担当者が元の記録へ戻って修正しやすくなります。

この記事は、受注・工程・検査などの管理表で入力漏れを点検する担当者向けです。空欄と指定した待ち状態を抽出するコードを紹介しますが、入力された内容が正しいかを判定するものではありません。まず本番とは別のコピーで、拾いたい行と除外する行を試し、自社の列と記入ルールに合わせてください。

状況	判断	最初の対応	次に調べること
空のひな形行まで一覧に出る	必須列だけで対象行を決めない	管理番号などの基準列を決める	実データの全行で基準列が入力されているか
複数項目の漏れをまとめて見つけたい	行ごとに必須列をまとめて判定する	必須列と確認待ちの値を列挙する	未入力項目名を一覧に表示するか
毎回フィルタを設定している	別シートへ毎回再出力する	出力専用シートを用意する	誰が修正し、誰が一覧と元表を照合するか

未入力行を一覧化する前に、対象行と必須列を設定する

最初に決めるのは、どの行を実データとして扱うかです。サンプルでは管理番号の列を基準にし、番号が空の行を除外します。これにより未使用のひな形を減らせますが、入力を始めたのに番号だけ空欄の案件も出なくなります。冒頭の表のとおり、基準列が実データの全行で埋まっているかを、抽出とは別に点検することが前提です。

次に、担当者、納期、確認結果など、作業を引き継ぐ前に埋める列を選びます。サンプルでは、空欄のほかに「確認待ち」という固定の状態も対象にします。似た言葉を自動で同じ意味と扱う処理はありません。複数の表記が混ざっているなら、入力規則や記入ルールを先にそろえると、抽出されなかった理由を追いやすくなります。

設定項目	決める内容	受注管理表での記入例
元シート名	抽出元となる管理表のシート名	受注管理
見出し行	列名が入力されている行	1
データ開始行	実データが始まる行	2
基準列	実データの存在を判断する列	A列：管理番号
必須列	空欄を調べる列	C列：担当者、D列：納期、E列：確認結果
対象とする待ち状態	空欄以外で抽出対象にする表記	確認待ち
出力先シート	一覧専用のシート名	未入力一覧
一覧に残す列	修正対象を特定する情報	元行番号、管理番号、案件名、未入力項目、担当者、納期

上の表に決めた内容を記入し、設定用シートや変更履歴へ残しましょう。このコードは列の位置で読み取るため、列名が同じでも位置を変えれば設定の見直しが生じます。列を追加・移動する担当者は、変更前の設定を残し、変更後のコピーで抽出結果まで照合してください。列番号だけを直して、一覧へ表示する情報を古い位置のままにしないことが大切です。

管理番号を基準列にするなら、採番と重複も点検する

管理番号を基準にすると、行を並べ替えても対象を探しやすくなります。ただし、未採番の行を見つける用途には使えません。登録時に番号を入れる運用にするか、実データに必ず入る別の列を基準にし、番号未入力は別途点検します。スプレッドシートで連番を自動採番し管理番号を重複させない方法も参考に、途中の入力段階を含めて番号の扱いを決めてください。

管理表と未入力一覧は別シートで用意する

抽出先を一覧専用の別シートにすれば、元表の並びや数式を変更せずに対象を読めます。下の表はサンプルの列構成です。A列を行の識別に、C〜E列を未入力判定に使い、一覧には案件名や担当者も載せます。自社の表へ合わせる際は、どの列で判定し、どの列を表示するかを分けて照合しましょう。

元表の列	内容	未入力一覧での扱い
A列	管理番号	対象行の判定と照合に使う
B列	案件名	対象案件を特定するため表示する
C列	担当者	必須列として判定し、一覧にも表示する
D列	納期	必須列として判定し、一覧にも表示する
E列	確認結果	必須列として判定する

出力先がなければ、コードは指定名のシートを作ります。既に存在する場合は内容を作り直すため、手入力のメモや別の集計を置かず、一覧専用にしてください。初回に既存シートを指定する場合は、残したい内容を先に退避します。元表と出力先が同じシートなら停止する検査も入れていますが、運用上も別用途のシートを指定しないことが前提です。

未入力項目名つきで別シートへ抽出するApps Script

下のコードは、A列に管理番号がある行について、C〜E列の空欄または「確認待ち」を調べます。元表を削除・並べ替えせず、設定とデータを読んでから出力先を書き換える構成です。設定する列番号は1始まりで、配列の添字は0始まりです。また、同じプロジェクトの重複実行をロックで抑えますが、人による編集は止めないため、処理中は元表を変更しない時間を選んでください。

function createMissingList() {
  const CONFIG = {
    sourceSheetName: '受注管理',
    outputSheetName: '未入力一覧',
    headerRow: 1,
    dataStartRow: 2,
    keyColumn: 1,
    requiredColumns: [3, 4, 5],
    waitingValues: ['確認待ち']
  };
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) throw new Error('別の実行が進行中です。');
  try {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    if (!spreadsheet) throw new Error('対象ブックに紐づけて実行してください。');
    const source = spreadsheet.getSheetByName(CONFIG.sourceSheetName);
    if (!source) throw new Error('抽出元シートが見つかりません。');
    let output = spreadsheet.getSheetByName(CONFIG.outputSheetName);
    if (CONFIG.sourceSheetName === CONFIG.outputSheetName ||
        (output && output.getSheetId() === source.getSheetId())) {
      throw new Error('抽出元と出力先は別のシートにしてください。');
    }
    const isPositiveInteger = value => Number.isInteger(value) && value > 0;
    if (![CONFIG.headerRow, CONFIG.dataStartRow, CONFIG.keyColumn].every(isPositiveInteger) ||
        CONFIG.dataStartRow <= CONFIG.headerRow || !CONFIG.requiredColumns.length ||
        !CONFIG.requiredColumns.every(isPositiveInteger) ||
        new Set(CONFIG.requiredColumns).size !== CONFIG.requiredColumns.length) {
      throw new Error('見出し行、開始行、基準列、必須列の設定が不正です。');
    }
    const lastRow = source.getLastRow();
    const lastColumn = source.getLastColumn();
    const maxConfiguredColumn = Math.max(4, CONFIG.keyColumn, ...CONFIG.requiredColumns);
    if (lastColumn < maxConfiguredColumn || lastRow < CONFIG.headerRow) {
      throw new Error('設定した行・列が元表の範囲を超えています。');
    }
    const range = source.getRange(CONFIG.headerRow, 1, lastRow - CONFIG.headerRow + 1, lastColumn);
    const values = range.getValues();
    const displays = range.getDisplayValues();
    const headers = displays[0].map(value => value.trim());
    const checkedColumns = [...new Set([CONFIG.keyColumn, ...CONFIG.requiredColumns])];
    const names = checkedColumns.map(column => headers[column - 1]);
    if (names.some(name => !name) || new Set(names).size !== names.length) {
      throw new Error('基準列・必須列の見出しが空欄または重複しています。');
    }
    const rows = values.slice(CONFIG.dataStartRow - CONFIG.headerRow);
    const isMissing = value => {
      const text = String(value == null ? '' : value).trim();
      return text === '' || CONFIG.waitingValues.includes(text);
    };
    const result = rows.reduce((list, row, index) => {
      const key = String(row[CONFIG.keyColumn - 1] == null ? '' : row[CONFIG.keyColumn - 1]).trim();
      if (key === '') return list;
      const missingNames = CONFIG.requiredColumns
        .filter(column => isMissing(row[column - 1]))
        .map(column => headers[column - 1]);
      if (missingNames.length > 0) {
        const display = displays[CONFIG.dataStartRow - CONFIG.headerRow + index];
        list.push([
          CONFIG.dataStartRow + index,
          display[0], display[1],
          missingNames.join('、'),
          display[2], display[3]
        ]);
      }
      return list;
    }, []);
    const outputHeaders = ['元行番号', '管理番号', '案件名', '未入力項目', '担当者', '納期'];
    const writeData = result.length > 0
      ? result
      : [['該当なし', '', '', '', '', '']];
    const asLiteral = value => typeof value === 'string' && value.startsWith('=') ? "'" + value : value;
    const allData = [outputHeaders, ...writeData].map(row => row.map(asLiteral));
    if (!output) output = spreadsheet.insertSheet(CONFIG.outputSheetName);
    if (output.getMaxRows() < allData.length) {
      output.insertRowsAfter(output.getMaxRows(), allData.length - output.getMaxRows());
    }
    if (output.getMaxColumns() < outputHeaders.length) {
      output.insertColumnsAfter(output.getMaxColumns(), outputHeaders.length - output.getMaxColumns());
    }
    output.clearContents();
    output.getRange(1, 1, allData.length, outputHeaders.length).setValues(allData);
    SpreadsheetApp.flush();
    console.log('一覧更新完了: ' + result.length + '件');
  } finally {
    lock.releaseLock();
  }
}

sourceSheetName、outputSheetName、keyColumn、requiredColumns、waitingValuesが主な設定です。案件名や表示したい情報の位置を変えた場合は、list.push内のdisplay[0]なども直します。必須列の項目名は元表の見出しから拾いますが、列名が埋まっていれば正しい列を指定したことになるわけではありません。設定表と実際の内容を照合してください。

この例の一覧は、元行番号、管理番号、案件名、未入力項目、担当者、納期の順です。列を移動した際は、設定表の位置、requiredColumns、list.push、出力見出しの4か所を合わせて見直しましょう。基準列を変えた場合も、その列から正しい識別情報を表示するように修正します。変更日、担当者、試した元行番号と管理番号を残すと、後から違いを調べられます。

空欄、数式の空文字、未使用行は分けて判定する

getValues()で読んだ値を判定に使い、trim()によって前後の空白を除いています。空セル、スペースだけのセル、数式の結果が空文字のセルは未入力です。一方、数値の0や論理値のfalseは、空欄とは扱いません。日付として不適切な文字や数式エラーでも、空文字でなければこの検査を通る点にも注意してください。入力値の妥当性は別の検査になります。

サンプルでは、判定用のgetValues()とは別に、表示用のgetDisplayValues()も読みます。こうすると、管理番号の先頭のゼロや納期の表示形式を、一覧でも文字列として残せます。ただし、表示書式で値を隠している場合は、値が入っていても一覧では空欄に見えることがあります。表示と判定の役割を分け、実際の表で両者が食い違わないか試しましょう。

未使用行を除く条件に、探したい必須列の空欄を使うと、実データとの区別がつかなくなります。そこで、管理番号などの基準列を別に置きます。ただし、その列が空の案件を漏らさない仕組みも併せて考えてください。たとえば登録途中の案件を別の入力一覧で追うなど、抽出結果に出ない行を「入力済み」と受け取らない運用にします。

別ファイルで初回試験を行い、日常更新の方法を決める

まず別ファイルへ複製した表で試します。対象ファイルに紐づくApps Scriptへコードを保存し、関数createMissingListを選んで実行してください。既存の同名関数がある場合は、そのまま重ねず内容を調べます。初回の権限画面ではシート操作の範囲を読み、このサンプルが元表を読んで出力先を書き換える処理であることを把握してから進めましょう。

• 管理表のコピーを作り、設定表どおりにシート名と列位置を入力する
• コードを保存し、createMissingListを手動で実行する
• 初回の権限画面で操作範囲を読み、元表の読み取りと出力先の書き換えを把握する
• 未入力一覧の管理番号・元行番号・項目名を元表と照合する
• 本番で使う前に、元表を修正する担当者と一覧を照合する担当者を決める

出力で使うclearContents()は、出力先シート全体の内容を消して書式を残す処理です。その後の書き込みで失敗すると、前の一覧が消えたままになることもあります。したがって、空になった一覧だけを見て該当案件がないとは判断しません。実行の完了ログと出力を併せて読み、結合セルや保護などがある出力先はコピーで試してください。手入力の記録は別シートへ残します。

実行頻度は手動、メニュー、定時更新から選ぶ

条件が固まるまでは、担当者が入力の区切りで手動実行すると試しやすくなります。入力のたびに更新すると、書きかけの行も並んでしまうためです。カスタムメニューから更新する方法もありますが、下の参考資料を使って別途実装する部分であり、このコードだけではメニューは作られません。まず通常の手動実行で結果を読める状態にしましょう。

毎朝の一覧を使うなら、試験後に時間主導型トリガーを追加する方法があります。ただし、厳密な時刻の実行や、一覧の完成通知まではこのコードで行いません。作成者のアカウントで動くため、担当交代時には旧トリガーの停止、後任者の権限と再設定、実行履歴の点検を引き継ぎます。後任者から旧担当者のトリガーが見えない場合もあるので、作成者と設定内容を記録しておきましょう。

抽出結果が空になる・関係ない行が出るときの点検表

出力が予想と違うときは、まず下の表に沿って設定と元データを調べます。シート名、開始行、列位置を変えた直後は、とくに照合が欠かせません。元表から代表の1行を選び、管理番号、判定対象の値、期待する一覧を記録し、実際の結果と比べてください。エラーがないことと、狙った列を調べていることは別です。

症状	調べる箇所	対応
一覧が「該当なし」になる	シート名、開始行、基準列に値があるか	全角・半角や余分な空白を含めて設定を照合する
対象の行が出ない	基準列が空になっていないか	別の基準列を使うか、管理番号を先に採番する
関係ないひな形行が出る	基準列の指定	実データで必ず埋まる列を基準列にする
確認待ちが出ない	waitingValuesの表記	入力表記をそろえるか、対象の表記を配列へ追加する
エラーで停止する	元表/出力先、行列の設定、見出し、権限や保護	列の追加・削除後に設定表と列番号を照合する
メモまで消える	出力先と消去範囲	一覧専用シートに分け、手入力を置かない

サンプルは、書き込み完了後に抽出件数をログへ出します。途中の件数を調べるなら、Logger.log(result.length)をconst writeDataの前に追加する方法もあります。ただし、途中の件数だけでは出力が成功した証拠になりません。実行の完了と一覧の内容まで照合し、検証用の出力を残す場合は通常の運用ログと区別してください。

未入力の修正後は重複データも点検する

対象行は、元表を修正した後に再実行すると一覧から外れます。ただし、これは指定した空欄と待ち状態がなくなったという意味で、納期や担当者の内容まで正しいと判定したわけではありません。担当者は管理番号から元の行へ戻り、入力元の資料とも照合してください。実行結果の件数を減らすこと自体を目的にしない運用が大切です。

このコードには、管理番号の重複を検出する処理はありません。同じ番号が別行に付いていると、一覧から元表へ戻る際に迷うため、スプレッドシートで重複データを安全に見つける方法も使って別途点検しましょう。未入力の修正と番号の取り違えは分けて扱い、どの記録に対応したかを残すと引継ぎやすくなります。

よくある質問

ここでは、複数の空欄、数式、再実行、並べ替え、定期更新の扱いを説明します。一覧へ出る条件が分かったら、修正する人と結果を読む人の役割も決め、自社の入力手順へ組み込んでください。

必須項目が複数あるとき、未入力項目を1つのセルにまとめて表示できますか？

はい。必須列ごとに空欄や待ち状態を調べ、見出し名をjoin('、')で連結しています。担当者と納期が空なら「担当者、納期」と表示されるため、どの欄を埋めるかが分かります。ただし、表示する順番は設定した必須列の順です。入力の優先順位や処置完了を示す順序ではありません。

数式で空欄に見えるセルを、未入力として判定するにはどうすればよいですか？

サンプルではgetValues()で数式の結果を読み、その結果が空文字なら未入力にします。一覧へ載せる管理番号や納期にはgetDisplayValues()の表示文字列を使います。両者を一律に置き換えると判定まで変わるため、どちらを変えるのかを分けて検討してください。数式エラーや内容の誤りも検出したい場合は、別の条件を追加して試します。

未入力一覧に表示された行を修正した後、一覧から自動で消すにはどうしますか？

元表を直した後、createMissingListを再実行します。このコードだけでは、編集と同時に一覧が更新されるわけではありません。実行が成功すれば、条件から外れた行は次の一覧に出なくなります。元表の行を削除する処理はないため、元の記録はそのまま残ります。

元データの行を並べ替えても、未入力一覧は使えますか？

再実行すれば、新しい並びの元行番号で一覧を作れます。ただし、前回の一覧の行番号は古くなるため、修正対象は管理番号から探してください。番号が重複していた場合は、そのまま推測して書き換えず、登録元の資料を調べて対象を特定します。案件名が似ているだけで同じ行と扱わないことも大切です。

時間主導トリガーで一覧を更新する場合、誰の権限で実行されますか？

トリガーを作成したアカウントの権限で実行されます。シートを開いている人の権限へ自動で切り替わるわけではありません。担当者や共有範囲が変わった際は、作成者、利用権限、設定内容を引き継ぎ、後任者が実行結果を読めるかまで試してください。

参考にした公式情報

以下は、値と表示文字列の読み取り、まとめての書き込み、メニュー、定期実行を調べるGoogle公式資料です。出力先の内容消去はSheetの公式資料（https://developers.google.com/apps-script/reference/spreadsheet/sheet）、重複実行の排他はLockServiceの資料（https://developers.google.com/apps-script/reference/lock/lock-service）も参照しました。ローカルの模擬試験だけでは、導入先の保護や結合セル、表示書式を実証できないため、コピーしたシートで試してから使います。

• Class Range | Apps Script | Google for Developers
• Spreadsheet Service | Apps Script | Google for Developers
• Custom Menus in Google Workspace | Apps Script | Google for Developers
• Installable Triggers | Apps Script | Google for Developers

まとめ

まず基準列、必須列、待ち状態の表記、一覧専用の出力先を決めましょう。その後、別ファイルで管理番号と未入力項目、表示形式、再実行の結果を照合します。運用後は、件数だけでなく、基準列が空のため漏れた案件や入力途中の扱いにも目を向けてください。列を変えたら設定と表示位置を合わせて更新し、元資料から修正した内容を追える一覧として使います。