共有したスプレッドシートを編集しているユーザ情報を取得したい

概要

ここではGoogle Apps Scriptを使用して、共有したスプレッドシートを編集しているユーザ情報の取得方法について記載します。

初めは簡単に考えていたのですが、進めていくとハマってしまいましたので解決までを記録として残したいと思います。試行錯誤した結果、本記事のような結論に至りました。もっと簡単な方法があればと願っています。

状況

• スプレッドシートを他のユーザと共有している状況下で、編集中のユーザ情報を取得する。
• ユーザ情報はClass Sessionで取得する。
• ユーザ情報はonOpen()を使用してスプレッドシートオープン時に取得する。

ポイント

• Class Sessionで取得できるユーザ情報は、スプレッドシートのオーナーあるいはトリガー設定したユーザ自身にとなる。
  • 例えば、編集時にユーザ情報を取得したいとして、オーナーがonEdit() をトリガー設定していると、他のユーザがシートを編集するとき、そのユーザ情報は全てトリガー設定したオーナーになってしまう。一方、編集中にユーザ自身がトリガー設定を行った場合は、ユーザ情報はトリガー設定したユーザ自身になる。
• それぞれのユーザがトリガー設定を保持したままにすると正確なユーザ情報を得ることができないため、トリガー設定はテンポラルとしてその間にユーザ情報を取得する。
• onOpen()だけではトリガー設定やGoogleの各API使用のための認証を行うことができない。
• メニューバーを用意し、ユーザがメニューバーから起動すると、トリガー設定や認証を行うことができる。

これらのポイントを踏まえて、次の２案を追加しました。

1. 認証は１度だけで良いので２度目以降は認証プロセスを使用しないようにしたい。

   • onOpen()はいろいろなAPIを認証無しで実行できるようですが、試してみると、認証を行わないと使用できないもの（DriveApp）や、認証を行っても使用できないもの（trigger）の存在が認められました。今の場合、認証を行わないと使用できないDriveAppを利用して2度目の認証確認を行うことにしました。

2. メニューバーから実行すると認証やトリガー設定を行ってユーザ情報取得までできるが、ユーザ側に立って毎回初めにメニューバーからユーザ情報取得のための関数を実行することを考えたとき、面倒あるいは失念する可能性を危惧したため、スプレッドシートを開くと初めにダイアログボックスを表示してボタンクリックによりユーザ情報取得ができるようにしたい。

   • トリガー設定はダイアログボックスのボタンをクリックして実行できるのですが残念ながら認証まではできません。そのため、初めてシートを開いたその時だけはメニューバーから認証を行ってもらうようダイアログボックスを使って促し、２度目以降はダイアログボックスのクリックのみで情報取得ができるようにしました。

スクリプト

下記はGoogle Apps Scriptです。

function showDialog(html, title){
  SpreadsheetApp.getActiveSpreadsheet().show(
    HtmlService
    .createHtmlOutput(html)
    .setTitle(title)
    .setWidth(400)
    .setHeight(100)
  );
}

function getUser(){
  var triggerId = ScriptApp.newTrigger('getUser')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create()
    .getUniqueId();
  var userInf = Session.getEffectiveUser();
  CacheService.getUserCache().putAll({
    "username": userInf.getUsername(),
    "usermail": userInf.getEmail()
  }, 7200); // 作業時間を考えて2時間保持
  var triggers = ScriptApp.getProjectTriggers();
  [ScriptApp.deleteTrigger(i) for each (i in triggers) if (i.getUniqueId() == triggerId)];
}

function dialogForGetUser(){
  showDialog('<input type="button"\
      value="OK"\
      onclick="google.script.run.withSuccessHandler(function(){google.script.host.close()})\
      .getUser()">',
      'Push OK button.'
  );
}

function dialogForAuth(){
  showDialog('<input type="button"\
      value="OK"\
      onclick="google.script.host.close()">',
      'Please authorize at "Authorization" of menu bar.'
  );
}

function getAuth() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.removeMenu("Authorization");
  getUser();
  ss.toast("Done.", "Authorization", 3);
}

function onOpen(){
  try {
    var temp = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId())
    dialogForGetUser();
  } catch(e) {
    dialogForAuth();
    SpreadsheetApp.getActiveSpreadsheet().addMenu(
      "Authorization",
      [{
        functionName:"getAuth",
        name:"Run this only when the first time"
      }]
    );
  }
}

ユーザ情報は下記のようにして取得します。onEdit()などをトリガーとして保存や利用をすることができるかと思います。あるいは、getUser()はシートを開いた１度だけ実行されますので、その際に保存するのもありと思います。

var user = CacheService.getUserCache().getAll(["username", "usermail"]);

気付いたこと

• DriveAppを利用した認証では、ダミー変数へ代入しないとエラーが発生しませんでした。
• CacheServiceをonOpen()で使用するとき、認証有無でエラーも発生せず、さらに動作自体もしませんでした。ダイアログボックス経由で動作しました。
• ユーザ数が増えてきたときには、ロックも考慮に入れる必要があるかと思われます。

Google Apps API Gourpのフォーラムで参考にさせていただいたところがあったのですがそのURLを失念してしまいました。申し訳ありません。