株主優待到着記録をGoogleカレンダーに取り込んでみる。

 2021年は、1年間、株主優待の到着記録をつけていました。これは、Googleスプレッドシートで手動で記録したものをブログに公開する形をとっています。

具体的にいうと、スプレッドシートでは、下記のような感じで記録してています。

今回は、この情報をGoogleカレンダーに取り込んでみて、到着情報をカレンダー形式で閲覧できるようにしてみました。

その結果はこんな感じです。   

今後も定期的にこのカレンダーをUpdateできるようにGoogle Apps scriptというのを利用してスクリプト化してみました。これをすることによって、Googleスプレッドシートに優待情報を登録だけしておけば、自動的に同期するというようなことも実現可能になります。

実際に書いてみた高度は、文末に参考情報として記載しておきますが、このプログラムでは主に下記のことを実行しています。

• スプレッドシートの情報をリンク情報も込みで読み込む
• Googleカレンダーに終日の予定として書きこむ
• 年単位で登録済みのカレンダーを削除する

Google apps script(通常GASと略される)は、実行環境が簡単に手に入るので、初心者のプログラム言語学習ツールとしてもいいのではないかと思います。Googleのサービスとの親和性も高く、うまく利用するととても有用なツールになると思います。

Google Apps scriptをどうやって始めればいいかというのについては、他にもいろいろまとまったページもあるので、そちらに譲ります。例えば、こちらのサイトなどは、初めてやる人が見るものとしてはよくまとまっていそうです。なお、初めて実行する際には、権限の確認とか出てきます。また、個人のアカウントだと安全でないというようなエラーっぽいのが出てきますが、これもこちらのサイトを参考にして許可していくといいと思います。

注意）本ページも含めてこの許可を実行する際には、コードを理解して安全であることを確認したうえで許可することを強く推奨します。なぜなら、こちらはプログラムの実行であり、悪意のあるコードを埋め込むことは理論上可能だからです。例えば、Gogoleドライブ上にファイルをスキャンしたり、アドレス帳の情報を取得して外部に情報を流すみたいな悪意のあるプログラムを書くことは実際に可能であり、そういうリスクがあることはご理解ください。

以下に、コードの説明を抜粋して書いてみます。よければ皆様もぜひご自身で実行してみていただくといいと思います。

最初に、スプレッドシートを読み取る部分です。

  // IDはいわゆるスプレッドシートを開いた時のURLに含まれる長い文字列です。

var ss = SpreadsheetApp.openById("GSS_ID");

// 上のスプレッドシートの指定された名前のシートを開きます。

  var sheet = ss.getSheetByName("GSS_SHEET_NAME");

//B列1行目からD列100行目までの値を取得します。getRichTextValuesの方は、

//URLなどを取得したいときに使います。

  var values = sheet.getRange("B1:D100").getValues();

  var rvalues = sheet.getRange(B1:D100").getRichTextValues();

値にアクセスするときには、values[0][1]というようにvalues[行数-1][列数-1]でアクセスしていきます。以下は、順番に100行のデータを読み取るサンプルです。

  for (var i = 0; i < values.length; i++) {

      var title = rvalues[i][1].getText();

      var url= rvalues[i][2].getLinkURL();

  }

次に、カレンダーの既存の部分を削除するパートです。これがなければ実行するたびにどんどんと増えてしまいます。

  var cals = CalendarApp.getCalendarsByName("CALENDER_NAME");

  var endDate = new Date("2021/12/31");

  var startDate = new Date("2021/1/1");

//2021年に登録されたものをすべて取得します。日付は上で定義しています。

  var events = cals[0].getEvents(startDate, endDate);

  for (var i in events) {

    //イベントを削除します。

events[i].deleteEvent();

  }

最後に登録する部分のロジックは下記の感じです。

  //同じ名称のものは1つしかない前提で0番目にアクセスしています。

var caender = cals[0];

// 2021/1/1にスケジュールを終日予定をします。

var ce = calender.createAllDayEvent("タイトル", new Date("2021/1/1"););

// 上で登録したスケジュールに補足説明を追記

  ce.setDescription("説明をここに書く");

こちらのコードを組み合わせて書いた全文は下記になります。

const CURRENT_YEAR = 2022;

const GSS_ID = "1a3EKPwYaNXXXXXXXXXXXXXXXXXXXX";

const GSS_SHEET_NAME= "到着記録";

const CALENDER_NAME="株主優待到着記録"

function execute() {

  var ss = SpreadsheetApp.openById(GSS_ID);

  var sheet = ss.getSheetByName(GSS_SHEET_NAME);

  var startRow = 3;

  var endRow = sheet.getLastRow();

  var values = sheet.getRange("B" + startRow + ":D" + endRow).getValues();

  var rvalues = sheet.getRange("B" + startRow + ":D" + endRow).getRichTextValues();

  updateCalender(values,rvalues);

}

function updateCalender(values,rvalues) {

  var cals = CalendarApp.getCalendarsByName(CALENDER_NAME);

  var endDate = new Date(CURRENT_YEAR + "/12/31");

  var startDate = new Date(CURRENT_YEAR + "/1/1");

  var events = cals[0].getEvents(startDate, endDate);

  for (var i in events) {

    Logger.log(events[i].getStartTime().getFullYear());

    events[i].deleteEvent();

    Utilities.sleep(50);

  }

  var pTitle = "";

  var content = "";

  var pdt;

  var isWrite = false;

  for (var i = 0; i < values.length; i++) {

    if (rvalues[i][1].getText()!='') {

      var title = rvalues[i][1].getText();

      var dt = new Date(values[i][0]);

      if (pTitle == title) {

        content = content + "," + getRText(rvalues[i][2]);

        isWrite = true;

      } else {

        if (pTitle!=''){

          writeCalender(cals[0], pdt, pTitle, content);

        }

        content = getRText(rvalues[i][2]);

        pTitle = title;

        pdt=dt;

        isWrite = false;

      }

    }

  }

  if (isWrite) {

    writeCalender(cals[0], pdt, pTitle, content);

  }

}

function getRText(rtext) {

  var ltext = rtext.getLinkUrl();

  if(ltext){

    return Utilities.formatString('<a href="%s">%s</a>', rtext.getLinkUrl(), rtext.getText());

  }else{

    return rtext.getText();

  }

}

function writeCalender(calender, dt, title, content) {

  var ce = calender.createAllDayEvent(title, dt);

  ce.setDescription(content);

  Utilities.sleep(100);

  Logger.log(dt);

}

参考）

• 2021年株主優待到着記録
• 優待到着記録(カレンダー方式)