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

2022年9月7日 18:28

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

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

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

・シート1 という名前のシートが対象
・B列が入力、編集されたら同じ行の A列にタイムスタンプを自動入力
・2行目がタイトル行なので、3行目以降が対象
・B列は手入力のみとし、コピペや複数セルの一括編集への考慮は不要
・B列のコメントを削除したら、A列のタイムスタンプも消したい

今回のタイムスタンプお題の要件

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

GAS でオーソドックスなタイムスタンプを作成する (onEdit）
タイムスタンプ(A列)をユーザーに編集させたくない (トリガー設定）
GASを使わないタイムスタンプ。（関数でも出来る？）

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

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

を考えてみましょう。

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

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

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

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

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

警告を表示する
編集できるユーザーを制限する

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

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

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

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

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

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

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

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

保護されているセルやオブジェクトを編集しようとしています。編集する必要がある場合は、スプレッドシートのオーナーに連絡して保護を解除してもらってください。

onEdit エラーログ

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

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

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

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

とすればよいのです。

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

■onEdit を使った シンプルトリガー（設定・承認不要）
　⇒　編集をした人（ユーザー）としてスクリプトを実行
（ただし、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を使わず 一般的なシート関数のみでタイムスタンプ機能を実現する 方法を紹介します。

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

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

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ

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