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

2022年8月31日 19:02

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

タイムスタンプの需要ってありますか？

そもそもGoogleスプレッドシートは、標準機能としてスプレッドシートの過去の「変更履歴」が自動保存され、これをいつでもその時点のファイルに戻せるのが魅力です。

さらに、セル単位なら 右クリック＞編集履歴を表示で、誰がいつどのように編集したかを遡れるのってのが凄い。

こんだけ機能で揃ってるんで、自分的にはタイムスタンプは「別にいらなくね？」って感じなんですが、世の中的には意外と需要があるんですよね。

というわけで、かなり定番ネタではありますが Googleスプレッドシートの自動タイムスタンプ の記事を書きたいと思います。

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

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

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

あるあるな要件ですね。
このお題に対して 3つのアプローチを考えてみます。

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

1~3、みなさん自力で出来そうでしょうか？

GASのタイムスタンプの基本形は 1行コードで書ける

タイムスタンプ機能を実装したい場合、ネットで検索すると GAS （Google Apps Script）を使う記事が色々出てきます。GASは簡単に言ってしまうとExcel における VBAみたいなもので、Javascript ベースのプログラミング言語です。

プログラミングと聞くとハードルが高そうに感じますが、ネット上にコピペで使えるコードがいっぱいあるので、まずはコピペで動かしてみましょう。

ちなみにタイムスタンプの基本的な動きは、非常に簡単なコードで実現できます。1行でいいです。

どこかのセルが編集されたら、その編集日時（タイムスタンプ）を右隣のセルに出力する場合、関数名に onEdit(e) を使って以下のように書けます。

function onEdit(e) {
  e.range.offset(0,1).setValue(new Date());　//これだけ
}

シンプルトリガー の onEdit を使うとトリガー設定も不要だし、GASでお馴染みのうざいスクリプトの承認も不要なのが良いですね。本当に上記コードをエディタに書き込むだけで使えます。

隣のセルを取得するのは offset を使うとスマートです。シート関数と同じ感覚で使えます。

日時に関しては、 new Date() をフォーマットしてから書き込む人もいますが、スプレッドシート側の表示形式で対応できるので、Dateオブジェクトそのままでも問題ないです。

ただ、これは動きはしますが致命的な問題があります。

編集トリガーは人の手で操作されたものだけが対象なので、タイムスタンが書き込まれた編集を検知して、さらに隣に書き込まれるといった無限ループ的なことにはなりません。

ただ、セル内の文字に対する全ての編集（入力・編集・削除）を検知し、さらにどのセルを編集しても隣のセルにタイムスタンプが表示される仕様なんで、画像の通りタイムスタンプが消せないという状態に陥ります。

消しても消しても甦る ゾンビ状態。これだと使い物になりません。

だから、特定の条件で動作するように if を組み合わせる 必要があります。
※上記コードを試した方は一回削除するか、ブロックコメントにして関数を無効化しておきましょう。

ここからがちゃんとした回答です。

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

これは普通に onEdit(e) の基本コードを改良する方法でいいですね。
サンプルコードは以下の通り。

//普通のタイムスタンプ
function onEdit(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();
  } 
}

コードを入れいてエディタでプロジェクトを保存。
シート側に戻って試してみましょう。

事前にA列を選択して、表示形式＞数字＞日時 を設定しておきましょう。

対象シートの対象とする範囲のセルを編集した時のみ作動させています。ポイントは特にないですが、get 系のメソッドを極力使わず e から情報を取得するコードにしています。

たまに getValue() , setValue() を乱用しているコードを紹介している人がいますが、どんだけ動き遅いんだろうと恐ろしくなります。

offsetの第1、第2引数はマイナスも使えるので、変更された range の左側や上を取得することも可能です。

コードの細かい解説は書きませんので、わからない場合は検索するか、公式を見てください。

後半の if文の value の判定は、もっと短く（モダン風）にコードを書きたいって場合は、

  const now = value? new Date() : '';
  range.offset(0,resultCol-targetCol).setValue(now);

としてもよいです。 三項演算子 ってやつですね。
clearContent() は空白を書き込むっていう処理に書き換えています。

要件にも記載していますが、残念ながら上記コードはコピペによるB列の更新や、複数セルの一括更新には対応していません。コピペした場合 e.value は undefined となり、タイムスタンプ消去の動きをしてしまいます。あとコードを短縮化する為に行、列ともに startのみ取得しているので、複数セル同時更新も対象外となります。

それらに対応させる方法もありますが、とりあえず今回作成したものは、

手入力で1セル更新時限定のシンプルなタイムスタンプ機能

だと思ってください。

スマホからの操作でもタイムスタンプは使える

GAS は、スマホ（アプリ版）では動かないといった情報もネット上にはありますが、「編集時」をトリガーとした GASは作動します。だから、このタイムスタンプ機能はスマホからでも利用可能です。

モバイルデバイスから利用できると、活用の幅がぐっと広がりますね。

今回は基本形の GASを使ったタイムスタンプを紹介しました。
続きのセルを保護した時のタイムスタンプは次回の記事で。

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

この記事が気に入ったらサポートをしてみませんか？