GAS Webアプリでスプレッドシートの顧客マスタをプルダウン表示する
Google Apps Script（GAS）のWebアプリで、スプレッドシートの 顧客マスタ から有効な顧客を読み込み、HTMLフォームのプルダウンに表示します。表示順もマスタで管理し、選んだ顧客コードと顧客名を作業依頼へ登録します。
ここで扱うのは、スプレッドシートのセルに設定する入力規則のプルダウンではありません。Webアプリ画面のHTML <select> を、google.script.run で取得したデータから作る処理です。
シリーズ全体の構成と完成画面は第0回で紹介しています。
1. 今回作るもの
| ファイル | 用途 |
|---|---|
Code.gs | getActiveCustomers() を追加する |
index.html | 顧客プルダウンの初期表示を読み込み中にする |
script.html | 顧客マスタを読み込み、プルダウンへ追加する |
2. 完成イメージ
顧客マスタシートから顧客を取得できる有効区分が有効の顧客だけを表示できる表示順の昇順でプルダウンに表示できる- 顧客コードと顧客名を作業依頼シートへ保存できる
- 有効な顧客がいない場合に画面へメッセージを表示できる
- 読み込みに失敗した場合は、空データの場合と分けてエラーを表示できる
3. 第5回のフォームを引き継ぐ
第5回では固定の顧客選択肢を使ってフォームから作業依頼を登録しました。今回の starter.zip は、その時点のファイルです。saveRequest(data) による登録処理は引き継ぎ、顧客欄の選択肢を顧客マスタから読み込む処理を追加します。フォームを開き直すと、更新したマスタの内容が選択肢に反映されます。
4. 使用するファイル
今回使用するファイルは Code.gs、index.html、style.html、script.html の4つです。
5. 顧客マスタの構成
| 項目 | 用途 |
|---|---|
| 顧客コード | 作業依頼シートへ保存するコード |
| 顧客名 | プルダウンに表示し、作業依頼シートへ保存する名称 |
| メールアドレス | 後続のメール送信で使う項目。今回は画面に表示しない |
| 表示順 | プルダウンの並び順 |
| 有効区分 | 有効 の顧客だけを表示する |
メール送信は第12回で扱います。今回の getActiveCustomers() はメールアドレスも返しますが、画面では表示も利用もしません。実データを使う場合は、画面側で使わない情報をサーバーから返す必要があるかも確認してください。
顧客マスタを作業依頼シートと分ける理由や、シートの列構成については第2回の記事で説明しています。
6. 顧客マスタを読み込むコード
Code.gs
function getActiveCustomers() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('顧客マスタ');
if (!sheet || sheet.getLastRow() < 2) {
return [];
}
const values = sheet.getDataRange().getValues();
const headers = values[0];
const rows = values.slice(1);
const columnMap = createHeaderMap_(headers);
return rows
.map((row) => ({
customerCode: row[columnMap['顧客コード']],
customerName: row[columnMap['顧客名']],
email: row[columnMap['メールアドレス']],
sortOrder: Number(row[columnMap['表示順']] || 0),
activeFlag: row[columnMap['有効区分']],
}))
.filter((customer) => customer.customerCode && customer.customerName && customer.activeFlag === '有効')
.sort((a, b) => a.sortOrder - b.sortOrder)
.map((customer) => ({
customerCode: customer.customerCode,
customerName: customer.customerName,
email: customer.email || '',
}));
}
function createHeaderMap_(headers) {
return headers.reduce((map, header, index) => {
map[header] = index;
return map;
}, {});
}index.html
<select id="customer" name="customer" required> <option value="">読み込み中です</option> </select>
script.html
画面表示時に顧客マスタを読み込み、プルダウンへ追加します。script.html には、次の3か所を追加します。
1. 変数の宣言
既存の変数宣言に、顧客プルダウンの要素を追加します。
const customerSelect = document.getElementById('customer');2. 画面表示時の呼び出し
変数宣言の直後に、読み込み処理を呼び出します。この1行がないと、プルダウンは「読み込み中です」のまま変わりません。
loadCustomers();
3. 関数の追加
function loadCustomers() {
customerSelect.disabled = true;
showMessage('顧客マスタを読み込んでいます。', 'info');
google.script.run
.withSuccessHandler(renderCustomerOptions)
.withFailureHandler(handleCustomerLoadFailure)
.getActiveCustomers();
}
function renderCustomerOptions(customers) {
customerSelect.innerHTML = '';
const defaultOption = document.createElement('option');
defaultOption.value = '';
defaultOption.textContent = '選択してください';
customerSelect.appendChild(defaultOption);
if (!customers || customers.length === 0) {
customerSelect.disabled = true;
showMessage('有効な顧客が登録されていません。顧客マスタを確認してください。', 'error');
return;
}
customers.forEach((customer) => {
const option = document.createElement('option');
option.value = customer.customerCode;
option.dataset.name = customer.customerName;
option.textContent = customer.customerName;
customerSelect.appendChild(option);
});
customerSelect.disabled = false;
showMessage('入力後に登録ボタンを押してください。', 'info');
}
function handleCustomerLoadFailure(error) {
customerSelect.innerHTML = '<option value="">読み込みに失敗しました</option>';
customerSelect.disabled = true;
showMessage(error && error.message ? error.message : '顧客マスタの読み込みに失敗しました。', 'error');
}7. 顧客マスタ取得からプルダウン表示まで
シートの値を一度に読み込む
getActiveCustomers() は、getDataRange().getValues() で顧客マスタの値を二次元配列としてまとめて取得します。配列の values[0] が見出し行で、values.slice(1) が顧客データの行です。シートとのやり取りをまとめ、取得後の絞り込みや並べ替えはJavaScriptの配列処理で行います。Apps Script公式ガイドでも、読み書きの回数を抑え、データを配列にまとめて処理する方法が案内されています。
ヘッダー名から列の位置を調べる
createHeaderMap_(headers) は、1行目のヘッダー名をキー、列番号（0始まり）を値とするオブジェクトを作ります。たとえば、顧客コード が左から何列目にあっても、row[columnMap['顧客コード']] と書けば同じ列を参照できます。列の順番ではなくヘッダー名を手がかりにできるため、マスタの列を並べ替えてもコード側の列番号を書き換えずに済みます。
このコードは 顧客コード、顧客名、メールアドレス、表示順、有効区分 のヘッダー名を使います。列名を変更した場合は、コード側のキーと一致しなくなるため、シートとコードの両方を確認してください。このヘルパー関数は、第8回以降の一覧表示や検索でも再利用します。
有効な顧客を絞り、表示順に並べる
map() で各行を customerCode、customerName などの項目を持つオブジェクトに変換した後、filter() で必要な行だけ残します。条件は、顧客コードと顧客名が入力され、有効区分 が文字列 有効 と一致することです。有効 以外の行や、コード・顧客名が空の行はプルダウンに渡しません。
続く sort() は、表示順 の小さい順に顧客を並べ替えます。Number(row[...] || 0) により、表示順が空欄の場合は 0 として扱います。意図した順に表示するには、マスタの 表示順 に数値を入力してください。
最後の map() では、画面へ返す項目を顧客コード、顧客名、メールアドレスに絞り、メールアドレスが空欄なら空文字にします。画面の <option> では顧客コードを value に、顧客名を表示文字列と data-name に使います。このため、フォーム送信時には選択中の顧客コードと顧客名を saveRequest(data) に渡せます。
取得結果をプルダウンへ反映する
画面を開くと loadCustomers() が google.script.run から getActiveCustomers() を呼び出します。この呼び出しは非同期なので、ブラウザーはサーバー側の処理が終わるのを待たずに画面の処理を続けます。結果は withSuccessHandler() または withFailureHandler() に登録した関数で受け取ります。詳しくはHTML Serviceの通信に関する公式ガイドを参照してください。
成功時は renderCustomerOptions(customers) が <option> を作ります。返却値が空配列なら、シートがない、データ行がない、有効な顧客がない、または必要な列名が一致していない可能性があります。このとき選択欄を無効にし、マスタの確認を促します。一方、サーバー処理そのものが失敗した場合は handleCustomerLoadFailure(error) が呼ばれ、別のエラーメッセージを表示します。この二つを分けることで、マスタの内容確認が必要なのか、処理エラーの調査が必要なのかを判断しやすくなります。
loadCustomers(); は関数を定義するだけでは動きません。画面の初期化時にこの呼び出しを実行して、顧客取得処理を開始します。
8. 導入手順
- 対象のスプレッドシートから開いたApps Scriptプロジェクトに、第5回までのファイルを用意する
Code.gsにgetActiveCustomers()を追加するindex.htmlの顧客プルダウンを読み込み中表示に変更するscript.htmlに顧客マスタ読込処理を追加する（変数宣言・呼び出し・3つの関数）- 保存する
- まだシートを作成していない場合は、第2回のコードで
setupAppSheets()を実行し、顧客マスタに5つのヘッダーとサンプル行があることを確認する - Webアプリのテストデプロイを開き、画面を確認する
既存のWebアプリURLで変更を確認する場合は、変更を保存するだけではなく、デプロイを管理画面から新しいバージョンへ更新してください。Apps Scriptでは、デプロイが特定のバージョンを使う仕組みです。詳しい手順はApps Script公式ガイドと第4回のデプロイ手順を参照してください。
9. 動作確認
画面を開き、顧客プルダウンに 有効 な顧客だけが表示されることを確認します。
第2回のサンプルデータでは、サンプル商事 と テスト工業 が表示され、デモサービス は 無効 のため表示されません。
登録後、作業依頼 シートに顧客コードと顧客名が保存されていれば成功です。
10. よくあるエラー
顧客が表示されない
顧客マスタ シートがあるか、1行目が 顧客コード、顧客名、メールアドレス、表示順、有効区分 になっているか確認してください。コードはヘッダー名をキーに列を参照するため、表記が一致しないと期待どおりに取得できません。
有効な顧客がないと表示される
有効区分 が 有効 になっている顧客があるか確認してください。
並び順が想定どおりにならない
表示順 に数値が入っているか確認してください。空欄は 0 として扱われるため、番号を付けて並べたい顧客には数値を入力します。
getSheetByName を呼び出す箇所でエラーになる
TypeError: Cannot read properties of null (reading 'getSheetByName') が出る場合は、SpreadsheetApp.getActiveSpreadsheet() が null を返しています。公式リファレンスにも、アクティブなスプレッドシートがない場合は null を返すと記載されています。このサンプルは対象のスプレッドシートに紐づいたApps Scriptプロジェクトを前提にしています。スプレッドシートを開き、メニューの「拡張機能」からApps Scriptを開いたプロジェクトにコードを配置しているか確認してください。単独のApps Scriptプロジェクトから使う場合は、対象ファイルをIDで開く処理が別途必要です。
11. starter.zipの案内
starter.zip には第5回完了時点のファイルを入れています。顧客選択肢はまだ固定のサンプル値なので、今回のコードを追加して差し替えてください。
12. complete.zipの案内
complete.zip には、Code.gs、index.html、style.html、script.html、README.txt を入れています。
13. 次回追加する機能
次回は、受付番号を自動採番します。今回の顧客マスタ連携は、作業依頼を登録する前に顧客コードと顧客名を選べるようにする処理です。次回は、その依頼データを識別する受付番号をサーバー側で作ります。
PropertiesService と LockService を使い、同時登録でも重複しにくい受付番号を作ります。
Your Message