見出し画像

Googleフォーム + スプレッドシート + GASで各種申請をペーパーレス化

中西 進之輔 | チトセ工業 三代目

はじめに

こんにちは、チトセ工業の中西 (@shnakan1sh1) です。
ここ最近は前にも増して、社内のデジタル化・ペーパーレス化を推し進めています。
今回は従来は紙媒体で行ってきた各種申請まわりをGoogleフォーム + スプレッドシートの連携に移行したやり方なんかをまとめようと思います。

移行したもの

今回それぞれ同様の手法で、下記の申請をデジタル化しました。

  • 勤怠申請: 遅刻・早退, 有給休暇, 休日出勤, 残業

  • 社長決済申請

  • 出張申請

  • 改善提案

など。

Googleフォームのフォーマットに従って入力、送信すると
自動的にスプレッドシートに回答内容が反映される仕組みです

移行の背景

中小企業もデジタル変革の必要性に迫られる昨今。
もはやDXの字面を目にしない日はないかもしれません。
僕自身は「アナログは全て悪、デジタル化することこそが正義」とまではいいませんが、割と自分自身の仕事環境も含めてなるべくデジタル化してるほうだと思います。
もともとの僕の性格としては新しいものとかに飛びつくタイプではないんですが、まあ、またその話は近日中にまとめます。

とりあえずなんでもかんでもデジタル化すればいいってものでもなくて、デジタル化の先のメリットを見据えないといけません。
これは常々意識しておかないと、落とし穴にはまってしまいがちかなと。

今回の申請まわりのペーパーレス化はデジタルの強みが活かせる部分かなと思って取り組んでみました。
これまでわざわざ申請用紙を会社からもらってこないといけませんでしたが、デジタル化することでスマホさえあればで手軽に申請できるようになりました。
管理する側の手間も削減できたと思います。

ネットで調べながらやれば、専門知識がなくてもそんなに時間もかけずにできたのでよかったら参考にしてください。
僕も素人なので、体当たりで作りました。
僕が作成する中でぶつかった課題と解決方法、という切り口で書いていきます。
ちなみにツールに費用はかけてません。

フォーム作成と連携

フォームの作成

さっそくやっていきましょう。
まずは大元となるGoogleフォームを作成します。
Googleドライブにアクセスして、新規ボタンからフォームを新しく作成します。

質問タブで必要に応じて質問項目やセクションを設定し、設定タブで各種設定を行います。

スプレッドシートとの連携

Googleフォームが作れたら、そのフォームへの回答を送信・集計するスプレッドシートとの連携を行います。
作成したフォームの回答タブに移動し、 "スプレッドシートにリンク" をクリック。
回答の送信先は "新しいスプレッドシートを作成" を選択します。

すると、自動的にスプレッドシートが作成され、画面が遷移します。
作成されたシートの一行目には先ほどGoogleフォームで設定した質問項目が入力されており、フォームに送信された回答は自動的にこのスプレッドシートの2行目以降に反映されます。

これでGoogleフォームとスプレッドシートの連携は完了なのですが、実際に運用していくにはもうひと工夫必要です。

そのままだと見にくい問題

確認用シートの作成と編集

まず、なんといっても連携しただけのシートでは見にくいです。

反映されたデータをそのまま表示したシート

また、反映されたデータになにか後処理 (確認済みのマーキングなど) をしたい時にもデータの行がずれてしまったりするのでなにかと不便です。
そこで、データが飛んでくるシート (デフォルトでは "フォームの回答 1" という名前) とは別に、データの確認・操作を行うシート (以下、確認用シート) を新たに作成します。

見やすいよう新たに作成したシート

このシートに色々と工夫をしていきます。
必要な項目だけ表示させたり、交互の背景色を設定したり。。。

しかし、まずは "フォームの回答 1" の内容が反映されるようにしなくてはいけません。

行がずれる問題

ここで "='フォームの回答 1'!$A1" という感じで指定してももちろん内容は反映されるのですが、これだとダメです。
フォームに回答が送信される度に新たな行が挿入されてしまい、表示がどんどんずれていってしまいます。
何を言ってるか伝わらないかもしれませんが、一先ず上記とは別の方法でセルを参照します。

確認用シートの操作

  1. A列に新たに一列挿入します。
    名前はなんでもいいです。
    適当に "行番号" としておきます。

  2. A2以降に2から順番に連続する整数を入れていきます。

  3. A列を非表示にします。

  4. B2に "=INDEX('フォームの回答 1'!A:A,$A2)" と入力します。

  5. 右方向、下方向にそれぞれオートフィルします。
    セル右下の少し大きめの四角をぐ〜っとドラッグするとできます。

こうすることで、 "フォームの回答 1" に飛んできたデータを自動的に確認用シートに転記することができるようになりました。
あとは確認用の列を追加したり、必要や用途に応じてカスタマイズしてください。

またしても見にくい問題

回答の種類に応じてシートを分けたい場合

通常であればここまででスプレッドシートの編集は終わりです。
ただ今回勤怠申請を管理するにあたり、回答の内容に応じて表示させる場所を指定したいと思いました。
というか、でないと見にくくてやってられません。

勤怠に関する申請は全てひとつのGoogleフォームから送信され、スプレッドシートに蓄積されていきます。
申請内容が有休だろうが残業だろうが、全部上から順に並んでいくわけです。
うん、めちゃくちゃ見にくい。

しかも申請内容によって質問項目も異なります。
(これはGoogleフォーム側の設定で、回答の内容に応じて個別のセクションに移動するようにしています)

選ぶ内容によってそれぞれ質問項目の異なるページに移動します

たとえば、有休の申請の場合はその期間の情報が要ります。
いつからいつまで休む、ということですね。
でも遅刻や早退の場合、期間は必要ありません。
ある時点の時刻 (=遅刻 or 早退した時刻) さえ分かれば十分です。
必要のない情報は表示させないようにしたい。
ということで、それぞれ別個のシートを作成していきます。

実際の作業としては簡単です。

  1. 上記 "確認用シートの作成と編集" と同様の手順で確認用シートを作成します。

  2. 必要のない項目を非表示にします。

  3.  "データ" タブからフィルタを作成し、抽出したい項目を選択します。

これだけです。
これを必要な分だけ新たにシートを作成し、フィルタ対象を変えることで回答の内容に応じてそれぞれ見やすく管理することができます。

ただし、ここまでの処理ではフィルタの自動更新が行われないため、毎回フィルタを設定し直さなければなりません。
そんな面倒なことイヤな方は下記のGASの部分も参照してください。

他にも方法はあります

もちろん、Googleフォームをあらかじめ複数作成しておいて、それらを全て1つのスプレッドシートに送信してもほぼ同じことはできます。
でも申請する側からすると、フォームが一個にまとまっている方がわかりやすいかなと。
さらに、もっとシンプルに申請の種類ごとにそれぞれフォームとスプレッドシートを用意してもいいですが、そうなると管理側の手間が増えてしまってなんのためにデジタル化するのかよく分からん事態に陥りますよね。

管理者にしか通知されない問題

複数人に通知したい

さて、フォームの設定もできてスプレッドシートも最適化できました。
これでOK！と言いたいところですがもうひとつ問題があります。
申請があったらすぐ通知してほしいですよね？

ということでさっそく設定しましょう。
フォームの "回答" タブにある点々マークをクリックして、 "新しい回答についてのメール通知を受け取る" にチェックを入れます。
これで誰かがフォームに回答するとメールで通知してくれます。

ただ、この方法だとフォームの管理者にしか通知されないのです。
会社で使用する場合、複数人に通知したい場合がほとんどだと思います。
それを実現する方法も何通りかあると思いますが、今回僕はGAS (: Google Apps Script) を使って管理者以外の関係者にもメール通知が届くようにしました。

Google Apps Script の設定

また新しい名前が出てきて大変ですが、あと一息です。
頑張りましょう。
まずはフォームの画面右側の点々から "スクリプトエディタ" をクリックします。

この時、複数のGoogleアカウントにログインしている場合はご注意ください。
フォームの管理者であるGoogleアカウントがデフォルトアカウントでない場合、エラーとなってGASの画面が表示されないと思います。
うまくいかないときは、一度すべてのGoogleアカウントからログアウトしてください。
そして再度フォームの管理者であるGoogleアカウントでログインすれば、そのアカウントがデフォルトアカウントに設定されます。
(これでも解決しなければすみません)

GASにアクセスできたら、"コード.gs" というタブが表示されていると思います。
プログラミングなんて分かりません！という方もご安心下さい。
僕もです。
既にちょこっと内容が入っていると思いますが、それは削除したうえで何も考えず下記コードをコピー&ペーストしてください。

function sendform(e){
  var items = e.response.getItemResponses();
  var msg = '';
  for (var i = 0; i < items.length; i++) {
    var item = items[i];
    var q = item.getItem().getTitle();
    var a = item.getResponse();
    msg += q + ': ' + a + '\n\n';
  }
  GmailApp.sendEmail('メールアドレス', 'メールのタイトル', msg);
}

コピーしたら、下から2行目のメールアドレスとメールのタイトルを書き換えてください。
ここで入力したメールアドレスに通知されます。
メールアドレスはカンマ (,) で区切ることで複数設定できます。
その場合は'メールアドレス1, メールアドレス2, メールアドレス3,' といった要領です。

ここまでできたら上部のフロッピーのアイコンをクリックして保存します。
これで "フォームが送信されたら指定のアドレスにメールを送る" プログラムができました。
そのあとは一番左のカラムの時計マーク = トリガーを選択してください。
今しがた頑張って作ったプログラムを起動するためのトリガーを設定していきます。

画面右下からトリガーを作成し、画像の通り項目を選択します。

トリガーを保存しようとすると、下記のようなポップアップが出てきます。
① → ② の順番でクリックして承認してください。

これでトリガーの設定も完了です。
実際にフォームに回答を送信して、スプレッドシートに正しく反映されているか、関係者にメールが送信されているかをテストしましょう。

フィルタを自動更新する

上記 "回答の種類に応じてシートを分けたい場合" で各シートにフィルタを設定した場合は、あともうひと踏ん張り必要です。
"フォームが送信されるたびにフィルタを更新する" プログラムによって常に最新の結果が表示されるようにしましょう。
本当にこれで最後ですので。。。

先ほどまではフォームに紐づけたGASを作成してきましたが、今回はスプレッドシートに紐づけます。
スプレッドシートの "拡張機能" タブからApps Scriptを選択します。

あとは先ほどと同じ要領です。
下記コードを貼り付けて、トリガーを設定します。
今回はコードはそのままコピペでOKです。

// onOpenでスプレッドシートが開かれる度に実行
function onOpen() {
  // すべてのシートを取得
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (const sheet of sheets) {
    // フィルタ取得
    const filter = sheet.getFilter();
    if (!filter) {
      continue;
    }
    // フィルタを同じ内容で更新
    const col = filter.getRange().getColumn();
    const criteria = filter.getColumnFilterCriteria(col);
    filter.setColumnFilterCriteria(col, criteria); 
  }
}

続いてトリガー設定。
画像の通り設定してください。

おわりに

お疲れ様でした。
システムが完成したらリンクを共有するなりして周知しましょう。
うちはMicrosoft Teamsを使っているので、そこにタブを作って埋め込んでます。

Microsoft Teams上から各種申請が可能

今回紹介した以外に、もっといい方法もあるかもしれません。
詳しい人は逆に教えてください。
ともあれ、この記事が手軽にはじめるデジタル化の助けになれば幸いです。
最後まで読んでいただきありがとうございました。

いいなと思ったら応援しよう！