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

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

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

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

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

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

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

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

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

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

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

3. 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を使ったタイムスタンプ を紹介しました。
続きの セルを保護した時のタイムスタンプは 次回の記事で。

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