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

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

スプレッドシート、使ってますか？

Google スプレッドシート（英: Google Sheets）は、Googleが提供する表計算ソフトである。Google スプレッドシートは、ウェブブラウザ、Android、iOS、iPadOS、ChromeOSで利用できる。Microsoft ExcelやOpenDocumentのファイル形式も扱える[1]。
Google スプレッドシートでは、他のユーザとリアルタイムで共同作業しながら、オンラインでファイルを作成および編集できる。編集は、変更を示す改訂履歴とともにユーザーによって追跡される。編集者の位置はそれぞれ固有の色とカーソルで強調表示される。権限システムはユーザが実行できることを制限する。
大半の機能は無償で利用可能だが、一部のプレミアム機能は有償契約（Google OneとGoogle Workspace）のフリーミアムとなっている。
Googleのサービスには他に、Google ドキュメント、Google スライド、Google 図形描画、Google フォーム、Google Sites、Google Keepなどがある。

ウィキペディア 様より

インターネット上で使えるExcelみたいなやつですね。
今回はそのスプレッドシートを使って、顧客を管理するツールを作ってみます。

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

スプレッドシートを作る

とにもかくにもスプレッドシートを作ります。
スプレッドシートにただ入力していくだけなら誰でもできます。
が、セルを一つ一つ選択して登録していくのは少し大変です。
ということで、登録用のフォームを用意して、そこから登録ができるようにします。

データを保持するシートを用意する

今回は、「data」というシートを作成し、名前・住所・電話番号・備考を登録できるようにしたいと思います。
名前のふりがなや、郵便番号などを追加したい場合は適宜追加してみてください。
また、テーブルのヘッダー(見出し)は日本語でも構いません。
idやnoといった一意に定まる(識別番号みたいな)ものがあると、後々便利だったりします。

登録用のフォームを作る

フォームを作る、と言っても、スプレッドシート上のセルのサイズや色をいい感じに調整するだけです。
今回はこんな感じになりました。
使い方は、

flowchart LR 登録情報の入力 --> 登録するボタンの押下 --> データシートに追記 --> 登録完了のポップアップ表示 --> フォームの初期化 --> 登録情報の入力

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

実装する

実装してみましょう。
困った時は公式リファレンスを眺めることにします。
Class SpreadsheetApp
まずは、拡張機能メニューのApps Script を選択し、開発画面を表示します。
必要になりそうな処理をコメントとしてメモします。
今回は全て関数として実装していきます。


ということで、メモを上から順に実装していきます。
また、メモだけでは不足している部分があったら追って実装します。

シートから登録するデータを取得する

まずは登録シートからデータを取得できるようにします。
スプレッドシートからデータを取得するには、

flowchart LR 1.SpreadsheetApp --> 2.Spreadsheet --> 3.Sheet --> 4.Range --> 5.データの取得

の順で取り出します。

// 登録シートからデータを取得する
function getRegisterData() {
  const SHEET_NAME = '登録シート';
  // #1.SpreadsheetApp を使って、現在アクティブ状態の Spreadsheet を取得する
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #2.Spreadsheet から Sheet を取得する
  const registerSheet = ss.getSheetByName(SHEET_NAME);
  // #3-4.Sheet から、Range を取得し、Range 内のデータを取得する
  const sheetData = registerSheet.getRange(3, 2, 1, 4).getValues()[0];
  // console.log(sheetData);
  return sheetData;
}

注意点として、シートからデータを取得する方法はいくつかあり、今回は Range オブジェクトを利用しています。
Range オブジェクトには、getValue()、とgetValues()の二種類あり、getValue()だと、Range内の左上の値が返されます。
getValues()を利用すると、Range内の値を二次元配列で返されます。
今回だと、登録シートの3行目から1行、2列目から4列(B3:E3)をの値を二次元配列として取得後、二次元配列の0番目を返しています。
そうすることで、[名前, 住所, 電話番号, 備考]という一次元配列として扱うことができるようになります。

使用する関数getValue()getValues()
返ってくる値Range内の左上の値Range内の値を二次元配列

登録シートのデータをdataシートに追加する

登録シートから取得したデータをdataシートに追記します。
また、追記する際にデータにidを追加します。
idは最終行 +1 の id を振っていきます。

上記の場合、次に追記する際は最終行 +1、つまり2を id とします。
ということでメモになかった最終行を取得する関数も追加します。

flowchart LR 1.SpreadsheetApp --> 2.Spreadsheet --> 3.最終行を取得 
// 最終行のidを取得する
function getLastId() {
  const SHEET_NAME = 'data';
  // #1.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #2.
  const dataSheet = ss.getSheetByName(SHEET_NAME);
  // #3.データシートの最終行、1列目を取得して返す
  return dataSheet.getRange(dataSheet.getLastRow(), 1).getValue();
}

シートから登録するデータを取得すると同様で、アクティブなスプレッドシートから「data」というシートを取得して、最終行の1列目を返しています。
今回は一つの値だけで良いので、getValue()を利用しています。

flowchart LR 1.SpreadsheetApp --> 2.Spreadsheet --> 3.最終行を取得 --> 4.最終行を設定 --> 5.データシートに追記 
// 登録シートのデータをdataシートに追加する
function addcCustomerData(data) {
  const SHEET_NAME = 'data';
  // #1
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #2
  const dataSheet = ss.getSheetByName(SHEET_NAME);
  // #3-4.最終行のid + 1 のidを設定する
  data.unshift(getLastId() + 1);
  // #5. データシートの最終行に追記
  dataSheet.appendRow(data);
}

#1-2はデータを取得するのと同様に、開いているスプレッドシートからdataというシートを取得しています。
#3-4では先ほど定義した最終行を取得する関数を呼び出し、その値に +1 したものを配列の先頭に挿入しています。
#5でデータシートの最終行に追記しています。

登録完了を通知する

「登録する」というボタンを押下した際、登録をしている人が分かりやすいように、登録が完了したらポップアップを表示するようにします。
今回は「登録しました！」と表示してみます。

// 登録したことを伝える
function notify() {
  SpreadsheetApp.getUi().alert("登録しました！");
}

SpreadsheetApp オブジェクトのから Ui オブジェクトを取得し、alert()を呼び出しています。
いろいろありますが、今回はOKボタンがあれば良いので alert() にしました。
一つ注意点があり、

Class Ui

Google アプリのユーザー インターフェース環境のインスタンスで、スクリプトでメニュー、ダイアログ、サイドバーなどの機能を追加できます。スクリプトは、開いているエディタの現在のインスタンスの UI のみを操作できます。また、スクリプトがエディタにコンテナ結合されている場合に限られます。

Google Workspace 様より

にあるように、スプレッドシート上で Apps Script を作成した場合のみ使用できます。
＊動画のようにスプレッドシート→拡張機能→Apps Script と選択していれば使えます。＊

登録シートのデータを空白にする

使う人が便利なよう、登録が完了したら登録シートのデータを削除しておきます。

flowchart LR 1.SpreadsheetApp --> 2.Spreadsheet --> 3.Sheet --> 4.Range --> 5.データの削除 
// 登録シートのデータを空白にする
function clearRegisterData() {
  const SHEET_NAME = '登録シート';
  // #1-2.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #3.
  const registerSheet = ss.getSheetByName(SHEET_NAME);
  // #4-5 登録するデータのRangeの内容をクリアする
  registerSheet.getRange(3, 2, 1, 4).clearContent();
}

Range オブジェクトの clearContent() を利用できます。
clear() を利用すると、書式と内容を削除することができます。

登録ボタンを押した時に登録するようにする

登録ボタンを押した時に登録するようにします。
ここまで作ってきた関数をまとめて呼び出すだけですね。

// 登録ボタンを押したときに登録するようにする
function onClickAddCustomerRegisterButton() {
  // #1.登録シートから登録するデータを取得
  const data = getRegisterData();
  // console.log(data);
  // #2.データシートに追記する
  addcCustomerData(data);
  // 3.登録完了通知
  notify();
  // 4.登録データのデータを空白にする
  clearRegisterData();
}

呼び出したい関数の名前を予めコピーしておきます。
今回は[onClickAddCustomerRegisterButton]を呼び出します。
スプレッドシート上で登録ボタン(図形)を用意して、ボタンを選択、クリックしてメニューを開き、[スクリプトを割り当て]を選択します。
先ほどコピーした関数名[onClickAddCustomerRegisterButton]を貼り付けて確定ボタンを押下します。

最後に

いかがだったでしょうか。
どんな人でも登録のしやすい環境が出来上がりました。
色々と不備、不便な点はありますが、登録自体はできるはずです。
次回はデータシートから特定のデータを検索・描画する処理を作成したいと思います。
完成したらXにて報告、この記事にもリンクを貼っておきます。
良かったらフォローしてあげてください。

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

// 登録シートからデータを取得する
function getRegisterData() {
  const SHEET_NAME = '登録シート';
  // #1.SpreadsheetApp を使って、現在アクティブ状態の Spreadsheet を取得する
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #2.Spreadsheet から Sheet を取得する
  const registerSheet = ss.getSheetByName(SHEET_NAME);
  // #3-4.Sheet から、Range を取得し、Range 内のデータを取得する
  const sheetData = registerSheet.getRange(3, 2, 1, 4).getValues()[0];
  // console.log(sheetData);
  return sheetData;
}
// 最終行のidを取得する
function getLastId() {
  const SHEET_NAME = 'data';
  // #1.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #2.
  const dataSheet = ss.getSheetByName(SHEET_NAME);
  // #3.データシートの最終行、1列目を取得して返す
  return dataSheet.getRange(dataSheet.getLastRow(), 1).getValue();
}
// 登録シートのデータをdataシートに追加する
function addcCustomerData(data) {
  const SHEET_NAME = 'data';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName(SHEET_NAME);
  // 最終行のid + 1 のidを設定する
  data.unshift(getLastId() + 1);
  dataSheet.appendRow(data);
}
// 登録できるかテストする
function addDataTest() {
  addcCustomerData([ '足利義満', 'ああいいとにかく長い住所の場合はどうなるんだろうね', 1023, 'びこう' ]);
}
// 登録したことを伝える
function notify() {
  SpreadsheetApp.getUi().alert("登録しました！");
}
// 登録シートのデータを空白にする
function clearRegisterData() {
  const SHEET_NAME = '登録シート';
  // #1-2.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // #3.
  const registerSheet = ss.getSheetByName(SHEET_NAME);
  // #4-5
  registerSheet.getRange(3, 2, 1, 4).clearContent();
}
// 登録ボタンを押したときに登録するようにする
function onClickAddCustomerRegisterButton() {
  // #1.登録シートから登録するデータを取得
  const data = getRegisterData();
  // console.log(data);
  // #2.データシートに追記する
  addcCustomerData(data);
  // 3.登録完了通知
  notify();
  // 4.登録データのデータを空白にする
  clearRegisterData();
}
スポンサーリンク