スプレッドシートで顧客管理を作る#2

スプレッドシートで顧客管理を作る

前回、スプレッドシートで顧客管理を作る上で、データを登録するフォームと登録処理を作りました。
今回は、データの検索と検索したデータを描画する処理を作りたいと思います。

今回の目標

検索ボタンを押して検索結果を描画。
矢印(前、次)で検索結果の描画切り替えができるようにします。
*見切れていますが、画面上部にスクリプト実行中の表記が出ています。

上記動画のようなところまで作成します。

検索用のボタンを用意する

今回はお客様カードに入力されている内容を検索できるようにしたいと思います。
というわけで、検索ボタンと検索結果、現在表示している番号、描画切り替えボタンを用意しました。

セル内容
A3表示中の id
A4検索結果の要素番号配列
B4表示中の要素番号

また、上記表の内容が、白文字で入っています。

使い方は、

flowchart LR 検索情報の入力 --> 検索ボタンの押下 --> データシートを検索 --> 検索結果の描画 --> 検索結果の表示切り替え --> 検索結果の描画 -->検索情報の入力

といった形になりそうです。

実装する

実装してみましょう。
前回、スプレッドシートにアクセスするところの解説を入れていましたが、今回からは省いていきます。

データシートからデータをすべて取得する

Sheet オブジェクトの getDataRange() が使えます。
Sheet. オブジェクトの getLastColumn()getLastRow() を利用しても同じようなことができます。

// データシートからデータをすべて取得する
function getCustomerData() {
  const SHEET_NAME = 'data';
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  return data;
}

シート上の最終行と最終列を取得し、データ左上からデータの右下範囲内のデータを取得することでシート上のデータをすべて取得することができます。
言葉にするとわかりにくいですが、

シートをイメージすると分かりやすいです。
データの右下は言い換えると、データの最終行と最終列ということになります。
Range オブジェクトの getValues() を利用して、範囲内のデータを二次元配列として取得しています。

検索結果の行数を登録シートに保持する

まず検索する方法を考えます。
今回は、
[名前・電話番号・住所・備考]を一つの文字列として扱い、その中に検索文字列が含まれているか。
で検索したいと思います。
検索キーワードは登録フォーム内のテキストを利用したいと思います。

// 行毎に検索して見つかった行をセルに入れておく
function setFilterDataRows() {
  // #1.検索キーワード群
  const keywords = getRegisterData();
  // #2.シートデータを行毎にまとめる
  const colData = (() => {
    // #3.登録済みデータ群
    const sheetData = getCustomerData();
    const data = [];
    sheetData.forEach(row => {
      data.push(row.join( ));
    });
    return data;
  })();
  // 検索結果
  const foundData = [];
  // #4.検索処理
  for(let row = 1; row < colData.length; row ++) {
    for(let keycnt = 0; keycnt < keywords.length; keycnt ++) {
      if(keywords[keycnt] === '') continue;
      if(colData[row].indexOf(keywords[keycnt]) === -1) continue;
      foundData.push((row + 1));
      break;
    }
  }
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // #5.検索結果をセルに登録
  if(foundData.length === 1) sheet.getRange(4, 1).setValue(foundData[0] + ',');
  else sheet.getRange(4, 1).setValue(foundData.join(','));
}

検索キーワードの取得は前回作成した、登録シートからデータを取得する getRegisterData() が利用できます。

#2.ではシートデータを行毎にまとめています。

0足利義光000-1252-5544下野国足利郡足利庄お名前、足利義満じゃない・・・?

という配列を、

0 足利義光 000-1252-5544 下野国足利郡足利庄 お名前、足利義満じゃない・・・?

文字列にしています。
列と列の間には半角スペースを入れています。
あとは、この文字列にキーワードが含まれているかチェックすることで検索ができます。

無名関数を利用していますが、setFilterDataRows() の外に定義しても良いと思います。

#4.文字列に変換した顧客データを配列で走査し、文字列中にキーワードが含まれているかをチェックします。
キーワードが含まれている場合は、見つけた行数を配列に格納しています。

#5.配列に格納した行数を , 区切り文字としてセルに格納します。
Array オブジェクトの join() は配列の要素を引数に渡した文字列で結合したものを返します。
今回だと、検索結果の行数が配列に入っているので、['2, 4, 5']のようなものをセルに格納しています。
要素が一つしかない場合、['2']のように区切り文字を無視したものが返ってきます。
['2,']とセルに格納しておいたほうがのちのち便利なので、要素が一ついかない場合は、あえて ','を足したものを格納しています。

現在描画中インデックスを描画する

検索結果の件数と現在描画中のインデックスを描画します。
また、「インデックス」と表記している場合は、スプレッドシート上に表示しているものを指します。
「要素番号」と表記している場合は、配列の要素番号を指しています。
分かりにくい表現となりますが、整理しながら読み進めてください。

上記画像の矢印の上の部分を実装します。

// 現在描画中のインデックスを描画する
function drawingNumber(next) {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // #1.検索結果の要素番号を取得する 末尾の空白を削除する
  const foundIndex = sheet.getRange(4, 1).getValue().split(',').filter(e =>  e !== '');
  // next は要素番号なので、わかりやすいように +1 しておく
  const index = next * 1 + 1;
  // #2.検索結果の件数と現在表示中のインデックスを描画する
  const drawData = [['検索結果:' + foundIndex.length + '件', '表示中:' + index + ' / ' + foundIndex.length]];
  sheet.getRange(5, 3, 1, 2).setValues(drawData);
}

#1.検索結果の要素番号を取得する 末尾の空白を削除する
String オブジェクトと Array オブジェクトの関数の使い方を気を付けるくらいかと思います。
split()は引数に渡した区切り文字で文字列を分割し、配列として返す関数です。
今回は , で分割したものを配列として返しています。
さらに、split() 返ってきた配列に Array オブジェクトの filter() を利用して、末尾の空白を削除したものを検索結果のインデックスにしています。

#2.検索結果の件数と現在表示中のインデックスを描画する
現在表示中のインデックスを表示する際、配列の要素番号を描画しても良いのですが、
表示中:0 / 3
のように、プログラマー以外には少し分かりにくい表記になってしまいます。
そこで、表示中の要素番号に + 1 したものを描画しています

検索結果の描画

検索結果の描画をします。

// 検索結果の描画 描画中の要素番号も描画しておく
function drawFoundData(index) {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const drawData = getCustomerData();
  const foundIndex = sheet.getRange(4, 1).getValue().split(',').filter(e =>  e !== '');
  // 顧客データがなければ false を返す
  if(drawData.length === 0) return false;
  // 検索結果がなければ false を返す
  if(foundIndex.length === 0) return false;
  // #1.検索結果よりも描画予定の要素番号が大きければ0番目を描画する
  if(foundIndex.length <= index) index = 0;
  // 描画予定の要素番号が 0 よりも小さい場合は検索結果の最後の要素を描画する
  if(index < 0) index = foundIndex.length - 1;
  // #2.描画中の要素番号をセルに入れておく
  sheet.getRange(4, 2).setValue(index);
  // #3.現在描画中のインデックスを描画する
  drawingNumber(index);
  // #4.描画用にインデックス(要素番号)を +1 したので、インデックスを -1 して要素番号にする
  index = foundIndex[index] - 1;
  sheet.getRange(3, 1, 1, 5).setValues([drawData[index]]);
  return true;
}


#1.矢印ボタンで描画の切り替えを行う際、描画要素番号が検索結果の要素数を超えないようにします。
また、配列の要素番号は 0未満はないので、0未満になった場合は 検索結果の要素数 -1 することで、検索結果の最後の要素を描画することができます。
下記に例を出しておきます。
要素数4の配列において、配列の添字変数を変化させてみます。

flowchart LR 0 --+1--> 1 --+1--> 2 --+1--> 3 --=0--> 0 --要素数 - 1 --> 3

0→3にする場合は要素数(4 - 1)  とすることで、配列の最後の要素番号が取得できます。
3→0にする場合は変数を0にすることで、配列の最初の要素番号にすることができます。

#2.描画中の要素番号をセルに入れておきます。
セルに入っている要素番号を利用して、現在表示中のデータが何番目か・矢印を利用して次、前に表示するデータを決定しています。

#3.検索結果のうち、フォームに描画しているもののインデックス(表示している要素番号 + 1)を描画します。

#4.描画用のインデックス(要素番号 + 1)のものを、- 1 して要素番号に戻し、データを描画します。

検索結果の初期化

検索結果が見つからなかったら使用者が見えない値を初期化しておくようにします。
検索結果と描画中の検索結果も初期化するようにします。

// 検索の結果の初期化
function initFindResult() {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // 表示中の id
  sheet.getRange(3, 1).setValue('');
  // 検索結果の要素番号
  sheet.getRange(4, 1).setValue('');
  // 表示中の要素番号
  sheet.getRange(4, 2).setValue('');
  const drawData = [['検索結果件数', '描画中の検索結果']];
  // 検索件数と現在表示中の検索結果のインデックス(要素番号 + 1)
  sheet.getRange(5, 3, 1, 2).setValues(drawData);
}

検索ボタンを押下した時に1件目を描画するようにする

検索ボタンを押下した時に検索を実行するようにします。
また、検索完了後に検索結果が見つからなかったらポップアップを描画し、見つかったら1件目をフォームに描画するようにします。

// 検索ボタンを押下した時に1件目を描画するようにする 見つからなかったらポップアップで通知する
function onClickFindButton() {
  // 検索の実行
  setFilterDataRows();
  // 検索結果が見つからなかったらポップアップを表示
  if(!drawFoundData(0)) {
    SpreadsheetApp.getUi().alert("データが見つかりませんでした!");
    return;
  }
  // 検索結果をフォームに描画
  drawingNumber(0);
}

特に難しいことはないですね。
作った関数を呼び出して、前回も使った、Ui オブジェクトを利用してポップアップを描画しています。
スプレッドシートを開いて、登録フォームのシートのボタンに「スクリプトを割り当て」をして完了です。

検索結果の表示切替ボタン

前・次の矢印ボタンを押下した時に、現在表示中の前・次のデータをフォームに描画します。
ほぼ同じなので両方とも書きます。

// 検索結果の表示切り替え 次へボタン
function onClickNextFoundData() {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // 現在描画中の要素番号をセルから取得
  const drawingIndex = sheet.getRange(4, 2).getValue() * 1;
  // 要素番号が見つからなかったら何もしない
  if(drawingIndex === '') return;
  // 現在表示中の要素番号の次の要素をフォームに描画する
  drawFoundData(drawingIndex + 1);
}
// 検索結果の表示切り替え 前へボタン
function onClickBackFoundData() {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const drawingIndex = sheet.getRange(4, 2).getValue() * 1;
  if(drawingIndex === '') return;
  drawFoundData(drawingIndex - 1);
}

作成した関数を呼び出してるだけですね。
次ボタンは現在表示している検索結果の次の要素を表示すればよいので、表示中の要素番号をセルから取得して + 1 したものを検索結果描画関数の引数に渡しています。
前ボタンは - 1 したものを検索結果描画関数の引数に渡しています。
検索結果が見つからなければ何もしていません。
スプレッドシートを開いて、登録フォームのシートのボタンに「スクリプトを割り当て」をして完了です。

最後に

検索と検索結果の描画を作ってみました。

次回は検索結果の更新、データの削除を実装してみます。
完成したらXにて報告、この記事にもリンクを貼っておきます。
良かったらフォローしてあげてください。

最後に今回作成したスクリプトをまとめたものを載せておきます。

// データシートからデータをすべて取得する
function getCustomerData() {
  const SHEET_NAME = 'data';
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  return data;
}
// 行毎に検索して見つかった行をセルに入れておく
function setFilterDataRows() {
  // #1.検索キーワード群
  const keywords = getRegisterData();
  // #2.シートデータを行毎にまとめる
  const colData = (() => {
    // #3.登録済みデータ群
    const sheetData = getCustomerData();
    const data = [];
    sheetData.forEach(row => {
      data.push(row.join( ));
    });
    return data;
  })();
  // 検索結果
  const foundData = [];
  // #4.検索処理
  for(let row = 1; row < colData.length; row ++) {
    for(let keycnt = 0; keycnt < keywords.length; keycnt ++) {
      if(keywords[keycnt] === '') continue;
      if(colData[row].indexOf(keywords[keycnt]) === -1) continue;
      foundData.push((row + 1));
      break;
    }
  }
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // #5.検索結果をセルに登録
  if(foundData.length === 1) sheet.getRange(4, 1).setValue(foundData[0] + ',');
  else sheet.getRange(4, 1).setValue(foundData.join(','));
}
// 現在描画中のインデックスを描画する
function drawingNumber(next) {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // #1.検索結果の要素番号を取得する 末尾の空白を削除する
  const foundIndex = sheet.getRange(4, 1).getValue().split(',').filter(e =>  e !== '');
  // next は要素番号なので、わかりやすいように +1 しておく
  const index = next * 1 + 1;
  // #2.検索結果の件数と現在表示中のインデックスを描画する
  const drawData = [['検索結果:' + foundIndex.length + '件', '表示中:' + index + ' / ' + foundIndex.length]];
  sheet.getRange(5, 3, 1, 2).setValues(drawData);
}
// 検索結果の描画 描画中の要素番号も描画しておく
function drawFoundData(index) {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const drawData = getCustomerData();
  const foundIndex = sheet.getRange(4, 1).getValue().split(',').filter(e =>  e !== '');
  // 顧客データがなければ false を返す
  if(drawData.length === 0) return false;
  // 検索結果がなければ false を返す
  if(foundIndex.length === 0) return false;
  // #1.検索結果よりも描画予定の要素番号が大きければ0番目を描画する
  if(foundIndex.length <= index) index = 0;
  // 描画予定の要素番号が 0 よりも小さい場合は検索結果の最後の要素を描画する
  if(index < 0) index = foundIndex.length - 1;
  // #2.描画中の要素番号をセルに入れておく
  sheet.getRange(4, 2).setValue(index);
  // #3.現在描画中のインデックスを描画する
  drawingNumber(index);
  // #4.描画用にインデックス(要素番号)を +1 したので、インデックスを -1 して要素番号にする
  index = foundIndex[index] - 1;
  sheet.getRange(3, 1, 1, 5).setValues([drawData[index]]);
  return true;
}
// 検索の結果の初期化
function initFindResult() {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // 表示中の id
  sheet.getRange(3, 1).setValue('');
  // 検索結果の要素番号
  sheet.getRange(4, 1).setValue('');
  // 表示中の要素番号
  sheet.getRange(4, 2).setValue('');
  const drawData = [['検索結果件数', '描画中の検索結果']];
  // 検索件数と現在表示中の検索結果のインデックス(要素番号 + 1)
  sheet.getRange(5, 3, 1, 2).setValues(drawData);
}
// 検索ボタンを押下した時に1件目を描画するようにする 見つからなかったらポップアップで通知する
function onClickFindButton() {
  // 検索の実行
  setFilterDataRows();
  // 検索結果が見つからなかったらポップアップを表示
  if(!drawFoundData(0)) {
    // 検索結果の初期化
    initFindResult();
    SpreadsheetApp.getUi().alert("データが見つかりませんでした!");
    return;
  }
  // 検索結果をフォームに描画
  drawingNumber(0);
}
// 検索結果の表示切り替え 次へボタン
function onClickNextFoundData() {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  // 現在描画中の要素番号をセルから取得
  const drawingIndex = sheet.getRange(4, 2).getValue() * 1;
  // 要素番号が見つからなかったら何もしない
  if(drawingIndex === '') return;
  // 現在表示中の要素番号の次の要素をフォームに描画する
  drawFoundData(drawingIndex + 1);
}
// 検索結果の表示切り替え 前へボタン
function onClickBackFoundData() {
  const SHEET_NAME = '登録シート';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const drawingIndex = sheet.getRange(4, 2).getValue() * 1;
  if(drawingIndex === '') return;
  drawFoundData(drawingIndex - 1);
}

*このサイトを参考に何かを作成した際に起こった全ての責任を負えません*