GAS でスプレッドシートからカレンダーに日をまたぐ終日予定を入れる

私はフリーランスをしているんですが、案件の管理を Google スプレッドシートで行っています。
以前、その Google スプレッドシートから Gmail でメールの下書きを作るための GAS(Google Apps Script)を作ったと書きました。

GAS(Google Apps Script)で下書きメールを作成する

こちらの回の続きです。簡単に言うと、メールを送信する直前に、必要なアドレスが入っているか添付ファイルが漏れていないかなどをチェックしたかったけど、Gmail だとそういう機能がなさそうだったのでメーラーを Outlook に変更し VBA ...

その後もちまちまと改良を加えていたんですが、最近「このまま Google カレンダーにも登録しよう」と思い、そちらの GAS も作りました。
Google スプレッドシートから Google カレンダーにスケジュールを登録する GAS は、それこそググればたくさん出てくるんですが、日をまたぐ予定の登録でちょっとつまずいたので載せておきます。

案件の Google カレンダーへの登録は、『終日』の予定としていました。
GAS で終日の予定を登録するには、『createAllDayEvent』を使います。

createAllDayEvent(タイトル, 開始日, 終了日)

今でも使っているスプレッドシートに仕事の予定をそのまま使うので、S 列(19列目)に案件の開始日、T列(20列目)に案件の終了日を入れる欄を作りました。
で、A 列(1列目)にチェックが入っている列に対して作業をするように作成しました。

A 列(1列目)　実行する行のチェックを入れる
N 列(14列目)　仕事をくれた会社の名前
Q 列(17列目)　案件名
S 列(19列目)　案件開始日
T 列(20列目)　案件終了日

function CreateNewSchedule() {

  var ssid = "　※案件を管理しているスプレッドシートの SSID　";
  var ss = SpreadsheetApp.openById(ssid);
  var datass = ss.getSheetByName("データ");
  var lastrow = datass.getLastRow();
  for (let i = 2; i<= lastrow; i++){
    if (datass.getRange(i, 1).getValue() == true){
      let myCalendar = CalendarApp.getCalendarById("　※案件の予定を入れたいカレンダーのID　");
      var title = "【" + datass.getRange(i, 14).getValue() + "】" + datass.getRange(i, 17).getValue();
      var end_date = new Date(datass.getRange(i, 20).getValue());
      end_date.setDate(end_date.getDate() + 1);
      myCalendar.createAllDayEvent(title, datass.getRange(i, 19).getValue(), end_date);

      datass.getRange(i, 1).setValue("");      
    }  
  }
}

『ssid』は、Google スプレッドシートの URL の『https://docs.google.com/spreadsheets/d/』の後ろから次のスラッシュまでの間の文字列です。
カレンダーの ID は、Google カレンダーで予定を入れたいカレンダーの『設定と共有』を開いて、下の方にある『カレンダー ID』に書かれている文字列です。
例えば、開始日が4/20、終了日が4/23で、会社名が『A社』、案件名が『チョコレートに関する仕事』だとすると、これを実行すると、4/20から4/23の間に終日予定で『【A社】チョコレートに関する仕事』という予定が帯状に入ります。

ここでポイントになるのが終了日である『end_date』です。
これね、はじめ普通に開始日と同じように入力していました。要するに

myCalendar.createAllDayEvent(title, datass.getRange(i, 19).getValue(), datass.getRange(i, 20).getValue());

としてたんですよ。そうしたら、なぜか、終了日の前日までしか予定が入らない。上の『チョコレートに関する仕事』で言うと、4/20から4/23に帯で入れてほしいのに、4/20から4/22までにしか帯が入らないんです。なんでだろう…と思っていろいろ調べたんですが、なんか「そういうものだ」としか…。『終了日』というものに関するとらえ方が違うんですかね…。仕事の最終日にはすでに仕事が終わっている、というのが Google さんの社内常識なんですかね…。違うか。

どうしようかな、と考えて、いろいろ案を出しました。

終了日には、本当の終了日の1日後の日付をいれる
　→ 混乱するし、別の GAS でこの日付を使って『担当になりましたメール』を送るので不可
終了日の隣の列に1日後の日付が入る欄を作る
　→ なんかダサい
コード上で1日後の日付にする
　→ まーこれだな

VBA だったらこんなの考えることもなかったんですが、使い慣れていないとなかなか気が回りませんね…。
というわけで、1日後の日付の取得の仕方を調べました。それが

var end_date = new Date(datass.getRange(i, 20).getValue());
end_date.setDate(end_date.getDate() + 1);

の部分です。一旦『end_date』にすでに入力してある終了日(4/23)を入れます。そのあと、『end_date』に+1した日付(4/24)を再度『end_date』に入れています。これで

myCalendar.createAllDayEvent(title, datass.getRange(i, 19).getValue(), end_date);

とすれば、4/20から4/24の1日前(4/23)まで帯状に予定が入ります。よかったよかった。
『var end_date = …』から2行使って日付を1日ずらしているんですが、これを1行で書けないか…といろいろやったんですが、なんか面倒になったのでこのままでいいやって。動いとるしね。
一応、この記事書く前に『copilot』さんに聞いてみたんですが、おんなじコード提示されたので、これで良しとします。
最後に

datass.getRange(i, 1).setValue("");

で、A列に入れたチェックを消しているんですが、カレンダーに入れた後にそのまま担当メールを送るから消さなくていいかなー。でも、送らない会社もあるから消したほうがいいかなー、どっちかなー、なんてどうでもいいことくらいですかね、後の改善点は。

まー、いろいろ考えるのはおもしろいです。
去年思い切って GAS に手を付けてみてよかった。ちょうど1年くらい前でしたね! やってみるもんです。
前の『請求書 PDF 作成してそのまま添付したメールの下書きを作る GAS』などで、作業効率は格段に上がったと思います。他に何かできることないかなーって探すのも楽しいですね。