pixiv insideは移転しました！ ≫ https://inside.pixiv.blog/

Google Apps Scriptで業務を効率化！ 大切なものは全てGoogle先生が作ってくれていた

この記事は、ピクシブ株式会社 Advent Calendar 2016 の19日目の記事です。

qiita.com

こんにちは、2代目社内旅行エンジニアこと@ikです。 普段は社内サービスで利用する決済システムを主に開発しています。今回の記事では、Google Apps Scriptを使用して、スプレッドシートの自動更新や、自動でのメール送信、Slackへの定期投稿などを行い、業務を効率化させる話をします。

Google Apps Script とは

Google Apps Script(GAS)は、Google スプレッドシートや、Googleフォーム、GmailなどのGoogleサービスと強力に連携できるJavaScriptベースのスクリプト言語です。

f:id:ik11235:20161218193642p:plain

スクリプト単体でも動作させることが出来る上に、スプレッドシートなどから呼び出して実行することも出来ます。 時間単位やイベント（スプレッドシートの更新やフォームの送信など）単位での自動実行も可能なので、定期実行用のサーバを自前で用意せずに、特定の処理を定期実行させることも可能です。

GASで出来ること

Google Apps Script は、前述のように多くのGoogleサービスと連携させることが出来るため、出来ることの幅は非常に多岐に渡ります。そのため、ここでは一部を抜粋して紹介します。 Googleサービスとの連携機能は、公式のリファレンスに記載されているので、詳しくはそちらを参照してください。

https://developers.google.com/apps-script/reference/developers.google.com

スプレッドシートのデータを読み書きする

例えば、スプレッドシートに以下のようなデータがあったとします。

f:id:ik11235:20161218201820p:plain

これをGASで読み込むには以下のコードを書くだけです。

  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在開いている(アクテイブな)スプレッドシートを取得
  var Sheet = ss.getActiveSheet(); //現在開いている(アクテイブな)シートを取得
  var Value = Sheet.getRange(2, 1, 3, 2).getValues(); // Sheetの2行1列目(A2)を起点に3行2列分のデータを取得
  // => [[ピクシブ 太郎, Mon Sep 10 00:00:00 GMT+09:00 2007], [山田 次郎, Sat Oct 11 00:00:00 GMT+09:00 2008], [田中 花子, Thu Nov 12 00:00:00 GMT+09:00 2009]]

逆に書き込む場合は、以下のコードです。

  var ss = SpreadsheetApp.getActiveSpreadsheet(); //現在開いている(アクテイブな)スプレッドシートを取得
  var Sheet = ss.getActiveSheet(); //現在開いている(アクテイブな)シートを取得
  var Range = Sheet.getRange(Sheet.getLastRow() + 1, 1, 1, 2); // Sheetの最終行+1行1列目を起点に1行2列分の範囲を取得
  Range.setValues([["佐藤 三郎", new Date()]]); // Rangeにデータをset

これで、すでにデータが入っている行に続いてデータが挿入されます。

f:id:ik11235:20161218201721p:plain

メールを読み込む、送信する

Google Apps ScriptはGmailとの連携もサポートされているので、Gmailのメールの読み出し、Gmail経由でのメール送信も可能です。

  // 読み出し
  var inbox = GmailApp.getInboxThreads(); // Gmailの受信ボックスを取得
  var thread =  inbox[0];// 先頭のスレッドを取り出す(確認用)
  var msg = thread.getMessages()[0]; // スレッドの先頭のメッセージを取り出す(確認用)
  
  var date = msg.getDate(); // 日付
  var from = msg.getFrom(); // 送信元アドレス
  var to = msg.getTo(); // 受信アドレス
  var subject = msg.getSubject(); // 件名
  var body = msg.getBody(); // 本文

  // 送信
  GmailApp.sendEmail(
    "[email protected]",  //送信先アドレス  
    "メールタイトル",
    "メール本文",
    {
      cc:  "[email protected]", // ccでの送信先アドレス
      bcc: "[email protected]", // bccでの送信先アドレス
      from: "[email protected]", // 送信元アドレス(利用しているgmailアカウントで設定可能な送信元のみ)
      name: "example" // 送信者名
    }
  );

外部サーバにGET, POSTする

GASから、HTTPでのGET, POSTリクエストが可能なので、特定のwebページから情報を取得することが可能です。

また、Googleサービス以外の連携は、標準では対応していませんが、HTTPで通信可能なAPIが存在していればそれらのサービスとも連携させる事ができます。

// GET
  var response = UrlFetchApp.fetch("http://www.example.com/"); // 与えられたURLにリクエストを行い、レスポンスを取得する
  var headers = response.getHeaders(); // HTTPレスポンス内のヘッダーを取得
  var responseCode = response.getResponseCode(); // HTTPレスポンス内のレスポンスコードを取得
  var contentText = response.getContentText()); // 取得したレスポンスをStringとして取得

// POST
  var payload = {
    "message" : "Sample message."
  }; 
  var options = {
    'method' : 'post',
    'contentType' : 'Content-type: application/json; charset=utf-8',
    'payload' : payload
  }
  
  var response = UrlFetchApp.fetch("http://www.example.com/", options);

なぜGASが良いの?

Googleの各種サービスとの連携が強いという点、それによって基本的な開発に必要な物が全て揃っている点が魅力的です。

Googleサービスとの連携が強いため、先程書いたようにスプレッドシートに記載したデータを元にスクリプトを実行することが出来ます。これによって、スクリプトに使用する元データをエンジニアだけでなく、スプレットシートを共有している人であればだれでも編集することが出来ます。

さらに、スクリプトで必要な機能が一通り揃っています。例えば自前でメールを定期送信する処理を作ろうと思うと、考えなければならない箇所が大量にあります。

  • メール送信をどこからどうやって行うか
  • 定期実行処理を設定はどこで、どのようにするか
  • データの記録はどこで行うか

GASを使えば、こういったことに必要なもののほとんどがすでに用意されています。

  • プログラム本体: Google Apps Script
  • メール送受信: Gmail
  • 記憶ストレージ: スプレッドシート
  • 定期実行: GASのトリガー

そのため、行うロジックと別途必要な外部サービスとのつなぎ方さえ考えれば、ほとんどの部分はすでにGoogle側が用意してくれているものに載せるだけで、処理を実行することが出来ます。

実際に業務に使えるスクリプトを作る

先程の節ではGASのできることを例示するために簡単なスクリプトのみを記載していましたが、ここからは実際に日常の業務に役立つスクリプトを紹介します。

毎日の日直をSlackに投稿する

チームに所属していれば、何かしらの当番で回すタスクが発生することがあります。しかし、スプレッドシートに書いているだけでは忘れることもあります。そのため、こういったものは直前や当日にリマインドしてほしいものです。

とは言え、それを人力で毎日リマインドするわけにはいかないので、スクリプトでリマインドすることを考えます。弊社ではコミュニケーションツールにSlackを使用しています。今回はSlackでリマインドの投稿をすることを考えます。

Slackに投稿するスクリプトを書くのは簡単ですが、メンバーの急な予定変更や増員などを考えると、スクリプト内だけでそれを管理するのは危険です。 しかもチームメンバーが全員エンジニアである保証はありません。エンジニア以外でも簡単に書き換えられることを考慮して、スプレッドシートで管理することにします。

f:id:ik11235:20161218212505p:plain

このシートに記載されたデータを元にして、毎日Slackに投稿するスクリプトを作成します。

var SHEET_URL = "https://docs.google.com/spreadsheets/d/XXXXXXXXXXXX";  
var SHEET = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName("日直シート");

var WEBHOOK_URL = 'https://hooks.slack.com/services/XXXXXXXXXXXXXXXXXXXXXXXX';
var SLACK_USERNAME = '日直を伝えるdog';
var SLACK_ICON_EMOJI = ':dog:';

function postData(date){
  var maxRows = SHEET.getMaxRows(), maxCols = SHEET.getMaxColumns();
  // 2行目以降から最期の行まで取得する
  var values = SHEET.getRange(2, 1, maxRows, maxCols).getValues();  
  var text = "";
  for(var i = 0; i < values.length; i++) {
    colDate = new Date(values[i][0]);
    
    // 日付のみで比較する。一致した場合、slackに投稿する
    if(colDate.toLocaleDateString() == date.toLocaleDateString()) {      
      var reviewer = values[i][1];
      var slackID = values[i][2];
      for(var j = 1; j <= 7; j++) {
        text = text + (new Date(values[i+j][0])).toLocaleDateString() + "\t" + values[i+j][1] + "\n"; 
      }
      var title = "今日の日直は "+ reviewer +"(" + slackID + ") です。よろしくおねがいします。";
      var attachments =  [
        {
          color: "#0000FF",
          title: "今後の予定",
          title_link: SHEET_URL,
          text: text
        }
      ];
      
      postToSlack(title, attachments);
      
    } else if(colDate.toLocaleDateString() > date.toLocaleDateString()) {
      // 現在日よりもデータの日付が後になった場合、当日は該当データがないので、skip
      return;
    }
  }
}

function cron() {
  var now = new Date();
  
  postData(now);
}

function postToSlack(text, attachments) {  
  var res = UrlFetchApp.fetch(WEBHOOK_URL, {
    method: "post",
    payload: JSON.stringify({
      username: SLACK_USERNAME,
      icon_emoji: SLACK_ICON_EMOJI,
      mrkdwn: true,
      link_names: true,
      text: text,
      attachments: attachments
    })
  });
}

このスクリプトで行っていることは、大まかに説明すると以下のようになります。

  1. スプレッドシートから日直が書かれた範囲を全て取得
  2. 取得したデータを探索して、今日の日付の行を探す
  3. 見つかった場合、Slackに投稿する
    • Slackへの投稿はWebHooks を利用すれば、POSTリクエストを送るだけでSlackへの投稿ができます

しかし、このままではこのスクリプトを毎回手動で実行しなければならないので、時間で自動的に実行されるようにします。

以下の手順で、毎日9時~10時の間にcronが実行されるように設定します。 f:id:ik11235:20161218224843p:plain f:id:ik11235:20161218224941p:plain f:id:ik11235:20161218225005p:plain

これで、毎日指定した時間に自動でslackに投稿してくれるようになりました。

f:id:ik11235:20161218215425p:plain

スプレッドシートが更新されたらSlackに投稿する

弊社では、新しい機能のリリース前の社内レビューの際に、感想やバグ報告をスプレッドシートに書いてもらうことがよくあります。

レビュー期間が短期であれば、定期的に開発者が見に行けますが、長期に渡ってレビュー・修正を繰り返しているプロジェクトになると、日が進めばそれだけレビューの追加頻度は減ります。追加頻度が減るほど、見に行く頻度も下がり、更新があっても気づけ無い恐れがあります。

なので、スプレッドシートが更新されたら投稿されるようにします。

変更があるたびに投稿する

スプレッドシートが変更されるたびに実行するには、トリガーを設定するだけで可能です。 例えば、先程のスクリプトで更新があるたびに投稿するようにするには、以下のように設定します。 f:id:ik11235:20161219001132p:plain

これで、スプレッドシートが編集(更新)されるたびにslackに投稿するスクリプトが実行されます。

1時間に1度、更新を確認して、更新されていたら投稿する

しかし、更新が頻繁にあるようなシートでこれを行うと、毎回slackに投稿されることになります。

そのため、1時間に1度更新を確認して、更新があれば投稿するようにしてみます。

初めに、新しく以下のようなシートを追加します。 f:id:ik11235:20161219130124p:plain

その上で、先程のスクリプトに以下のコードを追加(cron関数は書換)します。

var DATA_SHEET = SpreadsheetApp.openByUrl(SHEET_URL).getSheetByName("更新用データ");
var last_update_range = DATA_SHEET.getRange(1, 2);
var last_post_range = DATA_SHEET.getRange(2, 2);

function cron() {
  var now = new Date();
  if(last_update_range.getValue() > last_post_range.getValue()) {
    postData(now);
    updateLastPostDate(now);
  }  
}

function updateValue() {
  last_update_range.setValue(new Date());
}

function updateLastPostDate(now) {
  last_post_range.setValue(now);
}

そして、トリガーを以下のように設定します。

f:id:ik11235:20161219002233p:plain

これでシートが更新されたら、以下のように更新された時刻がシートに記入されます。

f:id:ik11235:20161219130508p:plain

そして1時間毎に更新があれば、Slackに投稿、その投稿時刻もシートに記載されます。

f:id:ik11235:20161219130842p:plain

変更されたのは、以下の点です。

  1. シートが更新されるたびに、updateValue()を呼び出し、最後にシートが更新された時間をスプレッドシート内に記録
  2. cronが実行される際に最後にシートが更新された時間と最後にSlackに投稿された時間 を比較
    • 最終更新時間 > 最終投稿時間 であれば新しく投稿する
    • 投稿した場合は、最終投稿時間を更新する

今回の例では、Slackへの投稿を行いましたが、この部分をメールで送信しようと思えば、その部分だけ書き換えればメールで送信を行うことも、ドキュメントに決まった書式で書き込むことも出来ます。

まとめ

紹介したようにGoogle Apps Scriptでは非常にいろいろな処理が出来ます。極端なことを言えば自社サービスに専用のAPIを生やすなどすれば、全てのデータの集計すらもGASの上でできるかもしれません*1。

もちろん、扱うデータの性質によってはGoogleサービス上に置くべきではないものであったり、そもそもGASだけではデータを取得・送信するのが困難なものも存在するので、そういった点を考慮する必要はあります。

しかし、そういったことを考える必要がないものについては、非常に手軽に利用できるので、是非活用してみてください。

引き続きピクシブ株式会社 Advent Calendar 2016をお楽しみください。

明日のアドベントカレンダー担当は、草野球系新卒エンジニアの@pawaくんです。ご期待下さい。

*1:現実的には、1実行あたり6分の制限があるので、膨大なデータに対して処理を行う際には、そこも考慮して作る必要があります