How to connect MS Excel with Google Drive

[daniel]

Introduction

This guide will walk you through connecting Microsoft Excel to the Google Drive API using the ZappySys ODBC API Driver, enabling seamless data integration and retrieval. The ZappySys ODBC Google Drive Connector provides a robust solution for integrating Google Drive data into Excel for reporting and analysis.

Prerequisites

• ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.
• Have a Google account: Ensure you have a Google account to access Google Drive data.

Steps

Step 1: Create/Select a Project in Google API Console:

1. Navigate to the Google API Console.

2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.
   

   

3. Once the project is set, click ENABLE APIS AND SERVICES.

4. Enable both Sheets API and Drive API by searching for them and clicking ENABLE.
   

5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.

6. Move to the Credentials tab.

7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.
   

   

Step 2: Create a New Driver

1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
   

2. To gain access for yourself or other users, go to the User DSN or System DSN. Go to the System tab for SQL Server Integration and add a new System DSN. Click the “Add” button.
   

3. From the driver list, select ZappySys API Driver, then select Google Drive in the connector list and press Continue.
   

   

Step 3: Google Drive Connector Configuration

1. Fill in the connector fields, including Client ID, Client Secret, and Scopes.

2. Generate the token and test the connection.
   

   

3. Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.
   

   

Step 4: Read data in Excel from the DSN

1. In Excel click Data, then select Get Data, proceed with From Other Sources and choose From ODBC item. This will get data from the ODBC data source we created:
   

   

2. A small window opens, then select the data source you created in previous steps:
   

   

3. You will most likely be asked to authenticate to a newly created DSN. Just select the Windows authentication option together with the Use my current credentials option:
   

   

4. You will be asked to select a table or view to get data. Select one and load the data!
   

   

5. Finally, use data extracted from REST API API in an Excel worksheet:
   

   

Considerations

Save the credentials so you do not have to search for them in your account every time.

Video Tutorial

ZappySys ODBC PowerPack - Drivers for JSON, REST API, XML, SOAP

Conclusion

.
The article provides steps to guide users through connecting Excel with Google Drive services using our ODBC API driver. If you encounter any issues or need further assistance, feel free to contact our support team via chat on our website or through email at support@zappysys.com.

Refences

Connect Google Drive with Excel using ODBC