スプレッドシートの台帳を共有しても壊されない入力フォーム

スプレッドシートの台帳を共有して入力を頼んだら、行が消えていた・計算式が壊れていた・思った入力と違うことになっていた、という経験はありませんか？

スプレッドシートを直接入力すると付きまとう問題ですが、専用の入力フォームを作ることで入力補助もできるし、こちらの意図した入力へ制限することもできます

入力フォームはGASで作成するので自由度高く対応でき、既存のスプレッドシートとも連携が可能です

今回は車両管理をサンプルとしていますが、せどり商品調査など入力を外注している場合なども有効なので、参考にするか下記のココナラ経由で私に発注してもらえると嬉しいです

共有したスプレッドシートが壊れる3つの理由

原因は、台帳を直接更新しているという当然の理由に行き着きます

入力を頼んだ時点で、台帳のどのセルでも書き換えられる状態を渡しているのと同じこと
そこから起きる崩れ方は、だいたい次の3つに分かれます

• 行や計算式を壊される:並べ替えの拍子に行がずれる・合計の列に数字を直接打たれる・フィルタをかけたまま行を消される
• 表記がばらつく:「(株)山田運輸」「山田運輸株式会社」「ヤマダ運輸」が並び、集計やフィルタで同じ会社として拾えなくなる
• 同じ情報を何度も入力する:台帳に入れた内容を請求用のシートにもう一度打ち、ドライバーへの連絡メールも手で書き直す

3つともスプレッドシートの自由度が高すぎるがゆえの問題で、どんなに手順を示したり注意を促したとしてもヒューマンエラーを防ぐことも、万人に伝わる手順を作ることも難しいところ

ジャベ雄

「気をつけて入力してください」と書いておいても、完璧に制御することはできないと思います

シートの保護・権限・Googleフォームで足りないところ

どれも片方は防げますが、入力はしてもらう・シートは直接触らせないの2つを同時には満たせないのが共通の弱点

シートを保護すると、サイドバーからの登録もブロックしてしまう

最初は、スプレッドシートの右側に入力画面(サイドバー)を出して、そこから台帳へ登録する作りにする予定でした
これはこれで一定の効果はあるのですが、サイドバーは操作している本人の権限で台帳に書き込むので、入力者が編集できないように台帳を保護すると、入力者の側ではサイドバーからの登録もできなくなります

つまりシート保護ができないので誤操作でデータを削除したりシートを壊してしまう可能性は残ってしまいます

保護そのものについても、Googleのヘルプ(範囲やシートの保護)はセキュリティ対策ではないと明記していて、非表示にしたシートの中身も閲覧者は見られると書かれています
マスタを非表示のシートに隠しておけば見られない、とはいかないので、ここはちょっと注意が要るところかもしれません

Googleフォームはマスタ連動できないし、台帳更新には向いていない

Googleフォームなら入力者に台帳を触らせずに済むので、思い浮かぶ手の一つだと思います
フォームにも「回答の編集を許可する」設定があって、回答した本人があとから自分の回答を直すことはできます

でも、この手の台帳運用では「管理番号○○の納車先を直したい」のような、台帳の任意の行を探して修正するパターンもあります
フォームは自分の回答を送る・直すための機能なので、他の人が入れた行や管理者が作った行を呼び出して直す使い方には向きません

ほかにも、顧客を選んだらその顧客のいつもの引取先が別の欄に入る・案件番号を連番で振る、といった動きは標準の設定だけでは作れません
回答に応じて次のセクションへ進ませる分岐はあっても値を埋めてはくれないので、そこまでやるなら結局GASの出番

ジャベ雄

フォームは「送る」には強いけど、「探して直す」は苦手です

専用の入力フォームを共有してシート自体は共有しない

やったことは、台帳とは別のURLで開く入力フォームを用意して、入力する人にはスプレッドシート編集権限を付与しない、この1点です

入力者はシートを操作できないので、行を消す・計算式を上書きする、という事故が起きる場所がそもそも無くなります

書き込みは「公開した本人として実行」で管理者の権限にする

入力フォームは、GASのウェブアプリという仕組みで作っています
スクリプトをURLで開ける画面として公開する機能で、公開するときに誰の権限で動かすかを「公開した本人」か「アクセスした人」から選べるのがポイントです

ここを公開した本人(管理者)として実行にすることでフォームから管理者の権限で台帳への書き込みができます
だから入力者に台帳の編集権限は要らず、台帳のシートを保護しても書き込みは可能です
ただしこれは、公開した人がスプレッドシートのオーナー(または保護の設定で編集できる人に入れた人)である前提の話
サイドバーで問題となっていた保護での入力制御は、この切り替えで対応できました

設定ファイル(appsscript.json)で書くと、この部分

"webapp": {
  "executeAs": "USER_DEPLOYING",
  "access": "ANYONE"
}

executeAs が誰として動かすか、access が誰がこのURLを開けるかの設定
USER_DEPLOYINGは「公開した本人として実行」、ANYONEは「Googleアカウントでログインしている人なら誰でも開ける」という意味

URLを知っている人は誰でも書き込める

入力者はGoogleアカウントでログインしていれば、URLを開くだけで入力できます
裏を返すと、URLを知っていれば、Googleアカウントを持つ誰でも台帳に書き込めるということ
なのでユーザー認証機能や履歴を残す仕様が望ましいです

もうひとつ、入力フォームの編集・削除タブからは、削除済みを除く全案件を呼び出して上書き・論理削除もできます
GASで作成するのでこの辺りをうまく制御してあげることでシートが壊れないような仕様にできます

各マスタについては、専用シートの値を入力画面に渡すので管理者なら選択項目を追加・削除することも簡単です

スマホからも入力でき、管理者はサイドバーを使う

入力フォームはスマホのブラウザでも同じ画面で開けて、画面の幅に合わせて表示が切り替わります
車を引き取った直後にその場で入力できるのは、外で動く仕事だと特にありがたいところだと思います

管理者のほうは、スプレッドシートを開いたまま右側に同じ入力画面(サイドバー)を出して使えます
スプレッドシートのオーナーは保護した範囲でも編集できるので、管理者がサイドバーから登録するぶんには保護に引っかかりません
台帳を見ながら入力したい管理者には、こちらのほうが使いやすいでしょう

ジャベ雄

入力者はURL、管理者はサイドバーの使い分けでより便利になります

誤入力を3段階で防ぐ 選ぶ・候補から選ぶ・フリー入力

表記ゆれと入力ミスは、項目ごとに入力のしかたを3段階で変えることで防いでいます

これとは別に、登録の前に必須の4項目(顧客名か新規顧客名・車種・引取先・納車先)の入れ忘れをチェックしています
分け方の基準は、値の種類が決まっていて増えにくいものは選ぶだけ、増えるけど同じ値を繰り返し使うものは候補付き、毎回違うものは自由入力
全部をプルダウンにすると初めての値が入れられず、全部を自由入力にすると表記がばらつくので、その間を取っています

マスタから選ぶだけ(顧客名・ドライバー)

顧客名とドライバーは、マスタのシートにある値から選ぶだけで、手入力させません
「(株)山田運輸」と「株式会社山田運輸」のように同じ会社なのに表記が異なる入力を防げます

顧客を選ぶと、その顧客のいつもの引取先・納車先が自動で入ります
手で上書きもできるので、今回だけ別の場所から引き取る、という案件にも対応できます

マスタに無い新しい顧客は、プルダウンの末尾にある「＋ 新規顧客を追加」を選ぶと、その場で顧客名と担当者を入れる欄が開きます
登録と同時に顧客マスタへ追加されるので、マスタに無い場合のイレギュラーにも対応できます
制御の抜け道にもなるので、管理者がときどきマスタを見直して表記をそろえる、みたいな感じの運用が前提
マスタ自体はスプレッドシートのただのシートなので、管理者が行を足したり直したりすれば、次に開いたときの選択肢に反映します

候補付きの自由入力(車種・引取先・納車先)

車種や引取先は候補が多く、新しい値もどんどん増えるので、プルダウンから選ぶだけでは逆に大変に
そこで、入力欄に文字を打つと候補が出て、候補に無い値もそのまま入れられる候補付きの自由入力にしました

候補の元は、候補リストのシートと、台帳に過去に入った値を合わせて重複を除いたもの
一度入れた引取先は次から候補に出てくるので、2回目以降は選ぶだけで済むのがやっぱり楽なところ

候補を出しているのはブラウザのdatalistという機能で、Chrome・Edge・PC版のSafariでは候補が出ます
AndroidのFirefoxは非対応とされていて、候補が出ずにただの自由入力として動きます
iPhoneのSafariとPC版のFirefoxは一部対応で、挙動がほかと違うことがあります
候補が出なくても入力そのものはできるので、環境による差で困りにくいのもこの方式を選んだ理由のひとつ

自由入力と登録前のチェック(車番・時間条件・料金)

車番・時間条件・料金は毎回違う値なので、そのまま打つ自由入力にしています
そのかわり登録ボタンを押した時点でチェックをかけ、必須の4項目(顧客名か新規顧客名・車種・引取先・納車先)が空なら、赤字の警告を出して登録エラーを出します

料金は数字以外が入っていたら止めますが、空欄ならOK
車番と時間条件にはなにもチェックをかけていないので、書き方をそろえたいなら入力者への説明で補う程度

チェックは画面の側と、台帳に書き込む直前のスクリプトの側の2か所でかけています
画面のチェックは入力者の手戻りを減らすため、スクリプト側のチェックは台帳を守る最終チェック、という分担

台帳の側で守ること 採番・論理削除・上書き防止

入口を分けたうえで、台帳そのものも管理番号採番・削除・同時編集の3つでデータが壊れないように工夫しています

管理番号は登録ボタンを押した時点で振る

管理番号(R-0001のような連番)は、入力画面に仮の番号を出さず、登録ボタンを押した時点で確定させます
最初は画面を開いたときに次の番号を表示していたんですが、そのあいだに誰かが登録すると画面の番号と確定した番号がバッティングするので仕様を変えました

番号を振る処理は、確定する時に1人ずつ順番に処理する仕組み(ロック)の中で現在の最大値+1を取るので、複数人が同時に登録しても番号は重複しません

削除処理してもデータは消さない(論理削除)

編集・削除のタブで管理番号を選ぶと、その案件の内容が呼び出されて、直して保存できます
削除は行を消さずに、削除フラグの列にチェックを入れて行に取り消し線を引く論理削除にしました
フォームから削除する限り行は残るので、番号が欠けたり同じ番号を使い回したりする心配はありません
管理者はシートで行ごと削除できますが、番号の欠番や振り直しが起きるので、削除はフォームから行うのが前提です

登録日時と更新日時も、台帳の列に自動で入ります

同時編集で後から保存した人を止める

2人が同じ案件を開いて別々に直すと、後から保存した人の内容で全項目上書きしてしまいます
そこで更新日時を版の目印にして、開いたときと保存するときで更新日時が変わっていたら、書き込まずに「他の人が変更しています」と知らせる作りにしました
画面には「最新の内容を読み込む」ボタンが出るので、読み直してから再度修正すればOK

管理者がスプレッドシートでセルの値を手で直した場合も、onEditを使ってその行の更新日時を上書きします
上書きされるのはセルの値を直したときだけで、行の削除や並べ替えでは変わりません

見出し名で読み書きするので、列の並びは今のままでいい

台帳の列は位置ではなく見出しの名前で探して読み書きしています
列の並びを変えなくていいので、現場が慣れたレイアウトや計算式をそのまま残せるのが、この作りのいちばんの狙い

その代わり、見出しの名前は見本と一字一句そろえる必要があります
名前が違うと、同じ名前の空の列が末尾に黙って足され、そちらに書き込まれてしまうからです
「更新日時」「削除フラグ」は足りなければ自動で末尾に足され、管理番号は「R-」+連番の形が前提
なので導入するときは、今の台帳の見出しを合わせる調整がちょっと要ります

ジャベ雄

作り直さずに、見出しを合わせて列を足すだけで済むのが使い勝手では大事だと思います

導入前に知っておきたい4つの制約

入口を分けた代わりに、ウェブアプリならではの制約が4つあります

1. 画面の上部にGoogleの注意書きが出る:個人のGoogleアカウントで公開すると、入力フォームの上に、Googleが作ったものではない旨の注意書きが表示されます(キャプチャ①の上部)
   Google Workspaceの組織内だけで使えば出ないという情報もありますが、社外のアカウントから開くと出るという説明もあるので、出るものとして入力者に一言伝えておくのがおすすめ
2. 入力した人のメールアドレスが取れない:公開した本人として動かす以上、アクセスした人のメールアドレスは基本的に取れません
   Google Workspaceの同じ組織内なら一般に取れるとされていますが、個人アカウント同士なら、担当者名を選ぶ欄で代わりにするのが現実的
3. 画面を直したら再デプロイ(公開し直し)が必要:公開中の入力フォームは、公開した時点のコードで固定されています
   直したあとは新しいバージョンを作って既存のデプロイを編集すると、URLを変えずに更新できます
   デプロイを新しく作ってしまうと入力者に配ったURLのほうは古い版のまま動き続けるので、ここは気をつけたいところ
   テスト用のURL(末尾が /dev)は編集権限のある人しか開けないので、入力者に渡すのは公開用のURL
4. 開きっぱなしの画面にはマスタの変更が反映しない:入力フォームは開いたときにマスタを読み込むので、管理者がマスタを直しても、再読み込みするまで選択肢は古いまま
   朝開いて夕方まで開きっぱなし、という使い方だと気づきにくいかもしれません

ジャベ雄

注意書きを見て手が止まる人もいるので、URLを渡すときに一言添えておくと親切です

まとめ

共有したスプレッドシートの台帳が壊れるのは、入力と保管が同じ場所にあるから
入口を入力フォームに分けて書き込みを管理者の権限で行えば、入力者に台帳を共有しなくて済みます

• 入力者にはURLだけを渡し、スプレッドシートは共有しない
• ウェブアプリを「公開した本人として実行」にして、書き込みは管理者の権限で行う
• 項目ごとに、選ぶ・候補から選ぶ・自由入力の3段階に分け、必須項目と料金は登録前にチェックする
• 台帳の側は、採番・論理削除・同時編集の上書き防止で崩れないようにする
• 見出し名で読み書きするので、列の並びは今のまま、見出しの名前を合わせて列を足せば導入できる

題材は回送の案件台帳でしたが、顧客台帳や受注台帳・在庫台帳でも、入力をアルバイトさんや委託先に頼んでいるなら同じ形が使えるはず
もっと大きな仕組みを入れる前に、今のスプレッドシートを活かしたまま入口だけ変える、という選択肢もあっていいと思います

サンプルで使ったスプレッドシートと入力フォーム

記事の中でキャプチャを貼っているスプレッドシートと入力フォームへのリンクを置いておきます

実際にどんな動きになっているか気になる人は適当に触ってもらってOKです

※別タブで開くリンクは下部へ

スプレッドシートを別タブで開く(閲覧専用)

入力フォームを別タブで開く(登録した内容は、上のスプレッドシートの台帳に入ります)