Googleスプレッドシート 自動でタイムスタンプを入力する3つの方法 -1
Googleスプレッドシートで使える 自動タイムスタンプ機能を実装する方法の紹介です。通常の GASを使ったものに加え セルが保護されている場合の回避方法や GASを使わない シート関数での実現方法 なども紹介します。
タイムスタンプの需要ってありますか?
そもそもGoogleスプレッドシートは、標準機能として スプレッドシートの過去の「変更履歴」が自動保存され、これをいつでもその時点のファイルに戻せるのが魅力です。
さらに、セル単位なら 右クリック > 編集履歴を表示 で、誰が いつ どのように編集したかを遡れるのってのが凄い。
こんだけ機能で揃ってるんで、自分的には タイムスタンプは「別にいらなくね?」って感じなんですが、世の中的には意外と需要があるんですよね。
というわけで、かなり定番ネタではありますが Googleスプレッドシートの自動タイムスタンプ の記事を書きたいと思います。
Q. 指定したシートの 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を使ったタイムスタンプ を紹介しました。
続きの セルを保護した時のタイムスタンプは 次回の記事で。
■このシリーズの次の記事
この記事が気に入ったらサポートをしてみませんか?