2018年8月28日火曜日

スプレッドシートを使ってスマートなワークフローを作ろう(2)

先日の記事でスプレッドシートと親子リストを使って、申請者情報を自動表示する方法をご紹介しました。
今回は、同じくスプレッドシートを使って、社員番号から所属部署やユーザー名・メールアドレスを自動的に表示させる方法をご紹介いたします。

◯スプレッドシートを準備する

今回は「親子テキスト」を使います。親子テキストとは親項目がテキストになっており、入力された内容に該当するものがあった場合は、子項目以下を表示するというものです。前回ご紹介したオーダーシートは1列目にメールアドレスが入力されていましたが、今回は親項目が社員番号になります。そこで、オーダーシートのデータを参照して、列を入れ替えたスプレッドシートを別に用意します。

◯IMPORTRANGE関数を使う

オーダーシートのデータはグループキャッシュに使用するため、列の入れ替えはできません。そこで、IMPORTRANGE関数を使います。IMPORTRANGE関数は別のシートの情報を参照し、対象データを表示するものです。
IMPORTRANGE
新規のスプレッドシートを作成します。
シート1のA1にIMPORTRANGE関数を入力します。第一引数にスプレッドシートのURL、第二引数にシート名・列を入力します。
初回のみ、「#REF!」になりますが、メッセージの「アクセスを許可」をクリックするとデータが表示されます。

◯QUERY関数を使う

親子テキストの親項目は社員番号ですので、マスターとして使うにはA列に社員番号が無いといけません。そこで、列の入れ替えのためにQUERY関数を使います。QUERY関数は参照元のデータを条件に見合った形で表示します。
QUERY
上記のシートに新しいシートを追加します。
シート2のA1にQUERY関数を入力します。第一引数に参照元のデータ(シート名・列)、第二引数にクエリ文を入力します。クエリ分は特に条件は無いので、Selectに続いて、表示したい列名を順に入力します。D列=社員番号が先頭に、他は元の並びで表示してほしいので、「D,A,B,C,E,F」となります。

◯マスターデータは1シート目を参照する

1シート目がIMPORTRANGEでデータ出力されたシート、2シート目がQUERYでデータ出力されたシートになっています。親子テキストのマスターデータは1シート目を参照しますので、QUERYでデータ出力された2シート目を1シート目に移動します。

◯親子テキストのマスターデータにスプレッドシートを使う

タイプが「親子テキスト」の入力フォームを作成します。選択し設定で「スプレッドシートで定義」を選択し、スプレッドシートを選択します。今回は「ログインユーザーでフィルター」のチェックはオフのままです。
これにより社員番号を入力し、一致するものがあれば、社員情報を表示します。参照元のデータはオーダーシートなので、社員番号用のスプレッドシートはメンテナンスが不要です。
※最初の画像は入力フォーム用のドキュメントを作成しています。

いかがでしたでしょうか。このように社員番号等からデータを参照する場合はこの方法を使ってみてください。