Googleスプレッドシート編集日時と編集者をログシートに記録するGASの作り方

mir

2024年4月7日 22:19

今回は GoogleAppsScript（通称GAS）による Googleスプレッドシート操作の基本といえる、誰かが編集したらログシートに編集日時と編集者を記録する GASについて書きます。

シンプルトリガーとインストーラブルトリガーの違い、GoogleWorkspace アカウントとフリーのGmail（Googleアカウント）の 挙動の違いなどにも触れていきます。

簡単に出来そうで、意外とうまくいかない人も多いネタかと思います。是非参考にしてください。

前回までは3回にわたって、GoogleスプレッドシートのIMPORTRANGE関数を考察する noteを書いてきました。

GASでスプレッドシートの編集日時をログシートに記録する

まずは一旦、編集者の情報は置いといて、編集日時だけログシートに記録する方法を考えましょう。こっちは簡単です。

実は、これに近いGoogleスプレッドシートのタイムスタンプネタを、noteを始めた初期に書いています。

今読み返すと初期のnoteは見せ方下手すぎるし、説明も不十分ですねｗ

まずは基本の onEdit を使おう

スプレッドシートのどこかのセルを編集（入力、削除、変更）したら、ログという名前のシートに編集日時が残るようにしていきたい。

これは シンプルなコードを書くだけで、実現することができます。

先にログを記録するログシートを用意しておきましょう。

ログシートは1行目を見出し行とし、空欄の2行目までを範囲選択して交互の背景色を設定しておくと、ログが追加されると交互の背景色の適用範囲も拡張されていい感じになります。

で、コードはこちら。

function onEdit(e) {
  const sheet = e.source.getSheetByName("ログ");
  const now = new Date();
  sheet.appendRow([now]);
}

↑ わかりやすく書いてますが、まとめて下のように1行コードにしてしまってもOK

function onEdit(e) {
  e.source.getSheetByName("ログ").appendRow([new Date()]);
}

メニューから 拡張機能＞ AppsScript でスクリプトエディタを開き、最初から入ってる

こちらを削除してから、上のコードをコピペ。

最後に保存ボタンを押す。これだけです。

GASでお馴染みの初回に実施するスクリプトの承認やトリガーの設定は、一切必要ありません。

実際に試してみましょう。

このように編集の度に編集日時がログシートの一番下に追加されていきます。

編集日時をログシートに記録するコードの解説

コードを少し解説しておきましょう。

onEdit(e)

onEdtit() はセルの編集時（入力、編集、削除）に発火するシンプルトリガーというものです。

関数名を onEdit とすることで、トリガーの設定やスクリプトの承認が無くても保存するだけで動くようになります。

ちなみにカッコ内の e は イベントオブジェクトと呼ばれるもので、様々な情報が入った e を引数とすることで、ここから処理に必要な情報を取り出すことができます。

今回は、このスプレッドシート（ファイル）を取得するのに

SpreadsheetApp.getActiveSpreadsheet()

定番の書き方

を使わずに

e.source　・・・　このスプレッドシートを取得

これでも同じ

としています。

ここから getSheetByName("ログ") でログシートを取得することで、記述を短くし getメソッドを削減しています。

new Date()　は、日時操作の定番。

実行時点の日時を取得しています。シート関数の now みたいなもんですね。

たまにスプレッドシートに書き込む前に

このようにUtilites.formatDate で成形する人がいますが、セルに書き込めば勝手に日時形式で表示されるので、気にする必要はありません。そのままでOK。

sheet.appendRow([now])

最終行にログを書き込む処理は、こちらの appendRowメソッドを使っています。

最終行にログを書き込むと書きましたが、正しくは 「シート内の現在のデータ領域の一番下に（値が入った）行を追加する」という動きです。

引数は一次元配列である必要があるので

✖　sheet.appendRow(now)
〇　sheet.appendRow([now])

としています。

ここまでは割と簡単ですね。

GASで指定したシートのセルが編集されたらメールでお知らせする

では、少し話がそれますが編集日時をログに残すではなく、「編集されたらメールでお知らせする」をGASで実現したい場合はどうすればよいでしょうか？

Googleスプレッドシートには編集を通知する機能がある

ちなみに GASを使わなくても、Googleスプレッドシートには「通知」という機能で、変更があった時にメールを受け取る設定が出来ます。

メニューから　ツール＞通知設定＞通知を編集　を選択し

こんな感じで設定します。

結構優秀な機能で、こちらを設定しておくと変更があった時にメールがくるんですが、

いつ、誰が編集したかが本文に記載され、さらに「ここをクリック」のリンクを開くと

このように「どこが」編集されたかも ハイライト表示で確認することが出来ます。

ただしこの「通知ルール」は、お知らせが欲しい人が自分で設定する必要があります。つまり、自分のアカウント以外のメールを通知先に指定することは出来ません。

さらにこの「変更」は、設定したスプレッドシートの様々変更が対象となります。セル内の値の編集はもちろん、セルの塗りつぶしや、行・列の幅を変えただけでも「変更」と見なされます。

更新頻度の高いスプレッドシートに設定してしまうと、かなり頻繁にメールが届くので注意が必要です。

あるシート、あるセルの編集の時だけ通知する、といった設定が出来れば便利なんですが、残念ながら出来ません。

これらを理解した上で使えば、非常に便利な機能だってことは間違いないです。

onEditではメール通知は設定できない

通知設定では、あるシートが編集された時だけ通知といった設定は出来ないので、そのような設定をしたい場合はGASを使う（コードを書く）ことになります。

しかし、編集日時をログシートに記録した時と同じように onEdit で、シート1が編集された時にメールで通知するコードを書いても

//これはエラーが出るコードです
function onEdit(e) {
  const targetSheet = "シート1"; //通知対象のシート
  const url = "【スプレッドシートのURL】";

  const sheetName = e.source.getActiveSheet().getName();
  console.log(sheetName);

  if(sheetName === targetSheet){
    GmailApp.sendEmail("【自分のメールアドレス】",
      "誰かが編集したよ",
      "以下のスプレッドシートが編集されました \n "+url);
  }
}

このように

Exception: スクリプトにはその操作を行う権限がありません。

というエラーが出てメールは送信されません。

この理由は、シンプルトリガーは GASの「スクリプトの承認」が必要ない代わりに、権限が制限されている為です。

https://developers.google.com/apps-script/guides/triggers?hl=ja

公式のリファレンスにも、上のように制限について明記されています。

基本的には、Googleスプレッドシートで onEditなどのシンプルトリガーを使った場合は、他のスプレッドシートやドキュメント、スライド、Gmail、ドライブの操作は出来ず、そのスプレッドシート内の処理だけ出来る と思った方が良いです。

インストーラブルトリガーなら編集されたらメール通知も出来る

では、特定のシート（シート1）が編集された時だけメール通知する方法はないのか？

そんなことはありません。

これは、コードの中身はそのままで関数名を onEditではなく別の名前にして、自分でトリガーを設定するだけで解決できます。

function onEditSendMail(e) {
  const targetSheet = "シート1"; //通知対象のシート
  const url = "【スプレッドシートのURL】";

  const sheetName = e.source.getActiveSheet().getName();
  console.log(sheetName);

  if(sheetName === targetSheet){
    GmailApp.sendEmail("【自分のメールアドレス】",
      "誰かが編集したよ",
      "以下のスプレッドシートが編集されました \n "+url);
  }
}

このように関数名を適当に onEditSendMail と変えてみましたが、これでOK。

あとはトリガーの画面を開き、右下の青い「トリガーを追加」ボタンを押して

イベントの種類を選択を「編集時」に設定して保存。

この時、初回に以下のような承認画面が表示されます。

ここで最初の1回だけ承認することで、編集時トリガーでGmail送信ができるようになるわけです。

メールを送る部分のコードですが

GmailApp.sendEmail("送り先のメールアドレス", "メールの件名", "メール本文")
※本文中に改行させたい場合は \n を使う

このようにシンプルなテキストメールの送信であれば、1行コードで記述できます。

この時の送信元のメールアドレスは、自分（トリガーを設定した人）のGmailアドレスとなります。

トリガー発火のスクリプトの「実行者」については、次回詳しく触れたいと思います。

Googleスプレッドシートを編集した時に GASでメール通知をすることが出来ました。

誰かが開いたら（閲覧したら）メール通知するコード

インストーラブルトリガーであれば、メール送信による通知が出来るってことは、上のコードをアレンジしてトリガーを「起動時」とすれば、通常はログには残らない 誰かがスプレッドシートを開いたら（閲覧したら）、オーナーにメール通知 も出来るってことですね。

function onOpenSendMail(e) {
  const targetSheet = "シート1"; //通知対象のシート
  const url = "【スプレッドシートのURL】";

    GmailApp.sendEmail("【自分のメールアドレス】",
      "誰かが開いたしたよ",
      "以下のスプレッドシートが閲覧されました \n "+url
    );
}

onOpenSendMail という関数名にして

起動時でトリガーを設定。

これで、誰かが開くとメールが届くようになります。

ただし、これも万能ではなく

Googleドライブのプレビュー機能で閲覧した場合や

編集権限がない（閲覧者、またはコメントのみの権限の）人が開いた場合は、トリガーは実行されず メールは飛びません。

https://developers.google.com/apps-script/guides/triggers/installable?hl=ja#restrictions

つまり、閲覧のみで共有した場合は、閲覧者の誰かが開いた時に通知を飛ばすことは出来ないってことです。

これはちょっと残念。

GASで編集したユーザー情報を取得する

少し話が反れましたが、本題の編集日時に加えて「編集者」を取得するGASを見ていきましょう。

GASで userを取得する

Googleスプレッドシートにおいて、GASでユーザー情報を取得する方法は2つあります。

1つは Sessionクラスを使う方法

もう1つは イベントから取得する方法です。

これらで取得できるのは Userオブジェクト（User情報のかたまり）なので、ここからさらに Eメールアドレスを取得するメソッドが用意されています。

実際にこれらを onEdti で試してみましょう。

こんなコードを書いて

function onEdit(e){
  const user = Session.getActiveUser();
  console.log(user.getEmail());

  const user2 = e.user;
  console.log(user2.getEmail());
}

スプレッドシートを編集してログを確認すると

このようにスプレッドシートを編集したユーザー（今回は自分）のアドレスがログに残りました。成功です！

共有メンバーが編集しても GASで userを取得できない？

では、上記の方法 e.user を使ってログシートに編集日時を残すコードをアレンジして、 編集日時 + 編集者 をログに残してみましょう。

function onEdit(e) {
  const sheet = e.source.getSheetByName("ログ");
  const now = new Date();
  const user = e.user.getEmail(); //編集者のメアドを取得

  sheet.appendRow([now,user]);
}

こんなコードを保存してから、まずは自分がスプレッドシートを編集すると

ログシートに編集日時に加え、編集者（自分）のアドレスが記録されました。

しかし、このスプレッドシートを共有している編集者権限のメンバーが編集すると・・・

このように、編集日時は記録されますが、編集者は空欄となってしまいます。

これはなぜか？

https://developers.google.com/apps-script/guides/triggers/events?hl=ja

公式リファレンスのイベントのページにも書いてありますが、userは「利用可能な場合」のみ取得ができるようです。

「複雑なセキュリティ制限のセットに依存」のリンク先に飛ぶと

このように書かれています。

ざっくり言えばユーザー（この場合は編集者メンバー）が承認していない状態では勝手にユーザー情報を取得することは出来ないってことですね。

上のような e.user や Session.getActiveUser() を使って、誰が編集したかログを残す GASを紹介しているサイトがありますが、無料版Googleアカウント利用で共有している場合は使えません。

これをきちんと伝えていないサイトも多いので、そのままコピペして動かくなくてハマった人も多いんじゃないでしょうか？

共有メンバーなんだから取得できてもいいのにって思いますが、ユーザー側の 勝手にメアドがログに残されるのは嫌だって感情もわかりますし、プライバシーを配慮した制限は必要ですね。

GoogleWorkspaceの同じ組織（ドメイン）内なら使える

先ほどの公式リファレンスのページの最後に

ユーザーと同じ Google Workspace ドメインに属している場合は、これらの制限は適用されません

このような一文があります。

そうです、GoogleWorkspaceの同一ドメイン内であれば、この方法で編集者情報が取得が出来るんです。

組織内であれば、ある程度メンバーは信頼できるという前提のもとプライバシー配慮より効率重視になってるのかもですね。

そもそも、通常（無料版Googleアカウントの場合）はログに残らない、誰が開いたか？という閲覧情報も GoogleWorkspaceだと

メニューの ツール＞アクティビティダッシュボード　から

こんな感じで閲覧記録として残りますしね。

GoogleWorkspace利用であれば、上のシンプルな編集日時、編集者をログシートに残すコードは便利に使えます。

無料版で共有している場合、GASで編集者の情報を取得する方法はないのか？

とは言え、無料版利用者でも編集者を取得したい！ってことがあります。少し面倒ですが、方法はあります！

ここからが本題。

誰が編集したかは実はログ―シートに残っている

先ほど編集者メンバーが編集した時は、日時しか記録されなかった無料版の方のログシートですが、実はこの日時に「編集者」の情報が入っています。

Googleスプレッドシートの機能、日時が入ったセルを右クリックで「編集履歴を表示」とすることで

このように誰が編集したか？が確認できます。ここに表示されるユーザーが、この時間の編集者ってことですね。

これは、onEditなどのシンプルトリガーの場合は、スクリプトの実行者はユーザー（編集した人）である為、スクリプトによって書き込まれた日時が「ユーザーが書き込んだもの」として記録されるからです。

もちろん、この方法で「誰が編集したか？」を取得できるのは、Googleアカウント指定で共有されたメンバーだけです。

当然、リンク共有で「編集者」として共有された人が編集した場合は、Googleアカウントでログインした状態であっても

このように匿名ユーザーとして編集ログが残りますし、そもそも編集者がGoogleアカウントしていない状態で編集すると シンプルトリガーは機能しません。

※シンプルトリガーの実行者はユーザーとなるが、そもそもGoogleアカウントでログインしていない場合はGASが使えない為

リンク共有やログインなしユーザーは仕方ないとして、アカウント指定で共有した場合は、このように 編集者情報が結局取得できるんだから、GASの user から取得できてもいいと思うんですけどね。。

スプレッドシートの最終編集者を DriveAPIで取得する

じゃあ、「編集履歴を表示」で確認できる編集者の情報を GASで取得してセルに書き出すようなコードを書けば良さそう！って思いますが、残念ながらこの情報を取得するメソッドは用意されていません。

つまり GAS（SpreadsheetApp）では出来ないってことです。

しかし、この編集者の情報が他にも記録されている場所があります。

それは、Googleドライブの画面で確認できる 「最終編集者」という情報です。

こちらの情報は GAS（DriveApp）では取得できないんですが、実はDriveAPIだと取得できるんです！！

では、DriveAPIを使ってまずは最終編集者をログに出力してみましょう。

まずはDriveAPIを有効にします。

スクリプトエディタの左側 「サービス」を選択して、サービスを追加という画面で DriveAPI を選択して追加します。

ここでポイントがあって、DriveAPIは初期値が V3なんですが、V2の方を選択しましょう。

V3だと最終編集者情報が取得できず、エラーとなる為です。（理由はよくわからない）

DriveAPIの最終編集者の取得については公式だと

https://developers.google.com/drive/api/reference/rest/v2/files?hl=ja

こちらで確認ができますが、先ほどのサイトを参考にした方がわかりやすいかも。

とりあえず今回のように Googleスプレッドシートのコンテナバインドスクリプトから実行するなら

function test(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const user = Drive.Files.get(ssId).lastModifyingUser.emailAddress;
  console.log(user);
}

こんな感じのコードになります。

Drive.Files.get(ssId).lastModifyingUser.emailAddress

↑ こで 最終編集者のメアドを取得しているわけですね。

実行してみると

まずは自分が最終編集者だった時はOK。そして

自分ではない共有メンバー（編集者）が最終編集者だった時もアドレスが無事取得出来ました。

編集時トリガーで、これを時間とセットでログシートに appendRowすればOKですね。

無料版Googleアカウントでも使える編集日時と編集者をログシートに記録するコード

function editLog(e){
  const ss = e.source
  const sheet = ss.getSheetByName("ログ");

  const now = new Date();
  const user = e.user.getEmail() || Drive.Files.get(ss.getId()).lastModifyingUser.emailAddress;
 
  sheet.appendRow([now,user]);
}

というわけで、こんなコードを作ってみました。

user のところですが、編集者メンバーが編集した場合は e.user.getEmail() は 空文字を返し、空文字はfalseと評価されることを利用して

条件付き論理 OR 演算子 を使って 空だったら後ろの

Drive.Files.get(ss.getId()).lastModifyingUser.emailAddress

が userに格納されるという処理にしています。

※Drive.Files.get(ss.getId()).lastModifyingUserName　としてもOK

作成した editLog を編集時トリガーにセットして、動作確認してみましょう。

共有メンバーが編集した時も、編集日時と編集者のメアドの両方がしっかりログに記録されました。

完成です～。

ただし、こちらのコードは、GoogleWorkspaceの同一ドメイン内での利用ではなく無料版Googleアカウントを利用している人がスプレッドシートを共有利用していても編集日時と編集者をログに残せますが

リンク共有ではなくアカウントを指定した共有での利用
同時に複数人がアクセスして頻繁に更新されるようなシートではない

という想定です。

短時間に頻繁な更新があるとログに不具合が出ますし。

リンク共有のユーザーが編集するとエラーが出るのでご注意ください。