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

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

## まとめ

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

## 完成イメージ

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

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

![コメント 2020-08-23 161055.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/296582/716ba47c-f29d-d84d-7674-12ea6e979c37.png)

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

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

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

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

![コメント 2020-08-23 224056.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/296582/de885a05-248d-ef57-6053-e69ca5f9188d.png)

## 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)
    * 2. フォームの追加・変更・削除を行う
    * 3. 回答する(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)
    * 2. フォームの追加・変更・削除を行う
    * 3. 回答する(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 フォームでデータを入力する
    * 2. 入力されたデータはGoogle スプレッドシートで管理する
    * 3. 推移は、スプレッドシートのピボットテーブルで確認できるようにする
* 設計の方針
    * 製品として 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
        * 帳票のフォーマットが得られる