How to Import Data from Google Sheets to Excel (3 Easy Ways)

The sample dataset contains information on Product, Size, and Price.

Method 1 – Download Google Sheets as Excel File

 

  • Open the Google sheet to be imported.

import data from google sheets to excel

  • Select File > Download.
  • Choose Microsoft Excel (.xlsx) from the options.

  • The Save As window will pop up.
  • Change the name of the file if needed and select Save.

  • The google sheet is saved as an Excel file.

import data from google sheets to excel result

 

Read More: How to Download Google Sheets to Excel


Method 2 – Import Live Data to Excel from Google Sheets Link

With this method, data updated in Google Sheets will automatically appear in the Excel worksheet.

  • Open the Google sheet.

Import Live Data to Excel from Google Sheets Link

  • Select Share from the upper right corner of the Google sheet.

  • A Share window will appear.
  • Select the Copy link and press Done.

  • Open an Excel worksheet and select the Data tab from the ribbon.
  • From the Data tab select Get Data > From Other Sources > From Web.

  • A From Web window will show up.
  • Paste the copied link in the URL field.
  • Change the attributes edit?usp=sharing to export?format=xlsx and press OK.

  • A Navigator window will appear.
  • Select the sheet to import then press Load.

 

  • If we change data in Google Sheets, select Refresh All from the Data tab to update the data.

import data from google sheets to excel result

Read More: How Can I Link Google Sheets to Excel in Real Time?


Method 3 – Apply VBA to Import Data from Google Sheets to Excel

  • Open the VBA window by pressing the keyboard shortcut Alt + F11.
  • Select the active sheet from VBA Projects and right-click.
  • Select Module from the options.

Apply VBA to Import Data from Google Sheets to Excel

  • A module window will appear, write the following code there.
Sub Import_Google_Sheet()
Dim iKyStrn As String
Dim iGidStrn As String
iKyStrn = "1YJXOrL5KxrSdcCMHUxktJ5kz3BbJU7w5afKY9kJ_R18"
iGidStrn = "1925099421"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://spreadsheets.google.com/tq?tqx=out:html&tq=&key=" & _
iKyStrn & "&gid=" & iGidStrn, Destination:=Range("$A$1"))
.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Note: In the code,

iKyStrng = "1YJXOrL5KxrSdcCMHUxktJ5kz3BbJU7w5afKY9kJ_R18"

replace the iKyStrng value with the address of the desired google sheet

iGidStrng = "1925099421"

replace iGidStrng value with the address of a specific tab of google Sheets.

  • Press the Run key from the VBA window.

  • You will be able to import data from google sheets to Excel worksheets.

import data from google sheets to excel by vba


How to Import Excel File to Google Sheets

 

  • Open the google sheet.

How to Import Excel File to Google Sheets

  • Go to File and select Import from the options.

  • An Import file window will appear.
  • Select Upload.

  • Click on Select a file from your device, browse the Excel file to import, and select Open.

  • In the Import file window select Replace spreadsheet.
  • Select Import data.

 

Read More: How to Import Data from Google Sheets to Excel Using VBA


Download Practice Workbook

You can download the practice workbook from here.

 


Import Google Sheets to Excel: Knowledge Hub

<< Go Back to Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

2 Comments
  1. Hey nice write up!
    Do you have advice on how to import google sheets into excel if the google sheet requires authentification?

    Best,
    Jan

    • Hello Jan,
      Thanks for sharing your problem with us. I understand that you want to import data from an authenticated Google Spreadsheet to Excel.

      This is a complex method and requires several steps. Since Google Sheets are authenticated using Google Sheets APIs, you have to collect some information like client_id, client_secret, target spreadsheet ID, target spreadsheet name, and the range to be imported.

      Here is a step-by-step process:

      Step 1: Go to Google Cloud Console and select the target project (i.e. the project used for authenticating the required Google Spreadsheet)

      Step 2: Make sure the Google Sheets API is enabled. Navigate the following directory.
      APIs & Services >> Library

      Step 3: Create an OAuth 2.0 Client ID using the following sub-steps.
      Step 3.1: Go to the directory APIs & Services >> Credentials.
      Step 3.2: Click the Create credentials button and select OAuth client ID.
      Step 3.3: Set the Application type to Desktop App.
      Step 3.4: Enter a name for the Application and click the Create button.

      This will create a JSON file containing your client ID and client secret. Download the file and open it using VB.net or any other suitable application.

      Step 4: Go to the target Excel workbook and open Visual Basic Editor using the keyboard shortcut Alt + F11. Insert a Module and enable the following 3 libraries from Tools >> References directory.

      1) Microsoft Scripting Runtime
      2) Microsoft XML, v6.0
      3) Microsoft VBScript Regular Expressions 5.5

      Step 5: Insert the following VBA code and make necessary adjustments (change the spreadsheet ID, client_id, client_secret, sheet name, required range, etc.)

      Excel VBA Code

      
      Sub ImportDataFromGoogleSheetToExcel()
          Dim spreadsheetId As String
          Dim rangeName As String
          Dim oAuthApp As Object
      
          ' Define your Google Sheet details
          spreadsheetId = "1Xz_LyRk0n81VqDjiJYNBMwR1lGSBmm213JtQxK5HRnQ" ' Replace with your Google Sheets document ID
          rangeName = "TestSheet!A1:C10" ' Change to your desired range
      
          ' Initialize the Google API client
          Set oAuthApp = CreateObject("Scripting.Dictionary")
      
          ' Configure OAuth2 client
          oAuthApp("client_id") = "304250172616-5ddsaumijhuigr05t8smc1prfqr4m60g.apps.googleusercontent.com"
          oAuthApp("client_secret") = "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"
          oAuthApp("auth_uri") = "https://accounts.google.com/o/oauth2/auth"
          oAuthApp("token_uri") = "https://oauth2.googleapis.com/token"
          oAuthApp("scope") = "https://www.googleapis.com/auth/spreadsheets.readonly"
      
          ' Wait for the user to authenticate manually (adjust the waiting time as needed)
          Application.Wait Now + TimeValue("00:00:10") ' Wait for 10 seconds
      
          ' Call the Google Sheets API to get data
          Call ImportDataFromGoogleSheets(oAuthApp, spreadsheetId, rangeName)
      End Sub
      
      Sub ImportDataFromGoogleSheets(oAuthApp, spreadsheetId, rangeName)
          ' Create a Google Sheets service instance
          Dim service As Object
          Set service = CreateObject("Scripting.Dictionary")
          service("spreadsheetId") = spreadsheetId
          service("range") = rangeName
      
          ' Use your OAuth2 client details
          Set service("oauth_client") = oAuthApp
      
          ' Authenticate with Google Sheets
          Call AuthenticateWithGoogleSheets(service)
      
          ' Check if the authentication was successful
          If Not service("sheets_service") Is Nothing Then
              ' Fetch data from Google Sheets
              Call GetDataFromGoogleSheets(service)
          End If
      End Sub
      
      
      Sub AuthenticateWithGoogleSheets(service)
          Dim oAuthClient As Object
          Set oAuthClient = CreateObject("MSXML2.ServerXMLHTTP.6.0")
      
          ' Use your OAuth2 client details
          Dim oAuthApp As Object
          Set oAuthApp = service("oauth_client")
      
          ' Authenticate with Google Sheets using OAuth2
          Dim authUrl As String
          authUrl = oAuthApp("auth_uri") & "?client_id=" & oAuthApp("client_id") & "&redirect_uri=urn:ietf:wg:oauth:2.0:oob&scope=" & oAuthApp("scope") & "&response_type=code"
          Call OpenDefaultBrowser(authUrl)
      
          ' Wait for the user to authenticate manually
          Application.Wait Now + TimeValue("00:00:10") ' Wait for 10 seconds
      
          ' Check if the user is authenticated
          Dim authCode As String
          authCode = InputBox("Enter the authorization code: ")
      
          ' Exchange the authorization code for an access token
          Dim postData As String
          postData = "code=" & authCode & "&client_id=" & oAuthApp("client_id") & "&client_secret=" & oAuthApp("client_secret") & "&redirect_uri=urn:ietf:wg:oauth:2.0:oob&grant_type=authorization_code"
      
          oAuthClient.Open "POST", oAuthApp("token_uri"), False
          oAuthClient.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
          oAuthClient.send postData
      
          Dim responseText As String
          responseText = oAuthClient.responseText
          Dim accessToken As String
          accessToken = GetJsonValue(responseText, "access_token")
      
          ' Create a service to interact with Google Sheets
          Dim sheetsService As Object
          Set sheetsService = CreateObject("MSXML2.ServerXMLHTTP.6.0")
          sheetsService.Open "GET", "https://sheets.googleapis.com/v4/spreadsheets/" & service("spreadsheetId") & "/values/" & service("range"), False
          sheetsService.setRequestHeader "Authorization", "Bearer " & accessToken
      
          ' Check if authentication was successful
          If sheetsService.Status = 200 Then
              Set service("sheets_service") = sheetsService
          Else
              MsgBox "Authentication failed. Please try again."
          End If
      End Sub
      
      Sub GetDataFromGoogleSheets(service)
          Dim sheetsService As Object
          Set sheetsService = service("sheets_service")
      
          ' Send a request to get data from Google Sheets
          sheetsService.send
      
          ' Check if the request was successful
          If sheetsService.Status = 200 Then
              Dim responseText As String
              responseText = sheetsService.responseText
      
              ' Parse the response data (you can customize this part)
              Dim data As Variant
              data = ParseGoogleSheetsData(responseText)
      
              ' Paste the data into the active sheet starting from cell A1
              Dim targetSheet As Object
              Set targetSheet = ThisWorkbook.Sheets(1) ' Change to your target sheet
              targetSheet.range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data
          Else
              MsgBox "Failed to retrieve data from Google Sheets."
          End If
      End Sub
      
      Function GetJsonValue(jsonString As String, key As String) As String
          ' A function to extract a value from a JSON string given a key
          Dim regex As Object
          Set regex = CreateObject("VBScript.RegExp")
      
          regex.Global = True
          regex.MultiLine = False
          regex.IgnoreCase = True
          regex.Pattern = """" & key & """:\s*""(.*?)"""
      
          If regex.Test(jsonString) Then
              GetJsonValue = regex.Execute(jsonString)(0).SubMatches(0)
          Else
              GetJsonValue = ""
          End If
      End Function
      
      Function ParseGoogleSheetsData(data As String) As Variant
          ' A function to parse Google Sheets data from JSON to a 2D array
          Dim json As Object
          Set json = JsonConverter.ParseJson(data)
      
          Dim values As Object
          Set values = json("values")
      
          Dim numRows As Long
          numRows = values.Count
      
          Dim numCols As Long
          numCols = values(1).Count
      
          Dim resultArray As Variant
          ReDim resultArray(1 To numRows, 1 To numCols)
      
          Dim i As Long, j As Long
          For i = 1 To numRows
              For j = 1 To numCols
                  resultArray(i, j) = values(i - 1)(j - 1)
              Next j
          Next i
      
          ParseGoogleSheetsData = resultArray
      End Function
      

      Step 6: Run the code and the required data from the authenticated Google Sheets will appear in your Excel Active Sheet.

      Note that, this code will only work if you have the Google Sheets API developers have authorized your email to the target Google Spreadsheet.

      Hopefully, we were able to help you. Let us know your feedback.

      Regards,
      Seemanto Saha
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo