前回の記事では、現場職員がスマホから報告できる「ヒヤリハットフォーム」の作成方法を紹介しました。
フォームが完成すると、回答は自動でGoogleスプレッドシートに蓄積されていきます。
しかし、
「集まったデータはどう活用すればいいの？」
「Excelで集計や分析をしたいけれど、毎回ダウンロードしないといけないの？」
と感じる方もいるかもしれません。
Googleフォームとスプレッドシートだけでもデータの収集はできますが、集計や分析まで行おうとすると、Excelの方が使いやすい場面もあります。
そこで今回は、Googleフォームから集まったデータをExcelで活用できるように、GoogleスプレッドシートとExcelを連携する方法を紹介します。
一度設定しておけば、スプレッドシートの最新データをExcelから読み込めるようになり、毎回ファイルをダウンロードして差し替える手間を減らすことができます。
また、この設定は次回紹介する「Power Queryを使ったヒヤリハット分析」の土台にもなります。
まずは、Googleフォーム・スプレッドシート・Excelをつなぐ環境を整えていきましょう。
Googleフォームとスプレッドシートを連携させる
フォームの準備ができたら、次は回答データを自動でGoogleスプレッドシートに蓄積する仕組みを作ります。
「スプレッドシートを使ったことがないから難しそう……」
と思うかもしれませんが、この作業は数クリックで完了します。
Googleフォーム側に連携機能が用意されているため、特別な設定や知識は必要ありません。
それでは、実際にフォームとスプレッドシートを連携していきましょう。
【ステップ1】「回答」タブからスプレッドシートを作成する
まずは、先ほど作成したGoogleフォームの編集画面を開きます。
画面上部にある 「回答」 タブをクリックします。
次に、右上にある緑色の「スプレッドシートにリンク」アイコンをクリックしてください。
【ステップ2】「新しいスプレッドシートを作成」を選ぶ
スプレッドシートアイコンをクリックすると、「回答の作成先を選択」という画面が表示されます。
通常は「新しいスプレッドシートを作成」が選択された状態になっています。
初期状態では「ヒヤリハット・気づき報告フォーム（（回答）」 のように入っていますが、後から見ても分かりやすいよう、「ヒヤリハット管理台帳」などの名前に変更しておくと便利です。
名前を入力したら、右下の「作成」ボタンをクリックします。
【ステップ3】連携できているか確認しよう
スプレッドシートとの連携ができたら、本番運用を始める前に、実際にデータが送信されるかテストしておきましょう。
以下の手順で回答用URLを開き、テストデータを送信してみます。
- Googleフォーム画面の右上にある「リンク（🔗）」アイコンをクリックします。
- 「回答者へのリンクをコピー」画面で、「コピー」をクリックします。
- ブラウザで新しいタブを開き、コピーしたURLを貼り付けて開きます。
- 実際の回答画面が表示されるので、テスト用のデータを入力して「送信」をクリックします。
送信後、先ほど作成したスプレッドシートを開いてみましょう。
フォームから送信した内容が、自動で1行追加されていれば連携成功です。
本番スタート前に！テストデータの削除方法
テスト入力で、フォームからスプレッドシートへ正しくデータが送られることを確認できたら、本格的に運用を始める前に、一度テストデータを削除しておきましょう。
Googleフォームの場合、スプレッドシート上の行を削除するだけでは、フォーム側の回答データは残ったままになります。
そのため、データをリセットするときは、必ずGoogleフォーム側から削除してください。
- Googleフォームの「回答」タブを開く
- 右上の「︙（三点リーダー）」をクリックする
- 「すべての回答を削除」を選択する
※フォーム側で回答を削除すると、連携しているスプレッドシートのデータも自動的に削除されます。
これで、本番運用を始める準備が整いました。
なぜExcel×パワークエリ？スプレッドシートだけで完結させない理由
「そのままスプレッドシートでグラフを作れば、Excelに移さなくてもいいのでは？」
と思う方もいるかもしれません。
確かに、スプレッドシートだけでも簡単な集計やグラフ作成は可能です。
しかし、毎月継続してヒヤリハットを分析していくことを考えると、Excelのパワークエリを活用した方が管理しやすい場面もあります。
そこで今回は、私がスプレッドシートだけで完結させず、Excelとパワークエリを組み合わせている理由を2つ紹介します。
理由①：スプレッドシートは「収集」、Excelは「分析」が得意だから
スプレッドシートの最大の強みは、インターネットにつながっていれば、現場の誰もがどこからでもデータを入力できることです。
一方で、
- 曜日ごとに集計する
- 発生時間帯ごとに分析する
- 場所とトラブル内容を組み合わせて集計する
といった分析を行う場合は、Excelの方が使いやすい場面も多くあります。
特にピボットテーブルやグラフ作成は、Excelの方が直感的に操作できるため、分析作業がスムーズに進みます。
そのため、
- データを集める → スプレッドシート
- データを分析する → Excel
というように役割を分けることで、それぞれの強みを活かした運用ができます。
理由②：パワークエリなら毎月の集計作業を大幅に減らせるから
「Excelで自動化」と聞くと、マクロ（VBA）を使った難しいプログラミングを思い浮かべる方もいるかもしれません。
しかし、今回使用するパワークエリ（Power Query）は、基本的に画面上の操作だけで設定できます。
一度設定してしまえば、翌月からはExcelを開いて「すべて更新」ボタンをクリックするだけ。最新のデータが取り込まれ、集計結果やグラフも更新されます。
スプレッドシートとExcelを連携する
ここからは、GoogleスプレッドシートのデータをExcelで読み込むための設定を行います。
「連携」と聞くと難しそうに感じるかもしれませんが、やることはそれほど多くありません。
基本的には、スプレッドシートのURLを取得し、そのURLをExcelに設定するだけです。
一度設定しておけば、以降はExcelからスプレッドシートの最新データを取得できるようになります。
【ステップ1】スプレッドシートの共有リンクをコピーする
まずは、Excelから読み込むためのスプレッドシートのリンクを取得します。
Googleスプレッドシートにはさまざまな共有方法がありますが、Power Queryで読み込むためには共有設定を変更する必要があります。
「制限付き」のままではデータを取得できないため、今回はExcelから読み込める設定に変更していきます。
そのため今回は、「リンクを知っている全員（閲覧者）」 の設定を利用して連携していきます。
- 連携したいGoogleスプレッドシートを開きます。
- 画面右上にある緑色の 「共有」 ボタンをクリックします。
- 「一般的なアクセス」 の設定を 「リンクを知っている全員」 に変更します。
- 右側の権限が 「閲覧者」 になっていることを確認します。
※「編集者」になっていると、リンクを知っている人が内容を変更できてしまうため注意してください。 - 「リンクをコピー」 をクリックし、メモ帳などに貼り付けます。
- コピーしたURLの末尾にある
edit?usp=sharingをexport?format=xlsxに書き換える
これで、スプレッドシートのデータをExcelから読み込むためのURLの準備は完了です。
次のステップでは、このURLをExcelに設定していきます。
【ステップ2】Excelを開き、「Webから」を選択する
続いて、Excel側でスプレッドシートのデータを読み込む設定を行います。
- パソコンで新しいExcel（空のブック）を開く
- 画面上部の 「データ」 タブをクリックする
- 「データの取得」→「Webから」 をクリックする
（Excelのバージョンによって表示が多少異なる場合があります）
小さな入力画面が表示されるので、先ほどコピーしたURLを貼り付けて 「OK」 をクリックします。
URLを入力すると、Excelがスプレッドシートのデータを確認し、「ナビゲーター」画面が表示されます。
【ステップ3】「ナビゲーター」画面で「データの変換」を選択する
URLの接続が成功すると、「ナビゲーター」という画面が表示されます。
これは、Excelが「どのデータを取り込みますか？」と確認している画面です。
画面左側に表示されるスプレッドシートのシート名（例：「フォームの回答 1」）をクリックします。
クリックすると、画面右側にデータの内容がプレビュー表示されます。
内容に問題がないことを確認したら、画面右下にある 「データの変換」 をクリックしてください。
ここでは「読み込み」ではなく「データの変換」を選択します
「読み込み」を選ぶと、データがそのままExcelシートへ取り込まれます。
今回は次回以降の集計や分析で活用しやすい形に整えるため、「データの変換」を選択してPower Queryを開きます。
「データの変換」をクリックすると、Power Query エディターが起動します。
最初は見慣れない画面かもしれませんが、これが今後データの集計や分析で活躍する Power Query の画面です。
画面上部に「発生日」や「発生場所」など、Googleフォームで作成した項目が表示されていれば、GoogleスプレッドシートのデータをExcelで正しく読み込めています。
まとめ
今回は、Googleフォームに集まったヒヤリハットデータを、Excelで活用するための連携方法を紹介しました。
ポイントを振り返ると、
- Googleフォームの回答はGoogleスプレッドシートへ自動で蓄積される
- スプレッドシートのデータはExcelから読み込める
- Power Queryを使うことで、毎回データをダウンロードし直さなくても最新データを活用できる
という3点です。
ここまで設定できれば、Googleフォームで集めたデータをExcelで分析するための準備は完了です。
次回は、今回接続したPower Queryを使って、ヒヤリハットデータを集計しやすい形に整える方法を紹介します。