How to Import Data from Google Sheets to Excel Using VBA

Get FREE Advanced Excel Exercises with Solutions!

Undoubtedly, Microsoft Excel and Google Sheets are two of the most useful and popular tools for data analysis. Nowadays, Google Sheets is very popular for syncing data. If you’re an Excel user, most likely you know the methods of sharing the Excel file online. However, you may need to extract data from Google Sheets to Excel for various purposes. In this article, I will show you 3 easy and quick steps to import data from Google Sheets to Excel using VBA code. In another article, we discussed two methods to share the Excel file online.

Table of Contents hide

Download Practice Workbook

3 Steps to Import Data from Google Sheets to Excel Using VBA

Step 01: Change Privacy of Google Sheets

Step 02: Insert VBA Code to Import Data to Excel

Step 03: Apply Formatting to Dataset to Make It Engage

Conclusion

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Importing Data From Google Sheets to Excel.xlsm

3 Steps to Import Data from Google Sheets to Excel Using VBA

For ease of understanding, we are going to utilize a dataset on Google Sheets. The sheet’s name is Importing Data to Excel. This dataset includes the Sales Rep, Product Name, Unit, and Sales in columns B, C, D, and E consecutively.

Now, we’ll use this dataset above to import data from Google sheets to Excel using VBA. So, let’s go through it step-by-step to understand it clearly.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.

Step 01: Change Privacy of Google Sheets

At the very beginning, we have to change the privacy of the sheet so that anyone can get access to it. So let’s begin.

📌 Steps:

First of all, click on the Share button with a picture of a lock at the right top corner of the display.

Immediately, it opens the Share “Importing Data to Excel” dialog box.

Here, click on the drop-down arrow under the General access section.
Then, select Anyone with the link from the options.
After that, click on Done.

Currently, this sheet becomes public. Anyone with the link can access this sheet. So, our first part of the work is done.

Step 02: Insert VBA Code to Import Data to Excel

Now, we’ll build a VBA code and execute it to extract the data from Google Sheets into our Excel workbook. So, without further delay, let’s see how we can do it.

📌 Steps:

At first, open a new workbook on Excel.
Next, go to the Developer tab.
Then, click on Visual Basic in the Code group.

Instantly, the Microsoft Visual Basic for Applications window appears before us.

Presently, move to the Insert tab.
Therefore, click on Module from the available options.

Suddenly, it creates a code module where we can write our code to import data from Google Sheets to Excel.

Now, paste the following code into the module.

Sub Import_Sheets_to_Excel()
Dim QRT As QueryTable, ul As String, ky As String
If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
ActiveSheet.Cells.Clear
ky = "1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs"
ul = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & ky
Set QRT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ul, _
Destination:=Range("$A$4"))
With QRT
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.Refresh
End With
End Sub

Now, we’ll explain the VBA code.

Code Breakdown

Firstly, we declared the different variables we’re using in this script.
Then, we give an If logical statement to clear the query table in our Excel sheet. If there is any table or data in the cells in the worksheet, then they’ll be cleared. Because the newly imported data will take that place.
After that, we set the ky variable. Actually, it’s the Google spreadsheet key. We can obtain this key from the sheet’s sharing link on the address bar of the browser. We can locate it after “/d/” and before the next forward slash. See the address below to get it clearly.

https://docs.google.com/spreadsheets/d/1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs/edit#gid=0

Here, the bold part is the spreadsheet key.

Now, we’ll get the output of the Google spreadsheet data as an HTML table. For this reason, we’ll use a modified URL like the following.

“https://spreadsheets.google.com/tq?tqx=out:html&key=” & ky

After that, we added the query to the Excel sheet and also defined the destination range which is cell A4 in our case.

Hence, Run the code by clicking on the green-color play button on the ribbon.

Then, get back to the worksheet Sheet1.

You can see that Excel is getting the data from an external source.

All the data appears very soon because our dataset is tiny in size. So, it takes a minimum amount of time to fetch the data.

Step 03: Apply Formatting to Dataset to Make It Engage

The data we imported from Google Sheets doesn’t have any type of formatting. So, we’ll apply different types of formatting to it to make it more appealing and soothing for the eyes. So, let’s see it in action.

📌 Steps:

Firstly, we gave a relatable heading to the dataset in cell B2. It’s formatted in Heading 2 Cell Style.

Secondly, select cells in the B4:E14 range.
After that, go to the Home tab.
Then, click on the Middle Align icon and the Center icon on the Alignment group of command.

Also, give All Borders to the selected range.

Now, it looks like the following.

Lastly, select the headings in the B4:E4 range and make them Bold. Also, change the Fill Color and Font Color.

Conclusion

This article explains how to import data from Google Sheets to Excel using VBA in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.

Sub ImportDataFromGoogleSheets() 'Set the URL of the Google Sheets document Dim googleSheetsURL As String googleSheetsURL = "https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit#gid=YYYYYYYYYY" 'Set the name of the sheet containing the data you want to import Dim sheetName As String sheetName = "Sheet2" 'Set the target range where you want to paste the imported data Dim targetRange As Range Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1") 'Define the source range of the data in the Google Sheets document Dim sourceRange As Range With CreateObject("MSXML2.XMLHTTP") .Open "GET", googleSheetsURL, False .send Dim responseText As String responseText = .responseText Dim startIndex As Long startIndex = InStr(1, responseText, sheetName & "!A1") If startIndex > 0 Then Dim endIndex As Long endIndex = InStr(startIndex, responseText, "class=""") - 1 If endIndex > startIndex Then Dim rangeAddress As String rangeAddress = Mid(responseText, startIndex, endIndex - startIndex) rangeAddress = Replace(rangeAddress, "'", "") rangeAddress = Replace(rangeAddress, "!", ":") Set sourceRange = Range(rangeAddress) End If End If End With 'Copy the data from the source range to the clipboard sourceRange.Copy 'Clear the target range to ensure that no existing data interferes with the import targetRange.CurrentRegion.ClearContents 'Paste the data from the clipboard into the target range targetRange.PasteSpecial xlPasteValues End Sub

' Load the Google API client library Private Sub LoadGoogleAPI() Dim objHTTP As Object Set objHTTP = CreateObject("MSXML2.XMLHTTP") objHTTP.Open "GET", "https://developers.google.com/oauthplayground/assets/js/library.js", False objHTTP.send Dim html As Object Set html = CreateObject("htmlfile") html.body.innerHTML = objHTTP.responseText Dim script As Object Set script = html.createElement("script") script.Language = "javascript" script.Text = "function DoNothing() {}" html.appendChild script Set google = html.parentWindow End Sub ' Authenticate to Google Sheets Private Sub AuthenticateToGoogle() Dim CLIENT_ID As String Dim CLIENT_SECRET As String Dim REFRESH_TOKEN As String CLIENT_ID = "your_client_id_here" CLIENT_SECRET = "your_client_secret_here" REFRESH_TOKEN = "your_refresh_token_here" ' Load the Google API client library LoadGoogleAPI ' Authenticate with Google google.gapi.auth.authorize Array("https://www.googleapis.com/auth/spreadsheets"), CLIENT_ID, CLIENT_SECRET, REFRESH_TOKEN ' Get the access token access_token = google.gapi.auth.getToken().access_token End Sub ' Upload data to Google Sheets Private Sub UploadDataToGoogleSheets() ' Authenticate with Google AuthenticateToGoogle ' Define the range of data to upload Dim range As String range = "Sheet1!A1:D4" ' Change this to the range of your data ' Get the spreadsheet ID Dim spreadsheet_id As String spreadsheet_id = "your_spreadsheet_id_here" ' Define the data to upload Dim data As Variant data = Range(range).Value ' Upload the data to Google Sheets Dim url As String url = "https://sheets.googleapis.com/v4/spreadsheets/" & spreadsheet_id & "/values/" & range & "?valueInputOption=USER_ENTERED" Dim xmlhttp As Object Set xmlhttp = CreateObject("MSXML2.XMLHTTP") xmlhttp.Open "PUT", url, False xmlhttp.setRequestHeader "Authorization", "Bearer " & access_token xmlhttp.setRequestHeader "Content-Type", "application/json" Dim json As String json = "{""values"": " & WorksheetFunction.Transpose(WorksheetFunction.Transpose(JsonConverter.ConvertToJson(data))) & "}" xmlhttp.send json End Sub

Sub Import_Sheets_to_Excel() Dim QRT As QueryTable, ul As String, ky As String If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete ActiveSheet.Cells.Clear ky = "1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs" ul = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & ky & "&range=A1:E10" Set QRT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ul, _ Destination:=Range("$A$4")) With QRT .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .Refresh End With End Sub

8 Comments

Obot Feb 13, 2023 at 5:30 PM

How do I import the data from other sheets other than the sheet1 of googlesheet?
- Reply
  Sabrina Ayon Feb 15, 2023 at 12:45 PM
  Hello, OBOT!
  Thanks for your comment!
  To import data from a specific sheet (e.g., “Sheet2“) of a Google Sheets document to Excel using VBA, you can use the following code:
```
Sub ImportDataFromGoogleSheets()

    'Set the URL of the Google Sheets document
    Dim googleSheetsURL As String
    googleSheetsURL = "https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/edit#gid=YYYYYYYYYY"
    
    'Set the name of the sheet containing the data you want to import
    Dim sheetName As String
    sheetName = "Sheet2"
    
    'Set the target range where you want to paste the imported data
    Dim targetRange As Range
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    
    'Define the source range of the data in the Google Sheets document
    Dim sourceRange As Range
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", googleSheetsURL, False
        .send
        Dim responseText As String
        responseText = .responseText
        Dim startIndex As Long
        startIndex = InStr(1, responseText, sheetName & "!A1")
        If startIndex > 0 Then
            Dim endIndex As Long
            endIndex = InStr(startIndex, responseText, "class=""") - 1
            If endIndex > startIndex Then
                Dim rangeAddress As String
                rangeAddress = Mid(responseText, startIndex, endIndex - startIndex)
                rangeAddress = Replace(rangeAddress, "'", "")
                rangeAddress = Replace(rangeAddress, "!", ":")
                Set sourceRange = Range(rangeAddress)
            End If
        End If
    End With
    
    'Copy the data from the source range to the clipboard
    sourceRange.Copy
    
    'Clear the target range to ensure that no existing data interferes with the import
    targetRange.CurrentRegion.ClearContents
    
    'Paste the data from the clipboard into the target range
    targetRange.PasteSpecial xlPasteValues
    
End Sub
```
  To use this code, you need to replace the googleSheetsURL variable with the URL of the Google Sheets document containing the data you want to import and replace the sheetName variable with the name of the sheet containing the data you want to import (in this example, “Sheet2“). You also need to set the targetRange variable to specify the cell or range where you want to paste the imported data (in this example, cell A1 of the Sheet1 worksheet).
  
  The code uses the MSXML2.XMLHTTP object to send an HTTP request to the Google Sheets document, and parses the HTML response to identify the range of cells corresponding to the specified sheet name. It then copies the data from the identified range to the clipboard, clears the target range to ensure that no existing data interferes with the import, and pastes the data from the clipboard into the target range.
  
  Hope this will help you!
  Good Luck!
  
  Regards,
  Sabrina Ayon
  Author, ExcelDemy.
Reza Mar 12, 2023 at 1:14 PM

Hello dear
Thanks you…
Is it possible to write a VBA program to upload data from an Excel file directly to Google Sheets?
- Reply
  Shahriar Abrar Rafid Mar 15, 2023 at 4:22 PM
  Hello REZA,
  Thanks for commenting and asking your valuable question. Actually, it’s quite costly to do it with VBA. Because of this, you have to enable the Google Sheets API, and quite expensive. So I wouldn’t suggest it.
  Rather, you can use Google Drive or the Import feature of Google Sheets to do this task easily. You can follow this linked article on our website to get the whole idea.
  Anyway, if you want to do it with VBA, you can follow this lengthy process:
  - First, you have to enable the Google Sheets API for your Google account and create a project in the Google Developers Console. Here are the steps to enable the Google Sheets API and create a project in the Google Developers Console:
  - Go to the Google Cloud Console website (https://console.cloud.google.com/).
  - If you don’t have a Google Cloud account, sign up for a free trial or create a new account.
  - Once you’re signed in to the Google Cloud Console, create a new project by clicking the “Select a project” dropdown menu in the top navigation bar and clicking “New Project“.
  - Give your project a name and click “Create“.
  - In the left-hand navigation menu, click “APIs & Services” and then click “Dashboard“.
  - Click the “+ ENABLE APIS AND SERVICES” button at the top of the page.
  - In the search bar, type “Google Sheets API” and click on the result.
  - Click the “ENABLE” button to enable the Google Sheets API for your project.
  - Next, you need to create a set of credentials to authenticate your VBA program with your Google account. In the left-hand navigation menu, click “Credentials“.
  - Click the “Create credentials” dropdown menu and select “OAuth client ID“.
  - Select “Desktop app” as the application type, give your client ID a name, and click “Create“.
  - In the “OAuth client created” dialog box, click “OK“.
  - Click on the name of the client ID you just created to download the client secret file.
  - Rename the downloaded file to “client_secret.json” and save it in a secure location on your computer.
  - Finally, authorize the API scopes for your Google account by clicking the “Configure consent screen” button on the “Credentials” page and following the prompts to enter your app details and authorized scopes.
  - After that, Create a Google Sheets spreadsheet and give it a name.
  - In VBA, create a reference to the Google API client library and authenticate to your Google account. Here is a sample VBA code to do it:
```
' Load the Google API client library
Private Sub LoadGoogleAPI()
    Dim objHTTP As Object
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", "https://developers.google.com/oauthplayground/assets/js/library.js", False
    objHTTP.send
    Dim html As Object
    Set html = CreateObject("htmlfile")
    html.body.innerHTML = objHTTP.responseText
    Dim script As Object
    Set script = html.createElement("script")
    script.Language = "javascript"
    script.Text = "function DoNothing() {}"
    html.appendChild script
    Set google = html.parentWindow
End Sub

' Authenticate to Google Sheets
Private Sub AuthenticateToGoogle()
    Dim CLIENT_ID As String
    Dim CLIENT_SECRET As String
    Dim REFRESH_TOKEN As String
    CLIENT_ID = "your_client_id_here"
    CLIENT_SECRET = "your_client_secret_here"
    REFRESH_TOKEN = "your_refresh_token_here"
    
    ' Load the Google API client library
    LoadGoogleAPI
    
    ' Authenticate with Google
    google.gapi.auth.authorize Array("https://www.googleapis.com/auth/spreadsheets"), CLIENT_ID, CLIENT_SECRET, REFRESH_TOKEN
    
    ' Get the access token
    access_token = google.gapi.auth.getToken().access_token
End Sub

' Upload data to Google Sheets
Private Sub UploadDataToGoogleSheets()
    ' Authenticate with Google
    AuthenticateToGoogle
    
    ' Define the range of data to upload
    Dim range As String
    range = "Sheet1!A1:D4" ' Change this to the range of your data
    
    ' Get the spreadsheet ID
    Dim spreadsheet_id As String
    spreadsheet_id = "your_spreadsheet_id_here"
    
    ' Define the data to upload
    Dim data As Variant
    data = Range(range).Value
    
    ' Upload the data to Google Sheets
    Dim url As String
    url = "https://sheets.googleapis.com/v4/spreadsheets/" & spreadsheet_id & "/values/" & range & "?valueInputOption=USER_ENTERED"
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "PUT", url, False
    xmlhttp.setRequestHeader "Authorization", "Bearer " & access_token
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    Dim json As String
    json = "{""values"": " & WorksheetFunction.Transpose(WorksheetFunction.Transpose(JsonConverter.ConvertToJson(data))) & "}"
    xmlhttp.send json
End Sub
```
  Just make sure to change specific things in your own code. Hope this could help you. Again, thanks to you.
  
  Regards
  SHAHRIAR ABRAR RAFID
  Team ExcelDemy
  - Reply
    Robert Apr 30, 2023 at 9:44 PM
    
    this doesnt work
    - Shahriar Abrar Rafid May 2, 2023 at 11:40 AM
      
      Hello ROBERT,
      Do you have Google API? I think the code works. But if it doesn’t work for you, you can use the simple Import feature which I mentioned in the previous reply.
      Even if you want to do it with VBA, you can do it through Google Forms. This process doesn’t require the API key.
      If you have further queries, you can comment below. Thanks for your feedback.
      Regards
      SHAHRIAR ABRAR RAFID
      Team ExcelDemy
Georgi Mar 16, 2023 at 9:57 PM

Hello – this is amazing information. I ahve been strugling to get it to work for ages. Can i shoose which rage to extract from the google sheet?
- Reply
  Shahriar Abrar Rafid Mar 21, 2023 at 11:02 AM
  Hello GEORGI,
  Thanks for appreciating our work. We are also happy to release you from prolonged suffering. Now, get back to your query.
  Sure, you can alter the “Destination” parameter of the “QueryTables.Add” method in your script to specify a specific range to extract from the Google sheet.
  For instance, you could change the code such that it retrieves data from cells A1 to E10 of the sheet:
```
Sub Import_Sheets_to_Excel()

    Dim QRT As QueryTable, ul As String, ky As String
    If ActiveSheet.QueryTables.Count > 0 Then ActiveSheet.QueryTables(1).Delete
    ActiveSheet.Cells.Clear
    ky = "1slumgR5et-cG7Er-9udweCSthufb7xYdsmoP9H2AOFs"
    ul = "https://spreadsheets.google.com/tq?tqx=out:html&key=" & ky & "&range=A1:E10"
    Set QRT = ActiveSheet.QueryTables.Add(Connection:="URL;" & ul, _
    Destination:=Range("$A$4"))
    With QRT
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .Refresh
    End With

End Sub
```
  The “ul” variable in this modified code has the addition “&range=A1:E10” to identify the range to extract from. Also, the starting cell where the extracted data will be stored is specified by the “Destination” parameter in the “QueryTables.Add” method, which is set to “$A$4“.
  I hope this helps. I wanted to personally invite you to check out our new Excel-related forum. We’ve created a space for Excel enthusiasts like us to share tips, tricks, and ideas, as well as to ask and answer questions about using Excel. We’re a growing community of Excel users, and we’d love to have you join us!
  Here’s the link to our forum: ExcelDemy Forum
  
  Regards,
  SHAHRIAR ABRAR RAFID
  Team ExcelDemy

How to Import Data from Google Sheets to Excel Using VBA

Download Practice Workbook

3 Steps to Import Data from Google Sheets to Excel Using VBA

Step 01: Change Privacy of Google Sheets

Step 02: Insert VBA Code to Import Data to Excel

Step 03: Apply Formatting to Dataset to Make It Engage

Conclusion

Related Articles

Leave a reply Cancel reply