Googleスプレッドシートでセルを編集した人の名前を表示する

目的

Googleスプレッドシートでセルを編集した人の名前を表示する。

前提

• Googleアカウントにログインした状態でスプレッドシートを利用していること

Google Workspaceを利用していれば、名前まで取得して表示できます。利用していない場合でもメールアドレスを表示することはできます。

スクリプトの作成

スプレッドシートのメニューから「ツール」 > 「スクリプト エディタ」を押します。

スクリプトエディタ画面が開きます。
以下のコードが初めから書かれていた場合、全て削除してください。

function myFunction() {
  
}

セルを編集した人の名前を表示する処理について、以下のコードを入力します。

const setUser = (e) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  
  const {range, user} = e;

  //書き込みのあったセルから数えて横方向で何番目のセルに名前を書き込むか設定
  const x = 1
  //書き込みのあったセルから数えて縦方向で何番目のセルに名前を書き込むか設定
  const y = 0

  sheet.getRange(range.rowEnd, range.columnEnd, 1, 1).offset(y, x).setValue(user.email);
  
}

この例では、「シート1」というシートで書き込みしたセルのすぐ右隣に名前が表示されるようにしています。
右隣ではなく、別の場所に名前を表示したい場合は、const x = 1;及びconst y = 0;という部分の数字を適宜書き換えて調整してください。
以上でスクリプトの作成は完了ですが、これだけではまだ動作しません。
次のトリガーの設定を完了させることで、動作することができます。

トリガーの設定

スクリプトエディタ画面の左側にある「トリガー」ボタンを押します。

この画面では、作成したスクリプトを自動で実行させる為の条件を設定できます。
画面右下にある「トリガーを追加」ボタンを押します。

以下のようにトリガーを設定して、「保存」を押します。

• 実行する関数を選択
  • setUser(先ほど作成したスクリプトの関数名)
• 実行するデプロイを選択
  • Head
• イベントのソースを選択
  • スプレッドシートから
• イベントの種類を選択
  • 編集時
• エラー通知設定
  • 毎日通知を受け取る(任意で構いません)

保存を押した後に承認画面が出た場合は、全て承認・許可してください。

実行してみる

スプレッドシートに何か書き込むと、その右隣にご自身のアカウント名が自動で書き込まれます。

Google Workplaceを利用している場合、下記の作業を追加することで名前まで取得できます。

ステップ1

スクリプトエディタの左サイドバー「サービス」の［＋］を押して、以下のAPIを追加する

• Admin SDK API

ステップ2

先ほどのコードを下記に書き換えて保存する。

const setUser = (e) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");

  const {range, user} = e;

  const x = 1;
  const y = 0;
    
  const googleAccount = AdminDirectory.Users.get(user.email);
  const userName = googleAccount.name.fullName;　

  sheet.getRange(range.rowEnd, range.columnEnd, 1, 1).offset(y, x).setValue(userName);
  
}

この状態でスプレッドシートに何か書き込むと、その右隣にご自身のフルネームが自動で書き込まれます。
目的を達成できました。

コードについて

肝はトリガーで動かしているコードの引数にあるeです。
これをBrowser.msgBox(JSON.stringify(e))などとして見た結果が以下。

{
	"authMode": "FULL",
	"oldValue": "d",
	"range": {
		"columnEnd":4,
		"columnStart":4,
		"rowEnd":14,
		"rowStart":14
	},
	"source":{},
	"triggerUid":"872XXXX",
	"user":{
		"email":"[e-mail]",
		"nickname":"hoge"
	},
	"value":"テスト"
}

入力したセルの位置や値、emailなどの情報を取得することができています。
Google Workspaceを利用していない場合、nicknameという値をメールアドレスの代わりに利用できそうな気もしますね。

ちなみにonEdit()では上記の情報を取得することができません。トリガー設定しないと機能しないようです。

最後に

スプレッドシートに挿入できるチェックボックスのオンオフにもトリガーは反応しますので、誰がチェックをつけたのか確認をする為によく利用しています。
標準機能でセル毎に変更履歴を確認することができますが、セルを一つ一つ確認するのは手間です。
誰が編集したのかを一目瞭然で残したい場合などに、この方法は有効だと思います。