ようへいの日々精進XP

よかろうもん

冬休みの自由研究 (2) 〜 Google SpreadSheet と Google Calendar の連携サンプルにコマンドラインツールの clasp を添えて 〜

tl;dr

以下のようなお誕生日リストを Google SpreadSheet で作っておいて, Google Calendar に登録する Google App Script (以後, GAS) を作ってみました. また, スクリプトをローカル環境からデプロイや関数の実行を操作出来る clasp も合わせて使ってみたのでメモしておきます.

f:id:inokara:20200103091651p:plain

作ったもの

github.com

スクリプトを実行すると, 下図のようにカレンダーに登録されます.

f:id:inokara:20200103105501p:plain

メモ

Google Calendar にイベントを登録する

Calendar クラスを利用して登録します.

developers.google.com

シンプルにイベントを登録する場合には, 以下のように createAllDayEvent メソッドを利用して書くことで登録できました.

// https://developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-date より引用
// Creates an all-day event for the moon landing and logs the ID.
var event = CalendarApp.getDefaultCalendar().createAllDayEvent('Apollo 11 Landing',
    new Date('July 20, 1969'));
Logger.log('Event ID: ' + event.getId());

一方, 今回のように誕生日のような, 年間で繰り返しで登録する場合には, createAllDayEventSeries メソッドを利用しました.

// https://github.com/inokappa/spreadsheet-to-calendar/blob/master/main.js#L10-L25
  var calId = PropertiesService.getScriptProperties().getProperty("clendarId");
  var cal = CalendarApp.getCalendarById(calId);
  var eventId = event['id'];
  var eventTitle = event['t'];
  var birthDay = event['dt'];
  var eventDesc = event['ds'];

  // カレンダーに登録 (毎年繰り返す)
  eventSeries = cal.createAllDayEventSeries(eventTitle, new Date(birthDay),
    CalendarApp.newRecurrence().addYearlyRule(), {description: eventDesc});
  if(eventSeries){
    sheet.getRange(eventId, 6).setValue("登録済み");
  }else{
    sheet.getRange(eventId, 6).setValue("登録失敗");
  }

さらに, createAllDayEventSeries メソッドの引数としては, タイトル, 開始日, そして, Recurrence (繰り返し) を設定してあげる必要があります.

developers.google.com

毎年の場合には, 上記のように addYearlyRule() メソッドを利用しています.

スプレッドシートで getRange().getValue() の利用を出来るだけ避けたい

当初は, 以下のようなコードを書いて満足していました.

function main() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('birthdays');
  var lastRow = sheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    var birthLastName = sheet.getRange(i, 1).getValue();
    if (birthLastName == "") {
      break;
    }

    var registed = sheet.getRange(i, 6).getValue();
    if (registed == "") {
      var today = new Date();
      var todayYear = today.getYear();
      var birthMonth = sheet.getRange(i, 4).getValue();
      var birthDate = sheet.getRange(i, 5).getValue();
      var birthDay = todayYear + "/" + birthMonth + "/" + birthDate;
      var birthFirstName = sheet.getRange(i, 2).getValue();
      var birthMember = birthLastName + " " + birthFirstName;
      var eventTitle = birthMember + " さん誕生日";
      var eventDesc = birthMember + "さん、お誕生日おめでとう! ";
(略)

スプレッドシートのセルから値を取得する為に, sheet.getRange(i, 2).getValue() のように getRange().getValue() 関数を多用しているのがどうしても気になってしまいました. getRange().getValue() 関数を多用しすぎると, エラーにはならないものの, 利用頻度が高い旨の警告が出るようです.

qiita.com

ということで, 上記の Qiita 記事にも書かれているように, getValue() ではなく, 以下のように getValues() 関数をラップした関数を作ることにしました.

function getBirthdays() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('birthdays');
  var values = sheet.getDataRange().getValues(); 
  return values;
}

こうすることで, シート全体の内容が以下のような配列として返ってくるので, これを処理するように実装しています.

[[ (必須入力),  (必須入力), 生年月日,  (必須入力),  (必須入力), 登録済み], [テスト1, 太郎, Mon Feb 03 00:00:00 GMT+09:00 2020, 2.0, 3.0, ], [テスト2, 花子, Sun Apr 05 00:00:00 GMT+09:00 2020, 4.0, 5.0, ], [テスト3, 太郎, Mon Jun 08 00:00:00 GMT+09:00 2020, 6.0, 8.0, ], [テスト4, 花子, Fri Jul 10 00:00:00 GMT+09:00 2020, 7.0, 10.0, ], [テスト5, 太郎, Sat Aug 08 00:00:00 GMT+09:00 2020, 8.0, 8.0, ], [テスト6, 花子, Tue Sep 01 00:00:00 GMT+09:00 2020, 9.0, 1.0, ], [テスト7, 太郎, Fri Oct 30 00:00:00 GMT+09:00 2020, 10.0, 30.0, ], [テスト8, 花子, Sun Nov 08 00:00:00 GMT+09:00 2020, 11.0, 8.0, ]]

裏取りは完全にできていないのですが, おそらく, getValue() は毎回スプレッドシートにアクセスするのに対して, getValues()スプレッドシートへのアクセスは一回になるので, getValues() を使ったほうがエコであることは間違いないようです.

サンプルでは, getValue() を使いまくっているスクリプトを main-old.js, getValues() に置き換えたバージョンを main.js として保存しています.

clasp は良い

clasp とは

clasp とは, GAS をローカル環境からガチャガチャ出来るコマンドラインツールです.

github.com

clasp を使うと, GAS のスクリプトがローカル環境のお気に入りのエディタで書くことが出来たり, 書いたスクリプトを直接デプロイしたり, 関数単位で実行することが可能になります. そして, ローカル環境にスクリプトを取得することも可能ですので, git でスクリプトが管理出来るようになる点です. 素晴らしいですよね.

clasp のインストール等の基本的な利用方法については, リポジトリREADME 等をご一読下さい.

clasp create からの clasp push

今回のように既に SpreadSheet は存在していて, その SpreadSheet でスクリプトを書きたい場合には, 以下のように実行することで SpreadSheet とスクリプト (プロジェクト) を関連付けることが出来ます.

$ clasp create --parentId ${スプレッドシートの ID}

後は, 適当なファイル名でスクリプトを書いていきます. サンプルでは, main.js というファイル名で書いています. 一通り書いたら, clasp push でプロジェクトにスクリプトを登録することが出来ます.

$ clasp push

あとは, ブラウザを開いて (clasp open で開くことが出来ます), スクリプトを実行してみましょう... しかし, なんか違和感があります. 時代は令和です, やっぱり, 全ての操作をコマンドラインだけで完結したくなりますよね.

clasp run

ということで, clasp run というコマンドが用意されています. その名の如く, 関数を実行する為のコマンドです. このコマンドを利用する為には, GAS のプロジェクトと GCP のプロジェクトを紐付ける為に, ひと手間が必要になります...

github.com

上記のドキュメントの https://github.com/google/clasp/blob/master/docs/run.md#setup-instructions に書かれている手順に従います. この手順を遵守すれば, 特にハマりポイントはありません. ただし, 個人的には GCP のプロジェクト番号を確認する方法が判らずに泣きそうになりました. 何のことない, 下図のように 設定 ページにて確認することが出来ました.

f:id:inokara:20200103104008p:plain

f:id:inokara:20200103104112p:plain

以下は, hello world を出力するだけの GAS を clasp run している様子です.

$ clasp run
Running in dev mode.
? Select a functionName helloworld
No response.
$ clasp logs
NOTICE     2020-01-03T01:15:33.220Z CreateProject   Setting up StackDriver Logging.
NOTICE     2020-01-03T01:15:35.484Z google.api.servicemanagement.v1.ServiceManager.ActivateServices Setting up StackDriver Logging.
NOTICE     2020-01-03T01:15:37.820744550Z google.api.servicemanagement.v1.ServiceManager.ActivateServices Setting up StackDriver Logging.
NOTICE     2020-01-03T01:15:39.241Z AssignResourceToBillingAccount Setting up StackDriver Logging.
NOTICE     2020-01-03T01:18:39.131Z CreateBrand     Setting up StackDriver Logging.
NOTICE     2020-01-03T01:19:29.055Z CreateClient    Setting up StackDriver Logging.
NOTICE     2020-01-03T01:21:38.087Z CreateClient    Setting up StackDriver Logging.
NOTICE     2020-01-03T01:23:15.875Z google.api.serviceusage.v1.ServiceUsage.EnableService Setting up StackDriver Logging.
NOTICE     2020-01-03T01:23:17.901384489Z google.api.serviceusage.v1.ServiceUsage.EnableService Setting up StackDriver Logging.
DEBUG      2020-01-03T01:24:08.766Z helloworld      {"message":"Hello world","serviceContext":{"service":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}}
DEBUG      2020-01-03T01:42:18.018Z helloworld      {"serviceContext":{"service":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"},"message":"Hello world"}

ついでに, clasp logs というスクリプトのログを標準出力に出力するコマンドも実行しています.

いい感じです.

以上

Google SpreadSheet と Google Calendar の連携サンプルをネタに色々と GAS をイジってみました. clasp を使うことで, 手元で好きなエディタでスクリプトを書いて clasp run で動作確認をするという流れが良い感じでした. 今後は, スクリプトを TypeScript で書いて (TypeScript を勉強するところから), テストも書いたり出来ると嬉しいなあと考えています.

引き続き, 精進してまいります.