Googleスプレッドシートの変更履歴をGoogleスプレッドシートに書き出す。ついでにSlackにも通知する

こんにちはこんにちは！！

Googleスプレッドシート使ってますか！
複数の人がブラウザ上で同時に閲覧・編集できるエクセルです！
これ超便利ですよね。
(パワーポイントみたいなやつも便利ですね！)

最近はエクセルの代わりに
これをチーム向けの情報共有ツールとして使ってる人も多いんじゃないでしょうか。

これさえあればもう、

バグ管理リスト.xls バグ管理リスト最新版.xls バグ管理リスト5月12日ver.xls バグ管理リスト最新版のコピー.xls

— はまちや２ (@Hamachiya2) 2010, 5月 12

こんなことにもならなくて済みますよね。ああ良かった！

そんなGoogleスプレッドシートなんですが、欠点もあって…、
変更履歴が超見づらい＞＜ んですよね…。
シートが複数あるようなものだと、一体どこを変更したのやら……といった感じです。

そこで、タイトルにあるように、
Googleスプレッドシートの変更履歴をGoogleスプレッドシート自身に書き出す仕組み
というものを作ってみたのでメモしておきますね。

こんな感じで誰かが何かを変更するたびに、変更履歴用のシートにログがどんどん自動的に追記されていくようになります。

(やりかた)

まずは任意のスプレッドシートに「Log」という名前の新規シートを作ります。

このシートに変更履歴が書き出されていきます。

次にツールメニューから「スクリプトエディタ」を選んで

元から書いてある myFunction を消して、
そこに以下のコードをコピペします。

function onMyEdit(e) {
	//Log保存用シートの名前
	var logSheetName = 'Log';

	// スプレッドシート
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// スプレッドシート名
	var ssName = ss.getName();

	// 選択シート
	var sheet = ss.getActiveSheet();
	// 選択シート名
	var sheetName = sheet.getName();
	// Logシートなら何もしない
	if (sheetName == logSheetName) {
		return;
	}

	// 選択セル範囲
	var range = sheet.getActiveRange();
	// セル範囲の行番号
	var rowIndex = range.getRowIndex();
	// セル範囲の列番号
	var colIndex = range.getColumnIndex();

	// getRange(始点行, 始点列, 取得する行数, 取得する列数)
	var v = sheet.getRange(rowIndex, colIndex, 1, 1).getValue();
	//内容が空だ
	if (v == '') {
		v = '※削除かな？';
	}

	//更新者情報は法人向けGoogle Appsの同一ドメインでないと取得できないかも？ふつうの @gmail.com だと無理かも？
	//https://productforums.google.com/forum/#!topic/docs/5D23Os_NIAc

	//更新者のメールアドレス
	var email = Session.getActiveUser().getEmail();

	//ここからLogシートに書き込み
	//Log保存用シート
	var logSheet = ss.getSheetByName(logSheetName);
	//引数で指定した行の前の行に1行追加
	logSheet.insertRowBefore(1);

	//日付
	logSheet.getRange(1, 1).setNumberFormat('yyyy/mm/dd(ddd)');
	logSheet.getRange(1, 1).setValue(new Date());
	//時刻
	logSheet.getRange(1, 2).setNumberFormat('h:mm:ss');
	logSheet.getRange(1, 2).setValue(new Date());
	//更新者
	logSheet.getRange(1, 3).setValue(email);
	//シート名
	logSheet.getRange(1, 4).setValue(sheetName);
	//行番号
	logSheet.getRange(1, 5).setValue(rowIndex);
	//列番号
	logSheet.getRange(1, 6).setValue(colIndex);
	//変更セルの内容(Stringフォーマットにする)
	logSheet.getRange(1, 7).setNumberFormat('@');  
	logSheet.getRange(1, 7).setValue(v);  

/*
	//Slackに通知する場合
	//tokenを取得(ボタン押す)→ https://api.slack.com/web
	//channels:id取得(ボタン押す)→ https://api.slack.com/methods/channels.list/test
	//先に更新通知用チャンネルを作っておいた方が良いかも？

	var token = 'ここにtoken';
	var channel = 'ここにchannelId';
	var userName = 'Spreadsheets'; // Slack投稿時に使われる好きな名前

	//Slackに通知するテキスト
	var text = '「'+sheetName + '」シート変更: ' + email + ' [' + rowIndex + ',' + colIndex + '] ' + v;

	UrlFetchApp.fetch('https://slack.com/api/chat.postMessage', {
		method: 'post',
		payload: {
			token: token,
			channel: channel,
			username: userName,
			text: text
		}
	});
	//実行トランスクリプトに「実行に失敗: fetch を呼び出す権限がありません」
	//と出た場合、メニューから実行して承認してやる必要あり？
	//こちらもやはりGoogle Apps for work でないとだめかも？
	//https://code.google.com/p/google-apps-script-issues/issues/detail?id=677
*/
}

ファイル→保存して…

念の為に一度、実行メニューから、「onMyEdit」を選んで実行しておきましょう。

最後にトリガーを設定します。
リソースメニュー → 現在のプロジェクトのトリガー → onMyEdit

イベントに「スプレッドシートから」、「編集時」と設定して保存します。
これでできあがり。

元のシートにもどって、Log以外のシートに何か書いてみます。
変更箇所がちゃんとLogシートに自動記録されていれば成功です。

左から、日付、時刻、更新者、シート名、行、列、変更後の内容になっています。
誰かがシートを編集するたびに、どんどん変更ログが(上に)追記されていきます。

もしうまくいかなければ、スクリプトエディタの表示メニューから
「実行トランスクリプト」を選べばエラーなどが表示されているはずなので、がんばってくださいね。

ただしこれには一つ欠点があって、法人向けGoogle Apps以外の人、
つまり普通の「@gmail.com」のユーザーの場合は、
セキュリティ上の都合のためか、更新者のメールアドレスだけは取得できないようです。

あと、おまけとしてスプレッドシートが更新されるたびにSlackに通知する仕組みもコードに入れておきました。
試してみたい人は、コメント部分を修正してみてくださいね。

※ただしこちらも、法人向けのGoogleアカウントでないとうまく動かないようです。

[この日記のブックマークコメントを見る/書く ]