バイセル Tech Blog

バイセル Tech Blogは株式会社BuySell Technologiesのエンジニア達が知見・発見を共有する技術ブログです。

LINEを使って自動で業績報告をしてみた話

はじめに

こんにちは。データサイエンス部の九町（くちょー）です。

今回は”サイエンス”とは関係ないのですが、会社のマクロ業績報告を自動化してみたお話をしたいと思います。ハードルとなったのは、弊社で敷いているセキュリティです。というか、この仕組みがないと逆にセキュリティ低過ぎて、別問題が発生してしまうのですが..。
弊社のデータサイエンスチームの立ち位置としては、分析から新しい示唆を出すことはもちろんですが、事業会社ならではのこのような集計レポーティングも大事な業務となっています。

環境

  • Google Cloud Strage（以下、GCS）
  • Google Apps Script（V8ランタイム）（以下、GAS）
  • Google SpreadSheet（以下、GSS）
  • ChatWork（以下、CW）
  • LINE Notify
  • bitly

ミッション背景

きっかけは役員の方が毎朝送信されていた業務報告にありました。機密上、お見せ出来ないのが残念なのですが（本当に見て欲しかった..）、毎日送っているとは思えないほどの膨大な情報量で…。これは見る方も送る方も大変だろうなって誰もが直感的に思うものでした。
僕の当初のミッションとしては、これを本当に必要なKPIのみに精査して、そしてその報告を自動化することです（後で少し要件は増えてしまいますが。ここは次のセクションで記します）。ということで、ものすごく分かりやすくシンプルな課題という訳です。

要件

基本要件

  1. KPIの精査、粒度の整備
  2. レポートの自動化

追加要件

どんな環境でも簡単に見れる….！？

実は基本要件まではほぼ1日で完了していましたが、それに対するFBでいただいたのがこの追加要件でした。
なので、この記事はこの追加要件に関わるお話になります。

どんな環境でも簡単に見れるとは？

これだけだと、何のことやら？という感じなので詳しく触れます。

前提

  • CWをメインのコミュニケーションツールとして採用している
  • CWへのログインは社用スマホでのアクセスをする or 社用PCでVPN接続してアクセスする必要がある
  • BIツールやG Suiteへのアクセスも似たようなもので、セキュリティ上そこそこ高いハードルがある

当初考えていたレポーティング手法

  • CWのある特定のグループにGSSから作成したPDFをGASで投げる

f:id:bst-tech:20201211170126p:plain

上司からのFB

シンプルに、もっと気軽に見たい！という要望でした。具体的には下記のようなことです。

  • 環境が限定され過ぎている：端末問題など
    • 社用端末を所有していないとアクセス出来ないなど一部の人に対して、レポーティング出来ない
    • 休みの日にわざわざ社用端末を出さないといけない
  • 環境が限定され過ぎている：ツールなど
    • CWにしてもBIツールにしても、G Suiteにしても”ログイン”という手間
    • ツールによってはそもそもアカウントがないというメンバーも多い

まぁ確かにレポーティングする立場としては、見てもらえないと話にならない訳でして。。
言われたことはご尤もと理解。…とは言え、セキュリティ大丈夫、、、？？

実現の必要が出てきたレポーティング

結論、要望を受け、目指したのは

  • グループLINEにGSSから作成したPDFをLINE Notify（GAS利用）で情報を投げる

というもの。
f:id:bst-tech:20201214100233p:plain

そう、「当初考えていた」ものとそれほど変わっておらず、「CWがLINEになっただけ」です。
….だけ、です。だけ、なんですが、諸々のハードルもありここからが少し大変でした。

ハードルと思考

LINE Notifyでは、PDFを投稿出来ない（png, imgなら可）
あれ？そうなのか。じゃあ画像にするしかないな..。と、思ったら、

GASでは、GSSをイメージ（png, img）化出来ない
え！？そんなことある？という落とし穴でした。ちなみに、

使用しているBIツールでもPDFは吐けるが、画像はダメ
でした。じゃあ、PDFをGoogle Driveに格納して、リンクを投げるしかないな。と、思ったら

（上にも書いたように）G Suiteは環境的にログインが面倒
あ、戻ってきてしまった。。これはボツ。

どこかサーバーに置こう。だけど、オープンなリンクでいいのか？誰かが端末落としたらまずいよな。
今更だけど、気軽に見たい⇔セキュリティ確保。この両輪ハードル高いな。

完全にど壺にハマりました..。

最終目指した形

  • GSSをGASでPDF化し、それをGCSにアップ
  • そのリンクをLINE Notifyで投げる（このリンクは何も制限のないオープンなもの）
  • この時にGCSにアップしたPDFは1日経ったら、削除する

セキュリティにやっぱり不安は残るものの、過去分が見れないようにしておくって対応さえできれば情報としては限定できるし、ってことで、ここに落ち着きました。

ここまでがとても長かった。ようやく実装です。
f:id:bst-tech:20201211171020p:plain

実装概要

  1. GSSからPDFを作成し、Google Driveに格納
  2. URLを作成し、GCSにPDFをアップ
  3. そのURLの短縮URLを取得
  4. 短縮URLをLINEにて送信
  5. GCSにアップしたPDFは1日で削除される

実装

GSSからPDFを作成し、Google Driveに格納

function createPDF(){  
  // PDFを格納するフォルダを指定
  var folderId = "xxxxx";
  var folder = DriveApp.getFolderById(folderId);
  // PDF化するスプレッドシートidを指定
  var ssId = "xxxxx";
  // PDF化するシートidを指定
  var sheetId = "xxxxx";
  // 作成するPDFのファイル名を指定（ここでは、「BST_RU_SalesReport_日時.pdf」となるようにしている）
  var fileName = "report";
  var datetime = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd_HHmmss');
  var fullFileName = fileName + '_' + datetime + '.pdf';

  // スプレッドシートをPDFにエクスポートするためのURL
  var url = "https://docs.google.com/spreadsheets/d/SSID/export?".replace("SSID", ssId);
  // PDF作成のオプションを指定
  var opts = {
    exportFormat: "pdf",    // ファイル形式の指定 pdf / csv / xls / xlsx
    format:       "pdf",    // ファイル形式の指定 pdf / csv / xls / xlsx
    size:         "A4",     // 用紙サイズの指定 legal / letter / A4
    portrait:     "true",   // true → 縦向き、false → 横向き
    fitw:         "true",   // 幅を用紙に合わせるか
    sheetnames:   "false",  // シート名をPDF上部に表示するか
    printtitle:   "false",  // スプレッドシート名をPDF上部に表示するか
    pagenumbers:  "false",  // ページ番号の有無
    gridlines:    "false",  // グリッドラインの表示有無
    fzr:          "false",  // 固定行の表示有無
    gid:          sheetId   // シートIDを指定 sheetIdは引数で取得
  };
  var url_ext = [];
  // 上記のoptsのオプション名と値を「=」で繋げて配列url_extに格納
  for(optName in opts){
    url_ext.push(optName + "=" + opts[optName]);
  }
  // url_extの各要素を「&」で繋げる
  var options = url_ext.join("&");
  // API使用のためのOAuth認証
  var gas_token = ScriptApp.getOAuthToken();
  // PDF作成
  var response = UrlFetchApp.fetch(url + options, {headers: {'Authorization': 'Bearer ' +  gas_token}});
  var blob = response.getBlob().setName(fullFileName);
  // PDFを指定したフォルダに作成
  var fileId = folder.createFile(blob).getId();
}

f:id:bst-tech:20201212113049p:plain
無事想定通りのファイル名で指定のフォルダに格納されました。

URLを設定（作成）し、GCSにPDFをアップ

それでは、このPDFをGCSにアップしていきます。
下準備として、まずはGCSのコンソール画面でPDFをアップする箱（プロジェクト、バケット、フォルダ）を用意します。今回は「RU Report」というプロジェクトの中に「bst_ru_reporting」というバケットを作成し、さらにフォルダとして「dailyReport_pdf」を準備しました。
f:id:bst-tech:20201212113149p:plain

それでは、アップしていきます。

function gcsUploadPFD(){
  // バケット名の指定
  var bucket = "bst_ru_reporting";
  // アップロード先フォルダを指定
  var gcsFolder = "dailyReport_pdf";
  // 格納パス設定
  var gcsPath = gcsFolder + "/" + fullFileName;
  // Access Tokenを取得
  var service = checkOAuth();
  // アップロードURLを組み立て
  var gcs_LongUrl = "https://www.googleapis.com/upload/storage/v1/b/" + bucket + "/o?uploadType=media&name=" + encodeURIComponent(gcsPath);
  // GSCにアップ
  var res = UrlFetchApp.fetch(
    gcs_LongUrl,
    {
      method:"POST",
      contentLength: bytes.length,
      contentType: blob.getContentType(),
      payload:bytes,
      headers: {
        Authorization: "Bearer " + service.getAccessToken()
      }
    }
  );
}

GCSリンクから短縮URLを取得

この工程はLINEに投稿するってことで、そのままのURLの長さだと見た目的に鬱陶しさがあるので、短縮にしたかっただけ（これはただのエゴですので、飛ばしてもOK）です。今回はbitly.comを利用しました（登録して、トークンを取得する必要があります）。ここの詳しい使い方はHashikakeさんに記載がありましたので参考にされてください。ちょっとした操作で簡単に短縮URLを作成することが出来ました。オススメです。
bitly.com
hashikake.jp

GAS側の記載はこんな感じ

function createBitlyUrl(longUrl) {
  var endpoint = 'https://api-ssl.bitly.com/v3/shorten?access_token=' + bitly_token + '&longUrl=' + longUrl;
  var result = UrlFetchApp.fetch(endpoint, {
    method: "GET",
    contentType: "application/json;"
  });
  var json = JSON.parse(result.getContentText('utf-8'));
  return json.data.url;
}

短縮URLをLINEにて送信

実装部分としては最終工程になります。あとは短縮したURLをLINE Notifyを使って該当のグループにえいっ！と投げます。これはLINEのアカウントがあれば、誰でも利用できるようです。詳しい内容はAPIドキュメントに記載があります。
notify-bot.line.me
f:id:bst-tech:20201212114620p:plain

ここでは2つの手順が必要です。

  1. トークンを発行する
  2. グループにLINE Notifyを招待する
トークンの発行

ログインしたら、アカウント名>マイページ>「トークンを発行する」で進みます
f:id:bst-tech:20201212114726p:plainf:id:bst-tech:20201212114739p:plain
通知を投げたいグループを探して選択してください（赤枠）
f:id:bst-tech:20201212114809p:plain
トークン名（青枠）はここで設定されたものが実際の通知の文頭に使われます。これは変更は出来ないので注意してください。例えば、このトークン名を「BST営業報告」とすると、下記のような通知になります。
f:id:bst-tech:20201212114843p:plain
そうすると、下記のようにトークンが発行されます。画面にも記載がありますが、これは二度と表示されないので、メモを取ってください。
f:id:bst-tech:20201212115027p:plain

グループにLINE Notifyを追加

上記で選択したトークルーム（グループ）にLINE Notifyを追加してください。普通に誰かをグループに招待する手順と同様です。このLINE Notifyというユーザが投稿した感じになるので、グループに入っていないと投稿自体が出来ません。

LINEの設定は完了です。GASはこんな感じです。

function sendLINE(content) {
  var options = {
    "method": "post",
    "payload" : {"message": content},
    "headers": {"Authorization": "Bearer " + GroupLINE_token}    
  };
  UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

contentは送信したい文章。GroupLINE_tokenは先ほど取得したトークンになります。

GAS全文です。（非エンジニアの為、可読性の低さや冗長性はご容赦ください..）

const GroupLINE_token = ['xxxxx']; //グループLINEのトークン：経営層グループ
const bitly_token = 'xxxxx'; //bitlyのトークン
const gssUrl = 'https://bit.ly/xxxxx'; //このGSSの短縮URL

function createPDF(){  
  // PDFを格納するフォルダを指定
  var folderId = "xxxxx";
  var folder = DriveApp.getFolderById(folderId);
  // PDF化するスプレッドシートidを指定
  var ssId = "xxxxx";
  // PDF化するシートidを指定
  var sheetId = "xxxxx";
  // 作成するPDFのファイル名を指定（ここでは、「BST_RU_SalesReport_日時.pdf」となるようにしている）
  var fileName = "BST_RU_SalesReport";
  var datetime = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMMdd_HHmmss');
  var fullFileName = fileName + '_' + datetime + '.pdf';

  // スプレッドシートをPDFにエクスポートするためのURL
  var url = "https://docs.google.com/spreadsheets/d/SSID/export?".replace("SSID", ssId);
  // PDF作成のオプションを指定
  var opts = {
    exportFormat: "pdf",    // ファイル形式の指定 pdf / csv / xls / xlsx
    format:       "pdf",    // ファイル形式の指定 pdf / csv / xls / xlsx
    size:         "A4",     // 用紙サイズの指定 legal / letter / A4
    portrait:     "true",   // true → 縦向き、false → 横向き
    fitw:         "true",   // 幅を用紙に合わせるか
    sheetnames:   "false",  // シート名をPDF上部に表示するか
    printtitle:   "false",  // スプレッドシート名をPDF上部に表示するか
    pagenumbers:  "false",  // ページ番号の有無
    gridlines:    "false",  // グリッドラインの表示有無
    fzr:          "false",  // 固定行の表示有無
    gid:          sheetId   // シートIDを指定 sheetIdは引数で取得
  };
  var url_ext = [];
  // 上記のoptsのオプション名と値を「=」で繋げて配列url_extに格納
  for(optName in opts){
    url_ext.push(optName + "=" + opts[optName]);
  }
  // url_extの各要素を「&」で繋げる
  var options = url_ext.join("&");
  // API使用のためのOAuth認証
  var gas_token = ScriptApp.getOAuthToken();
  // PDF作成
  var response = UrlFetchApp.fetch(url + options, {headers: {'Authorization': 'Bearer ' +  gas_token}});
  var blob = response.getBlob().setName(fullFileName);
  // PDFを指定したフォルダに作成
  var fileId = folder.createFile(blob).getId();
  // PDFをGCSにアップ
  var bytes = blob.getBytes();
  var gcsUrl = gcsUploadPFD(fullFileName, bytes, blob);
  createContent(fileId, gcsUrl);
}

function createContent(fileId, gcsUrl) {
  var date = Utilities.formatDate(new Date(), 'JST', 'MM月dd日');
  var yesterDay = sheet.getRange('A2').getValue();
  var date2 = Utilities.formatDate(yesterDay, 'JST', 'MM月dd日');
  var date2Month = Utilities.formatDate(yesterDay, 'JST', 'MM月');
  
  var folderPath = "https://drive.google.com/file/d/";
  var urlOpt = "?openExternalBrowser=1"; //LINEブラウザではなく、外部ブラウザで開かせる
  var PDF_longUrl = folderPath + fileId + urlOpt;
  var PDF_shoterUrl = createBitlyUrl(PDF_longUrl); //bitlyApiで短縮URLに
  var content = date;
  content += "\n\n▼" + date2 + "の実績速報";
  // ~~~中略：GSSより各種実績を取得
  content += "\n\n▼" + date2Month + "の着地予想";
  // ~~~中略：GSSより各種実績を取得
  content += "\n\n▼詳細はPDFで\n";
  content += gcsUrl;
  content += "\n\n▼さらに詳細はGSSで\n";
  content += gssUrl;
  sendLINE(content);
}

function sendLINE(content) {
  var options = {
    "method": "post",
    "payload" : {"message": content},
    "headers": {"Authorization": "Bearer " + GroupLINE_token}    
  };
  UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}

function createBitlyUrl(longUrl) {
  var endpoint = 'https://api-ssl.bitly.com/v3/shorten?access_token=' + bitly_token + '&longUrl=' + longUrl;
  var result = UrlFetchApp.fetch(endpoint, {
    method: "GET",
    contentType: "application/json;"
  });
  var json = JSON.parse(result.getContentText('utf-8'));
  return json.data.url;
}

// HTML側でチョイスしたファイルのIDを元に処理を開始する
function gcsUploadPFD(fullFileName, bytes, blob){
  // バケット名の指定
  var bucket = "bst_ru_reporting";
  // アップロード先フォルダを指定
  var gcsFolder = "dailyReport_pdf";
  // 格納パス設定
  var gcsPath = gcsFolder + "/" + fullFileName;
  // Access Tokenを取得
  var service = checkOAuth();
  // アップロードURLを組み立て
  var gcs_LongUrl = "https://www.googleapis.com/upload/storage/v1/b/" + bucket + "/o?uploadType=media&name=" + encodeURIComponent(gcsPath);
  // GCSにアップ
  var res = UrlFetchApp.fetch(
    gcs_LongUrl,
    {
      method:"POST",
      contentLength: bytes.length,
      contentType: blob.getContentType(),
      payload:bytes,
      headers: {
        Authorization: "Bearer " + service.getAccessToken()
      }
    }
  );
  
  // アクセスURLを短縮に
  var gcs_PublishUrl = "https://storage.googleapis.com/" + bucket + "/" + gcsPath;
  return createBitlyUrl(gcs_PublishUrl);
}

GCSから消す設定をする

忘れないようにしっかりアップされたPDFが削除されるように設定しておきます。
具体的には「ライフサイクル」という機能を使用していきます。このライフサイクルはバケット毎に設定が可能になっているようです。
GCSコンソールから該当のバケットに移動します。バケットに入ると、「ライフサイクル」タブ→ルールの追加と進みます。
f:id:bst-tech:20201214101752p:plain
今回は

  • アクションを選択：オブジェクトを削除する
  • オブジェクト条件の選択：年齢1日

期間以外にも月末に削除などの日時指定、Strageの料金をしっかり抑える為にがある一定になったらと言ったようなトリガーをつけることも可能です。
今回は次の日には見れなくなって良いので「1日」と設定しました。
f:id:bst-tech:20201214102124p:plain

効果

クリックやアクセスをカウントした訳ではないので、定量評価はないのですが、ちょっと数字が違ったり（何故かGASトリガーが上手く動かず、数字が更新されないことがある）すると、すぐに連絡がきたりするので、見てくれてはいるんだなぁという程度の感覚はありますw

僕自身も強制的に業績を目にするのでその日やその週、月のマクロの業績は常に頭に入っているようになりました。これよりも細かい粒度にしてしまうと、情報量が多く、見なくなったりするので今くらいの情報量（昨日の実績、当月の着地予想、月間・週間推移[PDF]）くらいで十分かなと思っており、満足しています。

まとめ

今回は最初の要件からまさかのFB（気軽に見たい）を受けたことで、自分自身色々と勉強になりました。bitlyもLINE NotifyもGCSもGAS以外の部分は完全に初めてだったので。

分析レポートも大切ですが、こんな風に普段見れる数値をどれだけ社員に浸透・意識させられるかってことも業績改善に間接的に繋がると信じて、また違う角度で取り組めたらと思いました。

バイセルでは、このように事業に寄り添ったレポーティング、分析・開発を一緒にやってくれるメンバーを募集しています。