こんにちは。ひとりで.comです。
今日はGoogleスプレッドシートで変更履歴をシート上に即時反映するスクリプトについて紹介していきたいと思います。
スプレッドシートで変更履歴をシートに記述するスクリプト
以前、Googleスプレッドシートには変更履歴機能があると紹介しました。こちら、過去にどのような変更だ誰によってなされたのか…を確認するのに非常に役立ちます。
また万が一、データに不備が発生した場合も、過去の履歴をもとにその時点に遡って修正することも可能です。この機能をマスターしたら、きっと離れられなくなると思います。笑
さて、今日はこれに引き続き、この変更履歴を自動でシート上に吐き出す仕組みを紹介したいと思います。
【目次】
1.Google App Script
2.変更履歴をシートに記述するための準備
3.注意事項
それでは実際に見ていきましょう。
1.Google App Script
と、実際に入る前に、今回使う仕組みについて簡単に説明しておきたいと思います。
今回のGoogleスプレッドシートの変更履歴をシート上に表示させるという仕組みについてですが、Google App Scriptという仕組みを用います。
Google App ScriptとはGoogleが提供をしているJavaScriptによって操作するためのスクリプト環境を指します。Exceで言うところのマクロ…に該当します（マクロがわからない方はググってくださいw）
このApp Scriptを使ってプログラムを組むと様々な事ができるようになります。今回はその機能を使って変更履歴の仕組みを実現したいと思います。
2.変更履歴をシートに記述する
では、実際にどのように設定するか見ていきましょう。
(1)Logというシート名のシートを新たに作成する。
(2)Google App Scriptにコードを入れる。
Google App Scriptは以下のメニューから選びます。[メニュー]から[ツール]、そして[スクリプトエディタ]を選択します。
すると…別画面で以下のような画面がでてきます。
この画面が出てきたら、
|
1 2 3 |
function myFunction(){ } |
のコードを消した上で、以下のコードをコピーして貼り付けてください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
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 = 'Delete'; } //更新者のメールアドレス 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); } |
貼り付けが完了したら、[保存ボタン]を押して、このプロジェクトの名前を決めてください（プロジェクトの名前は何でも構いません。ここでは「Log」というプロジェクト名にしております。
(3)トリガーを設定する
トリガーとは、「このスクリプトを動かすタイミング」を表します。何をした時にこのスクリプトを動かしますか？ということです。 このトリガーは以下のように[リソース]から[現在のプロジェクトのトリガー]を選択し設定します。
どこでどういう時に何を実行するのかを設定します。ここでは、先ほど設定した、
[onMyEdit]というスクリプトを[スプレッドシートから]、[編集時]として保存します。
(4)Googleスプレッドシートで確認してみる
スプレッドシートのシートに「aaa」と記載してみました。すると、A列から日時、時間、変更者、シート名、行、列、加えた文字列 の順番で表示され、無事に履歴がシート上に反映されていることを確認できます。
3.注意事項
ここからは、Google App Scriptを使って変更履歴を表示するときの注意事項について書きたいと思います。このGoogle App Scriptはgmailでも許可さえ出してしまえば、個人メールが履歴上に表示されてしまいます。
※今回の図ではメールアドレスは省いています。
従って、意図せず第三者に対して自身の個人アドレスを漏洩してしまう可能性があります。法人アカウントで、アカウントを見られても問題ない場合は特に問題ないですが、個人メールを使ってこの機能を使う際は充分注意してください。
