Excel VBA: How to Download Google Sheet

Downloading Google Sheets is easy as a click.

Sub DownloadGoogleSheets()

Dim ShtUrl, Location, FileName As String
Dim objWebCon, objWrit As Object

'Sheet Url
ShtUrl = "https://docs.google.com/spreadsheets/d/1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY/export?format=csv&id=1Fy8T1FeEDzFX9U8_lQDk0HrLNSDGjTDUZFxlx-PWXbY&gid=0"  'Need to replace id and gid

'Location
Location = ThisWorkbook.Path & "\"  'C:\Export\" Replace with location

'FileName
FileName = "GoogleSheet.csv"

'Connection to Website
Set objWebCon = CreateObject("MSXML2.XMLHTTP.3.0")

'Writer
Set objWrit = CreateObject("ADODB.Stream")

'Connecting to the Website
objWebCon.Open "Get", ShtUrl, False
objWebCon.Send (ShtUrl)

'Once page is fully loaded
If objWebCon.Status = 200 Then

'Write the text of the sheet
objWrit.Open
objWrit.Type = 1
objWrit.Write objWebCon.ResponseBody
objWrit.Position = 0
objWrit.SaveToFile Location & FileName
objWrit.Close

End If

Set objWebCon = Nothing
Set objWrit = Nothing

End Sub

See it in Action