spreadsheetで管理する日次KPIをSlackに自動投稿する

# 背景

- チーム全員が毎日KPIを確認し、各人が自律的にアクションをしていける組織環境を構築したい
- Slackの特定のグループに毎日自動通知する機能をGoogleAppsScriptにて実装し、上記環境を構築する。

## 前提

- SlackAppライブラリを使用する（[参考1](http://qiita.com/soundTricker/items/43267609a870fc9c7453)）。
- 基本仕様（[参考2](http://qiita.com/abe-perorist/items/dec054e95b473364cae6)）をよりSlackAppライブラリを活用する形へ変更、かつ日次で値を取得するセルを動的に変更する仕様とした。
- 上記2つを作成された方々へ最大限のリスペクトを送ります。

## 今回のOutput

- 毎日更新するKPI（業績重要指標）管理シートがある。毎日1列ずつ各KPI項目を更新している。

![image](https://qiita-image-store.s3.amazonaws.com/0/83135/f2d211d5-5e42-5c0a-98fc-047141dd8b67.png)

- ここから特定のKPI（例えば7行目「オーダー件数」と15行目「オーダー単価」、など）の値を日々動的に取得し、

- Slackに毎日自動通知するという機能を実装する

![image](https://qiita-image-store.s3.amazonaws.com/0/83135/b1c50959-9f5e-916f-43f8-08bcbe07455f.png)


# GASコード

- 全コード
- 各パート説明

に分けて説明する。なお[参考2](http://qiita.com/abe-perorist/items/dec054e95b473364cae6)記載の内容は割愛する。

## 全コード

```js:GoogleAppsScript

// 定義文
var slack = {
  postUrl: 'https://slack.com/api/chat.postMessage',
  token: '(A)', // Slackのtoken
  groupId: "(B)", // Slack MarketingグループのID
  userName: "(C)", // botの名前
}



// 前日のKPIを取得し、SlackのMarketingグループへ送る
function getTodayColumnPostSlack() {
  // 前日の日付をyyyy/MM/ddで取得する
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(D)");
  var yesterday = new Date(new Date().getTime()-1000*60*60*24);
  var formattedDate = Utilities.formatDate(yesterday, "JST", "yyyy/MM/dd");  
  
  // 前日日付のカラムの座標を指定する
  var startDate = new Date("2015/01/01");
  var msDiff = yesterday.getTime() - startDate.getTime();
  var column = Math.floor(msDiff / (1000*60*60*24)) + 3; 

  var cell;
  
  // KPIの値を取得する
  while(true) { 
    cell = sheet.getRange(2, column);
    if (Utilities.formatDate(yesterday, "JST", "yyyy/MM/dd") == formattedDate) {
      Logger.log("Findout");
      var orderNum = sheet.getRange(7, column).getValue().toFixed(0);
      var orderUnitPrice = sheet.getRange(15, column).getValue().toFixed(0);
      var signupNum = sheet.getRange(20, column).getValue().toFixed(0);
      var CPA = sheet.getRange(46, column).getValue().toFixed(0);
      var CPO = sheet.getRange(47, column).getValue().toFixed(0);
      var CVR = sheet.getRange(48, column).getValue().toFixed(4)*100;
      
      // Slackへ送る
      var slackApp = SlackApp.create(slack["token"]);
      var Slackpost = slackApp.postMessage(slack["groupId"],"```" + "\n" + "昨日のMarketing Quick Report" + "\n" + "オーダー件数：" + orderNum + "\n" + "オーダー単価：" + orderUnitPrice + "\n" + "会員登録数：" + signupNum + "\n" + "CPA：" + CPA + "\n" + "CPO：" + CPO + "\n" + "CVR：" + CVR + "%" + "\n" + "```", {username : slack["userName"]});
      return column;
    } else {
    }
    column += 1;
  }
}
```

## パート説明

### 定義文

```js:GoogleAppsScript
// 定義文
var slack = {
  postUrl: 'https://slack.com/api/chat.postMessage',
  token: '(A)', // Slackのtoken
  groupId: "(B)", // Slack MarketingグループのID
  userName: "(C)", // botの名前
}
```

- SlackAPIを利用して、メッセージを送るgroupIDとuserNameを指定する。groupはchannelでも可能。
- (A)~(C)の設定方法については参考ドキュメントを参照。

### 前日の日付をyyyy/MM/ddで取得する

```js:GoogleAppsScript
// 前日の日付をyyyy/MM/ddで取得する
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("(D)");
  var yesterday = new Date(new Date().getTime()-1000*60*60*24);
  var formattedDate = Utilities.formatDate(yesterday, "JST", "yyyy/MM/dd"); 
```

- 本項目では前日の日付を`yyyy/MM/dd`形式で取得（KPIシート記入の日付と合わせる）し、次項目でカラム座標指定に使用する。
- (D) : KPIを参照するシート名を記入


### 前日日付のカラムの座標を指定する

```js:GoogleAppsScript
// 前日日付のカラムの座標を指定する
  var startDate = new Date("2015/01/01");
  var msDiff = yesterday.getTime() - startDate.getTime();
  var column = Math.floor(msDiff / (1000*60*60*24)) + 3; 
```

- 今回KPIを取得したいシートは2015/01/01が3行目に有り1日1行ずつ進んでいく形式である（例えば、2015/01/03は5行目にある）。
- 以下のロジックで前日の行数を本項目にて算出している。
  - getTimeメソッドにより、前日及び2015/01/01に対し、1970年1月1日00:00:00 UTC からの経過ミリ秒を算出し、差を算出(...a)
  - aを日数に変換し、カラムを算出するために+3を行う

### KPIの値を取得する

```js:GoogleAppsScript
// KPIの値を取得する
  while(true) { 
    cell = sheet.getRange(2, column);
    if (Utilities.formatDate(yesterday, "JST", "yyyy/MM/dd") == formattedDate) {
      var orderNum = sheet.getRange(7, column).getValue().toFixed(0);
      var orderUnitPrice = sheet.getRange(15, column).getValue().toFixed(0);
      var signupNum = sheet.getRange(20, column).getValue().toFixed(0);
      var CPA = sheet.getRange(46, column).getValue().toFixed(0);
      var CPO = sheet.getRange(47, column).getValue().toFixed(0);
      var CVR = sheet.getRange(48, column).getValue().toFixed(4)*100;
```

- 前項で指定したカラムを使用し、以下rowにあたるKPIを取得している。
- 値の性質に応じて、整数化や%表記化を行っている（toFixedなど）。

### Slackへ送る

```js:GoogleAppsScript
  // Slackへ送る
      var slackApp = SlackApp.create(slack["token"]);
      var Slackpost = slackApp.postMessage(slack["groupId"],"```" + "\n" + "昨日のMarketing Quick Report" + "\n" + "オーダー件数：" + orderNum + "\n" + "オーダー単価：" + orderUnitPrice + "\n" + "会員登録数：" + signupNum + "\n" + "CPA：" + CPA + "\n" + "CPO：" + CPO + "\n" + "CVR：" + CVR + "%" + "\n" + "```", {username : slack["userName"]});
      return column;
    } else {
    }
    column += 1;
  }
}
```

- 先ほど取得した値を、SlackAppがもつpostMessageメソッドを利用して送る。
  - optionとしてuserNameを指定している 

# コメント

- そもそもGoogleAnalyticsのデータを毎日spreadsheetsで自動取得し、超使えるKPIシートを自動生成するTipsも合わせてどうぞ

> [GoogleAnalytics API Add-onを使ってspreadsheetsを最強のKPI管理シートに - 基礎編](http://qiita.com/Yamotti/items/41a513fe9592ee0cd11a)


- SlackAppライブラリとても使いやすい。
- マーケターが「spreadsheet上で実装したい機能を作りたいものをGASで実現する」程度のリテラシーを持つと、グンと業務の幅や出せるインパクトが増えることを実感しました。

# 参考
1. [Slack BotをGASでいい感じで書くためのライブラリを作った](http://qiita.com/soundTricker/items/43267609a870fc9c7453)
1. [スプレッドシートで管理しているKPIをSlackに自動投稿するGoogle Apps Scriptを作ってみた](http://qiita.com/abe-perorist/items/dec054e95b473364cae6)

@abe-perorist さん、参考にさせていただきました。ありがとうございます。