こまごまGASいじり ～フィルタでの絞り込み～

お疲れさまです。みやもとです。

前回のGASを使ったLINEBOT、最終的にそれなりうまく動くようにはなったものの途中で想定通りの動作にならず、とりあえず動くように修正したところがいくつかありました。

https://qiita.com/Mymt_aggw2208/items/09992425735e4c62b8ef

今回はそのうちフィルタによるデータの絞り込みについて、ちょっと原因を探ってみることにしました。

# 日付とIDでデータを絞り込みたい
まず、とりあえず動くようにしたメソッドのソースがこちらになります。
```JavaScript:コード.gs
/**
 * 日記データの位置を取得するメソッド
 * @param {String} id - データキー（日付＋ID）
 */
function getIndex(id){
  // 最終行の取得
  let lastRow = DiarySheet.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return -1;
  // データの取得
  let datas = DiarySheet.getRange(2,1,lastRow-1, 6).getValues();
  // データの検索
  let dataIndex = datas.findIndex((value) =>{
    return value[1] == id
  })
  return dataIndex;
}

/**
 * 日記データを読み込むメソッド
 * @param {Number} dataIndex - データ位置
 */
function getTodaysDiary(dataIndex){
  // データの取得
  let date = DiarySheet.getRange(dataIndex+2,1,1,1).getValue();
  let id = DiarySheet.getRange(dataIndex+2,2,1,1).getValue();
  let sentence = DiarySheet.getRange(dataIndex+2,3,1,1).getValue();
  let translated = DiarySheet.getRange(dataIndex+2,4,1,1).getValue();
  let comment = DiarySheet.getRange(dataIndex+2,5,1,1).getValue();
  let status = DiarySheet.getRange(dataIndex+2,6,1,1).getValue();
  let data = [date, id, sentence, translated, comment, status];

  return data;
}
```
まず日付とLINEのIDを結合したキー項目でスプレッドシートの決まった列を検索し、一致する値が書かれた位置を返しています。
これでその日の日記データがあるかどうかを判定し、データがある場合はその位置を使ってスプレッドシートの行を各項目としてリストに格納・呼び出し元に返すようにしています。

しかしこれ、最初はこんな感じでひとつのメソッドにしていました。
```JavaScript:コード.gs
/**
 * 日記データを読み込むメソッド
 * @param {String} date - 日付
 * @param {String} id - ユーザーID
 */
function getTodaysDiary(date, id){
  // 最終行の取得
  let lastRow = DiarySheet.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return;
  // データの取得
  let datas = DiarySheet.getRange(2,1,lastRow-1, 6).getValues();
  // データの検索
  let data = datas.filter((value) =>{
    appendLog(id, 'value：' + value)
    return value[0] == date && value[1] == id
  })
  return data;
}
```
日付とユーザーIDを結合せず、それぞれ個別の項目としてフィルタをかける形です。
何も返ってこなかったらその日の日記データなし、返ってきたらその内容を判定して動作を分けて……と考えていたのですが、うまくデータが返ってこなかったため先の内容に修正したのでした。
この時はフィルタに複数条件てできないんだろか、と思ったのですがさすがに単一条件だけの絞り込みしかできないのは使い勝手とか考えてもなさそう。

では、ここからは検証です。

## テストデータ
テスト用のスプレッドシート「検証１」をこんな感じで準備します。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/78a33ea3-a889-c04d-3a02-cb679301733c.png" width="50%">

適当に作ったもんだからキーで一意にならないですね。
……まあ今回は複数の条件に該当するデータが取れるなら何件取れてもいいので良しとします。
次やる時はちゃんと整えます。

## 1回目：現象再現
まず、失敗したのとほぼ同じ内容でコードを書いて実行します。

```JavaScript:コード.gs
// スプレッドシートの取得
let SS = SpreadsheetApp.getActiveSpreadsheet();
let TEST01 = SS.getSheetByName("検証１");
let RESULT01 = SS.getSheetByName("検証１出力");

function test01() {
  // 2024/06/06のuserId02データを取得する
  var date = new Date('2024/06/06');
  var keyDate = Utilities.formatDate(date, 'JST', 'yyyy/MM/dd');
  var result = getReadTest01(keyDate, 'userId02');
  for (var rs of result) {
    RESULT01.appendRow(rs);
  }
}

/**
 * 検証１データを読み込むメソッド
 * @param {String} date - 日付
 * @param {String} id - ユーザーID
 */
function getReadTest01(date, id){
  // 最終行の取得
  let lastRow = TEST01.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return;
  // データの取得
  let datas = TEST01.getRange(2,1,lastRow-1, 3).getValues();
  // データの検索
  let data = datas.filter((value) =>{
    return value[0] == date && value[1] == id
  })
  return data;
}
```
キーとして日付とIDを渡し、返ってきた配列をそのまま「検証１出力」というシートに出力するコードです。
そして実行結果がこちら。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/95014750-4f36-9044-3872-376e5af44382.png" width="80%">

案の定なんも出てませんね！
次行きましょう。

## 2回目：IDだけで取得
ここからは呼び出し元は書かずに読み込みメソッドだけ書いていきます。
まず原因特定ということで、おそらく間違いなく日付の方が原因とは思いますがIDだけでデータが取得できることを確認します。

```JavaScript:コード.gs
/**
 * 検証１データを読み込むメソッド
 * @param {String} date - 日付
 * @param {String} id - ユーザーID
 */
function getReadTest01(date, id){
  // 最終行の取得
  let lastRow = TEST01.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return;
  // データの取得
  let datas = TEST01.getRange(2,1,lastRow-1, 3).getValues();
  // データの検索
  let data = datas.filter((value) =>{
    return value[1] == id
  })
  return data;
}
```

実行結果はこの通り。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/80e969c9-c662-d94d-166d-29b3c98acb1a.png" width="80%">

ちゃんと条件に合うデータ3件が出力されました。
ということで、文字列条件で絞り込むのはうまくいってる。
やっぱり日付の問題のようです。

## 3回目：日付だけで取得
いよいよ原因と思しき日付での絞り込みです。

```JavaScript:コード.gs
/**
 * 検証１データを読み込むメソッド
 * @param {String} date - 日付
 * @param {String} id - ユーザーID
 */
function getReadTest01(date, id){
  // 最終行の取得
  let lastRow = TEST01.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return;
  // データの取得
  let datas = TEST01.getRange(2,1,lastRow-1, 3).getValues();
  // データの検索
  let data = datas.filter((value) =>{
    RESULT01.appendRow(['キー：'+ date, '検索値：'+ value[0]])
    return value[0] == date
  })
  return data;
}
```
今回、絞り込みの条件に合う内容を返す直前でキーの日付と比較するスプレッドシート上の日付を出力してみることにしました。
これで比較内容がどうなっているのかがわかるはずです。

そして結果がこう。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/66ad7644-830f-e464-6014-2e059ec79011.png" width="80%">

<b>めっちゃ違うな？</b>
最初見たときはかなりびっくりしましたが、ここで再度テストデータの日付部分を見てみましょう。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/78a33ea3-a889-c04d-3a02-cb679301733c.png" width="50%">

項目がセルの中で右寄せされています。文字列なら左寄せになるので、つまりこの日付は文字列ではない。

日記ボット作成時、私はコード上で日付の書き込み時にDate型を年月日のみの文字列フォーマット変換したので書き込んだ後の検索時にも文字列のつもりで処理を書いていました。
しかし考えてみればExcelでも日付を入力すると日付データとして処理されるので、考えてみれば上記の出力も当たり前。
そりゃ該当データもないと。

原因がわかったので次は修正です。

## 4回目：日付を変換して比較する
さっそくIDと日付の複合キーで絞り込みたいところですが、まずは日付だけで修正できるか見てみます。

```JavaScript:コード.gs
/**
 * 検証１データを読み込むメソッド
 * @param {String} date - 日付
 * @param {String} id - ユーザーID
 */
function getReadTest01(date, id){
  // 最終行の取得
  let lastRow = TEST01.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return;
  // データの取得
  let datas = TEST01.getRange(2,1,lastRow-1, 3).getValues();
  // データの検索
  let data = datas.filter((value) =>{
    return Utilities.formatDate(value[0], 'JST', 'yyyy/MM/dd') == date
  })
  return data;
}
```

スプレッドシート上の日付をパラメータとして渡している日付の形式に合わせて文字列フォーマット変換してから比較。
そして結果がこちら。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/60137a74-02d5-ff4d-bfd0-d781683b365b.png" width="80%">

どうやら想定通りに動いているようです。

では最後、IDと日付の2項目で絞り込んでみましょう。

## 5回目：日付とIDで絞り込み
最初に失敗した内容から、日付のフォーマット変換部分を修正して再度実行。
これであかんかったら「やっぱり複数項目での絞り込みが……？」と思うことにします。

```JavaScript:コード.gs
/**
 * 検証１データを読み込むメソッド
 * @param {String} date - 日付
 * @param {String} id - ユーザーID
 */
function getReadTest01(date, id){
  // 最終行の取得
  let lastRow = TEST01.getLastRow();
  // getRangeでは0を指定することができないのでデータが存在しないことになる
  if(lastRow <= 1) return;
  // データの取得
  let datas = TEST01.getRange(2,1,lastRow-1, 3).getValues();
  // データの検索
  let data = datas.filter((value) =>{
    return Utilities.formatDate(value[0], 'JST', 'yyyy/MM/dd') == date && value[1] == id
  })
  return data;
}
```

はたして結果は？
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3477580/72630599-dc2b-736c-7741-de246a7b87ca.png">

2項目を条件にしてちゃんと出力されました！

# 次はnull判定だ
めでたしめでたし、としたいところですが。
前回記事の最後に書いたようにほかにもなんかよくわからん動作してた箇所がまだあるので、次はその中でもいっそうよくわからんかったnull判定を調べてみようと思います。
ちゃんと原因がわかるといいのですが自信はあんまりない。