[無料]AIといっしょにGoogleフォームでプログラミングしてみる
Google App Scriptという、Googleドキュメントを簡単なプログラムで連携できる仕組みがあります。一見意味が分からないのですが、やってみると簡単なので今日はこれをやっていきましょう。
先生は、毎度おなじみChatGPT先生。
AIモデルはGPT-4o miniがよいでしょう。無料版だと回数や文字列の制限があるので、煩わしかったら素直に課金しましょう。課金したらGPT-4oがいいです。miniより少し賢いです（その代わりちょっと回答が遅い）。
無料版と有料版の違いもAIに聞いてみましょう。分からないことはどんどん聞けばいい。
-------------以下ChatGPTの回答
無料版
モデルのアクセス: 主にGPT-3.5モデルにアクセスできます。（※筆者注：現在はGPT-4o miniを使えます。GPT-4o miniはまだ発表されたばかりでAIの知識に入っていないようです）
使用制限: 無料ユーザーには使用制限があり、アクセスが集中する時間帯には応答が遅くなることがあります。
応答速度: 有料版に比べて応答速度が遅くなることがあります。
有料版 (ChatGPT Plus)
モデルのアクセス: GPT-4モデルにアクセスできます。
使用制限の緩和: アクセスが集中する時間帯でもよりスムーズに使用できます。
応答速度: 無料版よりも応答速度が速くなります。
価格: 月額$20（2024年7月現在）で提供されています。
その他の違い
機能の追加: 新しい機能やアップデートが先行して提供されることがあります。
カスタマーサポート: 有料ユーザー向けのサポートがより充実していることがあります。
これらの違いにより、頻繁に使用するユーザーやより高性能なモデルを必要とするユーザーには、有料版が適しています。
---------------ここまで
GoogleAppSctiot(GAS)をやっていきましょう
質問：GoogleのGASを使いたい。教えて
回答
GASの基本的な使い方
1. スクリプトエディタの開き方
Google SheetsやGoogle Docsを開きます。
メニューから「拡張機能」>「Apps Script」を選択します。
2. 新しいスクリプトを作成する
スクリプトエディタが開いたら、新しいスクリプトファイルが自動的に作成されます。以下に簡単なサンプルコードを示します。
function myFunction() {
Logger.log('Hello, world!');
}3. スクリプトを実行する
スクリプトエディタの上部にある「実行」ボタンをクリックします。
初めてスクリプトを実行する場合、Googleアカウントの認証が求められます。指示に従って認証を完了します。
4. ログを確認する
スクリプトが実行されると、ログが生成されます。ログを確認するには、スクリプトエディタの「表示」>「ログ」を選択します。
GASでできること
1. スプレッドシートの操作
function writeToSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.getRange('A1').setValue('Hello, Google Sheets!');
}2. Gmailの操作
function sendEmail() {
GmailApp.sendEmail('example@example.com', 'Subject', 'Body of the email');
}3. Googleカレンダーの操作
function createEvent() {
var calendar = CalendarApp.getDefaultCalendar();
calendar.createEvent('Meeting', new Date(), new Date(new Date().getTime() + 1 * 60 * 60 * 1000));
}
4. カスタムメニューの作成
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Custom Menu')
.addItem('Show Alert', 'showAlert')
.addToUi();
}
function showAlert() {
SpreadsheetApp.getUi().alert('Hello, GAS!');
}ドキュメントとリソース
これらのリソースを利用して、GASのスクリプトを作成し、自動化を始めてみてください。具体的な質問やプロジェクトがあれば、詳細に教えてください。
---------------ここまで
ふむ、なんとなく分かりました。言語はJavascriptなので、見てればなんとなくやってることが分かりますね。
もし分からないことがあったら、遠慮なくAIに聞いて下さい。それでも分からなかったら、分かるまで聞いて下さい。
予約フォームを作ってみる
質問：
希望日時、名前、メールアドレス、電話番号などを入力させる予約フォームを作って、入力されたら運営者にメールを送ってGoogleカレンダーに入力させるサンプルを書いて
回答
以下の手順で、希望日時、名前、メールアドレス、電話番号などを入力させる予約フォームを作成し、入力された内容を運営者にメールで送信し、Googleカレンダーにイベントを作成するスクリプトのサンプルを紹介します。
手順
Googleフォームの作成
Googleスプレッドシートの設定
Google Apps Scriptの設定
トリガーの設定
1. Googleフォームの作成
Googleフォームで以下のフィールドを作成します：
希望日時（日時型）
名前（テキスト型）
メールアドレス（テキスト型）
電話番号（テキスト型）
2. Googleスプレッドシートの設定
フォームのレスポンスが保存されるスプレッドシートを開きます。
3. Google Apps Scriptの設定
スプレッドシートに関連付けられたスクリプトエディタを開き、以下のスクリプトを追加します。
function onFormSubmit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // フォームのレスポンスが保存されるシート名を指定
var lastRow = sheet.getLastRow();
var row = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
var reservationDate = new Date(row[0]); // フォームの1列目が希望日時の場合
var name = row[1]; // フォームの2列目が名前の場合
var email = row[2]; // フォームの3列目がメールアドレスの場合
var phone = row[3]; // フォームの4列目が電話番号の場合
// Googleカレンダーにイベントを作成
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent('予約: ' + name, reservationDate, new Date(reservationDate.getTime() + 1 * 60 * 60 * 1000), {
description: '予約者: ' + name + '\nメールアドレス: ' + email + '\n電話番号: ' + phone,
guests: email
});
// 運営者への通知メール
var adminEmail = 'admin@example.com'; // 運営者のメールアドレスに置き換えてください
var subject = '新しい予約: ' + name;
var body = '新しい予約が入りました。\n\n' +
'希望日時: ' + reservationDate + '\n' +
'予約者: ' + name + '\n' +
'メールアドレス: ' + email + '\n' +
'電話番号: ' + phone;
GmailApp.sendEmail(adminEmail, subject, body);
Logger.log('予約情報を処理しました: ' + name + ', ' + reservationDate);
}
function createTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
4. トリガーの設定
スクリプトが自動的に実行されるようにトリガーを設定します。
スクリプトエディタを開く
Googleスプレッドシートで「拡張機能」>「Apps Script」を選択します。
スクリプトを貼り付ける
上記のスクリプトをスクリプトエディタに貼り付けます。
フォームIDを設定する
このスクリプトではフォームIDの設定は不要ですが、運営者のメールアドレスをadmin@example.comの部分に置き換えてください。
トリガーの手動設定
スクリプトエディタの左側にある時計アイコン（トリガー）をクリックします。
「トリガーを追加」ボタンをクリックし、以下のように設定します：
関数を選択: onFormSubmit
イベントのソースを選択: フォーム送信時
フォームを選択: 該当するフォームを選択します
設定を保存します。
注意事項
スクリプトが動作するためには、必要な権限を付与する必要があります。スクリプトを初めて実行するときに、Googleアカウントの認証を求められますので、指示に従って認証を完了してください。
フォームのレスポンスシートの名前が異なる場合は、getSheetByNameの引数を実際のシート名に変更してください。
これで、Googleフォームからの入力内容がスプレッドシートに記録され、Googleカレンダーにイベントが作成され、運営者に通知メールが送信されるようになります。
---------------ここまで
コード例で変更が必要なのは、
var adminEmail = 'admin@example.com'; // 運営者のメールアドレスに置き換えてくださいのメールアドレスと、冒頭のシート名ですね。フォームと連携させたスプレッドシートのシート名を入れてください。スプレッドシートの名前ではなく、シートの名前（画面下部のタブに書いてある）です。
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // フォームのレスポンスが保存されるシート名を指定Googleフォームの項目の順番や数が違っている場合は、
var reservationDate = new Date(row[0]); // フォームの1列目が希望日時の場合
var name = row[1]; // フォームの2列目が名前の場合
var email = row[2]; // フォームの3列目がメールアドレスの場合
var phone = row[3]; // フォームの4列目が電話番号の場合この辺を修正します。row[0]の0は項目の順番なので、増やしたり順番が違ったりしたら適用に変更してください。
ここの「var email = row[2]; 」というのは、「3列目の内容をemailという箱（変数）に入れてください」という意味で、なんで2なのに3かと言うと、1列目を表すのは0で、0，1，2と並んでいるからです。なんで0からなのかは、「そういう事になっている」と思ってください。
で、この内容だと日時の処理が上手く出来なかったので、フォームの時刻入力を使わずにプロダウンメニューから選ぶようにして、コードはこうなりました。
function onFormSubmit(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 2'); // フォームのレスポンスが保存されるシート名を指定
var lastRow = sheet.getLastRow();
var row = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
var email = row[1]; // フォームの1列目がメールアドレスの場合
var date = row[2]; // フォームの2列目が希望日付の場合
var time = row[3]; // フォームの3列目が希望時刻の場合
var name = row[4]; // フォームの4列目が名前の場合
var phone = row[5]; // フォームの5列目が電話番号の場合
var eventDate = new Date(date.toDateString() + ' ' + time); // 日付と時刻を結合してイベント日時を作成
// Googleカレンダーにイベントを作成
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent('予約: ' + name, eventDate, new Date(eventDate.getTime() + 1 * 60 * 60 * 1000), {
description: '予約者: ' + name + '\nメールアドレス: ' + email + '\n電話番号: ' + phone,
guests: email
});
// 運営者への通知メール
var adminEmail = 'keiziweb@gmail.com'; // 運営者のメールアドレスに置き換えてください
var subject = '新しい予約: ' + name;
var body = '新しい予約が入りました。\n\n' +
'希望日付: ' + date + '\n' +
'希望時刻: ' + time + '\n' +
'予約者: ' + name + '\n' +
'メールアドレス: ' + email + '\n' +
'電話番号: ' + phone;
GmailApp.sendEmail(adminEmail, subject, body);
Logger.log('予約情報を処理しました: ' + name + ', ' + eventDate);
}
function createTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
メールアドレスの扱いをちょっと変えたので、項目の列番号が少し変わっています。row[1]でメールアドレスを取るようにして、row[0]はタイムスタンプになりました。この辺はスプレッドシートを開けば、何列目にどういう項目が入ってるのか分かります。
フォームの見た目はこんな感じ。デフォルトのままなので適当にカスタムすればいいです。
これで、予約が入るとメールが送られてカレンダーに自動登録されます。
日毎の予約件数で打ち切りたい場合は？
予約なら、やっぱり1日に受けられる件数を制限したいですよね。
質問：
日付は今後30日分から選べるものとします。これまでの回答から日付別の件数をカウントして、件数が5件以下の日付だけプルダウンメニューに表示できるようにして。
回答
以下の手順で、Googleフォームのプルダウンメニューに今後30日分のうち、件数が5件以下の日付のみを表示するスクリプトを作成します。
手順
Googleフォームの作成
Googleスプレッドシートの設定
Google Apps Scriptの設定
1. Googleフォームの作成
Googleフォームを作成し、プルダウンメニューの質問を追加します。例えば、「希望日付」という質問をプルダウンメニュー形式で作成します。
2. Googleスプレッドシートの設定
Googleフォームのレスポンスが保存されるスプレッドシートを確認します。
3. Google Apps Scriptの設定
スプレッドシートに関連付けられたスクリプトエディタを開き、以下のスクリプトを追加します。スクリプトを使ってGoogleフォームのプルダウンメニューの内容を変更します。
サンプルスクリプト
function updateFormDropdown() {
var formId = 'YOUR_FORM_ID'; // GoogleフォームのIDをここに設定
var form = FormApp.openById(formId);
// プルダウンメニューの質問タイトル
var questionTitle = '希望日付';
// 現在の日付から今後30日間の日付を生成
var today = new Date();
var futureDates = [];
for (var i = 0; i < 30; i++) {
var date = new Date();
date.setDate(today.getDate() + i);
var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
futureDates.push(dateString);
}
// スプレッドシートから回答を取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // シート名を適宜変更
var data = sheet.getDataRange().getValues();
// 日付ごとの件数をカウント
var dateCounts = {};
for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップ
var date = data[i][2]; // 希望日付が3列目にあると仮定
if (date) {
if (!dateCounts[date]) {
dateCounts[date] = 0;
}
dateCounts[date]++;
}
}
// 件数が5件以下の日付だけを選択肢に追加
var validDates = futureDates.filter(function(date) {
return !dateCounts[date] || dateCounts[date] <= 5;
});
// フォームのプルダウンメニューを更新
var items = form.getItems();
for (var i = 0; i < items.length; i++) {
var item = items[i];
if (item.getTitle() == questionTitle) {
var dropdown = item.asListItem();
dropdown.setChoiceValues(validDates);
Logger.log('プルダウンメニューの内容を更新しました: ' + validDates);
}
}
}
説明
フォームのIDを取得:
GoogleフォームのURLからフォームIDを取得し、スクリプトのformId変数に設定します。
プルダウンメニューの質問タイトル:
questionTitle変数にプルダウンメニューの質問タイトルを設定します。
今後30日間の日付を生成:
todayから30日間の日付を生成し、futureDates配列に保存します。
スプレッドシートから回答を取得:
Googleフォームのレスポンスが保存されるスプレッドシートのデータを取得します。
日付ごとの件数をカウント:
スプレッドシートのデータをループして、希望日付ごとの回答件数をカウントします。
件数が5件以下の日付をフィルタリング:
カウントした件数が5件以下の日付をvalidDates配列に保存します。
フォームのプルダウンメニューを更新:
フォームのプルダウンメニューを更新し、validDatesを設定します。
スクリプトの実行
スクリプトエディタでupdateFormDropdown関数を実行すると、指定されたGoogleフォームのプルダウンメニューの内容が更新されます。
これで、Googleフォームのプルダウンメニューに、今後30日分のうち件数が5件以下の日付のみが表示されるようになります。
---------------ここまで
時刻ごとの件数でも打ち切りたい
予約フォームで重要なのは、日付と時刻で、その時刻に受け付けられる件数のキャパだったりしますから、ここまでの内容だと片手落ちだと思うでしょう。ある時刻だけ予約が集中してキャパを越えたら困ります。予約の指定は日付と時刻で出来ないといけません。
ということで、
質問：
日付と時刻をプルダウンメニューから入力するように変更します。プルダウンメニューには今後1週間の日付と、時刻は18時から22時までの30分刻みの文字列として設定します。
例）現在が2024/07/21の場合
2024/07/22 18:00
2024/07/22 18:30
2024/07/22 19:00
2024/07/22 19:30
途中省略
2024/07/22 22:00
2024/07/23 18:00
以下省略
これまでの回答から日時別の件数をカウントして、件数が5件以下の日時だけプルダウンメニューに表示できるようにしてください。
回答
以下は、Googleフォームのプルダウンメニューに今後1週間の日付と18時から22時までの30分刻みの時刻を表示し、これまでの回答から日時別の件数をカウントして件数が5件以下の日時だけを表示するスクリプトです。
手順
Googleフォームの作成
Googleスプレッドシートの設定
Google Apps Scriptの設定
1. Googleフォームの作成
Googleフォームを作成し、プルダウンメニューの質問を追加します。例えば、「希望日時」という質問をプルダウンメニュー形式で作成します。
2. Googleスプレッドシートの設定
Googleフォームのレスポンスが保存されるスプレッドシートを確認します。
3. Google Apps Scriptの設定
スプレッドシートに関連付けられたスクリプトエディタを開き、以下のスクリプトを追加します。
サンプルスクリプト
function updateFormDropdown() {
var formId = 'YOUR_FORM_ID'; // GoogleフォームのIDをここに設定
var form = FormApp.openById(formId);
// プルダウンメニューの質問タイトル
var questionTitle = '希望日時';
// 現在の日付から今後1週間の日付と時刻を生成
var now = new Date();
var futureDateTimes = [];
for (var i = 1; i <= 7; i++) {
var date = new Date();
date.setDate(now.getDate() + i);
var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
for (var hour = 18; hour <= 22; hour++) {
for (var minute = 0; minute < 60; minute += 30) {
var timeString = Utilities.formatDate(new Date(date.setHours(hour, minute)), Session.getScriptTimeZone(), 'HH:mm');
futureDateTimes.push(dateString + ' ' + timeString);
}
}
}
// スプレッドシートから回答を取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // シート名を適宜変更
var data = sheet.getDataRange().getValues();
// 日時ごとの件数をカウント
var dateTimeCounts = {};
for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップ
var dateTime = data[i][2]; // 希望日時が3列目にあると仮定
if (dateTime) {
if (!dateTimeCounts[dateTime]) {
dateTimeCounts[dateTime] = 0;
}
dateTimeCounts[dateTime]++;
}
}
// 件数が5件以下の日時だけを選択肢に追加
var validDateTimes = futureDateTimes.filter(function(dateTime) {
return !dateTimeCounts[dateTime] || dateTimeCounts[dateTime] <= 5;
});
// フォームのプルダウンメニューを更新
var items = form.getItems();
for (var i = 0; i < items.length; i++) {
var item = items[i];
if (item.getTitle() == questionTitle) {
var dropdown = item.asListItem();
dropdown.setChoiceValues(validDateTimes);
Logger.log('プルダウンメニューの内容を更新しました: ' + validDateTimes);
}
}
}
説明
フォームのIDを取得:
GoogleフォームのURLからフォームIDを取得し、スクリプトのformId変数に設定します。
プルダウンメニューの質問タイトル:
questionTitle変数にプルダウンメニューの質問タイトルを設定します。
今後1週間の日付と時刻を生成:
現在の日付から7日分の日付を生成し、それぞれの日付に対して18時から22時までの30分刻みの時刻を生成します。
生成された日時をfutureDateTimes配列に保存します。
スプレッドシートから回答を取得:
Googleフォームのレスポンスが保存されるスプレッドシートのデータを取得します。
日時ごとの件数をカウント:
スプレッドシートのデータをループして、希望日時ごとの回答件数をカウントします。
件数が5件以下の日時をフィルタリング:
カウントした件数が5件以下の日時をvalidDateTimes配列に保存します。
フォームのプルダウンメニューを更新:
フォームのプルダウンメニューを更新し、validDateTimesを設定します。
スクリプトの実行
スクリプトエディタでupdateFormDropdown関数を実行すると、指定されたGoogleフォームのプルダウンメニューの内容が更新されます。
これで、Googleフォームのプルダウンメニューに、今後1週間分の日付と18時から22時までの30分刻みの時刻のうち、件数が5件以下の日時だけが表示されるようになります。
---------------ここまで
フォームIDはGoogleフォームを編集中にURLとして表示されます。下記のようなURLだったら、
https://docs.google.com/forms/d/1d9IpaGZqlQ8EobEcｘxxxxxxxxxxxxxxxxx/edit
1d9IpaGZqlQ8EobEcｘxxxxxxxxxxxxxxxxxの部分がフォームIDです。
で、こんなコードになりました。
function onFormSubmit(e) {
updateFormDropdown();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); // ※フォームのレスポンスが保存されるシート名を指定
var lastRow = sheet.getLastRow();
var row = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
var email = row[1]; // フォームの1列目がメールアドレスの場合
var date = row[2]; // フォームの2列目が希望日付の場合
var name = row[3]; // フォームの4列目が名前の場合
var phone = row[4]; // フォームの5列目が電話番号の場合
var eventDate = new Date(date); // 日付と時刻を結合してイベント日時を作成
// Googleカレンダーにイベントを作成
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent('予約: ' + name, eventDate, new Date(eventDate.getTime() + 1 * 60 * 60 * 1000), {
description: '予約者: ' + name + '\nメールアドレス: ' + email + '\n電話番号: ' + phone,
guests: email
});
// 運営者への通知メール
var adminEmail = 'xxxxxxx@gmail.com'; // ※運営者のメールアドレスに置き換えてください
var subject = '新しい予約: ' + name;
var body = '新しい予約が入りました。\n\n' +
'希望日時: ' + date + '\n' +
'予約者: ' + name + '\n' +
'メールアドレス: ' + email + '\n' +
'電話番号: ' + phone;
GmailApp.sendEmail(adminEmail, subject, body);
Logger.log('予約情報を処理しました: ' + name + ', ' + eventDate);
}
function createTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function updateFormDropdown() {
var formId = 'xxxxxxxxxxxxxxx'; // ※GoogleフォームのIDをここに設定
var form = FormApp.openById(formId);
// プルダウンメニューの質問タイトル
var questionTitle = '希望日時を選んでください';//※日時を質問する欄の内容
// 現在の日付から今後1週間の日付と時刻を生成
var now = new Date();
var futureDateTimes = [];
for (var i = 1; i <= 7; i++) {
var date = new Date();
date.setDate(now.getDate() + i);
var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
for (var hour = 18; hour <= 22; hour++) {
for (var minute = 0; minute < 60; minute += 30) {
var timeString = Utilities.formatDate(new Date(date.setHours(hour, minute)), Session.getScriptTimeZone(), 'HH:mm');
futureDateTimes.push(dateString + ' ' + timeString);
}
}
}
// スプレッドシートから回答を取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※ シート名を適宜変更
var data = sheet.getDataRange().getValues();
// 日時ごとの件数をカウント
var dateTimeCounts = {};
for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップ
var dateTime = data[i][2]; // 希望日時が3列目にあると仮定
if (dateTime) {
if (!dateTimeCounts[dateTime]) {
dateTimeCounts[dateTime] = 0;
}
dateTimeCounts[dateTime]++;
}
}
// 件数が5件以下の日時だけを選択肢に追加
var validDateTimes = futureDateTimes.filter(function(dateTime) {
return !dateTimeCounts[dateTime] || dateTimeCounts[dateTime] <= 5;
});
// フォームのプルダウンメニューを更新
var items = form.getItems();
for (var i = 0; i < items.length; i++) {
var item = items[i];
if (item.getTitle() == questionTitle) {
var dropdown = item.asListItem();
dropdown.setChoiceValues(validDateTimes);
Logger.log('プルダウンメニューの内容を更新しました: ' + validDateTimes);
}
}
}
※の部分は適宜変更してください。正しく設定しないと動きません。
トリガーの設定も、AIの説明通りにやると、こんな感じの予約フォームから日時それぞれの件数を限定して予約を受け付けることが出来ます。
適当に動かしてみたら、ちゃんと件数制限、メール送信、カレンダー登録まで出来ていたので問題なさそうです。
データ件数が増えたら困るので改良してもらいます
上記のコードでも予約システムとして使えますが、データが増えてくるとおそらく処理が重くなります(まぁ、Googleのサーバーで動かすものなので問題は無いと思いますが)。都度過去のデータを自分で消すのも面倒ですから自動化しましょう。
質問：
下記のコードだと、スプレッドシートのデータ件数が増えてくると処理が重くなる。シートの内容をチェックして、タイムスタンプが10日以上前のデータはアーカイブ用のシートに移動するようにして。
（さっきのコードをペースト 省略）
回答
以下のコードは、Googleスプレッドシートのデータ件数が増えた場合の処理を改善し、10日以上前のデータをアーカイブ用のシートに移動するようにします。これにより、スプレッドシートのメインシートのパフォーマンスが向上します。
function onFormSubmit(e) {
archiveOldEntries(); // 10日以上前のデータをアーカイブする
updateFormDropdown();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※ フォームのレスポンスが保存されるシート名を指定
var lastRow = sheet.getLastRow();
var row = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
var email = row[1]; // フォームの1列目がメールアドレスの場合
var date = row[2]; // フォームの2列目が希望日付の場合
var name = row[3]; // フォームの3列目が名前の場合
var phone = row[4]; // フォームの4列目が電話番号の場合
var eventDate = new Date(date); // 日付と時刻を結合してイベント日時を作成
// Googleカレンダーにイベントを作成
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent('予約: ' + name, eventDate, new Date(eventDate.getTime() + 1 * 60 * 60 * 1000), {
description: '予約者: ' + name + '\nメールアドレス: ' + email + '\n電話番号: ' + phone,
guests: email
});
// 運営者への通知メール
var adminEmail = 'xxxxx@gmail.com'; // ※運営者のメールアドレスに置き換えてください
var subject = '新しい予約: ' + name;
var body = '新しい予約が入りました。\n\n' +
'希望日時: ' + date + '\n' +
'予約者: ' + name + '\n' +
'メールアドレス: ' + email + '\n' +
'電話番号: ' + phone;
GmailApp.sendEmail(adminEmail, subject, body);
Logger.log('予約情報を処理しました: ' + name + ', ' + eventDate);
}
function createTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function updateFormDropdown() {
var formId = 'xxxxxxxxxxxxxx'; // ※GoogleフォームのIDをここに設定
var form = FormApp.openById(formId);
// プルダウンメニューの質問タイトル
var questionTitle = '希望日時を選んでください';
// 現在の日付から今後1週間の日付と時刻を生成
var now = new Date();
var futureDateTimes = [];
for (var i = 1; i <= 7; i++) {
var date = new Date();
date.setDate(now.getDate() + i);
var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
for (var hour = 18; hour <= 22; hour++) {
for (var minute = 0; minute < 60; minute += 30) {
var timeString = Utilities.formatDate(new Date(date.setHours(hour, minute)), Session.getScriptTimeZone(), 'HH:mm');
futureDateTimes.push(dateString + ' ' + timeString);
}
}
}
// スプレッドシートから回答を取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※ シート名を適宜変更
var data = sheet.getDataRange().getValues();
// 日時ごとの件数をカウント
var dateTimeCounts = {};
for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップ
var dateTime = data[i][2]; // 希望日時が3列目にあると仮定
if (dateTime) {
if (!dateTimeCounts[dateTime]) {
dateTimeCounts[dateTime] = 0;
}
dateTimeCounts[dateTime]++;
}
}
// 件数が5件以下の日時だけを選択肢に追加
var validDateTimes = futureDateTimes.filter(function(dateTime) {
return !dateTimeCounts[dateTime] || dateTimeCounts[dateTime] <= 5;
});
// フォームのプルダウンメニューを更新
var items = form.getItems();
for (var i = 0; i < items.length; i++) {
var item = items[i];
if (item.getTitle() == questionTitle) {
var dropdown = item.asListItem();
dropdown.setChoiceValues(validDateTimes);
Logger.log('プルダウンメニューの内容を更新しました: ' + validDateTimes);
}
}
}
function archiveOldEntries() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※
var archiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('アーカイブ');
if (!archiveSheet) {
// アーカイブ用のシートが存在しない場合は新しく作成
archiveSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('アーカイブ');
}
var data = sheet.getDataRange().getValues();
var headers = data[0];
var currentDate = new Date();
var tenDaysAgo = new Date(currentDate.getTime() - (10 * 24 * 60 * 60 * 1000));
var rowsToArchive = [];
for (var i = 1; i < data.length; i++) {
var timestamp = new Date(data[i][0]); // タイムスタンプが1列目にあると仮定
if (timestamp < tenDaysAgo) {
rowsToArchive.push(data[i]);
}
}
if (rowsToArchive.length > 0) {
// アーカイブ用シートにヘッダーがない場合はヘッダーを追加
if (archiveSheet.getLastRow() === 0) {
archiveSheet.appendRow(headers);
}
// アーカイブ用シートに古いデータを追加
archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, rowsToArchive.length, rowsToArchive[0].length).setValues(rowsToArchive);
// メインシートから古いデータを削除
for (var i = rowsToArchive.length - 1; i >= 0; i--) {
sheet.deleteRow(i + 2); // ヘッダー行を考慮
}
}
}
説明
archiveOldEntries 関数の追加:
この関数は、メインシートから10日以上前のデータをアーカイブ用のシートに移動します。
アーカイブ用のシートが存在しない場合は、新しく作成します。
古いデータをアーカイブ用のシートに追加し、メインシートから削除します。
onFormSubmit 関数の修正:
archiveOldEntries 関数を呼び出して、10日以上前のデータをアーカイブする処理を追加します。
使用方法
Googleスプレッドシートで新しいシートを作成:
アーカイブ用のシートを「アーカイブ」という名前で作成します。シートが存在しない場合はスクリプトが自動で作成します。
スクリプトの設定:
上記のスクリプトをGoogle Apps Scriptエディタに貼り付けます。
updateFormDropdown 関数内のフォームIDを適切な値に置き換えます。
トリガーの設定:
createTrigger 関数を実行して、フォーム送信時に onFormSubmit 関数が実行されるようにトリガーを設定します。
これにより、スプレッドシートのデータが定期的に整理され、パフォーマンスが向上します。また、フォームのプルダウンメニューも最新の状態に保たれます。
---------------ここまで
予約できるのは1週間後までなので7日前の入力データはもう要らないんですが、多少のバッファを持たせてもいいかなという事で、10日前より古いデータはアーカイブシートに移すようにしてもらいました。
簡単な顧客管理も入れましょう
予約システムには顧客管理が付き物らしいです。なので予約回数を加算していくように変更しましょう。
質問：
顧客管理がしたい。電話番号で名簿を作って、過去の予約回数を積算していくようにして。
回答
顧客管理を行うために、電話番号をキーとして顧客名簿を作成し、過去の予約回数を積算するようにスクリプトを修正します。
1. 顧客名簿の作成
スプレッドシートに「顧客名簿」というシートを作成し、以下のようなカラムを用意します：
電話番号
名前
メールアドレス
予約回数
2. スクリプトの修正
既存のスクリプトに顧客管理機能を追加し、電話番号をキーとして顧客情報を管理します。
function onFormSubmit(e) {
archiveOldEntries(); // 10日以上前のデータをアーカイブする
updateFormDropdown();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※ フォームのレスポンスが保存されるシート名を指定
var lastRow = sheet.getLastRow();
var row = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
updateCustomerData(row); // 顧客データの更新
var email = row[1]; // フォームの1列目がメールアドレスの場合
var date = row[2]; // フォームの2列目が希望日付の場合
var name = row[3]; // フォームの3列目が名前の場合
var phone = row[4]; // フォームの4列目が電話番号の場合
var eventDate = new Date(date); // 日付と時刻を結合してイベント日時を作成
// Googleカレンダーにイベントを作成
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent('予約: ' + name, eventDate, new Date(eventDate.getTime() + 1 * 60 * 60 * 1000), {
description: '予約者: ' + name + '\nメールアドレス: ' + email + '\n電話番号: ' + phone,
guests: email
});
// 運営者への通知メール
var adminEmail = 'xxxxx@gmail.com'; //※ 運営者のメールアドレスに置き換えてください
var subject = '新しい予約: ' + name;
var body = '新しい予約が入りました。\n\n' +
'希望日時: ' + date + '\n' +
'予約者: ' + name + '\n' +
'メールアドレス: ' + email + '\n' +
'電話番号: ' + phone;
GmailApp.sendEmail(adminEmail, subject, body);
Logger.log('予約情報を処理しました: ' + name + ', ' + eventDate);
}
function createTrigger() {
ScriptApp.newTrigger('onFormSubmit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onFormSubmit()
.create();
}
function updateFormDropdown() {
var formId = 'xxxxxxxxxxxxx'; //※ GoogleフォームのIDをここに設定
var form = FormApp.openById(formId);
// プルダウンメニューの質問タイトル
var questionTitle = '希望日時を選んでください';
// 現在の日付から今後1週間の日付と時刻を生成
var now = new Date();
var futureDateTimes = [];
for (var i = 1; i <= 7; i++) {
var date = new Date();
date.setDate(now.getDate() + i);
var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy/MM/dd');
for (var hour = 18; hour <= 22; hour++) {
for (var minute = 0; minute < 60; minute += 30) {
var timeString = Utilities.formatDate(new Date(date.setHours(hour, minute)), Session.getScriptTimeZone(), 'HH:mm');
futureDateTimes.push(dateString + ' ' + timeString);
}
}
}
// スプレッドシートから回答を取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※ シート名を適宜変更
var data = sheet.getDataRange().getValues();
// 日時ごとの件数をカウント
var dateTimeCounts = {};
for (var i = 1; i < data.length; i++) { // ヘッダー行をスキップ
var dateTime = data[i][2]; // 希望日時が3列目にあると仮定
if (dateTime) {
if (!dateTimeCounts[dateTime]) {
dateTimeCounts[dateTime] = 0;
}
dateTimeCounts[dateTime]++;
}
}
// 件数が5件以下の日時だけを選択肢に追加
var validDateTimes = futureDateTimes.filter(function(dateTime) {
return !dateTimeCounts[dateTime] || dateTimeCounts[dateTime] <= 5;
});
// フォームのプルダウンメニューを更新
var items = form.getItems();
for (var i = 0; i < items.length; i++) {
var item = items[i];
if (item.getTitle() == questionTitle) {
var dropdown = item.asListItem();
dropdown.setChoiceValues(validDateTimes);
Logger.log('プルダウンメニューの内容を更新しました: ' + validDateTimes);
}
}
}
function archiveOldEntries() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1'); //※
var archiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('アーカイブ');
if (!archiveSheet) {
// アーカイブ用のシートが存在しない場合は新しく作成
archiveSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('アーカイブ');
}
var data = sheet.getDataRange().getValues();
var headers = data[0];
var currentDate = new Date();
var tenDaysAgo = new Date(currentDate.getTime() - (10 * 24 * 60 * 60 * 1000));
var rowsToArchive = [];
for (var i = 1; i < data.length; i++) {
var timestamp = new Date(data[i][0]); // タイムスタンプが1列目にあると仮定
if (timestamp < tenDaysAgo) {
rowsToArchive.push(data[i]);
}
}
if (rowsToArchive.length > 0) {
// アーカイブ用シートにヘッダーがない場合はヘッダーを追加
if (archiveSheet.getLastRow() === 0) {
archiveSheet.appendRow(headers);
}
// アーカイブ用シートに古いデータを追加
archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, rowsToArchive.length, rowsToArchive[0].length).setValues(rowsToArchive);
// メインシートから古いデータを削除
for (var i = rowsToArchive.length - 1; i >= 0; i--) {
sheet.deleteRow(i + 2); // ヘッダー行を考慮
}
}
}
function updateCustomerData(row) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('顧客名簿');
if (!sheet) {
// 顧客名簿シートが存在しない場合は新しく作成
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('顧客名簿');
sheet.appendRow(['電話番号', '名前', 'メールアドレス', '予約回数']);
}
var phone = row[5]; // フォームの4列目が電話番号の場合
var name = row[4]; // フォームの3列目が名前の場合
var email = row[1]; // フォームの1列目がメールアドレスの場合
var data = sheet.getDataRange().getValues();
var found = false;
for (var i = 1; i < data.length; i++) {
if (data[i][0] === phone) {
// 顧客が既に存在する場合、予約回数をインクリメント
var count = data[i][3];
count++;
sheet.getRange(i + 1, 4).setValue(count); // 予約回数を更新
found = true;
break;
}
}
if (!found) {
// 顧客が存在しない場合、新しい行を追加
sheet.appendRow([phone, name, email, 1]);
}
}
説明
updateCustomerData 関数の追加:
この関数は、新しいフォームの送信時に顧客名簿を更新します。
顧客名簿に電話番号をキーとして顧客情報を保存し、同じ電話番号が見つかった場合は予約回数をインクリメントします。見つからない場合は新しい行を追加します。
onFormSubmit 関数の修正:
フォームの送信時に updateCustomerData 関数を呼び出して、顧客名簿を更新します。
使用方法
Googleスプレッドシートで新しいシートを作成:
顧客名簿用のシートを「顧客名簿」という名前で作成します。このシートには以下のカラムを含めます：電話番号, 名前, メールアドレス, 予約回数。
スクリプトの設定:
上記のスクリプトをGoogle Apps Scriptエディタに貼り付けます。
updateFormDropdown 関数内のフォームIDを適切な値に置き換えます。
トリガーの設定:
createTrigger 関数を実行して、フォーム送信時に onFormSubmit 関数が実行されるようにトリガーを設定します。
これで、顧客管理ができるようになります。顧客情報は電話番号をキーとして管理され、過去の予約回数が積算されます。
---------------ここまで
もっと高度にしたければ、そのようにAIに指示を出してください。例えば休みの曜日を抜いてもらうとか。これで未来永劫無料で使える予約システムの完成です（※）。
以上で、日時の件数制限を入れた、メール＆カレンダー連動の予約フォームが完成しました。プログラミングが出来ない人でも簡単、とまでは言いませんが、これまでのソフト開発を考えたら相当に楽ですし、全部無料で使える機能です。
GoogleAppScriptを応用すれば、もっと色んな使い方が出来ますね。
（※試しに動かしたら若干バグがあったので自分で直しました。自分で直せない場合はエラーメッセージをAIに送って直させてください）
分からないことは全部AIに聞いとくれ
僕は48歳ですが33年間プログラムを書いてきたので、プログラムを書けない人が分からないことやコードを書かない理由がもう分かりません。なんで変数や代入、クラスなどなどが理解できないのかわかりません。歩き方や息の吸い方を聞かれてるように感じます。なんで自分でコードを書かずに、他人が作ったアプリだけでスマホを使ってられるんだろう…。不便を感じないのだろうか…とか。
なわけで、皆さんの疑問はなんも分からないので、質問があったら全部AIに聞いて下さい。「そんなことも分からないの？」なんて顔もせず、ひたすら真面目に、真摯に質問に付き合ってくれます。僕に聞かないでAIに聞いてください。
AIを先生にして勉強しましょう。似たような解説記事とかもいろいろあると思いますが、AIに聞きながら自分で手を動かしたほうが早いし勉強になります。
やってみてください。
