GoogleSheets
All connections to Google Sheets are authenticated using OAuth. The provider supports using user accounts, service accounts and GCP instance accounts for authentication.
User Accounts
AuthScheme must be set to OAuth in all of the user account flows. For desktop applications, the provider's embedded application is the simplest way to authenticate. The only additional requirement is to set InitiateOAuth to GETANDREFRESH.
When the driver starts, it will open a browser and Google Sheets will request your login information. The provider will use the credentials you provide to access your Google Sheets data. These credentials will be saved and automatically refreshed as needed. For desktop applications, the provider's default application is the simplest way to authenticate. The only additional requirement is to set InitiateOAuth to GETANDREFRESH.
When the driver starts, it will open a browser and Google Sheets will request your login information. The provider will use the credentials you provide to access your Google Sheets data. These credentials will be saved and automatically refreshed as needed.
Service Accounts
To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate.
For a JSON file, you will need to set these properties:
AuthScheme: Required. Set this to OAuthJWT.
InitiateOAuth: Required. Set this to GETANDREFRESH.
OAuthJWTCertType: Required. Set this to GOOGLEJSON.
OAuthJWTCert: Required. Set this to the path to the .json file provided by Google.
OAuthJWTSubject: Optional. Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
For a PFX file, you will need to set these properties instead:
AuthScheme: Required. Set this to OAuthJWT.
InitiateOAuth: Required. Set this to GETANDREFRESH.
OAuthJWTCertType: Required. Set this to PFXFILE.
OAuthJWTCert: Required. Set this to the path to the .pfx file provided by Google.
OAuthJWTCertPassword: Optional. Set this to the .pfx file password. In most cases this will need to be provided since Google encrypts PFX certificates.
OAuthJWTCertSubject: Optional. Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
OAuthJWTIssuer: Required. Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
OAuthJWTSubject: Optional. Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
If you do not already have a service account, you can create one by following procedure:
Enable the Google Sheets API
Follow these steps to enable the Google Sheets API:
Navigate to the Google Cloud Console.
Select Library from the left-hand navigation menu. This opens the Library page.
In the search field, enter "Google Sheets API" and select Google Sheets API from the search results.
On the Google Sheets API page, click ENABLE.
Create an OAuth Application for User Accounts (OAuth)
When using AuthScheme=OAuth, and you're using a web application, you must create an OAuth Client ID Application. For desktop and headless flows, creating a custom OAuth application is optional.
Follow these steps to create a custom OAuth application:
Navigate to the Google Cloud Console.
If you have not done so, follow the steps in the console to create an OAuth consent screen.
Select Credentials from the left-hand navigation menu.
On the Credentials page, select Create Credentials > OAuth Client ID.
In the Application Type menu, select Web application.
Specify a name for your OAuth custom web application.
Under Authorized redirect URIs, click ADD URI and enter a redirect URI. Press Enter.
Click CREATE, which returns you to the Credentials page.
A window opens that displays your client Id and client secret. Although the client secret is accessible from from the Google Cloud Console, we recommend you write down the client secret. You need both the client secret and client Id to specify the OAuthClientId and OAuthClientSecret connection properties.
Create an OAuth Application for Service Accounts (OAuthJWT)
When using AuthScheme=OAuthJWT, you must create a Service Account Application. Follow these steps:
Navigate to the Google Cloud Console.
If you have not done so, follow the steps in the console to create an OAuth consent screen.
Select Credentials from the left-hand navigation menu.
On the Credentials page, select Create Credentials > Service account.
On the Create service account page, enter the Service account name, the Service account ID, and, optionally, a description.
Click DONE. This returns you to the Credentials page.
GCP Instance Accounts
When running on a GCP virtual machine, the provider can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.
Connecting to Google Sheets
All connections to Google Sheets are authenticated using OAuth. The provider supports using user accounts, service accounts, GCP instance accounts, and API keys for authentication.
Authenticating to Google Sheets
The provider supports using user accounts, service accounts and GCP instance accounts for authentication.
The following sections discuss the available authentication schemes for Google Sheets:
User Accounts (OAuth)
Service Account (OAuthJWT)
GCP Instance Account
User Accounts (OAuth)
AuthScheme must be set to OAuth in all user account flows.
After setting the following connection properties, you are ready to connect:
InitiateOAuth: Set this to GETANDREFRESH, which instructs the provider to automatically attempt to get and refresh the OAuth access token.
OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
When you connect the provider opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process as follows:
Extracts the access token from the callback URL.
Obtains a new access token when the old one expires.
Saves OAuth values in OAuthSettingsLocation that persist across connections.
Service Accounts (OAuthJWT)
To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate. If you do not already have a service account, you can create one by following the procedure in Creating a Custom OAuth App.
For a JSON file, set these properties:
AuthScheme: Set this to OAuthJWT.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to GOOGLEJSON.
OAuthJWTCert: Set this to the path to the .json file provided by Google.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
For a PFX file, set these properties instead:
AuthScheme: Set this to OAuthJWT.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to PFXFILE.
OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
GCP Instance Accounts
When running on a GCP virtual machine, the provider can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.
API Keys
To connect using API Keys, set the APIKey property, and set AuthScheme to Token.
You can create an API key in the Google Cloud Console by clicking Create credentials > API key. You can restrict the key before using it in production by clicking Restrict key and selecting one of the Restrictions.
Last updated