How to connect to Google Drive in PowerShell using ODBC

[daniel]

Introduction

Google Drive is a widely used cloud storage service that allows users to store, manage, and share files. Automating data access and manipulation using PowerShell can significantly enhance productivity. This article will demonstrate connecting to Google Drive via ODBC in PowerShell using the ZappySys ODBC PowerPack. By leveraging the Google Drive Connector, you can seamlessly interact with your Google Drive files and folders using PowerShell.

Prerequisites

• Google account.
• Download and install the ZappySys ODBC PowerPack.

Steps

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

1. First of all, go to Google API Console.

2. Then click Select a project button and then click NEW PROJECT button:

   

3. Name your project and click CREATE button:

   

4. Wait until the project is created:

   

5. Select your project on the top bar:

   

6. Then click the “hamburger” icon on the top left and access APIs & Services:

   

7. Now let’s enable several APIs by clicking ENABLE APIS AND SERVICES button:

   

8. In the search bar search for drive and then locate and select Google Drive API:

   

9. Enable Google Drive API:

   

10. First of all, click the “hamburger” icon on the top left and then hit VIEW ALL PRODUCTS:

    

11. Then access Google Auth Platform to start creating an OAuth application:

    

12. Start by pressing GET STARTED button:

    

13. Next, continue by filling in App name and User support email fields:

    

14. Choose Internal option, if it’s enabled, otherwise select External:

    

15. Optional step if you used Internal option in the previous step. Nevertheless, if you had to use External option, then click ADD USERS to add a user:

    

16. Then add your contact Email address:

    

17. Finally, check the checkbox and click CREATE button:

    

18. In Google Auth Platform, select Clients menu item and click CREATE CLIENT button:

    

19. Choose Desktop app as Application type and name your credentials:

    

20. Continue by opening the created credentials:

    

21. Finally, copy Client ID and Client secret for the later step:

    

22. Done! We have all the data needed for authentication, let’s proceed to the last step!

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 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 Google Drive Data Using PowerShell

Once the ODBC driver is configured, use PowerShell to interact with Google Drive data.

Basic PowerShell Script to Retrieve Data

# Configure connection string and query
$connectionString = "DSN=API Driver - Google Drive"
$query = "SELECT * FROM Files"

# Instantiate OdbcDataAdapter and DataTable
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($query, $connectionString)
$table = New-Object System.Data.DataTable

# Fill the table with data
$adapter.Fill($table)

# Display data in the console
foreach ($row in $table.Rows) {
    Write-Host "$($row["FileName"]) - $($row["FileSize"]) bytes"
}

Read values in a console, save the script to a file and then execute this command inside the PowerShell terminal, You can also use even a simpler command inside the terminal, e.g. C:\Users\john\Documents\dsn.ps1:

Retrieving all fields

1. If you want to retrieve all columns you can use this variant of the code:

# Configure connection string and query
$connectionString = "DSN=API Driver - Google Drive"
$query = "SELECT CustomerId, CompanyName, Country, Phone FROM Customers"

# You can limit the number of lines to retrieve by using a `LIMIT` keyword in the query, e.g.
#SELECT * FROM Customers LIMIT 10

# Instantiate OdbcDataAdapter and DataTable
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($query, $connectionString)
$table = New-Object System.Data.DataTable

# Fill the table with data
$adapter.Fill($table)

# Display data in the console
# We will iterate through each row and column of the table to extract and display the values ​​separated by a tab.
foreach ($row in $table.Rows) {
    $line = ""
    foreach ($column in $table.Columns) {
        $value = $row[$column.ColumnName]

        # Let's handle NULL values
        if ($value -is [DBNull])
        {
            $value = "(NULL)"
        }
        $line += $value + "`t"
    }
    Write-Host $line
}

2. Run, and it will be successful; all column values are listed in the console
   
   

Using a Full ODBC Connection String (Without DSN)

Instead of relying on a DSN, use a full ODBC connection string:

$connectionString = "DRIVER={ZappySys API Driver};ServiceUrl=https://api.Google Driveapi.com/2/;Provider=Google Drive;AuthName=OAuth;ClientId=XXXXXXX;ClientSecret=XXXXXXXX;AccessToken=sl.B5IK2w5vXXXXXX;..."

Note: The ODBC connection string length is limited. If it’s too long, save it in a file and reference it using SettingsFile=C:\path\to\config.txt
e.g, DRIVER={ZappySys API Driver};SettingsFile=C:\temp\odbc-connection-string.txt.

---

Step 5: Writing Google Drive Data to a File in PowerShell

You can export Google Drive data to different file formats using PowerShell with this code:

# Configure connection string and query
$connectionString = "DSN=Google DriveDSN"
$query = "SELECT * FROM Customers"

# Instantiate OdbcDataAdapter and DataTable
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($query, $connectionString)
$table = New-Object System.Data.DataTable

# Fill the table with data
$adapter.Fill($table)

# Export table data to a file
$table | ConvertTo-File_Format-NoTypeInformation -Delimiter "`t" | Out-File "C:\Users\john\saved-data.txt" -Force

Export to CSV

$table | ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | Out-File "C:\Users\john\Google Drive-data.csv" -Force

Export to JSON

$table | ConvertTo-Json | Out-File "C:\Users\john\Google Drive-data.json" -Force

Export to HTML

$table | ConvertTo-Html | Out-File "C:\Users\john\Google Drive-data.html" -Force

Conclusion

By integrating Google Drive with PowerShell using the ZappySys ODBC API Driver, you can automate file management, extract metadata, and manipulate data efficiently. This approach provides a flexible and powerful solution, whether retrieving file details, updating records, or exporting data to various formats.

For further automation, consider scheduling these scripts in Windows Task Scheduler or integrating them into SSIS workflows to create an efficient data pipeline.

References

• Google Drive API Driver
• Google Drive API Driver – Documention
• Blog articles
• ODBC PowerPack

Contact us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.