Googleフォームで遊んでたら公共料金のレポートができちゃった

まとめ

これまでスプレッドシートで書き溜めた水道料金の入力を Google フォームにして、IT詳しくない嫁も入力できるようにしてみた。
入力できるようにするために、Google フォームの動き(課金、アクセス制御、機能など)を調べて、設計したらコードなしでレポートまでできた。(制作:30min)
Google フォームのスプレッドシートの扱いは癖があるので気を付けるべき。

完成イメージ

30分くらいで作ったフォーム画面

釣りしてるおっちゃんの絵がなごむ。好き。

フォーム画面で入力したデータをもとに作ったレポート画面

縦軸が日あたりの水量、横軸が２か月単位の時間軸。

特徴的な個所はライフ上の意味があり、それ以外では急上昇がないことが見えるのでヘルシーなグラフ。どこで何やった人か見えてしまうので水量と時期は削除した。

この記事ではあまり詳細書かないけど、ピボットテーブルで加工しているくらいです。気になる人はお声掛けください。

Google フォームの機能の調査

実装する前に、簡易調査をしてます。

調査: Google フォームの種類

主に次の２種類ある。

パーソナル
- 特徴: 無料。
ビジネス
- 特徴: フォームの閲覧者を社内のみに限定できる。

調査: Google フォームのアクセス制御

誰がフォームにアクセスできるか？

パーソナル、ビジネスで相違がある。
- パーソナル: URLを知っていれば誰でも回答できる。
- ビジネス: 社内のみに限定できる。

(組織のアカウントもあるんだけど...)、以降、パーソナルを主軸に検討する。

回答者の特定手段があるか？

パーソナルの場合:
- 回答した人を特定する手段は提供されない。
  - 1回のみ回答を強制させる場合、回答者特定手段は提供されない
    - Google アカウントにつき、回答回数を1回に限定させる。
    - Google アカウントのメールアドレス等の識別情報は提供されない。
      - メールアドレスの入力を強制できるが、回答者はGoogleアカウントに紐づかないメールアドレスを入力できる。
  - 1回のみ回答を強制させない場合、回答者特定手段は提供されない

調査: Google フォームのフォーム作成画面

かなりわかりやすいので、ここでは深く説明しない。

構造的にはこのような感じ:
- Google ドライブの「Googleフォーム」のアイテム1個で、1つの回答画面を作れる。
- 1つの回答は、1個以上のセクションがある
- セクションは回答者視点だとページの区切り
- セクション内に質問、または、文・画像・動画などの説明などを記載できる
回答者の画面
- およそ以下の構造となっている。
  - Google ドライブの1フォーム
    - フォームの設定関連
      - フォームの「テーマ」
        
        ヘッダー画像、テーマの色、背景色、フォント、スタイル、
      - フォームの「設定」
        
        全般
        
        メールアドレス収集
        
        回答を１回に制限
        
        (回答者が)送信後に編集
        
        (回答後に回答者が)概要グラフとテキスト回答を表示
        
        プレゼンテーション
        
        進捗状況、質問順序、など
        
        テスト
        
        フォームを使って、回答者の試験を行うための設定がある
    - 回答者が参照するフォームそのもの
      - フォーム (1個以上のセクションを持つ)
      - セクション
        
        質問
        
        １行、複数行、ラジオボタン、チェックボックス、プルダウン、ファイルアップロード、メモリ、グリッド、チェックボックスグリッド、日付、時刻
        
        回答を必須にするか否か
        
        回答に数値を要求する、数値がN以上、N未満等の検証を行うことができる
        
        正規表現なども選択できる
        
        表示のみで質問を要求しないもの
        
        テキスト
        
        画像(URL、Google ドライブ、Google画像検索など)
        
        動画(Youtube, URL)
フォームの出力
- 何もしなければフォームが回答を持つ。
- スプレッドシートを指定すると、そのスプレッドシートにレコードを加える
  - レコードは、タイムスタンプと、回答項目

スプレッドシート出力調査

事前調査で、Google フォームの出力は、若干クセがあることに気づいたので、追加調査をしてます。

調査: Googleフォーム出力先のスプレッドシートの概要

基本的に、各質問の「質問」がスプレッドシートの列名になる。
質問以外として、各回答は、左端にタイムスタンプがつく
列名
- タイムスタンプ: 回答の日時
- メールアドレス: メールアドレス収集を指定したときの列名

調査: スプレッドシートと列名

フォームの質問(Q1, Q2, .., Qi, .., Qj, .., Qn)があって回答があるとき、質問項目の追加・変更・削除したときの動き:

操作
- 1. 回答する(A)
- 1. フォームの追加・変更・削除を行う
- 1. 回答する(B)
確認ポイント
- 回答Aと回答Bで、列がどう変化するか
  - スプレッドシートの列の順番
  - 列のデータ
操作とその動き
- 追加
  - 操作: Qn の次に新しい質問 Qn+1 を追加
    - 動き: 既存データはそのまま。列の最右列にQn+1用の列が追加された
  - 操作: Qi 直後に新しい質問 Qi+1 を挿入
    - 動き: 既存データはそのまま。列の最右列にQi+1用の列が追加された
    - わかったこと: フォームの質問順序と、スプレッドシートの列順序は同一とは限らない
  - 操作: Qi と同じ名前の質問を挿入
    - 動き:
      - 既存データはそのまま。
      - 列の最右列にQi+1用の列が追加された
      - スプレッドシートに同名の列が存在するようになった。
    - わかったこと: Excelのテーブルのように「質問」「質問1」といった同名の列名を許さない、といった動きは見当たらなかった。
- 変更
  - 操作: Qi と Qj の順序を入れ替える
    - 動き: 既存データはそのまま。
    - わかったこと: フォームの質問順序を変更しても、スプレッドシートの列順序は変わらなかった。
- 削除
  - 操作: Qi を削除する
    - 動き:
      - 列はそのまま。
      - 既存データはそのまま。
      - 回答で、古い質問列は空白となった。

調査: スプレッドシートの行の扱い

スプレッドシートにフォームの回答A1, A2, ..., An があるとき、スプレッドシートの行を消したときの動き:

操作
- 1. 回答する(A)
- 1. フォームの追加・変更・削除を行う
- 1. 回答する(B)
確認ポイント
- 回答Aと回答Bで、行がどう変化するか
  - スプレッドシートの行はどこに追加されるか
  - 空白の行はどうなるか
操作とその動き
- 行Anを消す
  - 動き: 末尾にレコードが追加された
- 行An+1に追加して回答する
  - 動き: 追加したレコードの直前にレコードが挿入された
  - わかったこと: 毎回、表の末尾のレコードが増えるのではない
- 行Ai (iはn未満の値)の行を消す
  - 動き: 空白行など作られることなく、An+1にレコードが追加された
  - わかったこと: Google フォームが最後にいれた行が内部的に保管されており、その行の次に追加されるように見える
- 行Ai (iはn未満の値)のセルをすべて空白にする
  - 動き: Aiは空白のままとなった。An+1にレコードが追加された。
- 行An のセルをすべて空白にする
  - 動き: Anは空白のままとなった。An+1にレコードが追加された。

調査: Googleフォームの回答の扱い

フォームを編集できる人は、回答を個別に削除できる。
この削除はスプレッドシートに影響を与えないように見える。
また、スプレッドシートの影響を受けない。

スプレッドシートで、行を消しても、Googleフォームの回答は減らない。
Googleフォームの回答を消しても、スプレッドシートの行は減らない。

水道料金をフォームで入力してみる

試作: 実現したいこと

家庭の水道料金の実績の推移を管理したい
- 水道の料金は変動するので、水の使用量の推移を調べる

試作: 要件

限定したメンバーのみがフォームを入力できる (＝家族)
水道料金の料金明細票をもとに入力できる
- どの項目に入力すればいいのか迷わないようにする

試作: 設計

構成概要
- 1. Google フォームでデータを入力する
- 1. 入力されたデータはGoogle スプレッドシートで管理する
- 1. 推移は、スプレッドシートのピボットテーブルで確認できるようにする
設計の方針
- 製品として Google フォームを使う
- Google フォームは無料で利用できるパーソナルを利用する
- パーソナルは、アクセス制御機能を持たず、家族外にURLを公開しないことで実現する
- 入力する情報は、万一に備え、情報を限定する
  - フォームの回答時、最小限の情報を表示する
  - 万一、データが漏洩しても問題ない情報のみを入力する
    - お客様番号は、特定につながるため、一部の桁のみを入力する
    - Google フォームに紐づけるスプレッドシートは家族以外参照できないようにする
  - 万一、リンクが漏洩しても問題ないよう秘密情報を入れる
    - 「お客様番号」の一部を入力する
      - もし意図しない第三者の入力があっても、お客様番号からフィルタしやすい
- Google ドライブで、Googleフォームと、スプレッドシートを格納するフォルダ構造
  - (別途定めるものに従う)
- データバックアップ
  - 行う
設計の詳細
- 設定
  - メールアドレスを収集する
  - 回答を１回に制限する
  - 送信後に編集
  - 概要グラフとテキストの回答を表示
  - 上記以外は既定の値を使う
- テーマ
- フォーム
  - フォームの「名前」: 水道料金を入力するフォーム
  - フォームの「説明」: 水道料金の明細が来たらここに入力しましょう。すべての項目を必ず入力してから、「送信」をクリックしてください。
  - セクション
    - 画像:
      - ファイル: 横浜市水道局の例の画像をそのまま使わせていただく
      - 画像のタイトル: お手元に次のような帳票を用意してください
    - 質問: (1)お客様番号末尾3桁
      - 記述式
      - 説明: お客様番号が「95-15-10001」の場合、末尾３桁「001」を入力してください。
      - 回答の検証: 正規表現, 一致する, ^\d{3}$, 数字3桁を入力してください
      - 必須
    - 質問: (2)今回ご使用水量[m3]
      - 記述式
      - 回答の検証: 数値, 次以上, 0
      - 必須
    - 質問: (2)請求予定金額[円]
      - 記述式
      - 説明: 12,345円の場合、カンマなしで12345を入力してください
      - 回答の検証: 数値, 次以上, 0
      - 必須
    - 質問: (2)水道料金(税込)[円]
      - 記述式
      - 説明: 12,345円の場合、カンマなしで12345を入力してください
      - 回答の検証: 数値, 次以上, 0
      - 必須
    - 質問: (2)下水道料金(税込)[円]
      - 記述式
      - 説明: 12,345円の場合、カンマなしで12345を入力してください
      - 回答の検証: 数値, 次以上, 0
      - 必須
    - 質問: (3)前回検針日
      - 日付
      - 必須
    - 質問: (3)今回検針日
      - 日付
      - 必須

試作: データ移行

過去にGoogleフォームを使わずに使っていたデータを、Googleフォームに紐づけたシートにインポートする。
- スプレッドシートの行の扱いが特殊であるため、注意が必要。
  - ベストな手段は、すべてのデータをGoogleフォームに入力しなおすことだが、これは大変そう。
  - 次に良いのは、フォームを作成して1件回答後、1行目と2行目の間にインポートするのが最も良いように見える。
    - この場合、Googleフォームの動作が変わって、インポートしたデータが消える、といった動きとならないかは不安
    - バックアップの準備は怠らないのが良い。

試作: テスト

(あまり詳細に書くつもりがない)

試作: 運用

人の教育
- 入力者は、公共料金の明細がポストに投函されたらスマートフォンで入力フォームを開くリンクをクリックする。
- 入力したら帳票を捨てられるので部屋がきれいになる、グラフが見える、がモチベーション
レポート
- (書く時間なく省略。詳細知りたい人はお声かけください。)
データバックアップ
- (書く時間なく省略。詳細知りたい人はお声掛けください。)

所感

しまった。淡々とやってたら、釣りおじさん以外のあそびっけがない！
こういうのできるの、というコメントがあると嬉しいです。

参考資料

横浜市 - 水道・下水道使用水量のお知らせの見方
- https://www.city.yokohama.lg.jp/kurashi/sumai-kurashi/suido-gesui/suido/ryokin/oshirasehyo.html
  - 帳票のフォーマットが得られる