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
    • 帳票のフォーマットが得られる