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

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

Avatar photo
Written by Mehedi Hasan Shimul
Last updated: Oct 10, 2023
Get FREE Advanced Excel Exercises with Solutions!

Both Excel and Google Sheets are widely used for data analysis and performing different sorts of calculations and both have some specific benefits. Sometimes, we may want to import data from Google Sheets to Excel worksheets to get some advantage in data analysis. In this article, I will show 3 easy ways to import data from Google Sheets to Excel worksheets.

Table of Contents hide
Import Data from Google Sheets to Excel: 3 Easy Ways
1. Download Google Sheets as Excel File
2. Import Live Data to Excel from Google Sheets Link
3. Apply VBA to Import Data from Google Sheets to Excel
How to Import Excel File to Google Sheets
Conclusion
Related Articles

Import Data from Google Sheets to Excel: 3 Easy Ways

We can import data from Google Sheets to Excel in several ways. 3 such easy ways are described in the following section. To explain the procedure, I have introduced a dataset containing Product, Size, and Price.

1. Download Google Sheets as Excel File

One of the ways to import data from Google Sheets is to download the Google Sheets as an Excel file. I am showing the steps for the procedure.

  • Firstly, open the Google sheet that you want to import.

import data from google sheets to excel

  • Next, select File > Download.
  • Further, choose Microsoft Excel (.xlsx) from the options.

  • Consequently, the Save As window will pop up.
  • Now, change the name of the file if needed and select Save.

  • Finally, our google sheet is saved as an Excel file.

import data from google sheets to excel result

In this way, we have successfully imported data from Google Sheets to Excel Worksheets.

Read More: How to Download Google Sheets to Excel

2. Import Live Data to Excel from Google Sheets Link

Instead of saving Google Sheets as an Excel file, we can also link Google Sheets to Excel worksheets. This way, if we update data in Google Sheets it will automatically reflect on the Excel worksheet. Let’s follow the steps given below for the procedures.

  • Firstly, open the Google sheet from where we want to extract data.

Import Live Data to Excel from Google Sheets Link

  • Then, select Share from the upper right corner of the Google sheet.

  • Instantly, A Share window will appear.
  • Further, select the Copy link from there and press Done.

  • Now, open an Excel worksheet and select the Data tab from the ribbon.
  • Afterward, from the Data tab select Get Data > From Other Sources > From Web.

  • After that, a From Web window will show up. Paste the copied link in the URL field.
  • Then, change the attributes edit?usp=sharing with export?format=xlsx and press OK.

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

  • Bravo! We have successfully imported live data from Google Sheets to Excel.
  • If we change data in Google Sheets, all we need to do is 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?

3. Apply VBA to Import Data from Google Sheets to Excel

Another way to import data from Google Sheets to Excel is the use of Visual Basics for Applications(VBA) code. The procedures for using VBA code to import data from Google Sheets are given below stepwise.

  • Firstly, open the VBA window by pressing the keyboard shortcut Alt + F11.
  • Then, select the active sheet from VBA Projects and right-click.
  • Further, select Module from the options.

Apply VBA to Import Data from Google Sheets to Excel

  • Consequently, 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.

  • Now, press the Run key from the VBA window.

  • Finally, 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

For various reasons, we may want to export data from Excel worksheets to Google Sheets. Suppose, we have a Google sheet whose dataset contains product, Size, and Price. We want to export the dataset to Google Sheets. Here, I will discuss the way to import Excel files to Google Sheets. Please follow the steps given below.

  • Firstly, open the google sheet.

How to Import Excel File to Google Sheets

  • Then, go to File and select Import from the options.

  • Consequently, an Import file window will appear. Select Upload from there.

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

  • After that, in the Import file window select Replace spreadsheet.
  • Further, select Import data.

  • We are done with importing data from an Excel file to google spreadsheets.

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

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Both Google spreadsheets and Excel worksheets work fine in doing various mathematical operations though both have some different advantages. In this article, I have shown 3 easy ways to import data from Google spreadsheets to Excel worksheets. If you have any queries about the procedures, please let us know by commenting.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

2 Comments
  1. Reply
    Jan Oct 25, 2023 at 6:33 PM

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

    Best,
    Jan

    • Reply Avatar photo
      Seemanto Saha Oct 26, 2023 at 5:18 PM

      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

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

Submit Excel Task
See Our Review attrustpilot review
Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo