２．スプレッドシートを検索できるようにする

# 今日の目標：指定条件でスプレッドシートから情報を取得してHTMLに反映する

## 0．前回の振り返り
https://qiita.com/LoAMiat/items/527fd59660df5a17f69c

簡単なHTMLの出力とスプレッドシートの情報からの一覧表示ができるようになりました。

## 1．一覧画面と詳細画面のHTMLを用意する
まだまだ改善の余地はあるものの、ちょっとレイアウトも整えまして...
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2782982/05fbb2a9-4277-d278-0b00-20610df3ffb7.png)

詳細画面も用意しました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2782982/8beb2713-e2db-d381-00d0-25f7b6097f86.png)

この猫の情報をスプレッドシートから検索できるようにしたい。

## 2．スプレッドシートをご用意する
前回作成した猫の情報に加えて、日々更新がかかる情報を管理するためのスプレッドシートも作成しました。
今回はこれの検索も含めて行っていきます
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2782982/8d15fd75-1d94-9d30-038a-0fbb4019eaf2.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2782982/a3bdf869-66e9-313f-129a-97707bc0c4b6.png)


## 3．スプレッドシートを検索して１行すべてを取得できるようにする
前回はスプレッドシート内全量を取得する形でしたが、今回は特定の行のみを取得できるように検索していきます。
猫IDをキーにしているので、その項目で検索できるようにします。

```javascript:CatDetailsGS.gs
/** 
 * 詳細画面表示、キーワード検索して、特定の行を返却する
 * https://qiita.com/miemo78/items/24f6c08594be59d888bc
 */
function getAllLine(sheetName, searchString) {
  var url = 'https://docs.google.com/spreadsheets/d/🐈🐈🐈🐈🐈🐈🐈🐈/edit?usp=sharing';
  const ss = SpreadsheetApp.openByUrl(url);
  const sheet = ss.getSheetByName(sheetName);
  const textObject = sheet.createTextFinder(searchString);
  const results = textObject.findAll();
  const values = sheet.getDataRange().getValues();
  const labels = values.shift();

  const records = [];
  for(const value of values) {
    const record = {};
    labels.forEach((label, index) => {
      record[label] = value[index];      
    });
    records.push(record);
  }

  return records;
}
```

## 4．Query関数を使う形に変更する
[スプレッドシート内をGASで検索するのはQUERY関数利用が桁違いに爆速だった](https://pineplanter.moo.jp/non-it-salaryman/2021/06/03/gas-query/) 
こちらの先達の方の情報によると、ループ検索でもそんなに遅くないものの、Query関数のほうが良いらしいので書き換えます！
queryを別シートの数式で実行し、その結果を取得する形になります。

```javascript:CatDetailsGS.gs
/** 
 * 詳細画面表示、キーワード検索して、特定の行を返却する
 * https://qiita.com/miemo78/items/24f6c08594be59d888bc
 */
function getAllLine(sheetName, searchString) {
  var url = 'https://docs.google.com/spreadsheets/d/🐈🐈🐈🐈🐈🐈🐈🐈/edit?usp=sharing';
  const ss = SpreadsheetApp.openByUrl(url);
  const sheet = ss.getSheetByName('Query');
  const results = sheet.getRange(1, 1).setValue('=QUERY(\'' + sheetName +'\'!A:Q,"select * where A = \'' + searchString + '\'")');
  const values = sheet.getDataRange().getValues();
  const labels = values.shift();

  const records = [];
  for(const value of values) {
    const record = {};
    labels.forEach((label, index) => {
      record[label] = value[index];      
    });
    records.push(record);
  }

  return records;
}
```

## 5．スプレッドシートを検索して単一の値を取得できるようにする
同じようにqueryを利用して、特定のシートの特定の値を取得できるようにします。
ここでは最新の値を取得する形にします。
表示１項目に対して、検索が１回発生する感じになります。

```javascript:CatDetailsGS.gs
/** 
 * 詳細画面表示
 */
function doGet(e) {
  // 猫情報検索
  const catId = 'C00001';
  template.weight = getRecentlyCell("'猫体重履歴'!A:D",catId,'D');
  template.trial = getRecentlyCell("'猫備考系履歴'!A:F",catId,'D');
  template.note = getRecentlyCell("'猫備考系履歴'!A:F",catId,'E');
  template.profile = getRecentlyCell("'猫備考系履歴'!A:F",catId,'F');
}

/** 
 * 詳細画面表示、キーワード検索して、特定の列が入力されている最新の値を返却する
 * https://qiita.com/miemo78/items/24f6c08594be59d888bc
 */
function getRecentlyCell(sheetName, searchString, getField) {
  var url = 'https://docs.google.com/spreadsheets/d/🐈🐈🐈🐈🐈🐈🐈🐈/edit?usp=sharing';
  const ss = SpreadsheetApp.openByUrl(url);
  const sheet = ss.getSheetByName('Query');
  // 日付ソートで最新の取得・指定項目がから出ないデータ1件
  const results = sheet.getRange(1, 1).setValue('=QUERY(' + sheetName +',"select ' + getField +' where A = \'' + searchString + '\' AND ' + getField + ' <> \'\' order by C DESC LIMIT 1")');
  const cellValue = sheet.getRange(2, 1).getValues();
  return cellValue;
}
```

今日はここまで。