GASでスプレッドシートを簡易データベースとして使う設計方法
GAS Webアプリのデータ保存先として、Googleスプレッドシートを簡易データベースとして使い、6つのシートを作成します。
第1回では、Apps Scriptからスプレッドシートにテストデータを書き込めることを確認しました。今回は、今後のデータ登録や検索、PDF作成、メール送信、ログ記録に使う6つのシートを作ります。
1. 今回作るもの
ここでは、作業依頼管理Webアプリで使うデータ保存用のシートを作ります。
Apps Scriptエディタで setupAppSheets() を実行すると、次の6シートを作成します。
| シート名 | 用途 |
|---|---|
| 作業依頼 | 依頼内容と対応状況を記録 |
| 顧客マスタ | フォームで選ぶ顧客情報 |
| 設定 | PDF保存先やメールに使う値 |
| 操作ログ | 登録、更新、削除などの履歴 |
| エラーログ | 発生したエラーの記録 |
| PDFテンプレート | 受付票PDFのテンプレート |
画面や登録処理は今回扱わず、データを保存するシートを先に用意します。
2. 完成イメージ
この回で作成するものは次のとおりです。
setupAppSheets()を実行できる- 作業依頼管理アプリ用の6シートが作成される
- 各シートの1行目にヘッダーが入る
- 作業依頼、顧客マスタ、設定、PDFテンプレートに学習用サンプル行が入る
- 操作ログとエラーログはヘッダーだけ作成される
第3回以降では、今回作った 作業依頼 シートへデータを登録していきます。
3. 前回までの状態
第1回では、Code.gs に setupTestSheet() を作成し、初回テスト シートへテストデータを書き込めることを確認しました。
第2回の starter.zip には、第1回完了時点の Code.gs を入れています。
4. 使用するファイル
今回は Code.gs を使います。
| ファイル | 用途 |
|---|---|
Code.gs | シート作成用のGASコード |
HTMLファイルはまだ使いません。ブラウザ画面は第4回以降で作ります。
5. 簡易データベースとしてのスプレッドシート構成
GASでスプレッドシートを簡易データベースとして使う場合は、最初に各シートへ何を保存するかを決めておくと、後から書くコードの動作が安定します。
作業依頼
作業依頼 は、アプリの中心になるシートです。
| 列 | 項目 | 内容 |
|---|---|---|
| A | 内部ID | システム内で依頼を区別するID |
| B | 受付番号 | 利用者に見せる番号 |
| C | 登録日時 | 新規登録した日時 |
| D | 更新日時 | 最後に更新した日時 |
| E | 顧客コード | 顧客マスタのコード |
| F | 顧客名 | 登録時点の顧客名 |
| G | 件名 | 作業依頼の件名 |
| H | 依頼内容 | 作業内容の詳細 |
| I | ステータス | 未対応、対応中、完了 |
| J | 担当者 | 対応担当者 |
| K | PDF URL | 作成した受付票PDFのURL |
| L | メール送信日時 | 登録完了メールを送った日時 |
| M | 削除区分 | 論理削除の判定に使う値 |
内部IDと受付番号は役割が異なります。内部IDは、システム内で各作業依頼を区別するために使います。受付番号は、利用者が問い合わせや確認に使う表示用の番号です。
顧客マスタ
顧客マスタ は、入力フォームのプルダウンに使います。
依頼を登録するたびに顧客名やメールアドレスを入力すると、表記ゆれや入力ミスが起きやすくなります。顧客情報は 顧客マスタ でまとめて管理します。
登録時は、顧客コードと顧客名を 作業依頼 シートに保存します。後から顧客マスタの顧客名を変更しても、登録時点の名前を作業依頼シートに残せます。
設定
設定 には、運用担当者が変更する値を置きます。
たとえば、メールの差出人名、テスト送信先、デモモードなどです。
フォルダID、APIキー、パスワードなど、公開したくない値は記事内のコードに直接書きません。機密性の高い値は、Script Propertiesなどで管理します。
操作ログとエラーログ
操作ログ には、登録、更新、削除、PDF作成、メール送信などの履歴を記録します。
エラーログ には、エラーが起きた処理名やエラーメッセージを記録します。
第2回ではシートだけを用意し、ログを書き込む処理は後半の記事で実装します。
PDFテンプレート
PDFテンプレート は、受付票PDFの元になるシートです。
PDF作成処理は第11回で扱います。今回は、受付番号、顧客名、件名、依頼内容などを記入する欄を用意します。
6. 貼り付けるコード
Apps Scriptエディタの Code.gs に、次のコードを貼り付けます。
function setupTestSheet() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = '初回テスト';
const sheet = spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);
sheet.clear();
sheet.getRange(1, 1, 1, 2).setValues([['実行日時', 'メッセージ']]);
sheet.getRange(2, 1, 1, 2).setValues([[new Date(), 'GASから書き込みました']]);
sheet.autoResizeColumns(1, 2);
Logger.log('初回テストシートへ書き込みました。');
}
function setupAppSheets() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetDefinitions = [
{
name: '作業依頼',
headers: [
'内部ID',
'受付番号',
'登録日時',
'更新日時',
'顧客コード',
'顧客名',
'件名',
'依頼内容',
'ステータス',
'担当者',
'PDF URL',
'メール送信日時',
'削除区分',
],
sampleRows: [
[
'sample-request-001',
'20260624-001',
new Date(),
new Date(),
'C001',
'サンプル商事',
'資料作成の依頼',
'社内会議用の説明資料を作成する',
'未対応',
'山田',
'',
'',
'',
],
],
},
{
name: '顧客マスタ',
headers: ['顧客コード', '顧客名', 'メールアドレス', '表示順', '有効区分'],
sampleRows: [
['C001', 'サンプル商事', 'customer01@example.com', 1, '有効'],
['C002', 'テスト工業', 'customer02@example.com', 2, '有効'],
['C003', 'デモサービス', 'customer03@example.com', 3, '無効'],
],
},
{
name: '設定',
headers: ['設定キー', '設定値', '説明'],
sampleRows: [
['PDF_FOLDER_ID', '', 'PDF受付票の保存先フォルダID。実IDは記事内コードには書かない。'],
['MAIL_FROM_NAME', '作業依頼受付', '登録完了メールの差出人名。'],
['MAIL_CC', '', '必要に応じてCC候補を設定する。'],
['TEST_MAIL_TO', 'test@example.com', 'テスト送信先。実在する宛先へ送る前に必ず確認する。'],
['DEMO_MODE', 'TRUE', '公開デモ用の制限モード。'],
],
},
{
name: '操作ログ',
headers: ['操作日時', '処理名', '対象ID', '変更前データ', '変更後データ', '実行ユーザー'],
sampleRows: [],
},
{
name: 'エラーログ',
headers: ['発生日時', '処理名', 'エラーメッセージ', 'スタックトレース', '入力データの概要'],
sampleRows: [],
},
{
name: 'PDFテンプレート',
headers: ['項目', '値'],
sampleRows: [
['受付番号', ''],
['登録日時', ''],
['顧客名', ''],
['件名', ''],
['依頼内容', ''],
['ステータス', ''],
['担当者', ''],
],
},
];
sheetDefinitions.forEach((definition) => {
const sheet = getOrCreateSheet_(spreadsheet, definition.name);
setupSheet_(sheet, definition.headers, definition.sampleRows);
});
Logger.log('作業依頼管理アプリ用のシートを作成しました。');
}
function getOrCreateSheet_(spreadsheet, sheetName) {
return spreadsheet.getSheetByName(sheetName) || spreadsheet.insertSheet(sheetName);
}
function setupSheet_(sheet, headers, sampleRows) {
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
sheet.setFrozenRows(1);
sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
sheet.autoResizeColumns(1, headers.length);
if (sheet.getLastRow() === 1 && sampleRows.length > 0) {
sheet.getRange(2, 1, sampleRows.length, headers.length).setValues(sampleRows);
}
}7. コードの解説
setupAppSheets() が今回のメイン関数です。
シート名、ヘッダー、サンプル行は sheetDefinitions 配列に定義します。forEach で定義を1件ずつ処理し、setupSheet_() でシートを設定します。
getOrCreateSheet_() は、同じ名前のシートがあれば再利用し、なければ新しく作ります。そのため、関数を再実行しても同じ名前のシートを重複して作りません。
setupSheet_() は、1行目にヘッダーを設定して固定し、列幅を自動調整します。
コードは、シートの最終行が1行目のときだけサンプル行を追加します。関数を再実行したときに、既存データの下へサンプル行が重複するのを防ぐためです。
8. 導入手順
- 第1回で作成した学習用スプレッドシートを開く
- メニューから
拡張機能、Apps Scriptを開く Code.gsに今回のコードを貼り付ける- 保存する
- 関数一覧から
setupAppSheetsを選ぶ - 実行する
- 権限承認が表示された場合は、内容を確認して承認する
初回実行時は、スプレッドシートを操作する権限が必要です。学習用のスプレッドシートで実行していることを確認してから承認してください。
9. 動作確認
実行後、スプレッドシート下部のシートタブを確認します。
次の6シートがタブに表示されていれば、作成できています。
- 作業依頼
- 顧客マスタ
- 設定
- 操作ログ
- エラーログ
- PDFテンプレート
初回テスト は第1回のシートで、今回の6シートには含まれません。各シートの1行目にヘッダーが設定されていることも確認します。
作業依頼、顧客マスタ、設定、PDFテンプレート には、学習用のサンプル行が入ります。ログ記録の処理はまだ実装していないため、操作ログ と エラーログ にはヘッダーだけが入ります。
10. よくあるエラー
setupAppSheets が関数一覧に出ない
コードを保存していない可能性があります。保存後に関数一覧をもう一度確認してください。
シートが作成されない
Apps Scriptエディタを、対象のスプレッドシートから開いているか確認してください。
サンプル行が追加されない
この関数は、既にデータがあるシートにはサンプル行を追加しません。
関数を繰り返し実行したときに、サンプル行が重複するのを防ぐためです。ヘッダーが入っていれば、シート構成の作成は完了しています。
実際のフォルダIDやメールアドレスをどこに入れるか分からない
第2回では、実際のフォルダIDや本番用のメールアドレスは設定しません。PDF保存先やメール送信は後半の記事で扱います。機密性の高い値は、記事内のコードや公開ZIPに含めないようにします。
11. starter.zipの案内
starter.zip には、第1回完了時点の Code.gs と README.txt を入れています。
第1回の環境から第2回を進める場合は、starter.zip を展開し、Code.gs にこの記事のコードを貼り付けてください。
12. complete.zipの案内
完成版の complete.zip には、次のファイルが入っています。
Code.gsREADME.txt
Code.gs には、第1回の setupTestSheet() と、第2回の setupAppSheets() が入っています。
13. 次回追加する機能
次回は、今回作成した 作業依頼 シートに、GASから作業依頼を1件登録します。
内部IDには Utilities.getUuid() を使います。登録日時と更新日時も自動で記録します。
Your Message