Googleスプレッドシート 自動でタイムスタンプを入力する3つの方法 -2

Googleスプレッドシートで使える 自動タイムスタンプ機能の紹介です。通常の GASを使ったものに加え セルが保護されている場合の 少しアレンジしたコードやシート関数での実現方法なども紹介します。

今回は前回の続き、その2 です。

Q. 指定したシートの B列にコメントを入れたら、A列にタイムスタンプ（日時）を自動で入れたい

このお題に対して 3つのアプローチを考えてみます。

1. GAS でオーソドックスなタイムスタンプを作成する (onEdit）

2. タイムスタンプ(A列)をユーザーに編集させたくない (トリガー設定）

3. GASを使わない タイムスタンプ。（関数でも出来る？）

前回、1 を回答・解説しました。今回は

2. タイムスタンプ(A列)をユーザーに編集させたくない (トリガー設定）

を考えてみましょう。

A2.タイムスタンプ(A列)をユーザーに編集させたくない (トリガー設定）

前回 の記事で GASを使って B列を入力した 日時を A列に出力することは出来ましたが、このA列のタイムスタンプを ユーザーが誤操作で消してしまう、あるいは意図的な改ざんを防止したい という追加要件が出てくることもあります。

シートと範囲を保護 を使う

Googleスプレッドシートは、厳密には 編集者権限をもったユーザーの細かい制御は難しいんですが、今回は ニーズに手っ取り早く対応するということで、スプレッドシートの 保護機能を使う方法を考えます。

Googleスプレッドシートでは　シート単位または、範囲（指定したセル範囲）に対して 保護 をかけることが出来ます。設定できる 保護の種類は、

• 警告を表示する

• 編集できるユーザーを制限する

の2種類があります。うっかり防止なら 「警告を表示」でもいいんですが、意図的な改ざんを防止するためには、編集できるユーザーを制限する必要があります。

あと、この警告が弱いというか、エンター押せば突破できちゃうんで、警告を読まずに編集しちゃうユーザーも多いんですよね・・・。

タイムスタンプの入る A列をユーザーに触らせたくないので、ここでは編集者を「自分（オーナー）のみ」としましょう。
※コードも編集できてしまうので厳密な改ざん防止ではないです。

ちなみに 保護を 設定する際、「自分」を制限する（編集者から外す）ことはできません。また、ユーザー側でも保護設定はできますが、オーナーを 制限することもできません。

とりあえず オーナー側で A列 を保護設定（自分のみ編集可）として、ユーザー側に タイムスタンプが動くか試してもらいましょう。

保護されたセルは GASからの操作でも突破できない

結果としては失敗です。 タイムスタンプ が入らなくなってしまいました。
やはり GAS経由でも保護は突破できないのか。

スクリプトの手動実行と違って、トリガー実行のエラーはシート画面には表示されないので、エディタ の実行数のところからエラーログを確認する必要があります。

ログに「保護してるから編集ダメー」って書いてありました。たしかに、GAS経由で抜け道があったら保護の意味がないんで 当然のことなんですが・・・。じゃあ どうすればよいのか？

オーナー権限で スクリプトを実行させることで回避

保護設定で編集できるのがオーナーだけなら、

オーナーとして タイムタンプを書き込んだ 体（てい）にする
　　▼
オーナーとしてスクリプト実行する

とすればよいのです。

実は同じ編集時トリガーでも

という違いがあるんですよね。

つまり、onEditを使わずに オーナー側で トリガー設定をしてあげれば、オーナーが書き込んだ形になるので、ユーザーが操作できない範囲保護をかけても、GASから書き込みができるわけです。

//トリガー設定用タイムスタンプ
function TIMESTAMP(e) {
  //設定値
  const targetSheetName = "シート1";　//対象のシート
  const targetCol = 2; //対象の列番号（今回は B列）
  const targetRow = 3; //対象の行（この行以下を対象とする）
  const resultCol = 1; //タイムスタンプを入れる列番号（今回は A列）

  const range = e.range;
  const value = e.value;
  const sheet = range.getSheet();

  //設定値に該当しない場合はここで終了
  if(sheet.getName() != targetSheetName || range.columnStart != targetCol || range.rowStart < targetRow) return;

  if(value){
    const now = new Date();
    range.offset(0,resultCol-targetCol).setValue(now);
  }else{
    // value が空 （対象セルが Deletされた）時はタイムスタンプも消去
    range.offset(0,resultCol-targetCol).clearContent();
  } 
}

コードは中身は 1 で作成したものがそのまま使えますが、 function 名を変更しておきましょう。 onEditのまま 編集時トリガー設定すると、編集時に 2重で実行されちゃいます。注意！

コードを変更したら、「編集時」でトリガー設定を。

トリガー設定をする場合は、GASでおなじみのスクリプトの承認が必要になります。ただし、こちらは トリガー設定するオーナーが初回に1回やればOKなので、ユーザー側での スクリプト承認は不要です。

これで設定は完了です。

オーナーとして B列入力で タイムスタンプが機能することを確認したら、
ユーザー側でテストしてみましょう。

A列にタイムスタンプは入るけど、ユーザーは手作業でA列を編集できない。
無事、保護付きタイムスタンプ機能が実装できました。

インストーラブルトリガーは 匿名でも動く

こんな使い方はしないと思いますが、共有設定を ネット上の「リンクを知っている全員」 を 編集者 とした場合に、

ユーザー側は Googleログインしてない状態でも スプレッドシートの編集ができます。でも、前回紹介した onEdit のシンプルトリガーで作った タイムスタンプは 機能しません。

これは、onEdit だと セルを編集したユーザーが GASの実行者になる挙動なのに、 Googleに ログインしてない人が操作した場合は、GAS実行者が不在状態となるからです。

一方、インストーラブルトリガーで設定した タイムスタンプなら、スクリプトは トリガー設定者 権限で動くので、ネット上の匿名の人が編集しても GASが発動します。

便利なような怖いような。

セキュリティに注意

今回、保護されたセルへの タイムスタンプ書き込みを GASで実現する方法として、インストーラブルトリガーを紹介しました。

インストーラブルトリガーであれば、上に記載の通り本当に誰でも実行されますし、onEdit のGASが そのスプレッドシート内の動作に限定されるのに対して、設定者が初回に スクリプトの権限の承認さえすれば、より自由度が高い動作が可能となります。

例えば編集時トリガーで、他のスプレッドシートのデータを操作したり、Gmail送信、カレンダーへの予定登録 といったことも可能です。

ただ、そのような設定をする場合は、ユーザーを信頼できるメンバー（組織内 の一部 等）に限定 することをお勧めします。

理由としては スプレッドシートを 編集者として共有した場合、ユーザー側は スクリプトエディタが編集出来てしまうからです。

例として、

編集時のイベント e からは e.user.email  でセル編集者の メアドを取得できるので、これを利用して ContactsApp で オーナーの連絡先情報を使って ユーザーを メアド から 名前に変換、タイムスタンプと合わせて 編集者名を 書き込む。

こんな機能を実装した場合、オーナーは トリガー設定時に スクリプトへ 自分の連絡情報へのアクセスを許可することになります。

これは  編集権限をもってる ユーザー も、トリガーを経由して オーナーの連絡先情報へアクセスできる 状態なのです。

もちろん、普通にユーザーがスクリプトを実行すれば、 ContactsApp は そのユーザーの連絡先を取得する動きになるんですが、前述の通り設定されたトリガーで実行される場合はオーナーの権限で動いてしまい、連絡先もオーナーのものを参照してしまいます。

悪意ある ユーザーは コードを改変して、オーナーの連絡先情報を取得したり、消去したりが可能です。（さすがに一部の操作は 制限があるかもしれませんが）

編集時トリガーを設定してユーザーに使わせる場合

• そのスプレッドシート以外の権限を スクリプトに許可しない

• 諸々やりたい場合は、信頼できるユーザーのみへ共有する

基本的には、これらを守ることをお勧めします。

自分（オーナー）の権限でのスクリプト実行を、 ユーザー側に許可させるのは、怖いことなのだということを理解しておきましょう。

今回は 保護したセルへのタイムスタンプの書き込みを GASで実現する方法を紹介しました。

次回はタイムスタンプシリーズの最後

GASを使わず 一般的なシート関数のみで タイムスタンプ機能を実現する 方法を紹介します。

はたして、関数のみで本当に出来るのか！？
それはまだ……混沌の中。

■このシリーズの次の記事