Creating a private connection with Google Sheets

dtb_usr — Mon, 07 Oct 2024 15:44:31 GMT

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

Re: Creating a private connection with Google Sheets

agallard2 — Tue, 05 Nov 2024 07:30:31 GMT

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!

topic Re: Creating a private connection with Google Sheets in Data Engineering

Creating a private connection with Google Sheets

Re: Creating a private connection with Google Sheets