Get Last 10 Rows From Google Sheets Excel VBA Macro

In a previous post we saw how to import specific data from Google Sheets with Excel VBA. In this post we see a variation of the method to get the last ten rows with data in a Google spreadsheet. First we send a HTTP request to Google Sheets to get the data. Then we loop through the HTML response to target the values in the last ten rows only.

Macro/VBA code:

 
  Sub GetLast10RowsGoogleSheets()
      Dim HTTPreq As Object, url As String, sheetID As String, HTML As Object
      Dim tableLength As Long, r1 As Integer, r2 As Integer, tr As Object, td As Object
 
      Set HTTPreq = CreateObject("MSXML2.ServerXMLHTTP.6.0")
      sheetID = "1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI"
      url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & sheetID
 
      'send HTTP request to get the data
      With HTTPreq
          .Open "GET", url, False
          .Send
      End With
      Do Until HTTPreq.readyState = 4: Loop
 
      'set HTML variable and assign response
      Set HTML = CreateObject("htmlFile")
      HTML.body.innerHTML = HTTPreq.responseText
 
      ActiveSheet.Cells.ClearContents
      tableLength = HTML.getElementsByTagName("tr").Length
 
      'loop through HTML table to get values
      For Each tr In HTML.getElementsByTagName("tr")
          If tr.getElementsByTagName("td").Length > 0 Then
              r1 = r1 + 1
              If r1 = 1 Or r1 > tableLength - 10 Then
                  r2 = r2 + 1
                  For Each td In tr.getElementsByTagName("td")
                      c = c + 1
                      Cells(r2, c).Value = td.innerText
                  Next td
              End If
              c = 0
          End If
      Next tr
  End Sub
 

 

Macro explained:

• First of all, we need to add the Microsoft XML v6.0 and Microsoft HTML Object libraries to the VBA project under Tools -> References in the Visual Basic editor.
• We declare HTTPreq as an object variable to later create and assign a ServerXML object to it (late binding). We do the same with the HTML object variable. We also declare some other variables to store the Google spreadsheet ID or key, the Google Sheets web page URL, and the row and column index variables used while looping through the response.
• The Google spreadsheet ID or key can be found after “/d/” and before the following forward slash in the Google Sheets sharing link (see below).

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

• Note that the Google spreadsheet 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.

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

• Now we open an HTTP connection request using three arguments: The request method, in this case is a GET request, the target web page URL, and a third parameter indicating whether the call is asynchronous (False). After that, we send the request. The property readyState equals 4 when the request has successfully completed.
• Then we create a HTML document variable and define the innerHTML of the body as the HTTP response text. At this point, we can navigate the HTML table and get the values. For that we get the HTML elements by table row tags (tr) and then table cell tags (td).
• We use the variable r1 to track rows in the Google spreadsheet, while r2 defines the row index in the Excel sheet. The variable c defines the column index in the Excel sheet.
• A condition is added before the second loop to target only the last ten rows and the very first row (the headers). When the condition is met, the second loop gets the data for each column and adds that to each cell in the corresponding row of the Excel worksheet. 

This is how we get the last 10 rows from Google Sheets in Excel with VBA macros.

Other macros:

• How to Convert Range To Table with Excel VBA macros
• How to make Function Return Array with Excel VBA macros
• How to launch a Color Picker Dialog with Excel VBA macros