GoogleSpreadSheet / GoogleAppsScript のススメ

この記事は、ハンズラボ Advent Calendar 2017 8日目の記事です。

ハンズラボでiOSエンジニアをしている @kurapy-n と申します。普段はiPadのPOSレジの開発を行なっています。
2016年4月に新卒(大学では非情報系の理系専攻)で入社し、ほぼ一からプログラミングを学んできました。

最近ハマっている、GoogleAppScript(GAS)を使用したGoogleSpreadSheetの簡単な活用方法を紹介できればと思います。

GoogleSpreadSheetとは

Googleが無料で提供している、Webブラウザ上で動かすことができる表計算ソフトです。
Googleのアカウントを持っていれば使用することができます。

SpreadSheetの便利なところと言えば、やはりGoogleのアプリケーションや他のサービスとの連携がしやすい点にあると思います。

GoogleAppsScript(GAS)とは

JavaScriptを書くことで、Googleのアプリケーションをパワーアップさせることができるサービスです。

ライブラリを追加することで、Googleアプリ以外のサービスなどとも連携が可能です。

スプレッドシートの内容をSlackに通知してみよう

まずは何と連携したいかといえば、やはりSlackですよね！！！
自分のチーム以外の人に対して、出社なのか、休みなのか、イベント参加で不在なのか、わざわざGoogleカレンダーを見るのはめんどくさい時があります。そんな時、チームのchannelに勤務の予定が表示されていたらいいですよね。

そこで、シフト希望表を作成しているスプレッドシートから予定を取得し、このようにSlackに通知しています。

→
（弊社はシフト制なので、シフト表を作成しています。全員基本的に土日祝が休みです）

通知には、GASを使用しています。

Slackのtokenを取得する

Slackからtokenを取得します。
Legacy tokens | Slack

（本当はLegacy tokenではなく、必要な権限だけを与えたtokenを作成した方がいいですね）

GASにSlackのtokenを教える

取得したtokenは、GASのメニュー ファイル > プロジェクトのプロパティ > スクリプトのプロパティ のプロパティに token 、値には先ほど取得したtokenを貼り付けます。

「保存」を押せば、tokenの追加完了です。
コードに直書きしてもいいですが、いずれはLegacy tokenではなく必要な権限のみを加えたtokenを使用するつもりなので、簡単にtokenを変更できるようにプロパティに書いています。

ライブラリにSlackAppを追加する

GASのメニュー リソース > ライブラリ を選択し、Library Keyに M3W5Ut3Q39AaIwLquryEPMwV62A3znfOO を入力することでSlackAppを追加することができます。
このライブラリを追加することでSlackへ簡単にメッセージをpostすることができます！やったね！！

コードを書く

肝心のコードの内容はこんな感じになりました。
（業務の片手間に書いてからリファクタリングしていないので、コードがお粗末なのはご了承ください）

// 実際に動かす関数
function postMessage() {
  var text = getMessage();
  postSlack(text);
}

// postする内容を取得する
function getMessage() {
  var spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxx");
  // 日付と時間を取得する
  var dt = new Date();
  var today = Number(Utilities.formatDate(dt, 'JST', 'dd'));
  var this_hour = Number(Utilities.formatDate(dt, 'JST', 'HH'));

  var text = "今日のPOSチームの出勤状況\n";

  // 15時以降は翌日のシフトを参照
  if (this_hour > 15.0) {
    dt.setTime(dt.getTime() + (1*24*60*60*1000));
    today = Number(Utilities.formatDate(dt, 'JST', 'dd'));
    var text = "明日のPOSチームの出勤状況\n";
  }

  // 参照するスプレッドシートの名前を取得
  var this_month = Utilities.formatDate(dt, 'JST', "yyyy年M月");
  var sheet = spreadsheet.getSheetByName(this_month);
  
  var cell = sheet.getActiveCell();
  var sheetdata = [];

  //シートの最終行番号、最終列番号を取得
  var startrow = 1;
  var startcol = 1;
  var lastrow = sheet.getLastRow();
  var lastcol = sheet.getLastColumn();

  if (lastrow !== 0.0 || lastcol !== 0.0) {
    sheetdata = sheet.getSheetValues(startrow, startcol, lastrow, lastcol);
  }

  var shift_unit = [];
  var shift = [];
  // 横をなめて行き、参照する日付にたどり着くまでcontinue
  for(var i=4; i<sheetdata[6].length;i++){
    if (sheetdata[6][i] != today) {
      continue;
    }
   // 縦をなめて行き、チームメンバーの名前とシフト内容を取得し、配列にpush
   for(var k=18;k<sheetdata.length;k++){
      if (sheetdata[k][2] == "") {
        continue;
      }
      var name = sheetdata[k][2]
      switch (sheetdata[k][i+1]) {
        case "":
          var shift_naiyo = '出社';
        case "休":
          var shift_naiyo = '休み:zzz:';
        case "研":
          var shift_naiyo = '研修:lower_left_fountain_pen:';
        default:
          var shift_naiyo = sheetdata[k][i+1]; //上記以外
     }
     shift_unit.push(name + "： " + shift_naiyo);
     shift.push(shift_unit.join(": "));
     shift_unit = [];
   }
  }
  // 取得できた配列を、改行を加えてtextへ追加
  text += shift.join("\n");
  return text;

}

function postSlack(text) {
  var prop = PropertiesService.getScriptProperties().getProperties();
  var slackApp = SlackApp.create(prop.token);
  var postChannelId = "XXXXXXXX"；
  var option = {
    username: "shiftBot",
    icon_emoji: ":spiral_calendar_pad:",
    link_names: 1,  // 誰かにメンション送るなら必要
  };

  var response = slackApp.postMessage(postChannelId, text, option);
  Logger.log(response);
}

Webアプリケーションとして公開する

まずは、書いたコードを保存します。 ⌘ + s やら ctrl + s が使えます。
行末のセミコロンを忘れていたりすると、ここで怒ってくれます。いい子ですね。

保存後は公開のために、メニューの ファイル > 版の管理 でバージョン管理を行う必要があります。
任意で説明文を書いて「新しいバージョンを追加」を押します。
これで、改修を行う時に万が一満足な結果が得られないコードを保存してしまっても、前のバージョンに戻すことができます。

次に、メニューの 公開 > ウェブアプリケーションとして導入 を開き、
導入したいバージョンを選択、アプリケーションを実行するユーザーとアクセス可能なユーザーを選択します。
実行ユーザーは用途によるとは思いますが、今回のような外部アプリのBotのような形で使用するケースでは「自分」で大丈夫だと思います。
アクセス可能なユーザーは、組織で使い共有したい場合なら組織内、それ以外は「自分だけ」を選ぶのが良いと思います。

実行してみる！

メニューの 実行 > 関数を実行 > [実行させたい関数（ここではpostMessage）]を押すと……？

そしてBotへ……

せっかくGASを書いたので、手動実行ではなく自動化させましょう！
メニューの中に時計っぽいアイコンがあるので選択すると、トリガーをセットすることができます。

時間主導型の日タイマーをセットすれば、毎日動くbotになります。

Googleカレンダーへスケジュールを登録してみよう

スプレッドシートさえあれば、GASを使ったSlack連携は難しくなかったですよね？

それならば、Googleサービスとの連携はもっと簡単なのでは？？
ということで、シフト表からカレンダーへスケジュールの登録を行います。
今回は、休みなのか？出勤なのか？出張やイベント参加なのか？が分かればいいので、終日イベントを追加することを想定しています。

カレンダーへの終日イベントの追加はcreateAllDayEventという関数を使うのですが、日付はDate型で渡す必要があるところがあるので、そこだけ少し難しいのでコードを置いておきます。

/// カレンダーに終日イベントを追加する
///
/// - Parameters:
///   - calender_id: カレンダーID(Gmailアドレス)
///   - shift: カレンダーの題名(ここでは「休み」など、スプレッドシートから取得してくる)
///   - date: YYYY/mm/dd文字列
function register(calender_id, shift, date) {
  var calendar = CalendarApp.getCalendarById(calender_id);

  var all_date = toDate(date, '/');
  calendar.createAllDayEvent(shift, all_date);
}

/// 文字列を日付型に変換する
///
/// - Parameters:
///   - str: 文字列
///   - delim: 一旦配列に変換する際の分ける文字
function toDate (str, delim) {
  var arr = str.split(delim);
  return new Date(arr[0], arr[1] - 1, arr[2]);
};

カレンダーへの登録は月一の月タイマーをセットしておけば、シフトが確定したころに予定を入れてくれる気がきく子になってくれます。

総まとめ

今回は、GASを用いたGoogleSpreadSheetの活用方法を2つご紹介しました。
個人的には、Google HomeでIFTTTなどを用いてもっともっと活用していきたいです。

明日は、ハンズラボ Advent Calendar 2017 9日目です。
2回目の登場、@naokiurさんです！お楽しみに。

参考URL

複数の記事を参考にGASを作成しました。

初心者がGASでSlack Botをつくってみた - CAMPHOR- Tech Blog

Google Apps Script で spreadsheet から Googleカレンダーへイベント一括登録(2016/3/23 時点) - プログラマってこんなかんじ？？

Class Calendar  |  Apps Script  |  Google Developers