動的 Google フォーム！- Google カレンダー を使わない！！三者面談予約 Ver.3 & Ver.4
今回は、動的な Google フォーム を連携させて使うシステムです！！
※最後に、なんと、課題があります🤣笑
かなり運用度が上がっていると思いますので、参考にしてください！
今回もめっちゃ長いので、最初に動画を上げておきます！
前回までの記事を合わせてご覧ください！
（ちょっと前置きが多いです、すみません！🙇♂️）
また、今回は、こちらで紹介しているものを使っています！
上記で、参考にさせていただいているサイトはこちらです。
本題と関係ないんですが、ChatGPT を使ってプログラミングすることが多いんですけど、色々試してたら、こんな結果が出てきたんですよ…。
上が、これまで上記の管理人様に連絡をして使っていたもの。
下が、ChatGPT から返ってきたもの。
コメントまで同じ🤣笑
2023年2月26日現在で、2021年までのデータでやっているっぽいから、このウェブサイト（それか Git ?）も学習されているんですね。
こうやってコードも学習されて使われるんですね。
そりゃいろんなところで声を上げる人多い訳ですね🤔
公開する情報は、考えないといけないのかもしれませんね。
note ならここから下は購入者限定！とか。
話が逸れました。
それでは本題。
[1] Google フォーム の作成
回答は、「メールアドレスを収集」をオンにしてください！
そして、最初に「タイトルと説明を追加」から
こんな感じで予約枠の一覧を作りましょう！
3/21(火) 9:00〜9:50 ： 残り 1 名
3/21(火) 10:00〜10:50 ： 残り 1 名
3/21(火) 11:00〜11:50 ： 残り 1 名
3/22(水) 9:00〜9:50 ： 残り 1 名
3/22(水) 10:00〜10:50 ： 残り 1 名
3/22(水) 11:00〜11:50 ： 残り 1 名
3/23(木) 9:00〜9:50 ： 残り 1 名
3/23(木) 10:00〜10:50 ： 残り 1 名
3/23(木) 11:00〜11:50 ： 残り 1 名
この時、日付と時間の間は、半角スペースを入れてください！！
3/22(水) 11:00〜11:50 ： 残り 1 名
よく確認をしていただくと、3/22(水)と11:00〜11:50の間にスペースを入れてあります。
この後のプログラムで、このスペースで分割してカレンダーを認識するように作っています！！
希望日時という質問を追加して、プルダウンにして、選択肢を入れましょう！
（今回は、プルダウンとして動くように作ってあります）
この時、先ほどの予約枠一覧をコピペした方が早いし確実です！
何が確実かというと、、、、
これから、この選択肢の解答からスプレッドシートに入力していくんですが、それぞれの文字列（今回で言うと3/22(水) 11:00〜11:50とか）は、スペースも含めて、完全に一致していないといけないのです🤓
あとは、前回までの記事と一緒です！
別にセクションは分けなくても良いのですが、なんとなく分けてます笑
あとは、回答のスプレッドシートを作成しておきましょう！
[2] スプレッドシートの準備
スプレッドシートには、このようにシートを追加してください。
教師カレンダー
日程
予約
教師カレンダーのシートは、↓のように、フォームでしてある日時を表にしておきます！
日程のシートは、↓のように、日程、定員、予約済の列を作り、定員と現在の予約数を入力する欄を作ります。
この時の日程は、フォームの選択肢と一致していないといけないので、フォームのテキストからコピペした方が良いです！
予約のシートは、↓のように一覧を転記するシートになっています。
こんな感じで準備してください！
ここまででも大分疲れますね😅笑
もう少しがんばりましょう！！！！💪
[3] スクリプトの準備
今回大事なのは、この Google フォーム にスクリプトを入れる！
と言うことです。
今回は、どこか希望日時が入力されたら、その日時は選択できないようにします。
よって、Google フォーム を編集し直す必要があるんです！
右上にある３点リーダー（・・・）から、「スクリプト エディタ」をクリックしましょう！
追加するスクリプトはこちらです。
const form = FormApp.getActiveForm();
const spreadSheet = SpreadsheetApp.openById('');//ここは、スプレッドシートのIDを入れる
const reserveSheet = spreadSheet.getSheetByName('予約');
const listSheet = spreadSheet.getSheetByName('日程');
const calender = spreadSheet.getSheetByName('教師カレンダー');
// *--------------------*
// フォーム送信時の処理
// *--------------------*
function receivedApplication(e) {
// フォームの送信内容
const email = e.response.getRespondentEmail();
const items = e.response.getItemResponses();
const preferredDate = (items[0].getItem().getTitle() === "希望日時" ? items[0].getResponse() : "");
let result = "";
if (preferredDate) {
// 日程シートチェック＆更新
result = checkAvailability(preferredDate);
// 予約シートに申込内容を書き込み
if (result = "OK") { reserveSheet.appendRow([email, preferredDate]); }
// フォームを更新
editForm();
} else {
result = "NG";
}
// メール送信
sendEmail(email, preferredDate, result);
}
// *---------------------*
// 予約状況をチェックする
// *---------------------*
function checkAvailability(preferredDate) {
// listシートを配列に格納
const list = listSheet.getDataRange().getValues();
list.shift();
// リストから予約日を探し、定員を確認する
for (let i = 0; i < list.length; i++) {
if (list[i][0] == preferredDate) {
// 予約済 < 定員であればOK
if (list[i][2] < list[i][1]) {
listSheet.getRange(i + 2, 3).setValue(list[i][2] + 1);
// 回答から日付と時間帯を取得する例
var data = calender.getDataRange().getValues();
var dateAndTime = preferredDate.split(" ");
var date = dateAndTime[0];
var time = dateAndTime[1];
Logger.log(date);
Logger.log(time);
//該当の日時を検索して、そのカラム番号、列番号を取得
for (var k = 0; k < data.length; k++) {
for (var j = 0; j < data[k].length; j++) {
if (data[k][j] == date) {
dataColumn = j + 1;
}
else if (data[k][j] == time) {
dataRow = k + 1;
}
}
}
calender.getRange(dataRow, dataColumn).setValue('予約あり');
return "OK";
} else {
return "NG";
}
}
}
// 希望日がリストに存在しなかった場合はNG
return "NG";
}
// *----------------------*
// 予約結果をメール送信する
// *----------------------*
function sendEmail(email, preferredDate, result) {
const mailTitle = "予約結果について";
let mailBody;
if (result == "OK") {
mailBody = "予約が完了しました。\n"
+ `予約日：${preferredDate}`
+ '\n\nよろしくお願いします。'
} else {
mailBody = "定員超過のため予約できませんでした。\n"
+ "下記のフォームから再度申請してください\n"
+ form.getPublishedUrl();
}
// 結果メール送信
GmailApp.sendEmail(email, mailTitle, mailBody);
}
// *-----------------*
// フォームを更新する
// *-----------------*
function editForm() {
let infoText = ""; // 「空き状況」部分のテキスト
let choiceValues = []; // 「参加希望日」部分の選択肢
// 日程シートを配列に格納
const list = listSheet.getDataRange().getValues();
list.shift();
// 日程シートのデータからフォームの内容を作成する
for (const record of list) {
if (record[2] < record[1]) {
// 空きがある日程は「空き状況」に記載＋選択肢として設定
infoText += `${record[0]} ： 残り ${record[1] - record[2]} 名\n`;
choiceValues.push(record[0]);
} else {
// 空きがない日程は「空き状況」に満員を記載＋選択肢にはしない
infoText += `${record[0]} ： 満員（申込不可）\n`;
}
}
// 選択肢が１つもない場合、選択肢は「全日程申込不可」としフォームをクローズ
if (!choiceValues.length) {
choiceValues.push("全日程申込不可");
form.setAcceptingResponses(false);
}
// フォームに変更を反映
const items = form.getItems();
items[0].setHelpText(infoText);
items[1].asListItem().setChoiceValues(choiceValues);
}これでOK！
あとは回答を収集していくスプレッドシートのIDを↓の ' ' の間に入れましょう！
const spreadSheet = SpreadsheetApp.openById('');//ここは、スプレッドシートのIDを入れるもう、いろんな記事を見ていただいている方は、IDの場所は大丈夫ですよね！？🤣笑
どうしてもと言う方は、末尾の動画をご覧ください！笑
（ちょっと楽しました、すみません笑）
それでは、💾（保存）をして、▶️（実行）をクリックしょましょう！
いつものように権限の承認ステップがあります！
そしたら次にトリガーの設定です！
トリガーは左側の⏰（トリガー）から！
このように、 recievedApplication の関数のまま、「イベントの種類」を「フォーム送信時」にして保存してください！
場合によって、ここでまた権限を許可してください的な警告が出てくるので、同じように許可をしてください！
[4] テスト
それではテストをしてみましょう！
フォームの「プレビュー」から、
希望日時を選択します。
あとはテキトーに。
送信すると….。
満員となり….。
選択肢からも外れていますね！😆
それぞれのシートはこんな感じになります！
良い感じですね！！😆
ぜひご活用ください！
ただですね、これ、何回かテストすると、元の状態に戻すのがめちゃくちゃ面倒なんです….。
そんな時は、、、。
リセット用スクリプト
回答を集約しているスプレッドシートにある機能を入れると楽に….。
こちらをコピペし、
function onOpen() {
SpreadsheetApp.getActiveSpreadsheet().addMenu('リセットメニュー', [
{ name: 'フォームの選択肢をリセット', functionName: 'updateFormList' },
{ name: 'フォーム内の回答全削除', functionName: 'alldelete' },
{ name: '日程・予約シートのリセット', functionName: 'cellrst' },
]);
}
function updateFormList() {
//フォームの質問番号
var questionNo = 2;
//フォームID
var formId = '';//フォームのIDを入れます
//シートID
var sheetId = '';//このスプレッドシートのIDを入れます。
//シート名
var sheetName = '日程';
//データ配置列名
var colName = 'A';
//データ開始行
var rowNum = 2;
//フォームの取得
var form = FormApp.openById(formId);
//全質問項目を取得
var items = form.getItems();
//questionNoで指定した質問項目を指定（配列は0から始まるので-1）
var item = items[questionNo - 1];
//スプレッドシートを取得
var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
//シートの最終行を取得
var lastRow = sheet.getLastRow();
//リスト内容の取得
var arrList = sheet.getRange(colName + rowNum + ':' + colName + lastRow).getValues();
let infoText = ""; // 「空き状況」部分のテキスト
// 日程シートを配列に格納
const list = sheet.getDataRange().getValues();
list.shift();
// 日程シートのデータからフォームの内容を作成する
for (const record of list) {
// 空きがある日程は「空き状況」に記載＋選択肢として設定
infoText += `${record[0]} ： 残り 1 名\n`;
}
//選択肢をフォームに反映
items[0].setHelpText(infoText);
items[1].asListItem().setChoiceValues(arrList);
}
function alldelete() {
var Form = FormApp.openByUrl('');//フォームの編集画面の時のURLを入れます。
Form.deleteAllResponses();
}
function cellrst() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht = ss.getSheetByName('日程');
var sht2 = ss.getSheetByName('予約');
var sht3 = ss.getSheetByName('教師カレンダー');
var lastrow = sht.getLastRow();
var lastrow2 = sht2.getLastRow();
var lastrow3 = sht3.getLastRow();
var lastcolumn3 = sht3.getLastColumn();
for (let i = 2; i <= lastrow; i++) {
sht.getRange(i, 3).setValue('0');
}
sht2.getRange(2, 1, lastrow2, 2).clearContent();//１行目、５列目から、、、１行目３列目までを削除
//スクリプトに紐づいたスプレッドシートのアクティブなシートを読み込み
sht3.getRange(2, 2, lastrow3, lastcolumn3).clearContent();
}下記にそれぞれのIDを入れる
//フォームID
var formId = '';//フォームのIDを入れます
//シートID
var sheetId = '';//このスプレッドシートのIDを入れます。ここに、Google フォーム を編集（作っている時の）している時の画面の URL を入れてください。
var Form = FormApp.openByUrl('');//フォームの編集画面の時のURLを入れます。これでOK！
先ほどと同じように、💾（保存）をして、▶️（実行）をクリックしょましょう！
もちろん、権限は許可してくださいね！
そうすると…。
こんな感じでリセットメニューが出てくるので、これを使えば楽に元の状態に戻せます👍✨
あれ、これもう相当有料級だと思うんですけど笑
Ver.1 の動画
Ver.2 の動画
Ver.3 の動画
Ver.4 について
Ver.4 は、この Ver.3 と Ver.2 を組み合わせた感じになっています！！
そのため、動画や、これまでボクがあげた動画を参考にして、ぜひ挑戦してください！😎
課題は
①保護者用のカレンダーを別のスプレッドシートにして表示する！
②教師用のカレンダーには児童の名前、保護者用のカレンダーには「予約あり」を入力する
です。
動画でも解説しているのと、
解答はこちらです！
const form = FormApp.getActiveForm();
const spreadSheet = SpreadsheetApp.openById("");
const reserveSheet = spreadSheet.getSheetByName("予約");
const listSheet = spreadSheet.getSheetByName("日程");
const calender = spreadSheet.getSheetByName("教師カレンダー");
//保護者カレンダーを作りたい時↓
const sheet = SpreadsheetApp.openById('').getSheetByName('保護者カレンダー');
// *--------------------*
// フォーム送信時の処理
// *--------------------*
function receivedApplication(e) {
// フォームの送信内容
const email = e.response.getRespondentEmail();
const items = e.response.getItemResponses();
const preferredDate = (items[0].getItem().getTitle() === "希望日時" ? items[0].getResponse() : "");
let result = "";
const name = items[3].getResponse();
if (preferredDate) {
// 日程シートチェック＆更新
result = checkAvailability(preferredDate, name);
// 予約シートに申込内容を書き込み
if (result = "OK") { reserveSheet.appendRow([email, preferredDate]); }
// フォームを更新
editForm();
} else {
result = "NG";
}
// メール送信
sendEmail(email, preferredDate, result);
}
// *---------------------*
// 予約状況をチェックする
// *---------------------*
function checkAvailability(preferredDate, name) {
// listシートを配列に格納
const list = listSheet.getDataRange().getValues();
list.shift();
// リストから予約日を探し、定員を確認する
for (let i = 0; i < list.length; i++) {
if (list[i][0] == preferredDate) {
// 予約済 < 定員であればOK
if (list[i][2] < list[i][1]) {
listSheet.getRange(i + 2, 3).setValue(list[i][2] + 1);
// 回答から日付と時間帯を取得する例
var data = calender.getDataRange().getValues();
var dateAndTime = preferredDate.split(" ");
var date = dateAndTime[0];
var time = dateAndTime[1];
Logger.log(date);
Logger.log(time);
//該当の日時を検索して、そのカラム番号、列番号を取得
for (var k = 0; k < data.length; k++) {
for (var j = 0; j < data[k].length; j++) {
if (data[k][j] == date) {
dataColumn = j + 1;
}
else if (data[k][j] == time) {
dataRow = k + 1;
}
}
}
calender.getRange(dataRow, dataColumn).setValue(name);
sheet.getRange(dataRow, dataColumn).setValue('予約あり');
return "OK";
} else {
return "NG";
}
}
}
// 希望日がリストに存在しなかった場合はNG
return "NG";
}
// *----------------------*
// 予約結果をメール送信する
// *----------------------*
function sendEmail(email, preferredDate, result) {
const mailTitle = "予約結果について";
let mailBody;
if (result == "OK") {
mailBody = "予約が完了しました。\n"
+ `予約日：${preferredDate}`
+ '\n\nよろしくお願いします。'
} else {
mailBody = "定員超過のため予約できませんでした。\n"
+ "下記のフォームから再度申請してください\n"
+ form.getPublishedUrl();
}
// 結果メール送信
GmailApp.sendEmail(email, mailTitle, mailBody);
}
// *-----------------*
// フォームを更新する
// *-----------------*
function editForm() {
let infoText = ""; // 「空き状況」部分のテキスト
let choiceValues = []; // 「参加希望日」部分の選択肢
// 日程シートを配列に格納
const list = listSheet.getDataRange().getValues();
list.shift();
// 日程シートのデータからフォームの内容を作成する
for (const record of list) {
if (record[2] < record[1]) {
// 空きがある日程は「空き状況」に記載＋選択肢として設定
infoText += `${record[0]} ： 残り ${record[1] - record[2]} 名\n`;
choiceValues.push(record[0]);
} else {
// 空きがない日程は「空き状況」に満員を記載＋選択肢にはしない
infoText += `${record[0]} ： 満員（申込不可）\n`;
}
}
// 選択肢が１つもない場合、選択肢は「全日程申込不可」としフォームをクローズ
if (!choiceValues.length) {
choiceValues.push("全日程申込不可");
form.setAcceptingResponses(false);
}
// フォームに変更を反映
const items = form.getItems();
items[0].setHelpText(infoText);
items[1].asListItem().setChoiceValues(choiceValues);
}ぜひ挑戦してみてください！
ポートフォリオ
YouTube チャンネル
いちばんやさしい Google Apps Script
何かと０から１を作るのは大変だと思います。学校はどこも似たような問題課題に対応していると思います。それなのに、先生って自分だけで頑張ろうとするんですよね。ボクの資料やnoteが１になって、学校ごとの現状に合わせてカスタムしていただければと思います‼️