Googleフォームから送信した内容で スプレッドシートに反映された回答内容から GASを使って、Googleカレンダーへ予定を作成する

Question

実現したいこと

Googleフォームから送信した内容で
スプレッドシートに反映された回答内容から
GASを使って、Googleカレンダーへ予定を作成する

前提

一度実装ができたので、フォームの入力形式を少々変更し再度実装に挑んでいる最中でした。

発生している問題・エラーメッセージ

やはり９行目でエラーが出ます、、、

該当のソースコード

今実装中のコード
function createEvent(e) {
//エラー防止のため、以下の5行を追加
if (e === undefined) {
console.log('このスクリプトは、エディタから実行できません。');
console.log('フォームに回答してみてください。');
return;
}
const items = [];
const itemResponses = e.response.getItemResponses();
for (const itemResponse of itemResponses) {
items.push(itemResponse.getResponse());
}
let [ abouttime, YYYY, MM, DD, hoursS, minutesS, hoursE, minutesE] = items;
let id = '[e-mail]';
let calendar = CalendarApp.getCalendarById(id);
let title = abouttime;
let startTime = new Date(YYYY + MM + DD + hoursS + minutesS);
let endTime = new Date(YYYY + MM + DD + hoursE + minutesE);
// ▼勤務内容
// 送信日時: ${timestamp}
// 勤務日: ${YYYY}/${MM}/${DD}
// 開始時間: ${hoursS}:${minutesS}
// 終了時間: ${hoursE}:${minutesE}
// 時間帯: ${abouttime}`;

calendar.createEvent(title, startTime, endTime);
}

Comments

[YellowGreen]

const items = [];
の次の
const itemResponses = e.response.getItemResponses();
for (const itemResponse of itemResponses) {
items.push(itemResponse.getResponse());
}

までがなくなっていて
let [date, time1, time2] = items;
になっていますが、
そこは

const items = [];
const itemResponses = e.response.getItemResponses();
for (const itemResponse of itemResponses) {
items.push(itemResponse.getResponse());
}
let [date, time1, time2] = items;
となっていないと勤務日、開始時間、終了時間が取得できないと思います。

それから`▼で始まる行は、何も処理に関係していないので、行の冒頭に//を入れておきましょう。

前回のエラーでなくなっていたのは、1行だけではなかったのですね。
修正箇所をお教えした後、お教えしたとおりに修正されているのかを確認するため、修正後のコードを見せてもらうようにお願いしなかった私の過ちでした。
重ねて申し訳ありませんでした。

なお、ログの表示の遅延は、エディタをリロードすると（ログがあれば）表示されると思います。

[YellowGreen]

上記のコメントを反映して修正したスクリプト全文を改めてアップしてみていただけますか。

[YellowGreen]

それから、カレンダーでスクリプトに記載してあるtitleの値の文字列を検索してみてください。
1970年の1月1日にこれまでにフォームで回答した分の予定が登録されているかもしれません。

[ch-taros164]

1970年の1月1日にイベントが溜まっておりました

[YellowGreen]

相変わらず、
const items = [];

の次に

const itemResponses = e.response.getItemResponses();
for (const itemResponse of itemResponses) {
items.push(itemResponse.getResponse());
}

がありません。
これがないとフォームの回答がitemsに入らないので、ゼロから生成した日付である
1970年1月1日になってしまってるのだと思います。

[YellowGreen]

何度も申し訳ありませんが
上記のコメントを反映して修正したスクリプト全文を改めてアップしてみていただけますか。

[YellowGreen]

それから回答が選択肢になっていますが、
選択肢はどのようは内容でしょうか？

[ch-taros164]

プルダウンの選択肢は数字です

[YellowGreen]

もう一つあります。
回答が8つあるのですが、
itemsの値を受け取る変数が
let [timestamp, date, time1, time2] = items;
と4つなのであっていません。

[YellowGreen]

選択肢は、勤務日の3カ所に
2023
04
13
と数字のみですね。
時刻も
09
30
などと入力するということですね。

最初の時間帯は？

[ch-taros164]

今実装中のコードでは、変数数を増やしました。
時間帯は早番遅番などの文言です。

[ch-taros164]

TypeError: Cannot read properties of undefined (reading 'getItemResponses')
at createEvent(:9:36)
このエラーはどうすればよいですか？

[YellowGreen]

こちらのスクリプトがフォームのスクリプトでトリガーから起動されているのであれば、回答の値を取得できていないということです。

スクリプト全文をアップしてみていただけますか。

[ch-taros164]

アップしました

[YellowGreen]

const itemResponses = getItemResponses();

は、

const itemResponses = e.response.getItemResponses();

です。

[YellowGreen]

let [timestamp, abouttime, YYYY, MM, DD, hoursS, minutesS, hoursE, minutesE] = items;

の部分は、前の質問でも申し上げましたが、
タイムスタンプは、取得できませんし、その後、値を使ってもいません。

これも、上で申し上げましたが、質問項目の数と変数の数が合っていません。

質問項目が8つなのに、なぜ9つの値を取得できるようにコードを書いているのでしょうか？
どなたかからそのように書くように言われているのでしょうか？

[YellowGreen]

スプレッドシートの方に蓄積されるタイムスタンプは、回答者が記入したものではないです。

[YellowGreen]

今後のための、アドバイスです。
一度動作確認できたスクリプトを変更するときは、一度に変更せずに、少しずつ変更しながら動作を確認しながら変更すると、どこの部分でエラーになるのかがわかりやすくなります。

[ch-taros164]

やはり９行目でエラーになります

[YellowGreen]

このスクリプトは、フォームに保存されていますか？
それともスプレッドシートに保存されていますか？

[ch-taros164]

スプレッドシートです。

[YellowGreen]

スプレッドシートでの回答の取得方法は、前の質問に追加の回答でお示ししています。
このスクリプトは、フォームから回答を取得するスクリプトなので、
function createEvent(e) {
で e に値が渡されないので、今、発生しているエラーになります。

[YellowGreen]

回答の値の取得方法が全く違います。
スプレッドシートでは、回答が送信されてシートの最終行に追加された情報から
それぞれの回答項目の値を得ることになります。

[ch-taros164]

どうすればいいですか？

[YellowGreen]

前の質問への追加の回答が参考になりませんか。
values[1]がB列の値
values[2]がC列の値
values[3]がD列の値
values[4]がE列の値
values[5]がF列の値
values[6]がG列の値
values[7]がH列の値
values[8]がI列の値
にそれぞれなります。

それらを組み合わせて
startTime
endTime
を生成するときに
yyyy/MM/dd HH:mm
の形式の文字列になるように + で'/'や' '(半角スペース)、':'と組み合わせてから
new Date()の引数に与えます。

[YellowGreen]

頑張って！

[YellowGreen]

前の質問への追加の回答のスクリプトは細かくコメントを入れているので
修正するときの参考になると思います。

[YellowGreen]

今のスクリプトを修正するのではなく、
前の質問に追加で回答したスクリプトを
新たにコピペしてそれを修正していきましょう。
上にあげたフォーム用のスクリプトは忘れましょう。

[YellowGreen]

今頃気が付きました。
質問のタイトルにしっかりと
スプレッドシートに反映された回答内容からカレンダーに登録
とありました。
もっと早くにスクリプト違いに気づくべきでした。

[YellowGreen]

> const itemResponses = e.response.getItemResponses(); の行のエラーはどうしたら改善されるのですか？？

このスクリプトは、フォームに保存して使うものだからです。
スプレッドシートに保存して使うものではありません。

スプレッドシートに保存してスプレッドシートの回答からカレンダーに反映するには、
私が、2023/04/13 08:25にコメントしたように、
以前の質問（Googleカレンダーへの反映させるGASコード：2023/04/11 23:27の質問）に私が追加で回答した参考スクリプトをご覧いただき3列分の値を取得していたのを8列分の値を取得するように変更してみてください。上のコメントもそのことを前提にしています。
GPT-4man様の2番目の回答も参考になると思います。

[YellowGreen]

私の2023/04/13 08:25のコメント以降のコメントを読み返してみていただけますか。

[YellowGreen]

https://teratail.com/questions/odam616r7c5goo
こちらに回答が2つ付いてますよね。新しい回答の方です。

[ch-taros164]

//フォームの回答からカレンダーの予定を作成
//スプレッドシートに保存してトリガー起動
//こちらをトリガー設定するときは、フォームのトリガーを削除
//エディタから実行すると最終行の値でカレンダーを作成
function createEvent() {//eは不要
const name = 'フォームの回答 1';//回答が蓄積されるシート名をコピペ
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
if (sheet.getName() == name) {//トリガー起動したシート名が一致
const values = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn())
.getDisplayValues()//画面表示の値を文字列として取得
.flat();//一次元配列に変換
const abouttimes = values[1];//B列
const YYYY = values[2];//C列
const MM = values[3];//D列
const DD = values[4];//E列
const hoursS = values[5];//F列
const minutesS = values[6];//G列
const hoursE = values[7];//H列
const minutesE = values[8];//I列
const Date = (YYYY/MM/DD);
const startTime = ( YYYY/MM/DD + hoursS + ':' + minutesS);
const endTime = (YYYY/MM/DD + hoursE + ':' + minutesE);
const title = 'abouttimes';
const calendar = CalendarApp.getCalendarById('[e-mail]');
calendar.createEvent(title, startTime, endTime);
}
}

で、
Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent.
createEvent

というエラーが出ました。

[YellowGreen]

まず、
const Date = (YYYY/MM/DD);
を削除

さらに、
const startTime = ( YYYY/MM/DD + hoursS + ':' + minutesS);
const endTime = (YYYY/MM/DD + hoursE + ':' + minutesE);
は、
const startTime = new Date(YYYY + '/' + MM + '/' + DD + ' ' + hoursS + ':' + minutesS);
const endTime = new Date(YYYY + '/' + MM + '/' + DD + ' ' + hoursE + ':' + minutesE);
// DDの後の ' ' の中は半角スペース
としてはどうでしょう。

あるいは、
上の方の
.getDisplayValues()
.flat()
を
.getValues()
.flat()
としてから、
GPT-4man様の回答を参考に
const startTime = new Date(YYYY, MM - 1, DD, hoursS, minutesS);
const endTime = new Date(YYYY, MM - 1, DD, hoursE, minutesE);
としてみてはいかがでしょうか。

[YellowGreen]

createEventに与えるstartTimeとendTimeは日付の文字列ではなく、
日付オブジェクトになっていなければなりません。
日付オブジェクトを生成するのが、
new Date()です。

[ch-taros164]

ありがとうございました！！
全て解決できました！！

[YellowGreen]

できましたら、たびたびお手数をおかけして恐縮ですが、スクリプト全文を再度アップ願えますでしょうか。

[YellowGreen]

お役に立ったようで、よかったです。
スクリプト違いに気づくのが遅れてしまい時間をかけてしまいました。
また、先日の質問への回答についてわかりにくいコメントで申し訳ありませんでした。
早くリンクを加えておけばよかったと後悔しています。

[YellowGreen]

2023/04/13 13:50のコメントから
カレンダーIDの部分を伏せ字にされた方がよろしいかと思います。

Answer 1

「ChatGPT(GPT-4)]により生成された文章です」
※YellowGreen　さんに対する回答です。

スプレッドシートに蓄積される値からカレンダーに予定を登録する方法について、以下にGoogle Apps Scriptを使用したコード例を提供します。

function createEventFromSpreadsheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var calendarId = '[e-mail]';
  var calendar = CalendarApp.getCalendarById(calendarId);
  
  var lastRow = sheet.getLastRow();
  for (var i = 2; i <= lastRow; i++) {
    var abouttime = sheet.getRange(i, 1).getValue();
    var YYYY = sheet.getRange(i, 2).getValue();
    var MM = sheet.getRange(i, 3).getValue();
    var DD = sheet.getRange(i, 4).getValue();
    var hoursS = sheet.getRange(i, 5).getValue();
    var minutesS = sheet.getRange(i, 6).getValue();
    var hoursE = sheet.getRange(i, 7).getValue();
    var minutesE = sheet.getRange(i, 8).getValue();

    var title = abouttime;
    var startTime = new Date(YYYY, MM - 1, DD, hoursS, minutesS);
    var endTime = new Date(YYYY, MM - 1, DD, hoursE, minutesE);

    calendar.createEvent(title, startTime, endTime);
  }
}

このスクリプトは、Googleスプレッドシートのデータを読み取り、そのデータに基づいてGoogleカレンダーに予定を作成します。スプレッドシートには、次のカラムが必要です。

１．abouttime（予定のタイトル）
２．YYYY（年）
３．MM（月）
４．DD（日）
５．hoursS（開始時間の時）
６．minutesS（開始時間の分）
７．hoursE（終了時間の時）
８．minutesE（終了時間の分）
スクリプトは、スプレッドシートの各行に対して、カレンダーに予定を登録します。このコードをGoogleスプレッドシートのスクリプトエディタに貼り付け、適切なカレンダーIDに置き換えた後、createEventFromSpreadsheet関数を実行してください。

Comments

[YellowGreen]

ありがとうございます。お手数をおかけしました。
大変興味深い個別の環境に寄り添った提案でした。

1) フォームからの回答があったときに、過去の分も含めた全ての回答がカレンダーに反映されてしまい、同じ予定がいくつもできてしまいます。
2) スプレッドシートが複数のフォームの回答先になったときに、どのフォームからの回答なのかを区別せずに処理が実行されてしまいます。
3) 日付の処理は最初のGPT-4の回答よりも簡潔になりました。同様の質問が繰り返されたことにより、AIが進化しているのでしょうか。
4) フォームの回答には、A列にタイムスタンプが加えられるので列がずれています。
加えて、
5) getRange getValueメソッドが行×列の数だけ繰り返されてしまう。AIは配列が嫌いなのでしょうか。

Answer 2

「ChatGPT(GPT-4)]により生成された文章です」

コードにいくつかの問題があるため、修正が必要です。以下の修正を行いました。

１．日付および時刻の結合方法が間違っていたため、正しく日付および時刻を結合するように変更しました。
２．スプレッドシートから取得した月（MM）と日（DD）を、JavaScriptのDateオブジェクトが期待する形式に変換しました。Dateオブジェクトは、月を0（1月）から11（12月）の範囲で、日を1から31の範囲で扱います。

function createEvent(e) {
  if (e === undefined) {
    console.log('このスクリプトは、エディタから実行できません。');
    console.log('フォームに回答してみてください。');
    return;
  }
  const items = [];
  const itemResponses = e.response.getItemResponses();
  for (const itemResponse of itemResponses) {
    items.push(itemResponse.getResponse());
  }
  let [abouttime, YYYY, MM, DD, hoursS, minutesS, hoursE, minutesE] = items;
  let id = '[e-mail]';
  let calendar = CalendarApp.getCalendarById(id);
  let title = abouttime;

  // Adjust month value for JavaScript Date object
  MM = parseInt(MM, 10) - 1;

  let startTime = new Date(YYYY, MM, DD, hoursS, minutesS);
  let endTime = new Date(YYYY, MM, DD, hoursE, minutesE);

  calendar.createEvent(title, startTime, endTime);
}

この修正後のコードを使用して、Googleフォームから送信されたデータをもとにGoogleカレンダーに予定を作成できるはずです。

Comments

[YellowGreen]

私も、最初はフォームのスクリプトでカレンダー登録と勘違いしていましたが、質問者様はフォームの回答先のスプレッドシートに蓄積される値からカレンダーに予定を登録する方法をお望みです。
私も興味がありますので、ChatGPT(GPT-4)がどのようなスクリプトを提案してくれるのかお聞かせください。

[ch-taros164]

何度も申し訳ないのですが、
const itemResponses = e.response.getItemResponses();
の行のエラーはどうしたら改善されるのですか？？

[YellowGreen]

煩雑になるので、質問本文のコメントに追記しますね。