Creating a private connection with Google Sheets

[dtb_usr]

How do I ingest sensitive data from a googlesheet doc to Databricks unity catalogue without making the googlesheet public.

[agallard2]

Hi @dtb_usr,

You can share the Google Sheet with the Service Account and use Google Sheets API Client

• Open the Google Sheet you want to access.
• Click on Share and add the email address of the service account (it will look something like your-service-account@your-project.iam.gserviceaccount.com).

This step allows the service account to access the Google Sheet securely without making it public.

To access Google Sheets from Databricks, you’ll need the Google API Python client. You can install it in your Databricks notebook:

%pip install --upgrade google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client pandas

And now you can read data:

import pandas as pd
from google.oauth2 import service_account
from googleapiclient.discovery import build

# Path to the service account key JSON file
SERVICE_ACCOUNT_FILE = "/dbfs/path/to/your/service_account_key.json"  # Adjust the path as needed

# Set up the credentials
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE,
    scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"]
)

# Google Sheets API setup
spreadsheet_id = 'your_spreadsheet_id'  # Replace with your Google Sheet ID
range_name = 'Sheet1!A1:D'  # Specify the range you want to pull

service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()

# Fetch data from Google Sheets
result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_name).execute()
values = result.get('values', [])

# Convert to DataFrame for further processing
if values:
    df = pd.DataFrame(values[1:], columns=values[0])  # Assuming the first row is headers
else:
    df = pd.DataFrame()

display(df)

Replace 'your_spreadsheet_id' with the ID of your Google Sheet (the part of the URL between /d/ and /edit).

Regards!