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?
-
I found a similar thread in which an answer from Martin Omander explains about your query.Akshansha Singhal– Akshansha Singhal2021-09-30 10:54:08 +00:00Commented Sep 30, 2021 at 10:54
-
That thread is using the Google Sheets API, I'm using pandas.read_csv().Franco Piccolo– Franco Piccolo2021-09-30 11:04:34 +00:00Commented Sep 30, 2021 at 11:04
-
You can use the service accounts provided by GCP, and share the sheet only with that account rather than making it public.Gourav B– Gourav B2021-09-30 11:30:45 +00:00Commented Sep 30, 2021 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– Franco Piccolo2021-09-30 11:37:52 +00:00Commented Sep 30, 2021 at 11:37
-
Can you please include the repo code and the error message.Gourav B– Gourav B2021-09-30 13:40:03 +00:00Commented Sep 30, 2021 at 13:40
|
Show 3 more comments
1 Answer
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.
