Integrating Google Drive access in Excel Macros

[daniel]

Introduction

If you need to import Google Drive metadata (such as file names, types, and timestamps) directly into Excel for reporting or automation, ODBC integration with Excel VBA macros offers a powerful and flexible approach. By combining Excel macros with a configured Google Drive ODBC DSN, you can fetch live data without manual downloads or scripting APIs.

In this tutorial, you’ll learn how to integrate Google Drive access into Excel using VBA and ODBC, enabling automated data imports into your spreadsheets with just one click.

Prerequisites

• Download and install the ZappySys ODBC PowerPack
• A Google account with access to Google Drive
• Microsoft Excel (Windows version with macro support)
• Basic familiarity with the VBA editor in Excel

Step-by-step guide

Step 1: Create or select a project in the Google API Console

1. Navigate to the Google API Console.

2. Click the project dropdown in the top bar and either select an existing project or click CREATE PROJECT.
   

   

3. After the project is selected, click ENABLE APIS AND SERVICES.

4. Enable both Google Sheets API and Google Drive API.
   

   

5. Open the OAuth Consent Screen tab, fill in the required details, and save.

6. Go to the Credentials tab.

7. Click CREATE CREDENTIALS, choose OAuth Client ID, select Desktop App, and click Create to get your Client ID and Client Secret.
   

   

Step 2: Create a new ODBC DSN

1. Open the ODBC Data Source Administrator (64-bit) by typing “ODBC” in the Windows search box.
   

   

2. Under the System DSN or User DSN tab, click Add to create a new DSN.
   

   

3. Choose ZappySys API Driver, then select Google Drive from the connector list and click Continue.
   

   

Step 3: Configure the Google Drive connector

1. Enter your Client ID, Client Secret, and required scopes.

2. Click Generate Token and test the connection.
   

   

3. Open the Preview tab, select a sample table, and preview the results.

4. Click OK to save the DSN configuration.
   

   

Step 4: Write the Excel VBA macro

1. Open Excel and go to the Developer tab. Click Visual Basic or press Alt + F11.
   

   

2. In the VBA editor, go to Insert and then Module.

3. Paste the following macro:

Sub ImportDataFromODBCGoogleDrive()

    ' Declare necessary variables
    Dim connection As Object
    Dim recordset As Object
    Dim connectionString As String
    Dim sqlQuery As String
    Dim col As Integer
    Dim currentRow As Long

    ' Create the connection and recordset objects
    Set connection = CreateObject("ADODB.Connection")
    Set recordset = CreateObject("ADODB.Recordset")

    ' Set the connection string using your DSN name
    connectionString = "DSN=Google Drive - API Drive"

    ' Define the SQL query to retrieve data
    sqlQuery = "SELECT Id, Name, MimeType, CreatedTime, ModifiedTime FROM Files"

    ' Open the connection
    connection.Open connectionString
    connection.CommandTimeout = 900

    ' Run the query
    recordset.Open sqlQuery, connection, 0, 1

    If recordset.EOF Then
        MsgBox "No records found.", vbExclamation
    Else
        With ThisWorkbook.Sheets("Sheet1")
            .Cells.ClearContents

            ' Write column headers
            For col = 0 To recordset.Fields.Count - 1
                .Cells(1, col + 1).Value = recordset.Fields(col).Name
            Next col

            ' Write data rows
            currentRow = 2
            Do Until recordset.EOF
                For col = 0 To recordset.Fields.Count - 1
                    .Cells(currentRow, col + 1).Value = recordset.Fields(col).Value
                Next col
                currentRow = currentRow + 1
                recordset.MoveNext
            Loop

            .Columns.AutoFit
        End With

        MsgBox "Data was successfully copied to Excel.", vbInformation
    End If

    ' Clean up
    recordset.Close: Set recordset = Nothing
    connection.Close: Set connection = Nothing

End Sub

Step 5: Run the macro

1. Press Alt + F8, select ImportDataFromODBCGoogleDrive, and click Run.
2. If the DSN and API connection are valid, Excel will display the file metadata in your spreadsheet.
3. Optionally, you can assign this macro to a button for one-click data refresh.
   
   

Benefits of this integration

• No manual exports — automate data pulls from Google Drive into Excel
• SQL-powered filters — retrieve files by type, date, or folder
• Real-time sync — fetch the latest metadata directly from the cloud
• Scalable — integrate with Excel formulas, charts, or pivot tables

Troubleshooting tips

• No records returned? Test the query in the DSN preview tab to verify it returns data.
• Connection error? Make sure the token is valid and the correct client credentials are used.
• Macro issues? Ensure macros are enabled in Excel under Trust Center settings.

Conclusion

Integrating Google Drive into Excel macros via an ODBC connection is a powerful way to automate cloud data reporting. With the ZappySys ODBC Driver, Excel can act as your dynamic reporting dashboard—pulling metadata like file names, types, and timestamps with a single click.

This solution saves time, avoids repetitive tasks, and allows you to extend reporting with all the powerful features Excel offers.

Related resources

• API ODBC Driver
• API ODBC Driver documentation
• Google Drive Connector | API Integration Hub
• Blog articles
• ODBC PowerPack

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.