topic Re: Connecting live google sheets data to Databricks in Get Started Discussions

Connecting live google sheets data to Databricks

Patrick-Data — Wed, 31 Jan 2024 03:27:55 GMT

Hi! So we have a live google sheets data that gets updated on an hourly/daily basis, and we want to bring it to databricks as a live/scheduled connection for further analysis, together with other tables and views present there. Do you have any suggestions or recommendations to approach this matter?

Re: Connecting live google sheets data to Databricks

Ajay-Pandey — Wed, 31 Jan 2024 04:44:14 GMT

Hi @Patrick-Data

Yes, you can do it only you have to follow below steps -

1. Open Google sheet from which you want fetch data

2. Go to -File->Share->Publish to web- Select sheet->Select Sheet ->select .csv

4. Click on publish ->copy the URL

5. Use below code to fetch the live data

import pandas as pd pdDF = pd.read_csv("URL which you have copied") #Convert to spark DF sparkDF = spark.createDataFrame(pdDF)

Re: Connecting live google sheets data to Databricks

Patrick-Data — Wed, 31 Jan 2024 05:06:18 GMT

Thanks @Ajay-Pandey ! Appreciate your reply. I am new to Databricks, apologies, but I wonder if it's possible to put this live data into a table under a specific catalog-schema? Such that the table will reflect the live data in google sheet?

Re: Connecting live google sheets data to Databricks

Ajay-Pandey — Fri, 02 Feb 2024 08:27:41 GMT

Hi @Patrick-Data

Yes you can load the live data in delta tables, but data only be refreshed once you run the related jobs.

Also for this use case you can go with databricks workflow that will help you to schedule the table refresh

Re: Connecting live google sheets data to Databricks

sunnyh — Tue, 24 Sep 2024 20:07:59 GMT

Hi @Ajay-Pandey, I have similar question. I would like to import the Google Sheet content to Databricks but don't want to expose the content to the public. Is there a way to do it other than public the sheet? Thank you1