Import Data From Google Sheets Excel VBA Macro

In this post we see how to import data from Google Sheets in Excel using VBA macros. The macro uses Excel’s web query functionality to import the Google spreadsheet as a query table. The principle is similar to what we have seen in a previous post (Add Web Query To Worksheet). The Google spreadsheet to be imported needs to be public and accessed through a modification of the original link along with the key of the spreadsheet.

Macro/VBA code:

 

  Sub QueryGoogleSheets()

      Dim qt As QueryTable, url As String, key As String, gid As String

 

      If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete

      ActiveSheet.Cells.Clear

 

      key = "1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI"

      url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key

 

      'if needs to include gid

      gid = "2009384141"

      url = url & "&gid=" & gid

 

      Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & url, _

      Destination:=Range("$A$1"))

 

      With qt

          .WebSelectionType = xlAllTables

          .WebFormatting = xlWebFormattingNone

          .Refresh

      End With

  End Sub

 

 

Macro explained:

• First we declare a variable (qt) as a QueryTable, and three String variables to hold the modified url, the key, and the gid, of the Google spreadsheet.
• We delete the first query table (if any), as the new query table will take that position, and then clear the cells in the active sheet.
• Then we get the Google spreadsheet key from the Google Sheets sharing link, which can be found after “/d/” and before the following forward slash. See the example below.

https://docs.google.com/spreadsheets/d/1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI/edit?usp=sharing

• The Google spreadsheet to be imported in Excel needs to be public and shared in Google Sheets settings (right-click the spreadsheet -> Share -> Get Link -> Anyone in the internet with this link can view)
• Then we add the key of the Google spreadsheet to the modified url shown below, which is the reference to the Google spreadsheet output as an HTML table (that’s the format we need to import it as a query table in Excel).

       url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key

• If necessary, we concatenate the gid along with that url. The gid is found in the browser address of the spreadsheet (see example below).

https://docs.google.com/spreadsheets/d/1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI/edit#gid=2009384141

• Now we add the web query to the active worksheet in Excel. That’s the destination parameter in the query (starting at cell A1). The query to import the Google spreadsheet in Excel is a URL connection.
• Finally, we select all the tables to be imported from Google Sheets to Excel with the WebSelectionType method of the query variable qt, and refresh to display the table.

This is how we import data from Google Sheets in Excel with VBA macros.

Other examples:

• How to Show Chart For Selected Row with Excel VBA macros
  
• How to Sort and Merge Groups In Table with Excel VBA macros
  
• How to Add Buttons To Delete Rows with Excel VBA macros