There are several ways to import data from Google Sheets into Excel. In this post we review five different methods to import data from Google Sheets using Excel VBA. One way or another, there is always a HTTP request to the Google web resource that connects with the Google spreadsheet. The main difference between the various methods presented here lies on the Google web resource/API URL used to access the data. Another important difference is the format of the output data. Some methods allow to get only public data, while other may also let import private or restricted data.
Google Spreadsheet
A Google spreadsheet has a
unique identifier or key that can easily be found in the sharing link after
“/d/” and before the next forward slash. See the example below (the spreadsheet
ID is highlighted in yellow).
https://docs.google.com/spreadsheets/d/1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI/edit?usp=sharing
The last portion of the URL
can have different forms though, but that’s not relevant when getting the
spreadsheet ID. However, when pasting that link on a browser, it usually takes
another form and shows the “gid” parameter, which identifies a particular sheet
within the spreadsheet. This is important when the Google spreadsheet has more
than one sheet in order to target a specific sheet. The below URL leads to the
same Google spreadsheet as above. The spreadsheet has just one sheet, which has
been assigned that gid number.
https://docs.google.com/spreadsheets/d/1WopmB1ExWSNul9sw9n7I9FEyquS4AyEugt_fKQrHJSI/view#gid=2009384141
A Google spreadsheet can be
public or private (restricted). The Google spreadsheet is public when shared
with anyone with the link. This can be done while logged to your Google account
under Share -> Anyone with the link
All methods can get data
from a public Google spreadsheet, while only some of them also allow to get
data from a restricted spreadsheet.
Method 1: Web Query
The easiest way to get data in tabular format from a web resource, such as a standard HTML table embedded in a web page or a range with data in a Google spreadsheet, is using a web query. The web query can be added manually or programmatically (see code below to add it with Excel VBA). We have already seen how to add a query table with Excel VBA in this other post: AddWeb Query To Worksheet.
Dim qt As QueryTable, url As String, key As String, gid As String
key = "1ZiB42UOZXdmVis0RQDM7FwMJNXf6DiSbuEjHVDTASfU" 'REPLACE!
url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key '& "&gid=" & gid
'Set a web query/connection to the URL
Set qt = Sheet1.QueryTables.Add(Connection:="URL;" & url, Destination:=Range("$A$1"))
'Format and add the query table
With qt
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.Refresh
End With
End Sub
The query table establishes a connection with
the modified Google URL shown below, which outputs the data in the Google spreadsheet as an HTML table (that’s the
format we need to import it as a query table in Excel).
https://spreadsheets.google.com/tq?tqx=out:html&key=sheetID
This method and the macro above are explained in
more detail in this other post: ImportData From Google Sheets
Method
2: Simple HTTP request
A
HTTP request is used to access a resource on a server. We can use Excel VBA to
send a HTTP request to get data from the web. For example, we can get data from
a web page, make an API call, or get data from Google Sheets. There are several
examples in this other blog.
Actually,
this is similar to what Excel is doing when creating a web query. Thus, the
same URL used in method 1 is targeted when sending a simple HTTP request to get
the data from Google Sheets in method 2.
https://spreadsheets.google.com/tq?tqx=out:html&key=sheetID
The macro below sends a HTTP request to get the data from Google Sheets. It works with the following two libraries, Microsoft XML v6.0 and Microsoft HTML Object, that need to be added to the VBA project under Tools -> References in the Visual Basic editor. A server XML HTTP object is created to send a GET request to the target URL. The code below is explained in detail in this other post: Import Specific Cells From Google Sheets
Sub GetGoogleSheets_HTMLTable()
Dim url As String, key As String, HTTPreq As Object
Dim HTML As Object, r As Integer, c As Integer
key = "1ZiB42UOZXdmVis0RQDM7FwMJNXf6DiSbuEjHVDTASfU" 'REPLACE!
url = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & key '& "&gid=" & gid
'Send HTTP request to get the data
Set HTTPreq = CreateObject("MSXML2.ServerXMLHTTP")
With HTTPreq
.Open "GET", url, False
.send
End With
Do Until HTTPreq.readyState = 4: Loop 'waits till request completes
'set HTML variable and assign response
Set HTML = CreateObject("htmlFile")
HTML.body.innerHTML = HTTPreq.responseText
'loop through HTML table to get values (add conditions depending on your criteria)
Sheet2.Activate
For Each tr In HTML.getElementsByTagName("tr")
If tr.getElementsByTagName("td").Length > 0 Then
r = r + 1
'If r <= 10 Then 'condition to get only first 10 rows
For Each td In tr.getElementsByTagName("td")
c = c + 1
Cells(r, c).Value = td.innerText
Next td
c = 0
'End If 'could add Else to Exit For or Sub when done
End If
Next tr
End Sub
The
second part of the macro loops through the HTTP response to extract the data.
This method allows to specifically target certain rows or columns, or just specific
cells, by setting relevant conditional statements within the loops. Find more
detailed information in this other post: Import Specific Cells From
Google Sheets
Method 3: Google Visualization API
This method consists of sending a HTTP request to make an API call to
the Google Visualization API. The API URL looks totally different than the URL
we have seen before, and it accepts a query parameter to target certain data in
Google Sheets.
https://docs.google.com/spreadsheets/d/sheetID/gviz/tq?tq=query
The query is written using Google query language, which is similar to
SQL. For example, to import all the data in the target sheet the query is
“Select *”. See below some other
examples:
query = "Select *" 'selects all the
data
query = "Select A" 'selects data in
column A
query = "Select A,B where B > 35" 'selects data in columns A and B, when the value of B is greater
than 35
query = "Select A,B where B > 35 limit 2" 'same as above but limiting the result to 2 values
The query needs to be encoded before added to the URL. We can achieve
that using the Excel worksheet function EncodeURL.
encodedQuery = WorksheetFunction.EncodeURL(query)
The
code used to send a HTTP request to the Google Visualization API is similar to
the one used in method 2 (that’s actually going to be similar across all the
other methods too). But in this case, the HTTP response comes in JSON format. See
below an example of the HTTP JSON response.
There
are several ways to extract the values from JSON data in Excel. A simple example
was covered in this other post: Connect To API Excel VBA Macro
(Part 2).
There is also a JSON-to-VBA converter available on GitHub and widely
documented/explained on the web and YouTube.
Additionally,
the JSON response from Google Visualization API has 47 initial characters that
need to be removed prior to extracting the data. See below the entire macro to
get data from Google Sheets using the Google Visualization API.
Sub GetGoogleSheets_gviz()
Dim url As String, key As String, query As String, HTTPreq As Object, response As String
key = "1ZiB42UOZXdmVis0RQDM7FwMJNXf6DiSbuEjHVDTASfU" 'REPLACE!
query = "Select *" 'selects all the data
encodedQuery = WorksheetFunction.EncodeURL(query)
url = "https://docs.google.com/spreadsheets/d/" & key & "/gviz/tq?tq=" & encodedQuery
'Send HTTTP request to get the data
With HTTPreq
.Open "GET", url, False
.send
End With
Do Until HTTPreq.readyState = 4: Loop
response = HTTPreq.responseText
response = Right(response, Len(response) - 47)
'ConvertJSONToRange(response)
End Sub
Method 4: Google Sheets API
This method sends a HTTP request to make a call
to Google Sheets API in the same way that we have seen for method 3. The API
URL is completely different though. Google Sheets API URL accepts parameters
for sheet name and data range (in A1 notation) and requires an API key to make
the call. You need to setup a project in Google cloud to get a valid API key. Note
that the “key” parameter refers to the API key in this URL (not the sheet ID).
https://sheets.googleapis.com/v4/spreadsheets/sheetID/values/Sheet1!A1?key=apikey
The HTTP request is similar to what we have seen
for methods 2 and 3. See below the entire code to import data in Excel using
Google Sheets API.
Sub GetGoogleSheets_sheetsAPI()
Dim url As String, sheetid As String, sheetname As String, APIkey As String
Dim HTTPreq As Object, response As String
sheetid = "1ZiB42UOZXdmVis0RQDM7FwMJNXf6DiSbuEjHVDTASfU" 'REPLACE!
sheetname = "Sheet1"
APIkey = "AIaCocm40d9Y699poc0b1nmDC39Lo…" 'REPLACE!
url = "https://sheets.googleapis.com/v4/spreadsheets/" & sheetid & "/values/" _ & sheetname & "!A1:A5?key=" & APIkey
'Send HTTTP request to get the data
Set HTTPreq = CreateObject("MSXML2.ServerXMLHTTP")
With HTTPreq
.Open "GET", url, False
.send
End With
Do Until HTTPreq.readyState = 4: Loop
response = HTTPreq.responseText
'ConvertJSONToRange (response)
End Sub
Google Sheets API returns data as JSON. However,
the JSON response is slightly different than the one obtained earlier in method
3 with Google Visualization API. See below an example of the JSON response for
a call to get column A values in a simple sheet (same as used in the example
for method 3).
Google Sheets API JSON response is more user
friendly and easier to read with a simple macro. See the example in this other
post as guidance: Connect To API Excel VBA Macro
(Part 2). That example uses the VBA functions InStr and Mid (and Replace).
There’s yet a simpler way to achieve the same for this particular JSON string
using the function Split (and Replace) along a For Each loop.
Both methods 3 and 4 could potentially be used to get restricted data when setup with Google OAuth 2.0. That’s more complicated and requires some level of browser automation which is more challenging with Edge (as compared to IE that has been demised). Personally, I only used OAuth to authenticate a Google Sheets request when IE automation was still an option (Excel had great libraries to automate IE, but not Edge – unless using external libraries such as Selenium). Another possibility is creating your own API to access and import data from a restricted Google spreadsheet (see method 5).
Method 5: Custom Google script/API
This method consists of creating your own script
or API that returns data from the associated or targeted Google spreadsheet
(within the same Google account). The code (or macro) is written using Google
Apps Script and deployed as a web app (no need to deploy as API executable).
The resulting URL may look like the one below.
https://script.google.com/macros/s/GASmacroID/exec
The API can be setup to accept parameters such
as an encoded token to add second level authentication. If the API is not
directly related to the target Google spreadsheet, a spreadsheet ID parameter can
also be added (not recommended though).
See below an example of a Google script to get
spreadsheet data and return it as an HTML table. There are no additional
parameters in the function. The script needs to be deployed as a web app.
The following VBA code in Excel is sending a
HTTP request to that API to get the data in the HTTP response. It is similar to
what we have already seen in previous methods.
Sub GetGoogleSheets_GASAPI()
Dim url As String, key As String, HTTPreq As Object, response As String
url = "https://script.google.com/macros/s/AKfzcbx64w0xedrhiqJ1BK.../exec"
'& "?token=" & encodedToken 'REPLACE!
'Send HTTP request to get the data
With HTTPreq
.Open "GET", url
.send
End With
Do Until HTTPreq.readyState = 4: Loop
response = HTTPreq.responseText
'ConvertHTMLToRange(response)
End Sub
In this example, we have chosen to output the
data as an HTML table. Thus, the response would look like the example string
below.
response =
"<table><tr><td>Name</td><td>ID</td></tr><tr><td>Pedro</td><td>1234</td></tr><tr><td>Marta</td><td>5678</td></tr></table>"
This other macro can extract the values from the
HTML table string. Manipulating an HTML document with Excel VBA requires adding
the Microsoft
HTML Object library to the references of the VBA project.
Sub ConvertHTMLToRange(response) 'adding HTML table data to cells
Dim HTML As Object, r As Integer, c As Integer
Set HTML = CreateObject("htmlFile")
HTML.body.innerHTML = response
For Each tr In HTML.getElementsByTagName("tr")
r = r + 1
c = 0
For Each td In tr.getElementsByTagName("td")
c = c + 1
Cells(r, c).Value = td.innerText
Next td
Next tr
End Sub
This method allows to get data from a restricted
Google spreadsheet when having a script within the same Google account.
However, second level authentication is recommended when dealing with sensitive
data if this method is to be used.
No comments:
Post a Comment