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.

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.

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.

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.

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.

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.

How to Import Excel File to Google Sheets

Open the google sheet.

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.

Download Practice Workbook

You can download the practice workbook from here.

Import Data from Google Sheets.xlsm

Import Google Sheets to Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!

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

Sub UpdateGoogleSheet() Dim spreadsheetId As String Dim rangeName As String Dim oAuthApp As Object Dim dataToUpdate As Variant spreadsheetId = "YOUR_SPREADSHEET_ID" rangeName = "Sheet1!A1:C10" dataToUpdate = Array(Array("Updated Value 1", "Updated Value 2", "Updated Value 3")) Set oAuthApp = CreateObject("Scripting.Dictionary") oAuthApp("client_id") = "YOUR_CLIENT_ID" oAuthApp("client_secret") = "YOUR_CLIENT_SECRET" 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" Dim accessToken As String accessToken = GetAccessToken(oAuthApp) If accessToken <> "" Then Call UpdateGoogleSheetData(accessToken, spreadsheetId, rangeName, dataToUpdate) Else MsgBox "Authentication failed. Please try again." End If End Sub Function GetAccessToken(oAuthApp As Object) As String 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) Application.Wait Now + TimeValue("00:00:10") Dim authCode As String authCode = InputBox("Enter the authorization code: ") 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" Dim oAuthClient As Object Set oAuthClient = CreateObject("MSXML2.ServerXMLHTTP.6.0") 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 GetAccessToken = GetJsonValue(responseText, "access_token") End Function Function GetJsonValue(jsonString As String, key As String) As String 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 Sub OpenDefaultBrowser(url As String) Dim shell As Object Set shell = CreateObject("WScript.Shell") shell.Run url End Sub Sub UpdateGoogleSheetData(accessToken As String, spreadsheetId As String, rangeName As String, dataToUpdate As Variant) Dim updateUrl As String updateUrl = "https://sheets.googleapis.com/v4/spreadsheets/" & spreadsheetId & "/values/" & rangeName & "?valueInputOption=RAW" Dim jsonBody As String jsonBody = "{""range"":""" & rangeName & """,""majorDimension"":""ROWS"",""values"":" & ConvertToJson(dataToUpdate) & "}" Dim http As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0") http.Open "PUT", updateUrl, False http.setRequestHeader "Authorization", "Bearer " & accessToken http.setRequestHeader "Content-Type", "application/json" http.send jsonBody If http.Status = 200 Then MsgBox "Data updated successfully!" Else MsgBox "Failed to update data. Error: " & http.Status & " - " & http.statusText End If End Sub Function ConvertToJson(dataArray As Variant) As String Dim json As String json = "[" Dim i As Long, j As Long For i = LBound(dataArray, 1) To UBound(dataArray, 1) json = json & "[" For j = LBound(dataArray, 2) To UBound(dataArray, 2) json = json & """" & dataArray(i, j) & """" If j < UBound(dataArray, 2) Then json = json & "," Next j json = json & "]" If i < UBound(dataArray, 1) Then json = json & "," Next i json = json & "]" ConvertToJson = json End Function

4 Comments

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

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

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

Pablo May 30, 2024 at 7:40 PM

Hi SEEMANTO SAHA

I already have like, from excel import a sheet of sheets, but, now I need to process data in excel and then update only a certain column or a certain line in sheets, can you help me?, I’m blocked with permissions, do the same steps work?

Lutfor Rahman Shimanto Jun 2, 2024 at 9:09 AM

Hello Pablo

Thanks for visiting our blog and sharing your queries! You can modify the existing code slightly to update specific columns or rows in Google Sheets from Excel. Follow the previously mentioned steps, ensure you have the necessary permissions for the Google Sheet and adjust these with the VBA code.

Excel VBA Code:

Things to keep in mind: To adjust the VBA code, replace YOUR_SPREADSHEET_ID with your actual Google Sheet ID, and replace your YOUR_CLIENT_ID and YOUR_CLIENT_SECRET with the values from your OAuth 2.0 credential JSON file. Adjust the rangeName to specify the exact range (column/row) you want to update in the Google Sheet. Next, modify dataToUpdate to include the data you want to update. After running the VBA code, it will open a browser for you to authenticate with Google; enter the authorization code.

Hopefully, the code will fulfil your goal. Good luck.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy