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

2022年5月8日

URLをコピーしました！

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

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

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

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

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

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

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

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

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

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

現状のやり方は、

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

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

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

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

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

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

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

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

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

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

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スプレッドシートのデータを反映させたいです。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Option Explicit  Sub selfmagazine()           Application.DisplayAlerts = False          '■郵送済データの削除          Dim Send_row     Send_row = ThisWorkbook.Worksheets("send-data").Range("a" & Rows.Count).End(xlUp).Row          If Send_row > 2 Then     Worksheets("data").Rows("2:" & Send_row).Delete     End If      '■新規の郵送件数の確認          Dim max_row     max_row = Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row               '■郵送ラベルの作成           Worksheets("master").Copy               Dim i     For i = 2 To max_row         If i <> 2 Then             ActiveSheet.Copy after:=Worksheets(Worksheets.Count)         End If                  '郵便番号         Worksheets(i - 1).Range("a2").Value = ThisWorkbook.Worksheets("data").Range("c" & i).Value         '都道府県         Worksheets(i - 1).Range("a3").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value         '住所         Worksheets(i - 1).Range("a4").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value & ThisWorkbook.Worksheets("data").Range("f" & i).Value         'アパート         Worksheets(i - 1).Range("a5").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value         '氏名         Worksheets(i - 1).Range("a6").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value & " 様"          Next               '■PDFファイルで出力          Worksheets.Select          ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\Mac\0 inbox\selfmagazine.pdf"             ActiveWorkbook.Close           '■郵送済みデータへコピー                Worksheets("data").Range("h2:h" & max_row) = Date       Worksheets("data").Range("h2:h" & max_row).NumberFormatLocal = "yyyy/m/d"          Worksheets("data").Rows("2:" & max_row).Copy ThisWorkbook.Worksheets("send-data").Range("a" & Send_row + 1)                             '■ファイルを閉じる     ActiveWorkbook.Close savechanges:=True               Application.DisplayAlerts = True    End Sub

  Option Explicit  Sub selfmagazine()     Application.DisplayAlerts = False       '■郵送済データの削除    Dim Send_row  Send_row = ThisWorkbook.Worksheets("send-data").Range("a" & Rows.Count).End(xlUp).Row    If Send_row > 2 Then  Worksheets("data").Rows("2:" & Send_row).Delete  End If   '■新規の郵送件数の確認    Dim max_row  max_row = Worksheets("data").Range("a" & Rows.Count).End(xlUp).Row      '■郵送ラベルの作成     Worksheets("master").Copy      Dim i  For i = 2 To max_row  If i <> 2 Then  ActiveSheet.Copy after:=Worksheets(Worksheets.Count)  End If    '郵便番号  Worksheets(i - 1).Range("a2").Value = ThisWorkbook.Worksheets("data").Range("c" & i).Value  '都道府県  Worksheets(i - 1).Range("a3").Value = ThisWorkbook.Worksheets("data").Range("d" & i).Value  '住所  Worksheets(i - 1).Range("a4").Value = ThisWorkbook.Worksheets("data").Range("e" & i).Value & ThisWorkbook.Worksheets("data").Range("f" & i).Value  'アパート  Worksheets(i - 1).Range("a5").Value = ThisWorkbook.Worksheets("data").Range("g" & i).Value  '氏名  Worksheets(i - 1).Range("a6").Value = ThisWorkbook.Worksheets("data").Range("a" & i).Value & " 様"    Next      '■PDFファイルで出力    Worksheets.Select    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\Mac\0 inbox\selfmagazine.pdf"       ActiveWorkbook.Close     '■郵送済みデータへコピー    Worksheets("data").Range("h2:h" & max_row) = Date  Worksheets("data").Range("h2:h" & max_row).NumberFormatLocal = "yyyy/m/d"    Worksheets("data").Rows("2:" & max_row).Copy ThisWorkbook.Worksheets("send-data").Range("a" & Send_row + 1)          '■ファイルを閉じる  ActiveWorkbook.Close savechanges:=True     Application.DisplayAlerts = True    End Sub   

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

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

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