スプレッドシートで試算などを行っているときに、「いつの間にか数式が壊れてることに気づかずそのまま作業を進めてしまい死…」みたいなことがあるので、防止する仕組みを作ってみることにした。
仕組み
- 検算用のセルを用意しておき、数値が合わないときは特定の文字列を出すようにしておく
- このセルとこのセルの数値は必ず一致するという部分に
=if(A1=B1,"OK","NG【要確認】")を仕込んでおく
- このセルとこのセルの数値は必ず一致するという部分に
- 特定文字列（ここではNG【要確認】）がスプレッドシート内にあるということは、何かしら問題が起きているということなので、Slackで自分宛てにmentionする
- 手動で実行だと実行すること自体を忘れてしまうので、GASのトリガーでn分おきにスクリプトを実行する設定をしておくことで自動化する
特定文字列をチェックするスクリプト
function checkCell() {
// スプレッドシートを指定
var spreadshseet = SpreadsheetApp.openById('***********')
var numSheets = spreadshseet.getNumSheets();
//チェックする文字列を定義
var reg = "NG【要確認】";
loop:
for(var sheetNo = 0; sheetNo <= numSheets-1; sheetNo++) {
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[sheetNo];
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheets()[sheetNo].getSheetName();
var range = sheet.getDataRange();
var values = range.getValues();
for (var i = 0; i < values.length; i++) {
var row = "";
for (var j = 0; j < values[i].length; j++) {
if (values[i][j]) {
row = row + values[i][j];
if (row.match(new RegExp(reg))) {
var messageBody = "<@U8LPZ1BBP>" + sheetName + "シートの" + i + "行目でセルの差異チェックがNGになってるよ";
sendSlackMessage(messageBody);
break loop;
}
}
}
}
}
}
function sendSlackMessage(message) {
var postUrl = 'https://hooks.slack.com/services/******';
var username = 'bot';
var icon = ':hatching_chick:';
var jsonData =
{
"username" : username,
"icon_emoji": icon,
"text" : message
};
var payload = JSON.stringify(jsonData);
var options =
{
"method" : "post",
"contentType" : "application/json",
"payload" : payload
};
UrlFetchApp.fetch(postUrl, options);
}
スクリプトのトリガーを設定する
これで10分おきにチェックするようになり、特定文字列を発見したら、Slackに通知してくれるようになった。
GASとSlackの連動は便利だなあ。