はじめに
こちらはオプションサービス「Chatwork/Slack連携」をご利用いただいている方向けの記事となります。
オプションサービス「Chatwork/Slack連携」のご利用をご希望の方はサポートデスク宛へその旨お問い合わせくださいませ。
有償のオプションサービスとなりますので予めご了承ください。
仕様
「ATOM」のスプレッドシート出力機能を介して、チャットツール（Chatwork・Slack）に通知する機能です。
・出力シート
実際に出力される指標が表示されます。
内容の確認が可能です。
シート名は変更不可です。
・設定シート
送信内容をチェックボックスにチェックを入れることが設定が可能です。
クライアント別（個別ルーム）・クライアント×媒体（個別ルーム）は、クライアント別に通知するルームを分けたい場合に使用します。
上部のクライアント別・クライアント×媒体は、一つのルームにまとめて送信されます。
左半分（A～AE列）が設定する箇所、右半分（AG列～）が関数で出力内容を表示させている箇所になります（通常時はグループ化をして非表示にしています）
基本的に右半分（AF列～）は変更不可です。
ただし、出力したいクライアント・アカウント数を増やしたい場合は、追加作業をすることで追加可能です（追加方法は後述）
シートが重くなるため、初期設定ではクライアント・アカウント数は30個、クライアント×媒体数は10クライアントまでとしています。
シート名は変更不可です。
行や列の追加も不可です。
設定方法
・通知者、通知先
通知に必要な情報を入力してください。
| 設定内容 | 役割 | 設定方法 |
| Chatwork Token | 通知するユーザー名 | こちらを参考に設定してください。 |
| Chatwork Room ID | 通知するルーム | そのルームのURLのid以降の数字を入力してください |
| Slack Webhook URL | 通知するチャンネル |
こちらを参考に設定してください。 |
・送信メニュー
送信したいメニューにチェックを入れてください。
進捗全体～アカウント別：上記で設定したルームに通知されます
クライアント別（個別ルーム）、クライアント×媒体（個別ルーム）：クライアントごとに個別のルームに通知したい場合にチェックを入れます
・出力指標
出力したい指標にチェックを入れて下さい。
※予算金額、利用率、利用率着地予想(前日)は2021年1月に正式対応予定
・Fee
Fee抜き・Fee込みのどちらかにチェックを入れてください。
両方にチェック、あるいはどちらにもチェックを入れていないとエラーになります。
こちらの設定により、クリック単価とコンバージョン単価のFee抜き・込みが変化します。
・進捗管理
出力したい進捗管理全体の予算金額を入力してください。 ※2021年1月に正式対応予定
・クライアント一覧
・クライアント一覧
出力したいクライアント名を入力してください（初期設定ではUNIQUE関数で自動出力）
ただし、クライアントを追加すると変動するため、出力したいクライアントは値で入力することを推奨
・予算金額
クライアントごとの予算金額を入力してください。 ※2021年1月に正式対応予定
・出力有無
出力したいクライアントにチェックを入れてください。
・Chatwork Token
・Chatwork Room ID
クライアント別に個別のルームに通知したい場合に入力してください。
・媒体一覧
出力したい媒体にチェックを入れてください。
また進捗管理全体の、媒体毎の予算金額を入力してください。 ※2021年1月に正式対応予定
・クライアント×媒体
各クライアントの媒体ごとの予算金額を入力してください。 ※2021年1月に正式対応予定
・アカウント一覧
・アカウント一覧
出力したいアカウント名を入力してください（初期設定ではUNIQUE関数で自動出力）
ただし、クライアント・アカウントを追加すると変動するため、出力したいアカウントは値で入力することを推奨
・予算金額
アカウントごとの予算金額を入力してください。 ※2021年1月に正式対応予定
・出力有無
出力したいアカウントにチェックを入れてください。
Google Apps Script設定
①スプレッドシート＞ツール＞スクリプトエディタをクリックする。
②スクリプトの編集画面になるので、ファイル＞New＞プロジェクトより新規作成する
③以下をコピーして上書きペーストする。
④任意の名前で保存する（複数のスクリプトを作成する必要がある場合は、どのスプレッドシートのスクリプトか判別できる名前にする）
⑤リソース＞ライブラリより、ChatworkClientを追加する。
下部の「Add a library」よりChatworkのGoogle App Script Project Key （M6TcEyniCs1xb3sdXFF_FhI-MNonZQ_sT）を追加する。
function SendSlack(){
var title
= "本日の数値報告です。\n"
+ "---------------------------\n";
var message = title + GetMessage()
var postUrl = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('設定シート').getRange('B5').getValue();
var jsonData =
{
"text" : message
};
var payload = JSON.stringify(jsonData);
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : payload
};
UrlFetchApp.fetch(postUrl, options);
}
function SendChatwork(){
var title = "[info][title]本日の数値報告です。[/title]";
var end = "[/info]";
var message = title + GetMessage() + end;
var token = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('設定シート').getRange('B3').getValue();
var client = ChatWorkClient.factory({token: token}); //チャットワークAPI
var roomId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('設定シート').getRange('B4').getValue();
try {
//指定したルームIDにbodyの中身を送信する
client.sendMessage({
room_id:roomId, //ルームID
body: message});
} catch(error) {
}
}
function GetMessage(){
//対象のシートを取得
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('出力シート');
//B列の先頭行から下方向に取得する
var lastRowB = sheet.getRange(sheet.getMaxRows(), 1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
var rangeLabel = sheet.getRange("A3:B" +lastRowB );
var arrayLabel = rangeLabel.getDisplayValues();//セルの中身を取得
var indexarray = []
for(var i = 0; i < arrayLabel.length; i++){
indexarray.push([arrayLabel[i][0],arrayLabel[i][1],"\n"].join(" "));
}
var index = indexarray.join(" ");
let body = '';
body += index;
return body;
}
function SendChatwork_Client(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('設定シート');
if (sheet.getRange(9,5).getValue() === true || sheet.getRange(10,5).getValue() === true){
sheet.getRange('AR1').setValue(sheet.getRange('E9').getValue())
sheet.getRange('AW1').setValue(sheet.getRange('E10').getValue())
var indices = sheet.getRange(2,1,1,sheet.getLastColumn()).getValues();
indices = Array.prototype.concat.apply([],indices);
var column1 = indices.indexOf('クライアント一覧') + 1;
var lastRowB = sheet.getRange(sheet.getMaxRows(), column1).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
var title = "[info][title]本日の数値報告です。[/title]";
var end = "[/info]";
var array1 = sheet.getRange(3,column1 + 2,lastRowB - 2,1).getValues();
for (var i = 0;i <= lastRowB - 3;i++){
sheet.getRange(3, column1 + 2,lastRowB - 2,1).clearContent();
if (array1[i][0] === true){
sheet.getRange(i + 3, column1 + 2).setValue(array1[i][0]);
var message = title + GetMessage2() + end;
var token = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('設定シート').getRange(i + 3, column1 + 3).getValue();
var client = ChatWorkClient.factory({token: token}); //チャットワークAPI
var roomId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('設定シート').getRange(i + 3, column1 + 4).getValue();
try {
//指定したルームIDにbodyの中身を送信する
client.sendMessage({
room_id:roomId, //ルームID
body: message});
} catch(error) {
}
}
}
sheet.getRange(3, column1 + 2,lastRowB - 2,1).setValues(array1)
sheet.getRange('AR1').setFormula('=IF($E$5=FALSE,FALSE,IF(COUNTIF(R:R,TRUE)>0,TRUE,FALSE))')
sheet.getRange('AW1').setFormula('=IF($E$6=FALSE,FALSE,IF(AND(COUNTIF(R:R,TRUE)>0,COUNTIF(W:W,TRUE)>0),TRUE,FALSE))')
}
}
function GetMessage2(){
//対象のシートを取得
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('出力シート');
//B列の先頭行から下方向に取得する
var lastRowB = sheet2.getRange(sheet2.getMaxRows(), 16).getNextDataCell(SpreadsheetApp.Direction.UP).getRow()
var rangeLabel = sheet2.getRange("P3:Q" +lastRowB );
var arrayLabel = rangeLabel.getDisplayValues();//セルの中身を取得
var indexarray = []
for(var j = 0; j < arrayLabel.length; j++){
indexarray.push([arrayLabel[j][0],arrayLabel[j][1],"\n"].join(" "));
}
var index = indexarray.join(" ");
let body = '';
body += index;
return body;
}
トリガー設定
通知するタイミングを設定します。
通知のトリガーとして、
・スプレッドシートの変更、編集時
・日時（分、時間、日、週、月、特定の日次）
が用意されています。
ただし、前者は設定シートを変更しても動作してしまうため、基本的には後者の日時ベースになります。
・設定方法
①上記Google Apps Script設定で作成したトリガーを開いた状態（編集可能な状態）にする。
②時計マークのトリガー設定ボタンをクリックする。
③右下の「トリガーを追加」ボタンをクリックする。
④設定したいトリガー条件にして保存する。
・実行する関数を選択する
Chatworkに通知する場合（一括）：SendChatwork
Chatworkに通知する場合（クライアント別ルーム）：SendChatwork_Client
Slackに通知する場合：SendSlack
・実行するデプロイを選択
Headのまま
・イベントのソースを選択
時間主導型
・時間ベースのトリガーのタイプを選択
・時間の間隔を選択（時間）
設定したい頻度に応じて選択
通知するクライアント・アカウント数の増やし方
・クライアント
AP～AS列が作業箇所。
クライアント名～着地見込みまでの範囲を選択し、続き（下の部分）にペーストする。
初回はAP4150～AS4291をコピー＆ペーストする。
・クライアント×媒体
AU～AY列が作業箇所。
クライアント名～～Freakoutの着地見込みまでの範囲を選択し、続き（下の部分）にペーストする。
初回はAU16742～AY18600をコピー＆ペーストする。
・アカウント
BA～BD列が作業箇所。
アカウント名～着地見込みまでの範囲を選択し、続き（下の部分）にペーストする。
初回はBA4150～BD4291をコピー＆ペーストする。
通知する案件の増やし方
①上記で設定した既存のスプレッドシートをコピーする。
②コピーしたスプレッドシートに、ATOMのスプレッドシート出力設定を行う。
③置換機能で関数で参照しているシート名を修正する。
具体的には進捗管理No.を、②で生成されたrawシート名に含まれる数字に変更する。
④既存のrawシートは削除する。