Googleスプレッドシート GASを使って、あしたへジャンプ！ (GASで日付を扱うポイント他)

mir

2023年3月2日 19:24

前回の XMATCH 超応用例から派生した小ネタです。

ちなみに「あしたへジャンプ」は 1990年代前半に NHKの教育テレビで放送されていた小学校高学年向けの道徳ドラマです。

ただ単に今日以降のセルにジャンプするという動作のお題から、思い出した（思いついた）だけなので、今回の記事とは一切関係ありません。そして年代がバレるｗｗ

前回の平日更新記事

スプレッドシートで直近の予定の日付に飛びたい

前回は関数での解決策を検証した、こちらのあるあるなお題。

今回は、GAS（GoogleAppsScript）でシートを開いた時、もしくはボタンを押したら直近の予定の行（セル）へ飛べるようにしたい！

を解決する GASを検証したいと思います。

「今日の日付へ飛びたい」は GASをお勧めしない

ただ、前回も書いた通り本来この処理は GASはお勧めしません！

GAS（Javascript）は日付の扱いがちょっと面倒
スマホ（アプリ版）で利用できない
GAS の activateは省エネな動き

理由としてはこの3つをあげました。

唯一解決できないスマホ利用問題は除外するとして、今回はあえて「お勧めしない」GASに手をだしてみましょう。

女子に「先生～、〇〇くんが GASを使おうとしています」って言われそうで、まさに道徳番組でありそうな展開ですねｗ

GASを実行して A列の直近の予定の日付にジャンプしたい

前提条件としては以下の通り

・A列の A4以降に 2023年の日付が 1月から昇順で入ってる
・途中にA列が空白の行はない
・同じ日付に複数の依頼（複数行に連続して同じ日付）というケースもある
・依頼がない日付は入っていない（とばされている）
・直近の予定日とは今日以降の一番近い日付の予定を指す。
　（今日に2/21 が存在しない場合は今日から一番近い日付）

・GASはボタンから、もしくはスプレッドシートを開いたら実行したい
・できればジャンプ先のセルが一番上に表示されるようにしたい

上の画像の動きは最後の要件 「ジャンプ先のセルが一番上に表示されるようにしたい」が満たせてませんが、まずはこれを作っていきましょう。

今回はハマりやすいポイントも抑えていきます！

GASで日付を扱う

早速コードを書いてもいいんですが、せっかくなんで面倒なGASにおける日付の扱いを少し解説しておきましょう。

GASでの日付の扱いは、基本的には Javascriptと一緒ですが、一部 GASならではのメソッドもあります。

new Date() はシート関数でいうところの NOW()

今日の日付を得る為には

new Date()

と記述します。

中身をログに出力して確認すると

このように、この関数が実行された日付けと時間が返っているのがわかります。暦の表記が英語仕様でわかりづらいですが、

2023年2月26日日曜日 14:29:59

となってます。

つまり、ここで得られる日付は今日という日付ではなく、〇時〇分〇秒という情報も含む 今の日時 ってことです。（ただしくは日付オブジェクトです）

シート関数でいうと、TODAY() ではなく NOW() と考えるとわかりやすいかも。

ハマりやすいPoint1
new Date() は今日ではなく、今（時刻含む）である

もし、ここでログに出力された時間がズレている、もしくは「Japan Standard Time」となっていない場合は、このプロジェクト（GAS）のタイムゾーン設定が日本以外になっている可能性があります。

プロジェクトの設定（歯車アイコン）から、タイムゾーンを確認して、日本時間になっていない場合は修正しましょう。

ハマりやすいPoint2
GASのタイムゾーンはスプレッドシートと別。ズレてたら要確認

Utilties.formatDate で日付の表示形式を整える

これは Javascriptにはなくて GASオリジナル です。

GASには日付オブジェクトを文字列に変換する専用のユーティリティメソッドが用意されてます。

これはシート関数だと（日時専用の）TEXT関数みたいな感じですかね。
日付を自分の欲しい表示形式にできます。

formatDate(date, timeZone, format)

注意点
・date は日付オブジェクトを入れる必要がある（日付文字列は✖）
・timeZone は日本の場合は "JST" または "Asia/Tokyo" を指定
・format で月は M 時間は m と区分けされてる。大文字小文字に注意

今回、日付のみをスラッシュ区切りで取得するために

Utilities.formatDate(date,"Asia/Tokyo","yyyy/MM/dd");

このようにしましたが、結果として得られる 2023/02/26 は文字列 Stringという扱いです。今日の日付の Dateオブジェクトではありません。

ハマりやすいPoint3
Utilties.formatDateでの M（月）とm（分）は別モノ。
yyyy/mm/dd は誤り。yyyy/MM/dd とする。

今日の「日付」だけが欲しい時

今回は、後の処理で使う用に Dateオブジェクトが欲しいのですが、

new Date()　・・・現在時刻を含む今を表す日付オブジェクト
Utilties.formatDate 後　・・・　文字列

となると、どうすればいいのか？

シート関数ならシリアル値という考え方があるので INT関数で小数部分カットという技が使えるんですがｗ

実はUtilities.formatDateで日付のみにした文字列を再度 new Date() に与えることで本日の日付のみの Dateオブジェクトを生成できます。

function myFunction() {
  let date = Utilities.formatDate(new Date(),"Asia/Tokyo","yyyy/MM/dd");
  const today = new Date(date);
  
  console.log(today);
}

これは、Utilites.formatDateが使える GASならではの書き方で、Javascriptの場合は、

  let today = new Date();
  today = new Date(today.getFullYear(),today.getMonth(),today.getDate());

こんな書き方をすることが多いかと思います。

上のように new Date() は日付っぽい文字列をカッコ内に入れる以外に、カンマ区切りで年、月、日を数値で入れて日付オブジェクトを生成できます。

これは、シート関数の DATE(年,月,日) っぽいですね。

ただし、直接 new Date() に数値を入れる場合は注意点があります。
ここも月が注意ポイントなんですが、

このように 2023年2月27日に本日の日付オブジェクトから

today.getMonth()

で月の数値を得ると 2月なのに 1と表示されます。

逆に 2023年2月27日の日付オブジェクトを得ようと

new Date(2023,2,27)

とすると、Mar（マーチ）つまり、 2023年3月27日となってしまいます。

これは Javascript の月の数値は 0から11までと、日本人的感覚からすると-1ズレているからです。

0が January　・・・1月
11が December　・・・　12月

うーん、わかりづらい。

ハマりやすいPoint4
Javascript・GAS の月は 0スタート。
getMonth() した月の数値 +1が〇月と覚える

とりあえず、本日の日付のみの Dateオブジェクトが作れました。

getValue(getValues)で取得したセルの日付は Dateオブジェクトになってる

それではスプレッドシートのセルに入っている日付データは、同じように getValueで取得した後、 new Date() に渡して日付オブジェクトにすればよいのか？

実はそんな必要はなく、セルの日付はそのままで Dateオブジェクトになっています。

このように「今日の日付」の方の型は strig、2023/2/26 の方の型は object となっているのがわかります。

要は取得してそのまんま日付オブジェクトとして使えるってことです。

余談：セルの日付を文字列として使いたい場合は getDisplayValueが便利

余談ですが、取得した日付を Gmailの本文に使ったり、他の文字列と連結させたり、文字列として比較して一致を判定したい場合は、getValueで Dateオブジェクトになってしまうと不便だったりします。

そんな時は getDisplayValue(getDisplayValues) で取得すると便利です。

getDisplayValueはシート上で表示形式を反映させた表示そのままを文字列として取得できるメソッドです。

特に日付を文字列として取得したい時は便利です。

ハマりやすいPoint5
スプレッドシートから getValue(s)で取得した日付は 日付オブジェクト。
日付を文字列として扱いたい場合は getDisplayValue(s) が便利。

日付オブジェクトは一致は判定できないが大小比較はできる

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange(1,1,1,2).getValues();

  console.log(values[0][0] == values[0][1]); //false
  console.log(values[0][0] <= values[0][1]); //true
  console.log(values[0][0] >= values[0][1]); //true
}

同じ日付を表す日付オブジェクト同士を 等価演算子 == で一致するか判定しようとしても false となってしまいます。つまり日付オブジェクトは、そのままでは一致を判定できないってことです。

日付同士の一致を判定したい場合は、Utilites.formatDate で文字列にしたり、最初から getDisplayValueで取得し 文字列同士で判定するか、getTime()などのメソッドで ミリ秒（数値）にして判定する必要があります。

一方 < 、 <= 、> 、 >= といった比較演算子は使えます。意外かもしれませんが、日付オブジェクトのまま大小の比較判定は出来るってことです。

わざわざ比較する際にミリ秒に変換する必要はありません。

ハマりやすいPoint6
日付オブジェクトは、そのままでは一致は判定できない。
比較演算子による 大小の比較は 日付オブジェクトのまま判定できる。

今回のお題が 「本日以降の直近の日付」なので、比較演算子による処理が使えそうですよね？

これでGASにおける「日付」の基本理解と準備は整いました。

Javascript における XMATCHの上位互換検索メソッド findIndex

Googleスプレッドシートの最強関数の一つ FILTER関数は、なぜ検索・抽出において圧倒的に便利なのか？

もちろん配列処理し、複数の結果を配列で返せるという点も魅力ですが、検索条件に式が使えるという点も大きいんじゃないでしょうか？

FILTER関数は条件式が TRUEを返した要素を抽出するという動きをします。

XLOOKUPやXMATCHは、ワイルドカードや近似値一致など単純一致以外の検索もできる便利な検索関数ではありますが、

もし、XLOOKUPや XMATCH の一致条件が条件式で書けたら？

って思うことがあります。

これできたら XLOOUP、XMATCHは真の最強検索関数なんだけどなー。

実は GAS（Javascript）の配列メソッドには、このXMATCH の一致条件が条件式で書けたら？ という、あんなこといいな、出来たらいいなを実現するドラえもんの秘密道具のような強力な検索メソッドがあります。

それが findIndex() です。

説明をみると

findIndex() メソッドは、配列内の指定されたテスト関数に合格する最初の要素の位置を返します。

とあります。まさに今回の「本日以降の直近の日付」の位置（行番号）を得るのに最適なメソッドですね。

function sample(){
  const array = [
    ["田中",23],
    ["山田",26],
    ["鈴木",28],
    ["木内",30],
    ["鮫島",33],
    ["宮内",37],
    ["三田",42]
    ]; //6人の名前と年齢

    //31歳以上を探す
    const index = array.findIndex(v => v[1] >= 31);　

    console.log(index);　//4　・・・　0スタートなので 5番目の鮫島

}

Googleスプレッドシートで使えそうなサンプルコードはこんな感じ。

スプレッドシートからgetValuesで取得したデータを想定して、名前と年齢が入った二次元配列で 31歳以上の年齢という条件を満たす一人目の位置を取得しています。

二次元配列なので各要素 v （配列）の 2つ目（０始まりなので v[1]）を取り出して比較しています。

ハマりやすいPoint7
スプレッドシートから getValuesで取得したデータは二次元配列。
二次元配列に対して配列メソッドを使う場合は、個々の要素が配列であることに注意

そして結果は 0始まりの配列の indexなので、 4ということは人間目線では +1して 5番目であるという点も注意です。

ハマりやすいPoint8
配列は 0はじまりなので、行番号として使う場合は +1する必要がある

Array.prototype (配列メソッド）は、アロー関数でシンプルに書けるとクールです。何度も使って慣れましょう。

日付の扱いを踏まえ、findIndexを使って「直近の予定の日付に飛ぶ」コードを書いてみましょう。

A列の直近の予定の日付にジャンプするコード

function toNextSchedule() {
  const sheet = SpreadsheetApp.getActiveSheet();　//開いているシート

  const date = Utilities.formatDate(new Date(),"Asia/Tokyo","yyyy/MM/dd");
  const today = new Date(date);　//今日の日付

  const range = sheet.getRange("A:A");
  const days = range.getValues();　

  const index = days.findIndex(day => day[0] >= today);
  sheet.getRange(index+1,1).activate();　//行番号なので配列の位置に +1
}

上で日付の扱いと findIndex には触れたので、解説は不要ですよね？面倒なんで A列を値を全部ぶっこ抜きしてます。

お目当てのセルにジャンプする処理は、最後の

sheet.getRange(index+1,1).activate()

ここです。activate メソッドを使います。

思ったよりはシンプルなコードじゃないでしょうか？

GASの動きを確認

ボタンに割りあてて動かしてみましょう。

ボタンを作成し、GASの関数を割り当てる流れはスピンボタンの回で丁寧めに書いています。

今日 2/26の日付が A列にはないので直近の日付である2/27 がアクティブになりましたね。

ただし、前回も触れましたが GASの省エネな動き、アクティブなセルが画面の一番下で終了してしまう問題が残っています。

ハマりやすいPoint9
GASの activate() は、見つかった段階（画面にギリ表示されるのアクティブなセルが表示画面の下、もしくは右端）で止まる。
気を利かせて左上をアクティブにしてはくれない。

コードを加筆して、これを解決しましょう！

スプレッドシートで直近の予定の日付に飛びたい（アクティブセルが左上に表示される状態にしたい）

この問題を解決するに、上に 16行表示されてるから本来の行番号 + 16して、16行下をアクティブにすれば・・・なんてことを考える人がいますが、完全に見誤ってます。

Gmailでワードの送り状みたいな日付を右に寄せたり、レイアウトに凝りすぎた本文をスペースキーで作って、相手の環境で見たらガタガタってパターンと一緒です。

そもそも直近じゃない日がアクティブになったら意味ないですし、パソコンのモニターやその人が使ってる表示倍率によって 1画面に何行表示されるかは変わってきます。

じゃあどうするか？

上から下へという動きでアクティブセルが見つかった（画面に入った）段階で止まるので一番下に表示されるなら、表の一番下から上へという動きにすれば、アクティブセルが見つかった（画面に入った）段階で止まると一番上に表示されるのでは？

って考えるとよさげじゃないでしょうか？

この動きは GASを使わずに検証できます。やってみましょう！

下から上へ activate をテストしてみる

GAS実行前に A1アクティブ状態から手動で Ctrl + ↓ を何度か押して、一番したの A1000セルをアクティブにし表示させた状態とします。

この状態で GASを実行すると、予想通り見つかったセル（今回の場合は 2/27）が左上の状態でアクティブとなりました。

このGAS実行前の準備をコードに追記すればいけそうですね！

シートの最終行を取得する

では、GASでアクティブなシートの最終行を取得するにはどうすればよいか？

最終行と言えば、 getLastRow()というメソッドがありますが。

でも、実はこれ sheet（シート）に対して使う時と、range（セル範囲）に対して使う時で挙動が違います。

sheet.getLastRow()
シートのデータが存在する最終行を返す

range.getLastRow()
指定したセル範囲内の最終行を返す

シートに対して getLastRowを使った場合は、そのシートのデータが存在する最終行の行番号、つまり getDataRangeの最終行番号を返します。

一方、sheet.getRange でシートからセル範囲を取得した場合は、そのセル範囲の最終行の行番号を返します。

A:Aとした場合はシートの最終行（今回の場合は1000）となりますし、A10:A100とすれば最終行は100となります。

ハマりやすいPoint10
GASの getLastRow()は、対象によって挙動が違う。

・シートを対象とした場合は データが存在する最終行を返す。
・range(セル範囲）を対象とした場合は、範囲内の最終行を返す。
※範囲を A:A と列指定した場合は、シートの最終行を返す

今回はシートの最終行をまずはアクティブにしたいので、列指定したrangeに対して getLastRow() で良さそうです。

そこまでの処理を反映させる魔法の言葉　flush

flash（閃光のフラッシュ）じゃなくて flush（あふれ出る）という意味の方の単語です。

シートの最終行を1度アクティブにしてから、ヒットした今日以降の直近の日付をアクティブにする「下から上」の動きをさせる為に、

このように12行目に

sheet.getRange(range.getLastRow(),1).activate();

としただけではうまくいきません。

あれれーおかしいぞー？、じゃあちょっと待てばよいのかな？と、

Utilities.sleep() というミリ秒処理を待たせる方法を使ってもダメです。

GASは処理負荷を軽減する為か、コードの処理が全て終わってからでないと画面上反映されない という特徴があります。

これを知らないと、ここでかなりハマります。

例えば GASで数式を入れてその計算結果を getValue() しようとしても、
計算結果が取得出来なかったり、setValue()で色々入力してからスプレッドシートをPDF化したはずなのに、入れたはずの値が入ってなかったり。

こんなお悩みは、ほぼ全て SpreadsheetApp.flush() で解決します。

SpreadsheetApp.flush()

と書いて使います。

これを入れることで、「ここまでの処理を反映させる」ことができます。今回の場合は、最終行をアクティブにした状態を反映させて、その後今日以降の直帰の日付に飛べば良いですね！

ハマりやすいPoint11
GASの処理は途中では反映されていない。
そこまでの処理を一旦反映させたい時は
SpreadsheetApp.flush()

ドキュメントやスライドだとこれに該当するメソッド名がバラバラなのよね・・・

最後に完成版のコードと検証です。

A列の直近の予定の日付にジャンプし (上部）に表示するコード

function toNextSchedule() {
  const sheet = SpreadsheetApp.getActiveSheet();　//開いているシート

  const date = Utilities.formatDate(new Date(),"Asia/Tokyo","yyyy/MM/dd");
  const today = new Date(date);　//今日の日付

  const range = sheet.getRange("A:A");
  const days = range.getValues();

  const index = days.findIndex(day => day[0] >= today);

  sheet.getRange(range.getLastRow(),1).activate();　//一度最終行をアクティブに
  SpreadsheetApp.flush();　//画面更新

  sheet.getRange(index+1,1).activate();　//行番号なので配列の位置に +1
}

1度一番下の行を表示してから、直近の日付がアクティブになる動きが目で追えるので、なんかパッパッといった2段階の動きに若干違和感はあるものの、とりあえず完成しました。

もちろんこの関数名を onOpen() にすれば、開いた時に直帰の日付がアクティブ（左上）になるシートとなります。

次回はまた関数の単発ネタを

前回、XMATCH関数の近似値一致大と HYPERLINK関数で実装した

「A列の今日以降の直帰の予定に飛ぶ」

という処理を　今回は GASで実装してみました。

どうでしょうか、別にGAS使わなくてもよくない？って思いませんか？

個人的には関数・数式、標準機能で出来ることは、そっちで処理して GASは最後の手段って考えなんで、これは自分では使わないかなー。

でも、GASでの日付の扱い、使い方によっては便利な getDisplayValue、結構好きな findIndex、処理を反映させる flush と、この辺りを説明するのに良い題材だったと思います。

次回の平日更新は、また関数小ネタを予定。

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

Googleスプレッドシート GASを使って、あしたへジャンプ！ (GASで日付を扱うポイント他)

スプレッドシートで 直近の予定の日付に飛びたい

「今日の日付へ飛びたい」は GASをお勧めしない

GASを実行して A列の直近の予定の日付にジャンプしたい

GASで日付を扱う

new Date() は シート関数でいうところの NOW()

Utilties.formatDate で日付の表示形式を整える

今日の 「日付」だけが欲しい時

getValue(getValues)で取得した セルの日付は Dateオブジェクトになってる