GoogleスプレッドシートをExcelに自動連携する方法。

Excel

GoogleスプレッドシートのシートデータをExcelに自動連携させることができます。

その方法をまとめてみました。

Googleスプレッドシート→Excelへインポート

 

今回の事例は、セルフマガジンの申し込みフォーム

HPのフォームから申し込みがあった場合には、フォームへの入力データがGoogleスプレッドシートに反映されます。

Contact Form 7とGoogleスプレッドシートを連携して郵送先住所をシートにまとめる方法。(郵便番号自動検索付き) – GO for IT 〜 税理士 植村 豪 Official Blog

GOforIT 6

そのデータからExcelでマクロを使って宛名ラベルを作成しています。

Googleスプレッドシート→Excel連携。Excelマクロでセルフマガジンを郵送する宛名ラベルをつくってみた。 – GO for IT 〜 税理士 植村 豪 Official Blog

 

全体の流れとしてはこういったものです。

  1. HPのセルフマガジンのフォームから申し込み
  2. Googleスプレッドシートにデータが自動追加される
  3. GoogleスプレッドシートのデータをCSVファイルでダウンロード
  4. Excelを起動しダウンロードしたCSVファイルを読み込む
  5. Excelでマクロを動かしてラベル作成

 

現状のやり方は、

  • Googleスプレッドシートを開く
  • GoogleスプレッドシートのデータをCSVファイルでダウンロードする
  • ExcelでCSVファイルを読み込む

という操作が必要になります。

 

今回は、上記の処理をすることなく、Excelファイルを開いたときにGoogleスプレッドシートのデータを自動で反映させるという方法です。

 

  1. HPのセルフマガジンのフォームから申し込み
  2. Googleスプレッドシートにデータが自動追加される
  3. Excelを起動しGoogleスプレッドシートのデータが自動反映
  4. Excelでマクロを動かしてラベル作成

 

 

GoogleスプレッドシートのデータをExcelに自動連携

必要な処理は、Googleスプレッドシートのリンクの取得とExcelのWebリンクの設定です。

Googleスプレッドシートの共有リンク

Googleスプレッドシートを開き、右上の共有ボタンをクリックし、

GOforIT 5

「リンクを取得」の「リンクを知っている全員に変更」をクリックします。

すると、リンクが表示されます。このリンクをコピーしましょう。

GOforIT 9

 

Googleスプレッドシートではこのリンクがカギを握ります。
ただし、後述するようにこのままではExcel側で読み込むことができません。

「https://docs.google.com/spreadsheets/d/1k_5YULc…zJ8uHr3a4y-6g3haRHywvA/edit?usp=sharing」

このリンクをいったん適当な場所にコピーし、リンクアドレスの最後部「edit?usp=sharing」を「export?format=xlsx」と変更しておきましょう。

 

Excelでデータクエリの設定

次に利用したいExcelを起動します。

今回の場合には、セルフマガジンのラベルをつくるExcelマクロファイルを。シート「data」にGoogleスプレッドシートのデータを反映させたいです。

GOforIT 7

ここで先程、Googleスプレッドシート側でコピーしたリンクを使います。

「データ」→「Webから」とクリックすると、

GOforIT

URLの入力欄が表示されます。

GOforIT

Googleスプレッドシートの共有リンクをそのままコピペすると、テキストは「null」となり表示されません。

GOforIT 2

そこで前述したように、リンクアドレスの最後部を「edit?usp=sharing」から「export?format=xlsx
」と変更したURLを貼り付けます。

GOforIT 3

 

すると、シート1のGoogleスプレッドシートのデータが反映されているのがわかります。
GOforIT 11

ただし、1行目のヘッダー部分が別でできてしまうため、ここを修正して読み込む必要があります。右下の「データの変換」をクリックしましょう。

 

真ん中あたりの「1行目をヘッダーとして使用」をクリックすれば、ヘッダーの調整ができます。そのまま「閉じて読み込む」を。

GOforIT 12

Webクエリが設定されて、データが読み込まれました。

GOforIT

Googleスプレッドシート自動反映の設定変更

この時点ではまだ、Googleスプレッドシートのデータを直接読み込んだだけです。

自動反映をさせるためには、設定を変更しましょう。

クエリにカーソルを合わせて、右クリックでプロパティを選び、

GOforIT 14

「定期的に更新する」と「ファイルを開くときにデータを更新する」などにチェックを入れておきましょう。

GOforIT 4

「定期的に更新する」にチェックを入れておくことで、指定の時間になれば自動で最新データに更新されますし、

データが前回使用したままになっている場合でも、あらかじめ「ファイルを開くときにデータを更新する」にチェックを入れておけば、
GOforIT 16

 

数秒後には、Googleスプレッドシートからの最新データが反映されます。

GOforIT 17

 

そのあとにマクロを動かせば、ラベルをつくることができます。

GOforIT 8

ラベルをつくるマクロはこちらの記事にある内容です。

Googleスプレッドシート→Excel連携。Excelマクロでセルフマガジンを郵送する宛名ラベルをつくってみた。 – GO for IT 〜 税理士 植村 豪 Official Blog

これでGoogleスプレッドシートにアクセスすることなく、Excelファイルを開けばラベルをつくれるよになりました。

 

なお、今回のラベルをつくる設定を反映させたマクロはこちらです。

 

手数を減らすために、連携できるものはしくみをつくっておきましょう。
参考にしていただければ。


【編集後記】
昨日はオフ。朝に長男(7)とサッカーの練習。夕方にドラクエのコラボ中ということで家族で久しぶりにサーティワンでアイスを。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
スタバ 松栄店
サーティワン ぷにっとスライムホイミ味