Googleスプレッドシートの更新をChatWorkへ通知してみた

カテゴリーから探す

年別から見る

2017/08/10

Googleスプレッドシートの更新をChatWorkへ通知してみた

弊社では様々な連絡・コミュニケーションにChatWorkを利用しており、プロジェクトごとに必要な情報がだいたいChatWorkに集約していく、くらいに使い込んでます。

とはいえ、スプレッドシートでも様々な情報をまとめることもありますが、更新状況がいまいち掴みづらい。ということでGoogleスプレッドシートの更新状態を定期的にChatWorkへ通知する仕組みを作ったので解説します。

スプレッドシートの例

サンプルなので2列しかありませんが…　今回は「A列になんらかの入力があれば、ChatWorkへ通知する」という方針でやっていきます。

監視したいスプレッドシートのスクリプトエディタを起動して、Google Apps Scriptを書いていきましょう。Excelで言うところのVisual Basic for Applicationsみたいなものですが、こっちはJavaScriptベースなので馴染みやすいかと思います。こんな感じのスクリプトになります。


// 更新日時を記録するのスプレッドシートのID
// https://docs.google.com/spreadsheets/d/【ここ】/edit
var SHEET_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
//スプレッドシートのシート名
var SHEET_NAME = 'sample';

// ChatWork用定数
// チャットルームID
// https://www.chatwork.com/#!rid【ここ】
var ROOM_ID = 'YYYYYYYY';
// アクセストークン
var TOKEN = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ';

function postChatwork (message) {

    var payload = {
        'body' : message
    };

    var headers = {
        'X-ChatWorkToken' : TOKEN
    };

    var options = {
        'method' : 'post',
        'payload' : payload,
        'headers' : headers
    };

    UrlFetchApp.fetch('https://api.chatwork.com/v2/rooms/' + ROOM_ID + '/messages', options);
}

function onUpdateBatchCheck() {

    // スプレッドシートを特定
    var spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    var sheet = spreadsheet.getSheetByName(SHEET_NAME);

    // getvaluesの実行速度が遅いのでシート内容を一括で取得
    // A列1行目が[0][0]になることに注意する
    var data = sheet.getDataRange().getValues();

    // チャット送信状態を保持する列のインデックス
    var stateColumnIndex = 1;// B列でチャット送信状態を保持する
    var targetColumnIndex = 0;// A列の更新を監視する
    var targetRowIndexes = []; // チャット通知対象の行リスト

    // チャット通知対象の行リストを生成する
    // ヘッダが0行目なので読み飛ばす
    for (var i = 1; i < data.length; i++) {
        // チャット未送信状態　かつ　A列に何らかの入力がある
        if ((!data[i][stateColumnIndex]) && (data[i][targetColumnIndex])) {
            targetRowIndexes.push(i);
        }
    }

    // 通知対象行が0なら離脱
    if (targetRowIndexes.length == 0) return;

    // シートの行ごとにメッセージブロックを生成する
    var msg= '';
    for (var i = 0; i < targetRowIndexes.length; i++) {
        var row = targetRowIndexes[i];// シート上はi+1行目
        var cell = data[row][targetColumnIndex];// i+1行目A列のセルの値
        msg += Utilities.formatString('[info]%d行目が更新されました\n内容：%s[/info]', row + 1, cell);
        sheet.getRange(row + 1, stateColumnIndex + 1).setValue(true);
    }

    var message = Utilities.formatString('シート[%s]が更新されました\n%s', SHEET_NAME, msg);

    // ChatWorkへメッセージを送信
    postChatwork(message);
}

// 更新日時を記録するのスプレッドシートのID

// https://docs.google.com/spreadsheets/d/【ここ】/edit

var SHEET_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

//スプレッドシートのシート名

var SHEET_NAME = 'sample';

// ChatWork用定数

// チャットルームID

// https://www.chatwork.com/#!rid【ここ】

var ROOM_ID = 'YYYYYYYY';

// アクセストークン

var TOKEN = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ';

function postChatwork (message) {

var payload = {

'body' : message

};

var headers = {

'X-ChatWorkToken' : TOKEN

};

var options = {

'method' : 'post',

'payload' : payload,

'headers' : headers

};

UrlFetchApp.fetch('https://api.chatwork.com/v2/rooms/' + ROOM_ID + '/messages', options);

}

function onUpdateBatchCheck() {

// スプレッドシートを特定

var spreadsheet = SpreadsheetApp.openById(SHEET_ID);

var sheet = spreadsheet.getSheetByName(SHEET_NAME);

// getvaluesの実行速度が遅いのでシート内容を一括で取得

// A列1行目が[0][0]になることに注意する

var data = sheet.getDataRange().getValues();

// チャット送信状態を保持する列のインデックス

var stateColumnIndex = 1;// B列でチャット送信状態を保持する

var targetColumnIndex = 0;// A列の更新を監視する

var targetRowIndexes = []; // チャット通知対象の行リスト

// チャット通知対象の行リストを生成する

// ヘッダが0行目なので読み飛ばす

for (var i = 1; i < data.length; i++) {

// チャット未送信状態　かつ　A列に何らかの入力がある

if ((!data[i][stateColumnIndex]) && (data[i][targetColumnIndex])) {

targetRowIndexes.push(i);

}

// 通知対象行が0なら離脱

if (targetRowIndexes.length == 0) return;

// シートの行ごとにメッセージブロックを生成する

var msg= '';

for (var i = 0; i < targetRowIndexes.length; i++) {

var row = targetRowIndexes[i];// シート上はi+1行目

var cell = data[row][targetColumnIndex];// i+1行目A列のセルの値

msg += Utilities.formatString('[info]%d行目が更新されました\n内容：%s[/info]', row + 1, cell);

sheet.getRange(row + 1, stateColumnIndex + 1).setValue(true);

}

var message = Utilities.formatString('シート[%s]が更新されました\n%s', SHEET_NAME, msg);

// ChatWorkへメッセージを送信

postChatwork(message);

}

こういう感じでスクリプトを書いて、デバッグして良い感じに動作したら自動実行トリガーを指定します。指定する関数をどのタイミングで自動実行するか、を指定するわけです。

そして実行するとこういう感じでChatWorkに通知が来ます。

注意したいのは

スプレッドシートIDやCharkwork用の定数は当然ダミーです
指定範囲セルの値を取得・設定するetValue(s)とgetValue(s)はオーバーヘッドが多くて遅いので工夫する
セルの範囲はColumn、rowともに1から指定する
時間主導型トリガーで毎正時実行は保障されない

ってところでしょうか。
最後の「毎正時実行保障」というのは、毎正時(12:00とか17:00とか)周辺に処理が殺到するらしく、毎正時前後数分間を指定してもその時間に正しく動作する保障がありません。貧者のcronとも呼ばれるGoogle Apps Scriptですがこの辺の正確さが欲しい時はおとなしくcronを動かせる環境を使いましょう。

Google Apps Scriptは外部からのGETやPOSTのリクエストを受けられないこともない（クセが強い）ので、ちょっとしたAPIの動作テストにもオススメです。

Author Profile