Google sheets data migration to cloud storage

[purna05]

Hi Community,

I hope you are doing well.

I need suggestions or referrals to create a data pipeline. My source is Google Drive and Google Sheets, and I want to read data from Drive and load it to Google Cloud Storage (GCS). I created a service account, but company policies restrict granting it access to Drive files. I've explored two approaches using user authentication, but I prefer a solution using service account authentication.

Approach 1: Cloud function code triggered to load Google Sheets data using user authentication. Approach 2: External table creation using GSheets URI and export query to GCS with Cloud Scheduler. it also using User Authentication

Can you suggest an Google service or approach that leverages service account authentication for both Drive and Google Sheets?

[icrew]

Perhaps https://philipplies.medium.com/transferring-data-from-google-drive-to-google-cloud-storage-using-goo... would help? (I know almost nothing about this, other than the fact that that article exists...)

Cheers,

Ian

[purna05]

Hi Ian,

I tried with Drive Mount it will ask for user authentication. but I looking for a different approach because my company policies do not accept service accounts to access Google Drive and they are not  accepting user authentication because it contains user credentials need to reset quarterly 

Is there any approach or suggestion rather than service account and user authentication 

[mhawksey]

Not sure if it entirely fits your use case but if you only need to do this on a small scale it is possible to using Google Apps Script oAuth tokens by adding additional scopes to your project. I've written about how you can do this here https://medium.com/cts-technologies/managing-google-cloud-storage-files-with-google-apps-script-with...