自動車屋向けAppSheetで請求管理
友人から鹿児島の貴重な食材をいただいたお礼として、自動車屋さん向けの見積もり・請求管理を行うAppSheetアプリを作成し提供することになりました。技術的にはこれまでこのサイトで構築してきたものを組み合わせているものになりますが、一番のポイントがこのアプリは2日間で構築されたという事実。
ということで、アプリの仕組みと使い方手順として公開しようと思います。
今回利用するファイル等
- 車屋請求管理 - AppSheet
- 請求書雛形 - Googleドキュメント
- 見積書雛形 - Googleドキュメント
- 緯度経度変換スクリプト - Google Apps Script
- 車屋請求管理用シート - Googleスプレッドシート
今回は顧客住所を緯度経度変換して登録する部分以外は、Google Apps Scriptを使わずに実装しています。技術的な課題や解消法についてはこのエントリーではなく、以下のエントリーに追記しています。特にPDF生成周りは少々苦労しました。
個人で利用する目的のアプリであるため、複雑なフローは一切利用せずですが、使えるようにするまでの手順は結構あるので、１つずつ丁寧に追っていきましょう。基本的な機能としては以下のようなものになります。
- 顧客情報を管理する機能
- 扱ってる商材やサービスの一覧を管理する機能（ジャンルとそれにぶら下がる商品で１つのペアになります）
- 上記2つを元に見積書・請求書をドキュメントを雛形にPDFを生成する機能
- 生成済みPDFファイルを顧客メアド宛に送信する機能
- 顧客住所を元にYahooカーナビ連携してルート案内を行う機能
車屋さんなので商材だけじゃなく車検や板金、チューニングや積車で車を工場まで輸送など様々なお仕事があるのでそれらをカバーしつつ、PCではなくスマフォで仕事が完結出来るようにという目的でAppSheetで構築しています。
事前準備
AppSheetのテンプレートをコピーしてからGASのスクリプトの再割り当て、使用する雛形の再割り当てまで含めて一連の使えるようにするまでの事前準備をまとめています。
GASのコード割当て
スタンドアローンのGASの準備
AppSheetテンプレートをコピーした後はGASへの参照が切れています。故に自身でスタンドアローンコンテナのGASファイルを作成して繋げてあげる必要があります。ここではそのGASのファイルの作成手順です。
- Google Driveを開いて、左上の新規をクリック
- その他 => Google Apps Scriptをクリック
- スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
- ドライブにはスタンドアローンファイルが生成される
- スタンドアローンスクリプトをダブルクリックしてスクリプトエディタを開く
- 左上のプロジェクト名がファイル名になるので、無題のプロジェクトではなくきちんと名前を付けましょう。（今回は緯度経度変換スクリプトという名前にしています）。
- 今回のサンプルに掲載されてるコードをコピペして保存する
見つからない場合には、Chrome Webstoreから拡張機能インストールしましょう（Google Apps Scriptで検索すると出てくる）。
図：新規から作成することが出来る
参照先スプシのIDの変更
AppSheetのテンプレートをコピーするとスプレッドシートも一緒に複製されてコピーされます。そのスプシのIDをGASの中に記述する必要があります。以下の手順でコピーした直後のスプシを開き、IDをGAS内に記述して上書きしましょう。
- AppSheetの左サイドバーからData→適当なテーブル→View data sourceをクリックする
- 参照先スプシが開かれるので、URLの中からID部分をコピーする
https://docs.google.com/spreadsheets/d/ここがスプシのID/edit?gid=0#gid=0
- 前述で作ったGASのコンテナを開く
- 上部にある変数ssidに対してスプシのIDを記述して上書き保存
//スプレッドシートID var ssid = "ここに書き込み先スプシのIDを記述する";
- 一度適当にnavgenerate関数を実行して認証を適当に実行しておきます。
これでGASのコンテナファイルの準備が完了しました。
GASコードの参照を修正する
AppSheet側に入り、Automationに於いてGASを参照してる場所を修正します。
- AppSheetのアプリ開発画面に入る
- 左サイドバーよりAutomationをクリックし、顧客管理の中にある緯度経度変換を開く
- New Event4の次にあるNew Stepをクリックする
- Apps Script Projectの中をクリックし、前述で作成したスタンドアローンコンテナファイルを指定する
- Authorizeというボタンをクリックする
- 認証を実行して許可を与える
- Function Nameや与える引数については変更は無いのでそのまま利用する
- 右上のSAVEボタンを押して保存する
今回GASを利用してるAutomationはここの１箇所のみなのでこれで完了になります。
図：GASの参照を再設定する
PDF用テンプレートの割当て
見積書および請求書のPDF生成は、Googleドキュメントの雛形を利用して生成しています。AppSheetのテンプレートをコピーした時点でこれらを再度指定する必要があります。何よりも自分が使いたい雛形というものがあるでしょうから、それらに差し替えるという意味でもこの作業は行っておきましょう。
ただし、テンプレートの中の「<<税込金額>>」といった文字列はAppSheet側からデータを差し込む時に使う文字列なので削除しないように注意が必要です。
- AppSheetのアプリ開発画面に入る
- 左サイドバーよりAutomationをクリックし、請求書作成実行もしくは見積書作成実行をクリックする
- 真ん中のProcessにあるNew Stepをクリックする
- Create a new fileとなっており、「Template」の部分をDocId=の横のファイルアイコンをクリックする
- Google Drive Filesをクリックする
- 自分が用意した請求書もしくは見積書の雛形ファイルを指定する
- 右上のSAVEをクリックして保存する
- もう一方のほうも同様に雛形ファイルを指定し直して保存する
図：雛形ファイルを指定し直す
技術的なポイント
リレーションシップ
5つのシートそれぞれの間をリレーションシップを貼って連携させています。
- 見積もり⇔見積もりサブの間でリレーション
- 見積もり⇔顧客管理の間でリレーション
- 商材ジャンル⇔商材管理の間でリレーション
- 商材ジャンル⇔見積もりサブの間でリレーション
それぞれ本体と明細であったり、プルダウンでリスト出力のためにリレーションを張っています。また一部は本体側に無い顧客名を顧客管理側からデータを呼び出すといったことにも利用しています。
後述の商材ジャンル→商材管理のデータを二重プルダウンで呼び出す必要もあるため、リレーションシップが本アプリの最も重要な部分となっています。こうすることでサブテーブルに親のIDが自動で挿入されて関連付けしてくれるので、手作業でデータ整備する際のミスや手間がなくなります。
また副次的なものとして過去の商材がどれだけのお客に売り上げたか？といった集計にも役立つのでリレーションはマスターしておきたいテクニックです。
図：リレーションは非常に便利
仮想列で合計計算
PDFで明細の合計金額、消費税額、税込み金額を計算して表示していますがこれは、見積もりテーブル上に「Virtual Column」でそれぞれ用意してその中で計算させています。よってスプシ上には出てこない項目です。
リレーションで関連する見積もりサブの内容について、それぞれ以下のような数式を入れて計算させています。
//合計金額計算 SUM([Related 見積もりサブs][金額]) //消費税額計算 SUM([Related 見積もりサブs][金額]) * 1.1 - SUM([Related 見積もりサブs][金額]) //税込合計金額 [合計金額]+[消費税額]
税込合計金額は前述の合計金額と消費税額の仮想列の値を元に更に合計して計算させています。Virtual Columnを明細側じゃなく親側のテーブルで活用することで計算列を簡単に用意し、それをPDF上で表示ができるため集計や税額計算が簡単に自動で実現することが可能です。
同様に見積もりサブ側でもそれぞれの商品単価 * 数量で計算させた小計で同様の計算をさせています。
図：見積もり本体側で計算させる必要性がある
Automation用の作業列
見積もりテーブル上で殆どの作業をしてる為、見積PDF生成や見積メール送信などのボタンをクリックした時に、Automationを発火させています。しかし、どの作業で書き込みしたのか？の区別が付かないとAutomation側で発火出来ないので、
- PDF作成やメール送信それぞれ用の作業列を用意する
- 作業列に対して各ActionがUNIQUEID関数で値を書き込む
- 書き込まれた列の値を見て発火すべきAutomationが作動する
- Automation側のeventのConditionでどの列が書き込まれたか？を判定させています。判定式の例は以下の通り
[_THISROW_BEFORE].[見積更新フラグ] <> [_THISROW_AFTER].[見積更新フラグ]
- PDF生成やメール送信が適切に行われる。
といった仕掛けを使っています。Actionボタンをクリックして直接Automationを呼び出せないのでこのような値を更新させて間接的に発火させる仕組みはAppSheetの常套手段になりつつあります。
図：Actionで値を作業列に書き込み
図：Conditionで値書き込み列を判定させる
二重プルダウン
見積もり明細側の商材ジャンルを選ぶと商品名のプルダウンはフィルタされた内容が表示される二重プルダウンの仕掛けを採用しています。なので商材ジャンルでオイルを選ぶとその属性を持った商材一覧の中身だけが商品名プルダウンに出るようになってるので、膨大な商品から簡単に目的のものをピックアップ出来ます。
これは
- 見積もりサブの商材ID列のValid Ifに以下のような判定用数式を入れてある
select(商材管理[ID],[JunleID]=[_thisrow].[商材ジャンル])
- 商材ジャンルはLabelはJunleIDじゃなくジャンル名にラベルのチェックを入れてある
- 商材管理はLabelはIDじゃなく商材名にラベルのチェックを入れてある
- よって各プルダウンはラベルにチェックを入れた項目が表示され、選択されたらスプシには各IDが入るようになっている。
といった仕掛けを装備することで二重プルダウンを実現しています。AppSheet単体の機能ではたくさんのリストから選ぶには検索しか無いのでこのような手法を使うと、検索せずに簡単に目的の商品を選ぶことが可能になります。
図：見積もりサブの商材ID列のValid ifに数式を入れてある
ソースコード
顧客管理にデータが書き込まれると住所を元に緯度経度およびYahooカーナビ用のURL生成をスプシに追記するGASが発動するようになっています。その時、住所をGASのGeocoderを使ってジオコーディングしlat lonにそれぞれ値を格納しています。
//Yahooカーナビ用URL Scheme
var yahoonavi = "yjcarnavi://navi/select?";
//スプレッドシートID
var ssid = "ここに書き込み先スプシのID";
//住所から緯度経度情報を生成する
function navgenerate(id,address,kokyaku) {
//スプレッドシートを指定する
let ss = SpreadsheetApp.openById(ssid);
//addressから緯度経度情報を取得する
let res = Maps.newGeocoder().setLanguage('ja').geocode(address);
let result = res.results[0];
//結果から緯度経度を取得する
let lat = result.geometry.location.lat;
let lon = result.geometry.location.lng;
//yahooカーナビ用URL生成
let yahoo = yahoonavi + "lat=" + lat + "&lon=" + lon + "&name=" + encodeURIComponent(kokyaku)
//スプシをflushしておく
SpreadsheetApp.flush();
//スプシデータを取得する
let driveman = ss.getSheetByName("顧客管理");
let sheet = driveman.getRange("A2:K").getValues();
//引数idと一致する行を特定する
for(let i = 0;i<sheet.length;i++){
//レコードを一個取り出す
let record = sheet[i];
//レコードIDを比較する
if(id == record[0]){
//書き込み対象アドレスの作成
let rec = i + 2;
let target0 = "H" + rec;
let target1 = "I" + rec;
let target2 = "J" + rec;
//各種情報を書き込みする
driveman.getRange(target0).setValue(lat);
driveman.getRange(target1).setValue(lon);
driveman.getRange(target2).setValue(yahoo);
break;
}
}
return true;
}
アプリの使い方
アプリで利用するデータ類は基本、スプレッドシートを開いてスプシ上でメンテナンスする必要はありません。アプリ上から管理が出来るようになっています。まずは以下の順番で顧客データや扱う商材データを整備し、日々は見積もりからデータを作成して運用していく事になります。
以下はスマホ上での作業を元に説明を行っています。
顧客管理
顧客情報登録
アプリの顧客管理をクリックして、お客様情報を予め追加が必要です。見積もり側からも「New」にて顧客管理側に飛んで作成することも出来るようになっています。
- 顧客管理をクリックする
- 右下の＋をクリックする
- 顧客名、メアド、車名、車の型式、住所、電話番号、備考欄を埋める
- SAVEをクリックする
- 自動的に住所を元にGASが発動して緯度経度情報、Yahooカーナビ用URLが生成されてスプシに追記されます。
この緯度経度情報はYahooカーナビアプリに送り込んで、お客様の住所までルート案内をする際に利用されます。新規追加時と更新時に作動するようになっています。
図：同姓同名に注意が必要です
図：緯度経度はGASで算出します
Yahooカーナビ連携
前述で登録した顧客情報を再度開いてみると、情報編集ボタン以外にYahooナビボタンが出てきます。これをタップした場合、スマフォにYahooカーナビが入ってる場合には緯度経度情報を元に連携されて、お客様の住所までの経路検索を一気に行いカーナビ一歩手前まで行ってくれます。
積車などで故障車引取や修理した車を届けるといったような場合に手軽にナビまで持っていけるので手間が省けます。
図：ナビ呼び出しボタンをクリック
図：一発で経路検索まで行ってくれます
商材
商材ジャンル
商材ジャンルとはここの商材やサービスのカテゴリを指します。商品マスタ1本にしてしまうと、見積作成時に膨大な一覧から探すことになってしまいます。
そこで以前作ったジャンルのプルダウンを選ぶと、そのジャンルの値を持ってるアイテムが次のプルダウンで絞り込みされて表示されるようにしているテクニックを使ってる為（二重プルダウン機能）、まずはジャンルをある程度作り込んでおく必要があります。
デフォルトデータとしていくつか登録済みですが、使用者の環境に合わせてジャンルを作っておくと良いでしょう。
図：まずは商品ジャンルを固めておく
商材管理
次に前述で作ったジャンルにぶら下がる個別の商品データを商材管理で登録していきます。これらは見積もり画面からもNewで新規追加が随時出来るようになっていますが、予めある程度作り込んでおくと見積もり時はプルダウンで選ぶだけでホイホイと見積書が作成できるようになります。
- 商材管理をクリックする
- 右下の＋ボタンをクリックする
- 前述で作っておいた商材ジャンルを選択する
- 商材名は商品名です。ただプルダウンには他の情報が出てこないのでなるべくメーカー名や商品の型番も含めておくと選ぶ時にどこのメーカーだったっけ？みたいなことがなくなります。
- メーカーは商品のメーカー名ですが、自社のサービス（車検など）の場合は空っぽでオッケーです。
- 画像は商材の画像がある場合はその画像をカメラで取ったり、ウェブから拾った画像を割り当てられます。
- 最後にSAVEをクリックしてレコード登録完了です。
図：登録した商材の一覧が出てきます。
図：商材登録画面の様子
見積もり
見積もり追加
AppSheetの使用上、見積もり作成時に同時に明細追加が出来ないので、まずは見積もりの大元のデータを作成します。
- 見積もりを開いて右下の＋ボタンをクリックします。
- 日付、顧客名の選択、見積もりの概要（これが見積書のタイトルになります）、備考欄はオーダーの細かな注意点などを記述しておきます（これが見積書の備考として記載されてきます）。
- SAVEをクリックする
引き続き、見積もり明細を追加する必要があります。
図：見積もりの基礎をまず作る
見積もり明細追加
前述で見積もりの基礎を作ったら一覧に出てくるので、タップして明細のデータを追加していきます。
- 見積もりを開いたら一番下にある見積もり明細のAddボタンをクリックする
- 商材ジャンルをクリックして選ぶ
- 商品名をクリックすると商材ジャンルでフィルタした内容が出てくるので選ぶ
- 単価を入れる
- 数量を入れる
- SAVEをクリックするとこれで1つ商材が明細に入る
- 続けて他にも注文がある場合は同様の手順で明細を追加登録していく
図：明細追加画面
図：明細が複数追加された様子
各種PDF作成
次に入力内容に基づいてPDFを生成します。PDFはスプシのある場所に自動で生成されるFilesというフォルダ内に生成され、生成された場所のデータがスプシに追記され、ユーザはアイコンをタップするだけでいつでも作成されたPDFが自動で開かれるようになります。
PDFを作成しないとメールで送信できませんのでまずは見積書作成ボタンをクリックして生成しましょう。
- 見積もりデータを開く
- 上部にある「見積書作成ボタン」をクリックする
- 暫く待つとした方に、ファイル名とアイコンが表示されてPDFが後ろで自動生成されています。
- アイコンをタップすると見積書PDFが開かれます。
同様に作業完了後は「請求書作成」のボタンをクリックすると同じ仕組みで、請求書のPDFが生成されて開けるようになります。
図：見積書作成ボタンをタップする
図：下の方にPDFが生成されてボタンが出てくる
図：生成された見積書PDFを開いてみた
各種PDF送信
見積書および請求書のPDFを生成した後でそれぞれのPDFファイルをお客様のメアド宛にGmailでボタン一発でメール送信することが可能です。PDFは自動で添付されています。
- 見積もりデータを開く
- 上部にある「見積メール送信」をクリックする
- 問い合わせ画面が出るのでメール送信をクリックする
- 顧客情報の対象のメアドを利用してメールが自動作成されPDFが見積書PDFが添付されて自動送信されます。
- 請求書の場合も同様の手順で送ることが可能です。
なお、このメールの文言はAutomation→見積PDF送信→Processの中のNew Stepを開き、Email Bodyの中に記述されています。<<[顧客名称]>>で顧客名称列の値を差し込みが可能です。また、そのままだとFromがnoreply@appsheet.comとなってしまい、お客様が返信出来ないので、other email parametersの中のReply toにUSEREMAIL関数で自分のメアドを返信先として自動セットしています。
請求書についても請求PDF送信のAutomationの中に同様にセットされています。
図：メール送信ボタン
図：メール本文の設定箇所
図：送信されてきたメールの様子
