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

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

0．前回の振り返り

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

1．一覧画面と詳細画面のHTMLを用意する

まだまだ改善の余地はあるものの、ちょっとレイアウトも整えまして...

詳細画面も用意しました！

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

2．スプレッドシートをご用意する

前回作成した猫の情報に加えて、日々更新がかかる情報を管理するためのスプレッドシートも作成しました。
今回はこれの検索も含めて行っていきます

3．スプレッドシートを検索して１行すべてを取得できるようにする

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

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関数利用が桁違いに爆速だった
こちらの先達の方の情報によると、ループ検索でもそんなに遅くないものの、Query関数のほうが良いらしいので書き換えます！
queryを別シートの数式で実行し、その結果を取得する形になります。

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を利用して、特定のシートの特定の値を取得できるようにします。
ここでは最新の値を取得する形にします。
表示１項目に対して、検索が１回発生する感じになります。

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;
}

今日はここまで。