Google Sheets as a Data Source - Authenticating via OAuth2
Google Sheets are used by our First Line employees up to our Leadership team, we've had many requests to connect to these as a data source but we faced an issue; Google had upgraded their security for API calls and their new method made it difficult to connect to. It no longer allowed a user's Username and Password to be accepted as credentials and instead required a Client Secret Key that needed to be generated. (OAuth2)
After some research we have found out that there are some very useful and detailed documents made by Google Developers on how to create OAuth2 credentials in your company's Google Cloud Admin console. (Link below)
You can use many different programming languages after you set up your OAuth2 credentials to draw this data, we went with Python. Their Quickstart Python script is presented in the link above as well to get you started!
Once you get a hang of how to query and manipulate your data/results you can output them to CSV where we then had Data Discovery pointing to these files that get updated every morning.
Setting up OAuth2 Credentials will grant that 'user' access to any Google Sheets saved within the Company's security domain as it is seen as a registered user.
Figured I'd make a post about this and share our experience, it is possible and this documentation is great! If you have any questions I'll try my best to answer or help!