Google Sheet APIs Using Google Cloud Platform(GCP) Credentials

Cloud Service Provider is a third-party company which provides easy and affordable access to applications and resources without having any requirement for internal infrastructure or hardware. Amazon’s AWS (Amazon Web Services), Microsoft’s Azure, IBM’s IBM Cloud and Google’s Google Cloud are such kinds of cloud service providers that currently exist.

Accordingly, Google Cloud Platform is a suite where Google offers public cloud computing services. These services can be accessed through Google Cloud Console, a web-based, graphical user interface to manage Google cloud projects and resources. One of the major services is Google Cloud APIs, which are used to access Google Drive, Google Docs, Google Sheets and Google Slides, etc.

This article will provide an understanding of using Google APIs through all the available GCP credentials. For that, I have mainly taken Google Sheets API which allows you to read, write and format google sheets. Let's look at how to use GCP credentials to access Google Sheets APIs.

1. Create a new project in Google Cloud Console

A project organises all the Google cloud resources. Therefore, first and foremost, a new project needs to be created in the Google Cloud console. Please have a look at the Google Cloud doc to understand the process of creating a new project.

2. Enable Google Sheets API under the created project

In order to get access to the required API in the created project, the specific API needs to be enabled. Therefore, click ENABLE APIS AND SERVICES in the API Console of the newly created project and enable Google Sheets API.

Enabling Google Sheet API

3. Create specific credentials according to the requirement

Credentials are generally used to obtain an access token from Google’s authorization server in order to access Google APIs.

Access token -> It is a token-based authentication used to access an API through a third-party application.

There are three types of credentials that can be used to access the API in different scenarios according to the requirements.

Type of credentials to access Google APIs

I will use the Postman application and curl commands to explain the APIs and responses. Only the APIs responsible for reading the google sheet will be used here. You can try out any Google Sheet API according to the requirement you have.

First and foremost, create a sample google sheet with any kind of data that you wanted to try out the Google Sheets API. The sample data I have used is as follows.

Sample google sheet data

Type of credentials and examples of their usages

API key

The API key can only be used to access publicly available information through the API. Therefore, in order to access a google sheet through an API key, the google sheet needs to be made public (Anyone with the link).

Public access to the Google Sheet

The obtained API key can be used to access the spreadsheet using google Sheet APIs as follows.

curl --location --request GET 'https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{sheetName}?key={APIKey}'

spreadsheetId -> ID of the spreadsheet which can be found from the URL of the spreadsheet.

sheetName -> Name of the single sheet from which the data needs to be accessed.

APIKey -> API key generated in the google cloud console.

The API response will be as follows,

{
    "range": "Sheet1!A1:Z1000",
    "majorDimension": "ROWS",
    "values": [
        [
            "Fruits",
            "Price"
        ],
        [
            "Apple",
            "45"
        ],
        [
            "Banana",
            "22"
        ],
        [
            "Orange",
            "56"
        ],
        [
            "Pineapple",
            "89"
        ]
    ]
}

OAuth 2.0 Client IDs

The OAuth 2.0 Client ID can be used to access private information through the API. Therefore, in order to access a google sheet through the OAuth 2.0 Client ID, the google sheet needs to be made private (Restricted).

Restricted access to the Google Sheet

In order to create the OAuth Client ID credential, the OAuth consent screen needs to be configured as a pre-requirement. Please have a look at the Google workspace guide for configuring OAuth consent. Following are the type of information that needs to be filled in the OAuth consent screen.

• User type:- “External” (optionally “Internal” if you are a GSuite user)
• Authorized domains:- “oauth.pstmn.io” (Since we are using postman as the third-party application.)
• App information:- This shows in the consent screen, and helps end users know who you are and contact you
• Developer contact information:- This email address is for Google to notify you about any changes to your project (use the same email address)
• Scopes:- “https://www.googleapis.com/auth/spreadsheets.readonly” (Since we require just the read access of the spreadsheet)
• Test users:- The email address of the user that requires to authenticate with the client id of the project. Initially, your project will be in testing mode and will only be accessible to the users that you add to the list of test users. (You can simply use the same email address)

Note:- Refer to the GitHub issue for more understanding regarding the OAuth consent screen configuration.

After the configuration of the OAuth consent screen, create an OAuth client ID credential with the authorized redirect URI https://oauth.pstmn.io/v1/callback as mentioned below.

OAuth 2.0 client ID creation

The created Client ID and Client Secret will be visible as follows.

OAuth 2.0 client credentials

Note:- According to the requirement, you can create multiple OAuth 2.0 Client ID credentials (In case you wanted to access google APIs using several 3rd party applications). Anyhow, make sure that you have provided the correct Authorized redirect URIs.

Initially, according to the OAuth 2.0 standards, we need to obtain the access token to access the Google Sheet data.

• Token Name-> Name of your choice
• Grant Type-> Authorization Code
• Auth URL -> https://accounts.google.com/o/oauth2/v2/auth
• Access Token URL -> https://oauth2.googleapis.com/token
• Client ID -> Obtained client ID from the created project in GCP
• Client Secret -> Obtained client secret from the created project in GCP
• Scope -> https://www.googleapis.com/auth/spreadsheets.readonly (depends on the requirement)

POSTMAN new token configuration

Then, you need to provide consent to give view access to all your Google Sheets spreadsheets to obtain the access token which can be used to access Google Sheet API.

The obtained access token can be used to access the spreadsheet using google Sheet APIs as follows.

curl --location --request GET 'https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1' \
--header 'Authorization: Bearer {ACCESS_TOKEN}'

The API response will be the same as earlier since there is no change in the spreadsheet data.

Service Accounts

A service account is a special type of Google account which can be created under the credentials of a project and represents a non-human user that needs to be authenticated and authorized in order to access data using Google APIs. The service account's credentials will include a unique generated email address and public/private key pair.

Creating a new JSON key under a service account will download a JSON file which would contain the information such as project_id, private_key, client_id, client_email, etc.

Service account key generation

For a clear understanding of a service account, you can simply think of it as a separate email account which can be used by the applications to make authorized API calls by authenticating it as a service account itself.

Since the service account itself needs to be authenticated, the google sheet that we want to access using Sheets APIs needs to have access using that created service account. Therefore, we need to share the particular google sheet with the created service account.

Sharing the Google Sheet with service account email

Then after, the JWT token needs to be created using a header and a payload as follows. The access token needs to be generated using this created JWT token.

• Header

{
  "alg": "RS256",
  "typ": "JWT"
}

• Payload

  {
    "iss": "sample-service-account@sample-project-******.iam.gserviceaccount.com",
    "scope": "https://www.googleapis.com/auth/spreadsheets.readonly",
    "aud": "https://www.googleapis.com/oauth2/v4/token",
    "iat": 1672085129,
    "exp": 1672085189
  }

iat and exp provide the current UNIX time and the token expiry UNIX time respectively. Configure exp with a difference of 60 minutes which will be the maximum allowed access token expiry time (exp can also be specified according to the requirement, which means less than 60 minutes).

Finally, an Access token can be obtained from the JWT token using an API call to the token endpoint. The obtained access token can later be used in POSTMAN or any kind of application to read data from the particular google sheet.

The sample bash script for obtaining an Access token using JWT is as follows. (Here, sample-project-key.json is the downloaded JSON key file which is in the same directory of the bash script)

#!/bin/bash

key_json_file="sample-project-key.json"
scope="https://www.googleapis.com/auth/spreadsheets.readonly"
epoch_now=$(date +%s)
epoch_expire=$((epoch_now+60))

base64encode() {
    base64 | tr '/+' '_-' | tr -d '=\n'
}

private_key=$(jq -r .private_key $key_json_file)
client_email=$(jq -r .client_email $key_json_file)

header='{"alg":"RS256","typ":"JWT"}'
claim=$(cat <<EOF | jq -c .
  {
    "iss": "$client_email",
    "scope": "$scope",
    "aud": "https://www.googleapis.com/oauth2/v4/token",
    "exp": $epoch_expire,
    "iat": $epoch_now
  }
EOF
     )
request_body="$(echo -n "$header" | base64encode).$(echo -n  "$claim" | base64encode)"
signature=$(openssl dgst -sha256 -sign <(echo -n "$private_key") <(printf "$request_body") | base64encode)
JWT="$request_body.$signature"

RESPONSE=$(curl -d "grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=${JWT}
  " https://oauth2.googleapis.com/token)
ACCESS_TOKEN=$(echo $RESPONSE | jq '.access_token')

Conclusion

That’s it. I hope that this article would have given a clear understanding of all three types of credentials of Google APIs that can be used in different scenarios according to the requirement. Especially, the API key for public data access, OAuth 2.0 credential for private data access and Service accounts for the non-human user (automation) to access public and private data. Anyhow there is a separate way to use OAuth 2.0 credentials for automation using a refresh token. This will be discussed in a separate article.

I appreciate you and the time you took out of your day to read this! Please feel free to add your comments if any.😃❤️