結論

はじめに

Google Sheetsは表データを共有して管理するための優れたツールです。
運用面の利点として、たとえば

• データを一か所に間違いなく集約することができる
• アクセス権もgoogleアカウントに応じて柔軟に設定できる
• 複数ユーザが同時に編集することもできる
• どのユーザがどの値を編集したのか履歴が残る
• ロールバックも自由にできる
• 多くのAPIが提供されていてデータの再利用性が高い

などがあり、ファイルで管理するExcelよりも簡単に運用できます。

とはいえレポートを作成するときには伝統的なExcelで作成せよという場面もまだまだ残っています。
このとき、レポートはExcelで作る、ただし元データはGoogle Sheetsに持ったままでExcelはGoogle Sheetsとデータ接続されている、という構成にしておくことで、データ管理はGoogle Sheetsで行いながら、Excelではレポートをそのまま更新できます。美しい。

問題はどうやってExcelでGoogle Sheetsとデータ接続するかです。
手段はいくつかありますが、たとえばwebクエリを使う方法はきれいに取得できなかったり、データ接続に失敗したら内容が消えてしまったりと悩みが多かったです。
色々試行錯誤した結果、私はExcelでGoogle Sheetsに接続するベストな方法は

Google Sheetsのweb公開⇒xmlデータインポート

だと判断しました。
この手順について説明します。
なお私の環境はExcel2010です。

接続方法

まずGoogle Sheetsを『webページとして公開』します。

１．ファイルからwebページとして公開を選択

２．『公開』ボタンを押します。

３．公開したらExcelを選択してURLをコピーします

次にExcelを使ってweb公開されているGoogle Sheetsを読み込みます。

４．『データ』リボンから『その他のデータソース』『XML データ インポート』を選択します。

５．『データファイルの選択』という窓が開くのでファイル名を入力する欄に先ほどコピーしたURLを貼り付けて『開く』。

『テーブルの選択』という窓が出ると接続成功です。接続するまで少し時間がかかります。
読み込みたいシート名を選択してください。

もしGoogle Sheetsへのアクセスがコントロールされていれば、step5が完了せずにブラウザが開きログイン画面が出てくるかもしれません。
その場合はその窓を使ってログインしてください。
『ファイルのダウンロード』という窓が開くかもしれませんが『開く』にしてもExcelがローカルに保存されるだけなのでキャンセルしてブラウザを閉じます。
そしてもう一度step4から『XMLデータインポート』をやり直すと成功します。

以上の手順でGoogle Sheetsの表データをExcelに取り込めたはずです。
ExcelとGoogle Sheetsとの接続が確立されているため、テーブルを右クリックして『更新』するだけで最新のGoogle Sheetsの値にデータ更新されるようになりました。

まとめ

XMLデータインポートによりExcelでGoogle Sheetsとデータ接続する方法を説明しました。
なお、用いる公開アドレスをcsvにしてテキストファイルとして接続すれば、読み込む書式を設定しながらテーブルを読み込むことができます。
場合によってはこちらのほうが良いかもしれません。

参考サイト