Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What is going on with this article?
@3mc

Googleスプレッドシートで、今日以前の行を非表示にする方法。

More than 1 year has passed since last update.

はじめに

下記のようなスプレッドシートでシフトを管理しているとします。

日付 ジョバンニ カムパネラ
2018/10/01 早番 遅番
2018/10/02 早番 遅番
2018/10/03 早番 遅番
2018/10/04 通し
2018/10/05 遅番 早番

二人のバイト歴が長くなるにつれて、スプレッドシートの行が長くなってしまうため、2行目から前日までの行は自動で非表示されるようにしたい。

完成したコード

Code.gs
function HideRowsBeforeToday() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('Sheet1'); // シート名で指定
  const values = s.getRange('A:A').getValues(); //A列に日付けが入っているので、配列で取得。
  const today = new Date(new Date().setHours(0, 0, 0, 0)); //本日の0時の日付を作成

  //後続でIndexOfするので、それぞれ文字列にする。
  const arrayDateToString = values.map(String); 
  const todayToString = Utilities.formatDate(today, 'JST', "E MMM dd yyyy HH:mm:ss 'GMT'Z '(Japan Standard Time)'");

  //今日の日付のindexを取得
  const indexOfToday = arrayDateToString.indexOf(todayToString);

  //2行目から前日までの行を非表示する。
  s.hideRows(2, indexOfToday-1);  

}

コードの説明

Code.gs
function HideRowsBeforeToday() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('Sheet1'); // シート名で指定
  const values = s.getRange('A:A').getValues(); //A列に日付けが入っているので、配列で取得。

スプレッドシートを指定して、A列にある日付けを配列で取得します。
A列には時間の情報が含まれていませんが、値を取得してみると0時0分0秒になっているようです。

[Wed Aug 03 00:00:00 GMT+09:00 2016]

Code.gs
  const today = new Date(new Date().setHours(0, 0, 0, 0)); //本日の0時の日付を作成

A列と比較するために。new Date()で本日の日付けを取得します。
setHours()でA列に合わせて時刻を0時0分0秒にしますが、このままでは戻り値が数値になってしまいます。

戻り値
協定世界時 (UTC) 1970 年 1 月 1 日 00:00:00 から更新された日時までの間のミリ秒単位の数値。
Date.prototype.setHours() - JavaScript | MDN

なので、再度new Date()してDateオブジェクトにします。

1.5380604E12 ←new Date().setHours(0, 0, 0, 0)
Fri Sep 28 00:00:00 GMT+09:00 2018 ←new Date(new Date().setHours(0, 0, 0, 0))

日付けが出揃ったので、IndexOfを使って、A列(日付け)の配列から、今日の日付けの行番号を取得しようとしても、-1が返ってしまいうまくいきません。

理由は下記の通りで、オブジェクトの型が一致しないことが原因のようです。

indexOf は searchElement と配列の要素を 厳密な同値(三重イコール演算子 === で使われるのと同じ方法)を使って比較します。
Array.prototype.indexOf() - JavaScript | MDN

Code.gs
  //後続でIndexOfするので、それぞれ文字列にする。
  const arrayDateToString = values.map(String); 
  const todayToString = Utilities.formatDate(today, 'JST', "E MMM dd yyyy HH:mm:ss 'GMT'Z '(Japan Standard Time)'");

そこで、それぞれを文字列にして比較できるようにします。
後者の文字列への変換方法がかなり力技ですが、気にしないでください。

formatDate(date, timeZone, format)
Formats date according to specification described in Java SE SimpleDateFormat class. Please visit the specification at http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
Class Utilities  |  Apps Script  |  Google Developers

Code.gs
  //今日の日付のindexを取得
  const indexOfToday = arrayDateToString.indexOf(todayToString);

  //2行目から前日までの行を非表示する。
  s.hideRows(2, rowNumHidden-1);  

}

indexOfで今日の日付の行番号(正確にはarrayDateToStringのindex)を取得します。

引数に与えられた内容と同じ内容を持つ配列要素の内、最初のものの添字を返します。存在しない場合は -1 を返します。
Array.prototype.indexOf() - JavaScript | MDN

hideRowsで先頭行と行数を引数に指定し、行を非表示にします。

hideRows(rowIndex, numRows)
Hides one or more consecutive rows starting at the given index.
Class Sheet  |  Apps Script  |  Google Developers

冒頭の例で本日の日付が2018/10/03だとすると、先頭行は2で非表示にする行数は2になります。

日付 ジョバンニ カムパネラ
2018/10/01 早番 遅番
2018/10/02 早番 遅番
2018/10/03 早番 遅番
2018/10/04 通し
2018/10/05 遅番 早番

このとき行数は、const rowNumHidden = arrayDateToString.indexOf(todayToString)から-1した値になります。

トリガーの設定

コードが完成したので、日次でトリガーを設定してあげればOKです。

やり残したこと

Code.gs
  //後続でIndexOfするので、それぞれ文字列にする。
  const arrayDateToString = values.map(String); 
  const todayToString = Utilities.formatDate(today, 'JST', "E MMM dd yyyy HH:mm:ss 'GMT'Z '(Japan Standard Time)'");

  //今日の日付のindexを取得
  const indexOfToday = arrayDateToString.indexOf(todayToString);
}

今日の行数文字列に変換して比較するのが力業なので、よりスマートな方法を知りたい。

Stack Overflowではスマートな方法が紹介されていましたが、うまくいかなかったので採用を見送りました。

2
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
3mc
プログラミング初級者。色々調べて得た内容を書いています。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
2
Help us understand the problem. What is going on with this article?