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. By leveraging the Google Drive Connector, which is part of the ZappySys ODBC PowerPack., 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
-
Navigate to the Google API Console.
-
Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.
-
Once the project is set, click ENABLE APIS AND SERVICES.
-
Enable both Sheets API and Drive API by searching for them and clicking ENABLE.
-
Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.
-
Move to the Credentials tab.
-
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
-
Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
-
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.
-
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
-
Fill in the connector fields, including Client ID, Client Secret, and Scopes.
-
Generate the token and test the connection.
-
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 the connection string and query
$connectionString = "DSN=API Driver Google Drive"
$query = @"
SELECT
Id, Name, MimeType, Size, CreatedTime, ModifiedTime
FROM Files
"@
# Create the ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
try {
# Open the connection
$conn.Open()
# Create the ODBC command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
# Create the adapter and data table
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$table = New-Object System.Data.DataTable
# Fill the table with data
$adapter.Fill($table) | Out-Null
# Check if data is available
if ($table.Rows.Count -eq 0) {
Write-Host "No files found in Google Drive."
} else {
Write-Host "Files found: $($table.Rows.Count)"
Write-Host "-----------------------------"
# Print the retrieved data with null value handling
foreach ($row in $table.Rows) {
$id = if ($row["Id"]) { $row["Id"] } else { "N/A" }
$name = if ($row["Name"]) { $row["Name"] } else { "Unnamed" }
$mimeType = if ($row["MimeType"]) { $row["MimeType"] } else { "Unknown" }
$size = if ($row["Size"]) { $row["Size"] } else { "0" }
$created = if ($row["CreatedTime"]) { $row["CreatedTime"] } else { "N/A" }
$modified = if ($row["ModifiedTime"]) { $row["ModifiedTime"] } else { "N/A" }
Write-Host "$name ($mimeType) - $size bytes - Created: $created - Modified: $modified"
}
}
}
catch {
Write-Host "Query error: $_"
}
finally {
# Close the connection
$conn.Close()
$conn.Dispose()
}
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. powershell .\GoogleDrivedsn.ps1:
Retrieving all fields
- If you want to retrieve all columns you can use this variant of the code:
# Configure the connection string and query
$connectionString = "DSN=API Driver Google Drive"
$query = "SELECT * FROM Files"
# Create the ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
try {
# Open the connection
$conn.Open()
# Create the ODBC command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
# Create the adapter and data table
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$table = New-Object System.Data.DataTable
# Fill the table with data
$adapter.Fill($table) | Out-Null
# Check if data is available
if ($table.Rows.Count -eq 0) {
Write-Host "No files found in Google Drive."
} else {
Write-Host "Files found: $($table.Rows.Count)"
Write-Host "-----------------------------------"
# Get all column names dynamically
$columns = $table.Columns.ColumnName
# Print the column headers
Write-Host ($columns -join " | ")
# Print each row with all column values
foreach ($row in $table.Rows) {
$values = @()
foreach ($col in $columns) {
$values += if ($row[$col]) { $row[$col] } else { "N/A" }
}
Write-Host ($values -join " | ")
}
}
}
catch {
Write-Host "Query error: $_"
}
finally {
# Close the connection
$conn.Close()
$conn.Dispose()
}
- 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, like JSON or HTML, using PowerShell with this code:
# Ensure the output directory exists
$OutputDir = "C:\Temp"
if (!(Test-Path -Path $OutputDir)) {
New-Item -ItemType Directory -Path $OutputDir | Out-Null
}
# Configure the connection string and query
$connectionString = "DSN=API Driver Google Drive"
$query = "SELECT * FROM Files"
# Define output file paths
$jsonFile = "$OutputDir\GoogleDriveFiles.json"
$csvFile = "$OutputDir\GoogleDriveFiles.csv"
$htmlFile = "$OutputDir\GoogleDriveFiles.html"
# Create the ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
try {
# Open the connection
$conn.Open()
# Create the ODBC command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
# Create the adapter and data table
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$table = New-Object System.Data.DataTable
# Fill the table with data
$adapter.Fill($table) | Out-Null
# Check if data is available
if ($table.Rows.Count -eq 0) {
Write-Host "No files found in Google Drive."
} else {
Write-Host "Files found: $($table.Rows.Count)"
Write-Host "Saving data..."
# Convert DataTable to an array of objects for easy export
$dataArray = @()
foreach ($row in $table.Rows) {
$rowObject = @{}
foreach ($col in $table.Columns.ColumnName) {
$rowObject[$col] = $row[$col] -as [string] # Convert values to strings to avoid issues
}
$dataArray += $rowObject
}
# Save as JSON
$dataArray | ConvertTo-Json -Depth 3 | Set-Content -Path $jsonFile
Write-Host "JSON file saved: $jsonFile"
# Save as HTML
$htmlContent = "<html><head><title>Google Drive Files</title></head><body>"
$htmlContent += "<h2>Google Drive Files</h2><table border='1'><tr>"
# Add column headers
foreach ($col in $table.Columns.ColumnName) {
$htmlContent += "<th>$col</th>"
}
$htmlContent += "</tr>"
# Add row data
foreach ($row in $dataArray) {
$htmlContent += "<tr>"
foreach ($col in $table.Columns.ColumnName) {
$htmlContent += "<td>$($row[$col])</td>"
}
$htmlContent += "</tr>"
}
$htmlContent += "</table></body></html>"
# Save HTML file
$htmlContent | Set-Content -Path $htmlFile -Encoding UTF8
Write-Host "HTML file saved: $htmlFile"
}
}
catch {
Write-Host "Query error: $_"
}
finally {
# Close the connection
$conn.Close()
$conn.Dispose()
}
Export to CSV
# Convert the hashtable to an array of PSCustomObject
$dataArray = @()
foreach ($row in $table.Rows) {
$rowObject = [PSCustomObject]@{}
foreach ($col in $table.Columns.ColumnName) {
# Convert collections to a comma-separated string
$value = $row[$col]
if ($value -is [System.Collections.IEnumerable] -and $value -notmatch "String") {
$value = ($value -join ", ") # Convert collection to a readable format
}
$rowObject | Add-Member -MemberType NoteProperty -Name $col -Value $value
}
$dataArray += $rowObject
}
# Save as CSV
$dataArray | Export-Csv -Path "C:\Temp\GoogleDriveFiles.csv" -NoTypeInformation -Encoding UTF8
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 more advanced SSIS file operations, explore the ZappySys ODBC PowerPack.
References
Contact us
If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.