GASやAppSheetで使うスプレッドシート設計
Google Apps ScriptやAppSheetを始めるにあたって、なんとなくフワっと今あるスプレッドシートの現業のデータを元にスタートするという人は多いと思います。しかし、それは大きな誤りです。
単純なコピペだけしかしない「マクロ」程度のものであるならばいざ知らず、きちんとしたアプリケーションとして構築するうえでは様々な障害がこの先に待っています。よって、ここではしっかりしたスプレッドシート設計を行い、アプリとして運用できる「型を学ぶ」ということでまとめています。
今回利用するスプレッドシート
- プログラムに適したスプシ設計 - Google Spreadsheet
今回のテーマはGoogle Apps Scriptそのものと言うよりも、GASで利用する土台であるスプレッドシートの設計手法に関する話題になります。GASに限らず、Power Automate DesktopやVBAなどでも同様の手法が必要であり、初心者が躓く大きなポイントであるにも関わらず、あまり初心者向け資料が無いのが現状です。
プログラミングのスキルというよりはセンスに該当するもので、一言で言えば「人間に適したスプシではなくプログラムに適したスプシで設計しましょう」という事に集約されます。
このテーマの土台になってる話題は過去に以下のエントリーの「データの整備管理編」でも語られています。
数々のスプシの問題点
アンチパターン
よくある駄目なパターン
多くの人がアプリ構築をする際に躓いたり挫折する根本は、JavaScriptの構文を覚えられないことではなく、データを操作するスプレッドシートが「コンピュータが操作するのには適していない形」になってるが故であることが多い。
過去これまでの経験上もデータベースとしてのルールや型・思想が全く無い形式のデータで、殆どが「人間が見ることにフォーカスされたデータ形式」になってることが殆どです。つまりこれまで手作業で人間が入力する事が前提となってるシートでは、GASで自動化することは難しいということになります。
特にこの傾向は関数が扱える中級者によく見られる傾向で、下手に出来るが故に自動化の障害になっているケースです。よくあるパターンは以下のようなケース
- 初代作成者がもう居ないで利用され続けてる秘伝のタレ化してるスプシ
- 元データが無くいきなり集計表形式で作成されているスプシ（しかも手入力）
- 人間が見るのに最適化されているが故に、見た目はキレイだがプログラムではめちゃくちゃ扱いにくいリッチなデータ
- データ列の中身のデータ型がまるで統一されていない（日付一つとってもバラバラの形式）
- 人間視点で見やすくする為と称して、むやみに複数のシートに分割してるケース
- データシートに特化しておらず、シェイプや他の表データが混在してDTP代わりに使ってるようなケース
属に言うExcel方眼紙やら神Excel、Excelレガシーといったものが本問題に該当する事例になります。これらはGoogleスプレッドシートでも共通の問題です。
Excel方眼紙問題
ワープロを使わずにExcelのマス目を調整してセルの結合や枠線などを活用してレイアウトを自由自在に組んで利用する手法を「Excel方眼紙」と呼びます。これは昔から問題視されつつも、一理あるということで現在も活用されている手法です。
この一理というのが曲者で、
- ワープロだと自由度が低いため思ってるようなレイアウト組が出来ない
- Excel方眼紙だとセルの結合などで割と思ってるレイアウトを構築することが出来る
- 差込印刷的な処理をする場合も、特定セルの値を変えるだけで入れ替えが出来る
方眼紙シートと実際のデータシートを分けているならばまだしも、直接値を入れ込んでるとなると、プログラムで作った場合にその後のレイアウト変更の度にコードの手直しまでしなければなりません。
これらをプログラマブル且つ自動化と考えた場合には、方眼紙シートとデータシートは必ずわけて、方眼紙側からデータシートの1レコードを参照するように構築するなどのテクニックが必要です。レイアウトを変えてもコードまで影響が及ばないように配慮する必要があります。
ですがなるべく方眼紙ではなく、ドキュメントでレイアウトを構築しPDFで出力するなどをしないと余白設定などはスプレッドシートとドキュメントでは結構違うのでPDF化の時点で苦しむことになります。
神Excel問題
前述のExcel方眼紙と似て非なるものがこの神Excel問題。この正体なのですがもはや祟り神になっており
- セル内のレイアウト調整の為に機能を使わず、スペースで調整などが入っていたりする
- 複数のスプレッドシートに対して複雑に連携をしてデータを取ってきてる（Excel外部リンク機能やImportrange関数がコレ）
- VBAが使えないが故に、複雑な関数の入れ子で条件判定させてしまってる。そんなのがあちこちに多数存在し、動作を極度に重たくしてる。
- 設計思想やバージョン管理がまるで無いので誰もメンテ出来ない
- 印刷すること前提で構築されてるので、人間が手入力するのにものすごいストレスが掛かる
- 必要なのはデータであって印刷物ではないことが理解できない人がいる。OCRを使うなど論外です。
- よって、社内申請などでわざわざスプレッドシートに書き込み、申請書のPDFを出力して・・・これ自体が必要ない作業の代表例です。印鑑文化をまず破壊しましょう。
- タスク管理やスケジュール管理ツールを使わず、スプレッドシートでガントチャート作成をしてしまい、膨大な手作業が発生してる。見にくい上に入力が非常に大変。
- そもそもファイル単位で管理という概念自体が時代錯誤。必要なのはデータのレコードです。
特にこの傾向が強いのが、役所の書類。ホームページに掲載されてる申請書がこんなパターンがあり、入力者の負担が半端ない。当然こういうファイルの集計をやるとなると、複雑なデータの成形や集計をする仕掛けを専用で用意する必要があるため、自動化の大きな障害になっている。
神Excelは百害あって一理ナシです。この神Excelは多くの人間の生産性を低下させている元凶とも言えます。そしてこれらは、本人がVBAやGASを使えないが故に力技で実装した末路なのです。
Excelレガシー問題
前述までの内容はどちらかというとExcel初心者〜中級者がやりがちな、スキルが無いが故にむりくり構築した魔窟のようなスプレッドシートのお話でした。そしてこのExcelレガシー問題は上級者がやりがちな問題で、主な内容は以下の通り
- Excel内で高度な関数が複雑に入り組んで構築されている
- マクロなども併用されて活用されていて、ボタン一発で色々な作業が出来るようになっている
- このツールを使うことが暗黙知のように業務のプロセスに深く食い込んでる状態（しかし上長はそれを把握していないケースも多い）。マニュアルまでこれを使うことが前提になってる。
- 法対応や仕様変更が発生しても誰もメンテ出来る人間が居ない
- 本来は基幹業務システム側で巻き取ってきちんとしたシステムとして構築すべきものを情シスが引き取りを拒否する
- Excel内だけじゃなく、下手に他のシステムまで自動化出来る為、第三者が読み解くのが非常に困難（OutlookやらNotesやらいろんなシステムをCOM経由で操縦出来る）。
などなど。これはRPAなどでも同様のことが起きており、一言で言えば「ブラックボックス化されたシステム」と化してる状態です。作り込みが過ぎて確かに便利で貢献してる一方で、手出しも出来なくなってる状態に陥っています。
これは企業のガバナンス問題にも発展し、最終的には莫大な費用を掛けてシステム化するという末路が待っていたりします。つまり、本来情シスがきっちり深く踏み込んで取り組むべきものを放棄し、金が無い・人手がないなりに仕方なく現場が長年掛けて作り上げたサブシステムであり、企業全体の問題でもあります。
このあたりは以下のRPAのエントリーでも深堀りしてまとめています。この問題に派生する問題としては以下のように語り草になっているものまであります。殆どがその組織の人事の問題です。
- 「Excelできる人が辞めてしまったので手作業で」→「Excelできる人が入社し自動化」→「否定されてExcelできる人退職〜無限ループ」の地獄の流れ「まさにうちもこれ」
- 退職した社員のExcelの解析相談され見積もりを出したら「高い。他を当たる」となった企業が出戻ってきた話
- 「派遣にできるなら正社員もできる」とExcel業務をマクロで自動化して残業ゼロだった派遣社員が解雇される地獄話
- 顧客「長年１人で社内システムを開発していた社員が退職し、新規のシステムに刷新したいので見積りを。予算は5千万」→その結果こうなった
- 社内システム開発していた社員が辞めた結果大打撃…… 「他人事ではない」「もっと技術者を評価して」
- 業務ハックしてクビにならないために
- 業務ハックしてクビにならないためにを実践し続けた組織の末路
一番重要なのは、まず断捨離
スプシ設計にあたっての最も重要な要素が「そもそもその作業いる？」「何かを書き込んでるけれど、そのフローいる？」という断捨離がほぼほぼどのケースでも存在していたりします。なぜこのようなことが起きるのか？と言ったら
「理由はわからないけれど前任者からそうやれと引き継いでる」
というパターン。また、次に多いパターンが
「自分は必要だと思って追加した」
さて、前者の場合これはもはや誰も精査をしておらず、意味もなく存在してる作業だったり、ファイルだったりします。けれどもこれを除外した場合に何が起きるのかわからなくて怖いからそのままにしてるという最悪のパターンです（10年間誰もメンテせず動いてたというケースも。当然誰も手出しが出来ない）
後者の場合、本人のスキルが低いが為に無理やりそういうフローを入れていたり、「本人しか必要としていない」のに組み込まれてる機能であったりします。
本人のみが使うならばいくらでも機能として作り込んでも問題ありません。問題はそれが部署の作業や会社のフローの１部を担っているということであるならば、そこには相応のレベルを持った人間によるレビューを入れて断捨離（現業とIT両方に精通してる事）しなければなりません。これが昔から言われてるエンドユーザコンピューティングの弊害と言われてるものです。
DX推進は重要なのですが、EUCとイコールならばそれはDXではありません。そしてこのレビューをせずに作成されたスプシと、それに基づくGASアプリは将来的に弊害をもたらす可能性が非常に高いです。
スプシを利用するか否か？
そもそも論として、スプレッドシートを土台につくるべきなのか？というケースがあります。スプレッドシートには上限値というものが存在し、またデータベースサーバとは異なり読み書きは非常に遅いです。故に
- 万を超えるようなレコードを格納するようなケースでは利用しない
- 莫大なデータを検索するような用途には利用しない
- 複数のテーブルを連結したり、大きなデータを集計する用途には利用しない
現在ある1000万セルの上限ですと、30カラムで50,000レコードならば1,500,000セル消費する。例えばこれが1ヶ月のデータであるならば、7ヶ月分くらいのデータしか格納出来ないことになります。
また、読み出しのスピードも50,000レコードの医薬品データをGASで呼び出してみたことがありますが、数秒待機が生じるくらい。書き込みになるともっと遅い。
故にこういった大規模になってきた場合には、Cloud SQLやBigQueryを土台に利用するのが望ましいです（もちろん相応の追加コストが必要です）。
スプレッドシート設計ノウハウ
ここからは前述の様々なスプシの抱えてる問題点をクリアし、いよいよスプシの設計をしGASを書いていこうとなった場合の、設計ノウハウになります。ここでしっかりとした設計をすることがプログラム作成全体の5割を占めると言っても過言ではありません。
以下はGoogleスプレッドシートにフォーカスしてそのノウハウをまとめています。
厳格なデータのルール
スプレッドシートはその特性上自由自在に色々なデータを置くことができますが、Google Apps Scriptなどのプログラムで利用する場合には厳しいルールを敷く必要があります。主なルールは以下の通り。これはデータベースのルールそのものであり鉄則です。
この件に関しては既に総務省からも統一ルールとして公開されており、いかにコレに準拠していないファイルが多いのか経験のある人も多いでしょう。
- 1セル1データは鉄則。1つのセルに複数のデータを備考のように詰め込んでるようなケースはNGです。
- 数値のセルデータに▲などの記号類や円マーク、単位名などを含めない（セルの書式設定でやるべき事です）
- セルの結合を行わない。人間にとって見栄えが良くても、プログラムが扱う上では最悪です。
- スペースで均等割り付け風にしない。スペースの存在は邪魔以外の何もでもありません。
- 複数のレコードで同じような項目が列挙されてるケースで1つ目だけ項目名を表示して、以下を省略するような表記を行わない。
- 1つの表で実現できる事を、特定のジャンルでわざわざシートを分割しない（都道府県別に別々に表を作る事になんの意味もありません）。都道府県という列を用意してジャンルを記述しておけば済む話です。
- 生データなくいきなり集計表形式で表を作らない。集計表というものは生データから加工して作るものです（ピボットテーブルなども生データから加工して作る事例の１つ）。生データ→集計表は簡単でも、集計表→生データは非常に面倒です。
- 同じ列なのに、数値のものと文字列のものが混在してる。数値の列は数値のみ、文字列のものは文字列のみにすべき。
- 数値に於いて全角文字は使わない。
- 日付の形式をyyyy/mm/ddの形式を使用し、21.10.5といったような入力をしない。
- 列の非表示、フィルタを掛けっぱなしで運用するような真似をしない。
- 月別合計列などの小計を列と列の間、行と行の間に設けない。邪魔です。合計列は一番右、合計行は一番下は鉄則です。
- 100列も200列もあるような巨大で横長なデータは作らない。行数はいくらあっても構わないけれど、列数はきちんと項目を整理するべき。目的の値を探す時間が無駄以外の何物でもありません。
- 無闇矢鱈に条件判定や複数の入れ子にした関数を駆使したような表は作らない。メンテナンス性最悪です。
- 一方でSUM関数1つで済むようなものを、A1+B1+C1+・・・といった悪い数式で合計したりしない。
- テーブル機能を活用し、vlookupなどで固定もせずに作ってるような表は作らない。
- 追跡不可能なほどに複数のスプレッドシートを参照するといった表は作らない。
- 1列のステータス列だけで表現できる事を、複数の列を使って表現するような表は作らない。
- 備考欄にすべき内容を、セルのコメント機能を使って表現しない。
- 部門によって同じような内容なのに、表の形式・入力ルールが統一されていない（列の順番すら違うケースも多数有り）表は作成しない。
- 縦横の1枚の表以外に余計な場所に入力欄やら、マクロのボタンなどは設置しない。
- 表のタイトル列に環境依存文字であったり、特殊記号、数字から始まるタイトル、括弧などを含めたタイトルは使用しない（命名規則を勉強しましょう）
- IDなどは必ず一番左端に配置し、また重複しない連番の列も用意すべき。
- おなじくタイトル行に於いて、同じ列名を使用しない。
- 可能であれば、マスタとサブマスタは分離管理し、結合したい場合はQuery関数を使うか？Cloud SQLなどを利用しましょう（結合に関しては次項を参照）
これら厳しい掟ですが、一言で言えば「余計なことはしない」ということです。余計な加工や人間に取って見やすい表は、これら生データを元に別のシートで好きなだけ作ってください。
コンピュータ本位ではなく人間本位に作るということであるならば、自動化は出来ないと思ってください。
シートの分割と結合
この項目はかなりデータベース的な思想が入ってきます。初期の頃は不要であっても、時間経過や機能増強、より高度な実装が必要になればなるほど必須となっていく項目です。ソレ故に、Power QueryやAccessなどで挫折する人が出るのと同じポイントがここにはあります。腰を据えて取り組む必要があります。
マスターとサブマスター
概要
はじめのうちは、GASなどで自動化やアプリ化をする場合、単一のシートに対して行うといった事が主になるでしょう。しかし、アプリのデータ量増加や機能増強に伴って様々な課題が発生していきます。その為将来を見据えて始めからこれらの問題に対応できるように設計するのが常になります。
例えば以下のような感じでシートを分割し用意していく作業になります。
- 目的別にマスターシートを用意する（顧客マスタ、商品マスタ、売上マスタなど）
- 売上のレコードにぶら下がるような明細をサブマスタシートとして用意する
- 場合によっては過去データ退避用のシートを用意する
AppSheetでもこれらシートを複数にわけて、それぞれを目的に応じてリレーションで結合・参照したり、請求書に対する明細書を連結するなどを行ったりするシーンで頻繁に利用します。
マスターシート
マスターシートとは、重複しないID列を持ったシートのことで、主な利用目的は
- 商品データ一覧
- 人事データ一覧
- 売上データ一覧
といったプログラムの中心を為すシートになります。さらに大きく2つに分類されて
- 固有のデータをまとめたシート（本当の意味でのマスター）
- 他のマスターシートの参照を含めたシート（記録を残す為のマスター）
となり、固有のデータは社員名簿的なものや商品マスター的なものを指します。一方、他のマスターシートの参照を含めたシートというものは、売上マスターのようにID / 商品ID / 数量といったように、固有のIDの他に売上記録の為の重複有りの「商品ID」を持つシートで、商品固有の情報は商品マスター側に任せて自身は商品IDのみを保持するようなものが該当します。
もし、商品マスター側の情報（例えば商品名や規格）を含めたものが必要な場合には、Query関数などで連結した読み取り専用のテーブルを別途用意する必要がありますす（後述）。
ここで疑問が出るのが「なぜ1枚のシートで表現しないのか？」ですが、この分割を正規化と呼び、主な目的としては売上マスターを例に取って説明すると
- 常に全データ入力となるとユーザの入力負担が大きくなる
- 全データを持ってしまうとシートのサイズが大きくなり、速度面で不利になる
- 通常は変動することの無い商品マスタに切り出しておくことでデータの再利用性が高まる（セル数の消費を低減できる）
- 仮にも商品名等に変更があった場合、全データを持ってしまうと、それらを全て置換しなければならない（IDだけ持ってる場合は、IDは変わらないので、引っ張ってくる時に最新データであれば良い）
- 売上マスタと商品マスタ間についてはリレーションという連結を行う
- 但しその場合、単価などの変動要素は計算時の参照では有効でも、記録後に単価変更がされると逆に過去の単価が変わってしまうので、これらは売上マスタに敢えて記録する。
- 社員マスタのように記録ではなく常に最新情報を参照するようなものの場合は、1枚のシートで完結させる。
- その社員マスタでさえも、例えば勤怠マスターのように記録をするマスターの場合は参照される側になり得る。
このようにマスターと言っても役割が異なる為、使い分けとなぜこのような分け方をするのか？は、この手のアプリを作る上では欠かすことのできない概念です。もし、1枚のシートに全データを記録し続けるような形を取った場合、時間経過とともに重くなり、データの置換の必要性が発生したり、アプリのメンテナンス性が著しく落ちる為、始めからこのような設計をするのが常になっています。
図：通常のマスターの概念図
図：記録を取るマスターの概念図
サブマスターシート
サブマスタとはマスターシートの1項目に対して複数の項目がぶら下がる場合に、さらにシートを分割して分けて作成されるもので、売上IDに対して売上明細が複数ぶら下がるようなイメージです。前述の場合、売上IDに対して1つの商品が記録されるのに対して、売上明細を加えた場合には、売上明細の１つずつに対して商品IDが複数連結されるイメージに変わります。この売上明細が売上シートに対するサブマスタになります。
ある程度の規模になると、このように売上テーブルを更に分割する正規化を推し進めて効率化を図るのは定石で、AppSheetでもこのような手法を用いることで、Accessなどでもよく利用する明細サブフォームといった形で表現することがよくあります。
図：サブマスタ採用時の連携イメージ
選択項目用のシート
Excelのドロップダウンリストのように、ユーザが選択して入力する項目は通常別のシートに切り出します。データ量は選択項目ですので大した量ではないものの、ここには１つ設計に迷う点があります。
- 選択する項目名だけを列挙して、そのまま対象のテーブルに入力するタイプ
- IDと項目名の2つを列挙して、表示は項目名、入力値はIDを入れるタイプ。
簡便な方法は前者ですが、リレーションを利用するならば後者を利用する。後者の場合、入力値はIDだけなので項目名を後で変更しても、入力側テーブルの置換などは不要ですし、データの軽量化・再利用性が高まります。また、入力者の負担軽減にも繋がります（もちろん連結せず、入力のみだけ用いるケースもある）。
AppSheetなどでもこういった実装をするシーンは多いので、随所で利用するシーンがあるでしょう。
過去データの切り出し
売上マスタや売上明細マスタのように記録を取るマスターの場合、年月を経ると共にシート上のデータがどんどん蓄積し、結果的に表示するアプリ側の重さが増していくことになります。
過去データはその後のビッグデータの解析といったような分析作業で利用するなどの別の目的がありますが、メインのシートに残したままですと前述の問題があるため、蓄積用のシートやCloud SQL・BigQueryなどに移行させて切り出しを行う必要性があります。
これらは手動で行うのではなく、プログラム側で実装して例えば新年度に入ったら、過去2年以上の前のデータは移行させるなどを行い、メインデータは常にある程度のデータ量に留めるよう努力する必要があります。
シートの結合
前述のようにシートデータの正規化を進めると、売上テーブルには明細IDや商品IDのみが存在してる状態であるため、ユーザが日常的に見るUIであったり、人間が分析をする場合には使いにくい状態にあります。そこで、これらのデータをリレーションで連結し、表示する為用のシートを用意します。集計表などの整形でも利用します。
リレーションの連結にはGoogleスプレッドシートの場合であればQuery関数を利用して結合することが可能です。またCloud SQLやBigQueryの場合はクエリにて連結して表示させてあげることが可能です。
結合結果をアプリ側で表示し、マスターデータが書き換われば、リロードすることで最新の結合データを表示することが可能です。
図：売上と売上明細を結合してみた
アクセス権限から見た設計手法
今回のテーマはGASやAppSheetのアプリから使うスプシの設計なわけですが、GASのウェブアプリやAppSheetからの書き込み権限や、シート自体はユーザに直接触らせたくないといった要望があります。データベースの場合は別個にアクセス権限を設定できるものの、スプシとなると少々厄介です。
アクセス権限概要
AppSheetやGASといったアプリからの読み書きを考えてスプシを設計する必要性があるシーンでは前述のようなテーブル設計の他にも考慮する必要性があります。
主なアクセス権限ですが
- AppSheetの場合はアプリをデプロイした人の権限でスプシにアクセスされます。
- Google Apps Scriptの場合のアクセス権限は複雑です
- ウェブアプリとしてデプロイした場合、ユーザの権限か？オーナーの権限か？を指定可能です
- デプロイ時にオーナー権限でデプロイした場合は、ファイルに対してユーザのアクセス権限は不要です。
- トリガーの場合は、設置者の権限で動作します。
- ウェブアプリではない場合には、ファイルに対してユーザのアクセス権限が必要です。
入力シートを支店毎に分けるケース
ウェブアプリではなくスプシを直接ユーザに操作させる場合の設計です。但しこの時、ユーザは他の支店等のデータが閲覧できる状態であってはならないというケースです。よって、1枚のシートに複数支店の人間を書き込ませるわけにはいきません。
この場合、以下のような仕掛けと設計を行います。
- 同様の入力ファイルを支店毎に別個に用意する
- 管理者はこれらを集計する別個のファイルを用意する
- 管理者は全ての支店毎のファイルにアクセスできるが、他のユーザは集計用ファイルにはアクセス権は無い
- 集計用ファイルはImportrange関数で支店データをロードし、Query関数で集約するか？Google Apps Scriptで支店毎のファイルIDリストを元に集計するコードを実装する。
- 集計はトリガーなどで夜間にバッチ集計しても良い
- 但し、ファイルが多数ある場合にはGASの6分の実行限界を超える可能性がある。
シートを直接触らせるので、シートの構造破壊や余計なデータの入力などを行われるリスクがあります。自分もかつてこの方法で30近い施設のデータを集計するスクリプトを作って運用していた事があります。しかし、6分の壁を超えることも多いため、その為のロジックを組む必要があったりで、思っている以上に労力が掛かった為、現在はこういった運用はしていません。
ウェブアプリケーションを利用するケース
前述の方法の場合、要件は満たせても集約の為のロジックや無駄な時間が掛かる為、運用は結構難しいです。また、ユーザの入力負担は軽減できていない為、労務時間削減効果は期待できません。
そこで、GASでウェブアプリケーションを生成し、UIを用いてユーザにはアクセスしてもらう方法でこれらの問題点をクリアすることが可能です。
- 支店毎にファイルを分ける必要はありません。書き込みマスターに支店名の列は必要です。
- デプロイしたユーザの権限で動かすのでスプシに別途アクセス権限は不要です。
- ユーザ毎にどの支店所属なのか？を管理するシートが別途必要。アクセス時にその支店名をもってフィルタして表示する。（アクセス時にユーザのメアドは自動取得が可能）
- 書き込み時にもユーザの支店名を加えたレコードとしてスプシに登録する。
- 別途、ユーザに入力してもらうウェブアプリとしてUIの構築が必要になります（VueやVuetifyなど）
- デプロイした人の権限で動いてるので、操作するCalendarやGmailなどのメソッド類は、すべてデプロイした人のアカウントのデータになってしまう。
- デプロイはオーナー権限で動かしつつ、ユーザのアカウント情報やデータを操作する場合は、ユーザのAccess Token取得とREST APIを使う必要がある。
もっともオススメの方法であり確実な方法ですが、構築する為のスキルがかなり要求されます。また、実行権限がデプロイした人の権限で動いてしまう為、スプシの書き込み程度ならともかく、各人のカレンダーに書き込む等は高度な仕掛けが必要になります（要サービスアカウント）。
AppSheetの場合
AppSheetのケースの場合、そもそもデプロイしたユーザの権限でアプリが動作してるので（Call as scriptも同様）、前述のスプレッドシート同様のメリットがあります。
- 起動時にユーザの所属を取得するロジックを構築する必要があります（ユーザリストシートが必要）
- 支店毎にファイルを分ける必要はありません。
- 一覧読み込み時はユーザと所属にリレーションを構築してフィルタして表示が必要
- 書き込み時はユーザ所属をCall a scriptで追加書き込みが必要
- ユーザのメアドだけで判定する場合には上記のようなロジックは不要で、ユーザのメアドでフィルタした結果を表示すればオッケー。
- AppSheetがUIとなるため、別途ウェブアプリのようなUIの構築スキルは不要。
- アクセスしてきてるユーザの情報やデータの変更は、ウェブアプリ同様の仕組みが別途必要
プログラム特有の設計方法
各種フラグ類
例えば、申請するアプリ等の場合、メインのUIにいつまでも処理済みのデータが残っていると邪魔だったりします。よって、何かの列をフラグとして用意し、その列を基準にフィルタしたものだけを表示するといった仕掛けが必要です。
例えば終了済みという列を用意し、チェックボックスを配置する等。また、特定の列の値でもって判定し、項目の表示非表示をコントロールするといったことにも使えます。
これらを俗に「フラグ」と呼び、ゲームの制作や給与計算などのシーンで多く遭遇することでしょう。一般的なプログラムでもワークフロー的なアプリでは使うテクニックです。複数のフラグを用意してそれぞれのシーンで活用するので、シート設計上こういった元のシートには無い「アプリケーションならではのフラグ列」を追加する必要があります。
AppSheet、GAS共にこれらのテクニックは必要です。
ロジックの為のスプシ設計
これはアプリ初心者には難しい内容ですが、プログラムならではのシート設計というものも存在します。前述のフラグの設計にも通じるものですが、よりプログラマティックな目的に使われます。
- 一時的な中間作業の為に利用される作業用シート
- ワークフローの承認経路を格納した自身の承認者リスト用シート
- コードでフィルタ等せず、Filter関数やQuery関数でフィルタした内容を格納するシート
- 特定のライブラリで利用するデータ形式用に整形したシート（例：以下のエントリーのVis.js用に整形など）
これらのシートデータをさらにプログラムにて、加工したりクラスにて処理をしたりすることに繋げることになります。こうした作業テーブルの為のシート設計も時として必要になりますが、その多くは特有の設計になるため、自身の経験やスキルによって難易度が変わってきます。
データの書き込みは手動禁止に
入出力用のUIを用意出来、書き込みなどのロジックも装備出来ているのであるならば、スプシそのものに対するアクセス権限は「読み取り専用」として手動でデータの改変は出来なくするよう制御しましょう。
書き込み自体はデプロイした人の権限で動かすので、本人に権限が無くとも問題有りません。
この制御を行う理由は
- シート構造の改変を行われるリスクを低減する
- おかしなデータを手動で登録されるリスクを低減する
- 連番データなど重複した値を入れられるリスクを排除する。
- 他にコピーされたり、データが他の人に閲覧されるリスクを低減する。
こういった問題点を回避する為にも、スプシ自体はユーザの手の届かない権限分けを普段から行っておきましょう。
関連リンク
- Google Apps Script 開発マイルール（設計編）
- GASで本格的に開発するために考えたこと - システム設計
- [Asana API]スプレッドシート設計とGASの下準備
- 【初心者向け】GASでスプレッドシートのデータを基にドキュメントを自動生成する方法
- 3 時間で学ぶ Google Apps Script 入門 ～スプレッドシートと連携するアプリケーションを開発してみよう｜研修コースに参加してみた
- [GAS]オブジェクト指向によるGAS開発のススメ #2外部設計
- Excel方眼紙」と「神Excel」は違うのか？ Excel方眼紙の賛否を問う「Excel方眼紙公開討論会」実施［PR］
- 元・公務員が20年前に目撃した｢神エクセル｣がうまれた現場
- スプレッドシートの1000万セル制限の回避方法
- なぜExcel方眼紙は嫌われるのか？ 「Wordより使いやすい」「生産性を下げる」など賛否分かれる
