【一括登録】関数で改善・GASを使ってスプレッドシートをもとにカレンダーに登録する【GAS】【Googleスプレッドシート】【Googleカレンダー】【関数】

今回は、こちらの記事の続きです。

スプレッドシートに記載した情報を、Googleカレンダーに一括登録するというスクリプトのご案内でした。
スプレッドシートはこういう表です。

前回繰越の課題

１．日付をもう少し入力しやすくできないか
２．招待リスト、カンマ区切りは書きにくい
３．繰り返しがエラーで終わるのが気持ち悪い
この3つ課題として改善してみます。

課題解決のため、スプレッドシートを改定する

課題１：日付をもう少し入力しやすくできないか

10/21 10:00 って、みなさん入力しやすいですか？なんとなく、私打ちにくくて。いえ、困らないよという方はそのままでも問題ないのですけど。

=date(A2,B2,C2)+time(D2,E2,0)

スクリプトに、日時として理解させるために、時間の形式に変換する関数です。

date(2022,10,21) →　2022/10/21

time(11,30,0) →　11:30:00

これを＋でつないで、2022/10/21 11:30:00 という時間として認識できる形式になります。

機能を縮小化して、年・日付、同じにしています。

Googleカレンダーの機能は当然日をまたぐイベントや、終日イベントにも対応しています。
ここでは、複数入れる予定って、一日内に完結する予定だと想定しています。会社内での不定期ＭＴＧとか、社長のアポを代わりに入れるとか。
毎回、2022/10/21 って2回打ち込んでると間違って1年越しのイベントになってしまったり。
プロではありませんので、使用頻度の少ないものは切り捨てるのもメンテナンス性や効率化を上げる方法だと思います。

課題２：招待リスト、カンマ区切りは書きにくい

こちらに対する課題です。
aaa@bbb.com,ccc@ggg.comこれひとつのセルに入れるの難易度高くないですか？セルを分けて入力し、テキスト結合しましょう。「,」区切りが必要なのが注意点です。
スプレッドシートは、このように変更しました

TEXTJOIN(TRUE)で対応できます。

10名以上招待する場合が多いのでしたら、10以上にカスタマイズしましょう。
=TEXTJOIN(",",TRUE,【範囲】)の詳細はこちらをどうぞ

こちらは同じこと転用で、メールを自動送信するとき、「TO」「CC」「BCC」に複数メールアドレスを入力するときも同じ対応ができますね。

もう少し作りこむなら

招待リストが基本は固定されているなら、
氏名：メールアドレスの別の表をつくる
テキスト入力を指名で行い、VLOOKUP関数などでメールアドレスに変換するという次のステップも検討できるのではないでしょうか。

課題３：繰り返しがエラーで終わるのが気持ち悪い

こちらは以前メールでも利用したものを使います。
「列の一番下の行を取得する」スクリプトです。
var lastRow = sheet.getRange(1,1,1,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

完成したコード

function myFunction_rep2() {
　var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

 var  lastRow = sheet.getRange(1,1,1,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

　for(row=2;row<=lastRow;row++){

   var startDate = sheet.getRange(row,21).getValue();//開始  
   var endDate = sheet.getRange(row,22).getValue();//終了
   var title = sheet.getRange(row,23).getValue();//タイトル
   var setumei = sheet.getRange(row,24).getValue();//説明
　 var basho = sheet.getRange(row,25).getValue();//場所・住所
　 var shotai = sheet.getRange(row,26).getValue();//複数はカンマ区切り


  var defaultCalender = CalendarApp.getDefaultCalendar();//デフォルトカレンダー
  defaultCalender.createEvent(title, startDate, endDate,{description:setumei,location:basho,guests:shotai });
}
}

シートにあわせて各データを取得する列が変わっている

まとめ

機能の改善ということで記事を書いてみましたが、
スクリプトではなく、スプレッドシートの関数で対応したところが大きかったです。
プロの方には怒られるかもしれないですけど、簡単にスクリプトで再現できる日がきっと来ますので、その日までは関数と共存していきましょう！

今回のシートへのリンクです。
https://docs.google.com/spreadsheets/d/1TtLDR8md4BcnqfbmKSYh4O-pjRXYiNYMiuc3qyBDzfk/edit#gid=80126683