How to Import Data from Google Sheets to Excel Using VBA

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