Connecting live google sheets data to Databricks

[Patrick-Data]

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?

[Ajay-Pandey]

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) 

[Patrick-Data]

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?

[Ajay-Pandey]

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

[sunnyh]

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