-1

I managed to load a sheet into a pandas dataframe using pd.read_csv(sheet_url) but first I had to make the sheet public, which is not desirable.

Is there a way to authenticate somehow when running pd.read_csv(sheet_url) from the cloud function?

Akshansha Singhal
  • 844
  • 2
  • 9
Franco Piccolo
  • 6,845
  • 8
  • 34
  • 52
  • I found a similar [thread](https://stackoverflow.com/questions/44448029/how-to-use-google-sheets-api-while-inside-a-google-cloud-function) in which an answer from Martin Omander explains about your query. – Akshansha Singhal Sep 30 '21 at 10:54
  • That thread is using the Google Sheets API, I'm using pandas.read_csv(). – Franco Piccolo Sep 30 '21 at 11:04
  • You can use the [service accounts](https://cloud.google.com/iam/docs/service-accounts) provided by GCP, and share the sheet only with that account rather than making it public. – Gourav B Sep 30 '21 at 11:30
  • @GouravB I tried sharing the sheet with the service account associated with the Cloud function but that is not working when running pd.read_csv, have u tried it and it works? – Franco Piccolo Sep 30 '21 at 11:37
  • Can you please include the repo code and the error message. – Gourav B Sep 30 '21 at 13:40
  • The code is the one shared. I can’t share the sheet url, but i tried the same code with the sheet as public and it works. – Franco Piccolo Sep 30 '21 at 13:47
  • Are you looking for a built-in pandas API like one for [Google Big Query](https://pandas.pydata.org/docs/reference/api/pandas.read_gbq.html) – Gourav B Sep 30 '21 at 18:45
  • I want to use pd.read_csv if possible, if not possible maybe another alternative implemented in Python – Franco Piccolo Sep 30 '21 at 21:28

1 Answers1

0

I am suggesting below the different approaches which you can try to resolve your query:

GCP provides service accounts for sharing the sheet only with that account rather than making it public and steps for that are: Create the service account. Save the service account key Create an API key Save the API key Grant spreadsheet access to the service account Here is the quickstart which you can perform to get yourself familiar with the official Python APIs for Google Drive. Additionally, you can refer to how to access Google Sheet using OAuth and Python API as mentioned here.

Pandas support the Google Cloud BigQuery APIs including Authenticating with a Service Account, but similar out-of-box APIs are not available for Google Drive/Sheets. However, you can look at this StackOverflow answer on how to achieve this using pandas.

I’ve found a guide to Google Sheets with Python which can help, it uses a python package called pygsheets.

Akshansha Singhal
  • 844
  • 2
  • 9