Google Sheets Connector > Introduction to Google Sheets Connector > Administration of Google Sheets Connector
  

Administration of Google Sheets Connector

Before you use Google Sheets Connector, complete the following prerequisite tasks:
  1. 1Create a Google account to access Google Sheets.
  2. 2On the Dashboards page of the Google API Console, https://console.developers.google.com/, enable the Google Sheets API for your project. Google Sheets Connector uses the Google APIs to integrate with Google Sheets.
    3. The following image shows the Dashboard page where you can enable the APIs:
    The image shows the Dashboard page where you can enable the Google APIs.
  3. 3On the Credentials page of the Google API console, click on Create Credentials > OAuth client ID.
    4. The following image shows the Credentials page where you can create the credentials for your project:
    The image shows the Credentials page where you can create a service account and obtain a service account key.
  4. 4Fill the form on the OAuth consent screen tab.
  5. 5Click Save.
  6. 6Select Application type as Other.
  7. 7Enter the Name.
  8. 8Click Create.
    9. The Client ID and Client Secret appears on the screen. Copy the Client ID and Client secret values.
  9. 9In the OAuth 2.0 Client IDs section, download the JSON file corresponding to your OAuth client ID that contains the client_id, project_id, auth_uri, token_uri, auth_provider_x509_cert_url, client_secret, and redirect_uris values.
  10. 10Generate the OAuth 2.0 access tokens. You will need to enter these details when you create a Google Sheets connection in Data Integration.
    11. For more information on generating the OAuth 2.0 access tokens, click the following URL: https://developers.google.com/identity/protocols/OAuth2WebServer#obtainingaccesstokens

Generating OAuth 2.0 access tokens

Google Sheets Connector uses OAuth 2.0 protocol to connect to Google Sheets. After you generate the client ID and client secret for your project, generate the OAuth 2.0 access tokens.

Step 1. Generate the authorization code

You must generate the authorization code to gain access to the current site and to generate a valid refresh token.
Perform the following steps to generate the authorization code:
    1Enter the following URL in the Google Chrome browser:
    https://accounts.google.com/o/oauth2/auth? access_type=offline&approval_prompt=auto&client_id=<client_id>&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets&redirect_uri=<redirect_uri>
    For example, https://accounts.google.com/o/oauth2/auth?acess_type=offline&approval_prompt=auto&client_id=1234-abc54dfa6.apps.googleusercontent.com&response_type=code&scope=https://www.googleapis.com/auth/spreadsheets&redirect_uri=http://localhost
    2Choose the Google Account for which you want to approve the access request.
    3Click Allow to grant the permission to see, edit, create, and delete your spreadsheets in Google Drive. Click Allow to confirm.
    The redirect URL page includes the authorization code as a query string in the following format:
    https://<redirect_url>/?code=<authcode>&scope=https://www.googleapis.com/auth/spreadsheets
    The following image shows the authorization code as a query string in the URI:
    The image shows the authoization code as a query string in the URI.
    4Copy the authorization code displayed in the URL.

Step 2. Generate the refresh token and access token

Google Sheets Connector uses a authorization code to generate an access token and a refresh token. Google Sheets Connector uses an access token to authorize and access Google Sheets. The access token expires after a period of time. When the access token expires, you can use the refresh token to generate a new access token.
Perform the following steps to generate the refresh token:
    1Enter the following URL in the Postman application: https://accounts.google.com/o/oauth2/token
    2Select the POST method.
    3On the Body tab, select the x-www-form-urlencoded body type.
    4On the Body tab, add the following key-value pairs:
    Key
    Value
    grant_type
    Enter authorization_code.
    code
    Enter the authorization code that you generated in step 1.
    client_id
    The client_id value present in the JSON file.
    client_secret
    The client_secret value present in the JSON file.
    redirect_uri
    The redirect_uri value present in the JSON file.
    For example, http://localhost.
    The following image shows the Body tab where you can add the key-value pairs to generate the refresh token:
    The image shows the key-value pairs to generate a refresh token and access token in the Body tab.
    5Click Send.
    The refresh token and access token is generated in the Response tab. The following image shows the refresh_token and access_token in the Response tab:
    The following image shows the refresh_token and access_token in the Response tab.
    Note: Refresh token is generated only when you set the access_type parameter to offline in the initial request to obtain the authorization code. The expires_in field specifies the remaining time period of the access token in seconds.
    6Save the refresh token and access token in a secured location.

Step 3. Refreshing an access token

When the access token expires, you can use the refresh token to generate a new access token.
Perform the following steps to generate the access token:
    1Enter the following URL in the Postman application:
    https://accounts.google.com/o/oauth2/token
    2Select the POST method.
    3On the Body tab, select the x-www-form-urlencoded body type.
    4On the Body tab, add the following key-value pairs:
    Key
    Value
    grant_type
    Enter authorization_code.
    client_id
    The client_id value present in the JSON file.
    client_secret
    The client_secret value present in the JSON file.
    refresh_token
    The refresh token that you generated in step 2.
    The following image shows the Body tab where you can add the key-value pairs to generate a new access token:
    The image shows the key-value pairs to generate a new access token in the Body tab.
    5Click Send.
    The new access token is generated in the Response tab. The following image shows the new access token in the Response tab:
    The image shows the new access token in the Response tab.