目次
Googleスプレッドシートに更新日を自動挿入するにはを考えてみた
皆さんがデジタル世界に飛び込むための一歩を踏み出す時、必ずと言っていいほど「データ」と向き合う経験をするのではないでしょうか。ダッシュボードなどでよく見る洗練されたビジュアルも、裏側で構築されたデータがその良し悪しを左右しています。
昨今、データは様々な方法で加工や成形が可能であるにも関わらず、多くの人が同じような壁にぶつかり、疑問を抱えて立ち止まっています。本連載では、データに革新をもたらすひらめきを求めて、筆者自身が日頃ぶつかったデータにまつわる課題の解決策や、試行錯誤の過程、失敗談などを、毎回ワンテーマにフォーカスして紹介していきます。同じような壁にぶつかったどなたかの、一助になれば幸いです。
第一回は、Googleスプレッドシートに「更新日」を自動で挿入するにはどうするか考えます。さあ、実験開始です。
0日目：Googleスプレッドシートに「更新日」を自動で挿入したきっかけ
私のいるチームでは、Googleスプレッドシート上の該当タスクに進捗があったタイミングで、担当者がコメント欄を更新しておく、という作業があります。そして、週次ミーティングの際に報告しやすいように、更新分は手動で文字色を赤くする、というルールも設けています。そこで、以前から課題として感じでいたのが
・更新が滞っているタスクが分かりづらい
・手動で文字色を赤くする手間がある
という2点。解決案としては
・コメントの更新日列を設ける
・更新日を基に、変更されたコメントは自動で文字色を赤くする
などが考えられます。
ただし、この解決策をただ実行してもらうだけでは、単に他のメンバーへ更新日記入の手間を増やしてしまうということ、また手作業による更新漏れが発生し得るということが懸念点でした。そこで、対応策として「更新日を自動で取得できる方法があるのか」について調べることにしました。
対応策のヒントになったのは、「変更履歴」の存在です。Googleスプレッドシートではファイル全体の変更履歴以外に、セル自体にも変更履歴を確認できる機能があるので、セルが何かしら更新に関する情報を保持しているということであれば、その取り出し方を考えればいいのでは、と思い至りました。
そして、いよいよ「更新日が自動で取得できるかどうか」について調べることにしました。これはもう、ネットの力です。私はシステムエンジニアだった頃、「プログラミングはネット検索でいかにいいお手本を見つけるかが重要だ」という風に教わりました（もちろんそれだけが正解ではありません！）。結果、やはり世の中には同じことを実現しようとした方が多くいたようです。更新日を指定のセルに自動入力させるという試みであればいくつもサンプルが見つかったので、これなら問題なく作業を進められそうだと判断しました。
まずはGoogleスプレッドシート自体の改修です。更新日を表示する列を追加し、変更されたコメントは自動で文字色が赤に変更されるように設計しました。
ここで、記事の本題ではないものの、「自動で文字色を赤くする」手順も紹介しておきます。使うのは「条件付き書式」です。「条件付き書式」の基本的な使用方法は別記事で以前紹介しましたので、ぜひご覧ください。
今回は、カスタム数式で「=DATE(YEAR($K6),MONTH($K6),DAY($K6))>(TODAY()-7)」という条件を作成しました。文字色を赤くするのは更新日（K列）が前週のミーティング日以降の場合である、という風に条件を整理するのがポイントです。連休などを挟んだ場合、更新日が前回のミーティングから7日以上空くこともありますが、1年のうちそういったイレギュラーなケースの方が少ないはずです。今回はイレギュラーの場合は手動対応する、などの対策でよいと判断しました。
全てのケースに当てはまる判定式が思いつかない場合、更に複雑な数式を考えて解決することもありますが、とりあえず発生する可能性が高い方に合わせておくことでその分の負担を軽くする、という考え方をぜひ皆さんにも知っておいて頂きたいと思います。
次に、本題である「更新日の自動取得」です。使用するのは「Google Apps Script：通称GAS」です。5年以上まともにプログラミングに触れていない私でしたが、前述の通り様々なサンプルがあれば問題ないと思っていました。
ですが、結果的に私が意図する自動取得の完成まで、3日間ほどかけて悩むことになります。そのままコピーすればすぐに使えるサンプルがあるにも関わらず、普通はあまり起きないエラーを2度経験しました。普通はあまり起きないせいで、解決策を検索しても中々当てはまらず3日間も費やすことになったのです。
ここで強調しておきたいのですが、「更新日の自動取得」自体は、すんなりいけば1時間もかからず実行できるはずなので、やってみたいという方にはぜひおすすめです！この記事では、その基本的な準備方法に加えて、非常にマイナーな壁にぶつかり苦戦した体験についても、同じ状況に見舞われるかもしれないどなたかのためになればと思い残しておきます。
1日目：GASを作成するための準備と一つ目のエラー
まず、実際にGASのコードを書き込むための画面を開く必要があります。これは、Googleスプレッドシート上部の「ツール」から「スクリプトエディタ」を選択するだけです。
ここで、早速一つ目の壁にぶつかります。GASそのものの作業に入る前に、非常にマイナーなエラーが発生しました。
上記の方法でスクリプトエディタを選択し、開いたGASの画面をよく見ると、右上に紐付いているGoogleアカウントが表示されています。これが、GASの処理を紐付けたいGoogleスプレッドシート側と同じアカウントでないと、セキュリティの問題なども関連して上手く実行されない可能性や、そもそも権限がなくGASを作成できない可能性があります。ところが、私が持っている複数のGoogleアカウントのうち、画面にはGoogleスプレッドシートとは違うアカウントが表示されていました（下図：赤い矢印部分）。
通常、Googleアカウントを切り替えるなら右上のアカウント表示部分を押して再度選択し直せばよいのですが、なぜか全く上手くいきませんでした。どうやっても別のアカウントでスクリプトエディタが開かれてしまうのです。結論からいうと、この解決策はネット上にもありませんでした。マイナー中のマイナーなエラーだったようです。
次に、アカウント切り替え方法を探る以外に、Googleスプレッドシートからの遷移ではなく、GASのプロジェクト一覧画面から新規プロジェクトを立ち上げる方法を試しました（プロジェクトについては後述）。素人考えながら、スクリプトの実行時にファイル名を指定すればもしかしたら上手くいくかもしれないと思ったからです。
※GAS一覧画面はこちら：https://script.google.com/home/
このような方法でGASのスクリプトエディタ自体を準備することはできますが、今回の目的に沿った実行結果を得るには決定的に不足しているポイントがありました。
それが「トリガー」の設定内容です。GASではスクリプトを実行するトリガーを併せて設定しておく必要があるのですが、今回の目的である「コメントが更新される度に、更新日を自動で取得する」場合では、多くのサンプルで下記の設定をするように書かれていました（トリガーについては後述）。
設定内容
・イベントのソースを選択：Googleスプレッドシートから
・イベントの種類を選択：編集時
ところが、GASの一覧画面からプロジェクトを立ち上げてスクリプトを作成した場合、この選択肢が表示すらされなかったのです。今思えば、Googleスプレッドシートに紐付いたスクリプトエディタを利用していないのですから当然ですが、当初は全体像がよく分かっておらず、とにかく他の選択肢を使って試してみるものの上手くいかない、という失敗を繰り返していました。
こうした経緯から、やはりGoogleアカウントの問題を解決して、Googleスプレッドシートからのスクリプトエディタ起動をせざるを得ないという結論に至り、単純な解決策でこの壁を乗り切ることにしました。
それは、シークレットウィンドウで作業する、というものです。こうすれば、Googleスプレッドシートを開いたときにログインしているアカウント情報から変更されることなくスクリプトエディタを開くことができました。解決策としては非常に単純なのですが、試行錯誤したものの最終的には目的を果たせる可能性がない、という結果を経て、有効な手段はシークレットウィンドウのみ、と結論付けられました。この時点ですでに疲れ切っていたのも今ではいい思い出です。
2日目：GASスクリプトの作成・実行と二つ目のエラー
作業を始めて2日目にしてようやく、スクリプトエディタ上でGASそのものの作業に進みます。これはサンプルが沢山あるため、コピーして一部を書き換えれば終わるはずです。ポイントとなるのは以下の2点です。
①スプレッドシートのファイル名を指定できること
②更に、スプレッドシート内の対象シートも指定できること
①は、そもそも複数人が扱うファイルであり、各自がそれ以外のファイルも同時に開いている状態で作業をするケースが多いため、よく使われる「今開いているファイルを取得する：getActiveSpreadsheet()」という関数では実行不可であったためです。調べてみるとファイル指定用のサンプルもちゃんとありました。
var ss = SpreadsheetApp.openById(‘●●●’);
※●●●部分には、GoogleスプレッドシートIDが入ります。
※GoogleスプレッドシートIDは「https://docs.google.com/spreadsheets/d/●●●/edit」の●●●部分です。
ただし、ファイル名の指定は実際には不要だったことが分かりました。1日目と違って、そもそも作成しているGASスクリプトはGoogleスプレッドシートに紐付いているので、他のファイルでは実行されません。勉強にはなりましたが、getActiveSpreadsheet()関数を使用することにしました。
②もそこまで珍しい話ではありませんが、ファイル上にシートが複数ある場合、こちらもよく使われる「今開いているシートを取得する：getActiveSheet()」という関数では、関係ないシート上でもGASが実行されてしまします。このシート名指定の書き方もサンプルにはありましたが、ここから二つ目の壁が現れることになります。
私が見つけたサンプルの本来の記述
function insertLastUpdated() {
var ss = SpreadsheetApp.getActiveSpreadsheet(); //ファイル指定なし
var sheet = ss.getSheetByName('●●●'); //対象のシート名を指定(●●●部分)
var currentRow = sheet.getActiveCell().getRow(); //アクティブなセルの行番号を取得
var currentCol = sheet.getActiveCell().getColumn(); //アクティブなセルの列番号を取得
var currentCell = sheet.getActiveCell().getValue(); //アクティブなセルの入力値を取得
var updateRange = sheet.getRange('K' + currentRow); //K列に更新日時を挿入する
Logger.log(updateRange); //更新日の挿入
if(currentRow > 2 && currentCol == 10) { //3行目以降、J列で変更があった場合）
if(currentCell) {
updateRange.setValue(new Date());
}
}
}
※//以降のテキストは、「コメントアウト」といってメモ書きのようなものです。そのままコピーしてGASスクリプトに利用できます。
これでGASスクリプトを実行しても（実行までに必要なその他の設定は後述）、何も反応が起きないため、2日目はこの調査に費やすことになりました。私は経験上簡単なデバッグ作業ならできるため、原因を調査してみました（デバッグ作業については後述）。
その結果、②でポイントとしていたシートの指定箇所からすでに上手く実行できていないということが判明しました。ただし、サンプル通りの記述でも別の処理であれば問題がないことも確認しました（例えば、指定したシート名そのものを取得するといったテスト）。
なぜかは分からないものの、シート名の指定処理と、それ以降のアクティブ（編集中）なセルを取得したり更新日を挿入するといった処理との相性が悪いようでした。
さらにデバッグ作業を行い、シート名を指定すると「今アクティブなセルを取得する：getActiveCell()」の関数部分が常に【A1セル】に値固定されてしまい処理が意図通りに動かない、という現象が起きていることが分かりました。そこで、シート名の指定の仕方を変える方法を検索しますが、「getSheetByName()」以外には見つからず、そのまま3日目に突入します。
3日目：一つだけ見つけたサンプルコードでやっと成功
ネット上であらゆる検索を行い、少ないながらも同じようにgetActiveCell関数で取得されるセル位置が【A1セル】になってしまうケースを発見しました。ただ、原因はバグであるというような記述もあり、それを見た瞬間、この件の解決はほとんど素人のような私には無理だと判断し諦めました。残すはやはり、getActiveSheet関数を使えばうまく実行される処理に、どうにかしてシート名指定を加える方法を模索する道です。
いわゆるif文というもので条件を指定すればよいのでは、と何となくアタリをつけてみましたが、ろくに調べずにGASスクリプトを勘で書いてもやはり成功しませんでした。そこで更に検索し続けると、ついに探し求めたサンプルを発見します。それを基に完成させたのが以下のGASスクリプトです。
function insertLastUpdated() {
var ss = SpreadsheetApp.getActiveSheet(); //開いているシートが指定したものと合致するか次のif文で確認
if (ss.getName() == "シート名") {
var Row;
var Col;
Row = ss.getActiveCell().getRow();
Col = ss.getActiveCell().getColumn();
if(Row > 2 && Col == 10){ //3行目以降、かつJ列で変更があった場合
ss.getRange(Row, 11).setValue(new Date()); // K列に更新日時を挿入
}
}
}
これを実行すると、やっと思い通りに成功することができました。
まとめ：実行までの一連の流れ
スクリプトエディタを開き、サンプルコードをコピペし、いくつかのアレンジを加えて完成したGASスクリプトですが、実際に処理が動くようにするためにはいくつかの手順を踏みます。当然私が検索したように、ウェブ上にも細かい説明が載っているサイトがありますので、今回の目的に必要な部分に絞って、簡単に流れを紹介します。
プロジェクト名
スクリプトエディタを開いて現れる画面上で設定できる処理の塊をプロジェクトと呼びます。GASの一覧画面でもこの単位で表示されます。この中で更にいくつかのGASスクリプトを「.gs」ファイルとして保持できるようですが、いずれにせよ、実行する前に名前を確定しておかないと保存についてのウィンドウが表示されるので、予め入力するようにしましょう。
デバッグ
前述した通り、デバッグを実施することで処理の不具合の原因を検証することができます。今回のような細かい調査目的でなくても、記述ミスやGASスクリプト自体の間違いによるエラーが起きないかを一度確認する意味で、まずはデバックボタンを試すと良いでしょう。エラーがあれば画面上部にメッセージが表示されるので分かりやすいです。
トリガー
すでにほとんど説明していますが、何をきっかけにGASスクリプトで作成した処理を実行するかを併せて設定しておく必要があります。それがトリガーです。今回の目的であれば、以下の選択でよいでしょう。
実行
全ての設定が整ったら、実行ボタンを押します。これで、トリガーの条件に合致するアクションを起こした時に、このGASスクリプトの処理が実行されます。
GAS一覧
これもすでに紹介していますが、作成したGASのスクリプトやトリガーは一覧上のプロジェクト単位で確認・編集が可能です。今回のようにGoogleスプレッドシートと紐付いているGASスクリプトはアイコンが違っていることも発見しました。
いかがだったでしょうか。冒頭で強調したように、本来は3日間も費やすような作業ではありません。とても簡単です。ですが私はよくマイナーなエラーにぶつかるらしく、こういった試行錯誤を繰り返しています。なお、出来上がった自動挿入は早速チームに共有し、ミーティングで（地味ながらおそらくは）役立っています！
次回の研究結果も、お楽しみに！
facebook
X (twitter)
LINE
