Access to Google Workspace documents from Cloud Build

Published in

Google Cloud - Community

5 min readOct 27, 2022

Modern code development is based on automation, and especially CI/CD pipelines (Continuous Integration Continuous Deployment/Delivery). On Google Cloud, Cloud Build is the serverless solution to achieve CI/CD and offers many features and highly customizable pipelines.

During the code packaging, you might need to rely on other data than only your code, for configuration, enrichment, customization, versioning,… That new source of data can be in a database but also on a Google Workspace document.

And accessing a Google Workspace document from Cloud Build is not so easy!

Accessing a Google Workspace document

Before trying to access the document, let’s start by writing a small python script main.py that read a Google Sheet document

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = 'YOUR_SHEET_ID'
SAMPLE_RANGE_NAME = 'DATA!A2:E' #Example


def main():
    """Shows basic usage of the Sheets API.
    Prints values from a sample spreadsheet.
    """

    creds, _ = google.auth.default()
    try:
        service = build('sheets', 'v4', credentials=creds)
        # Call the Sheets API
        sheet = service.spreadsheets()
        result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                    range=SAMPLE_RANGE_NAME).execute()
        values = result.get('values', [])

        if not values:
            print('No data found.')
            return

        print('Name, Major:')
        for row in values:
            # Print columns A and E
            print('%s, %s' % (row[0], row[4]))
    except HttpError as err:
        print(err)


if __name__ == '__main__':
    main()

Install the dependencies (file requirements.txt)

google-api-python-client
google-auth# Install dependencies with: pip3 install -r requirements.txt

You can perform a first try.

python3 main.py

Fail… Insufficient scopes. There is an issue with the default authentication.

When you authenticate yourself on your environment with the command gcloud auth application-default login, you use the default scopes, which do not include the Google Sheet scope.
Let’s fix that

To scope your personal account you just have to explicitly define the scopes and add thoses that you want in addition of the Google Cloud Platform ones.

gcloud auth application-default login \
--scopes='https://www.googleapis.com/auth/spreadsheets.readonly',\
https://www.googleapis.com/auth/cloud-platform'

And try your code again. This time it works!!

Perfect!! Let’s use this script in Cloud Build!

The naïve first try

To run the Python script in Cloud Build, you have to write a cloudbuild.yaml file that describe the operation. We will use a single step with a Python image.

steps:
  - name: 'python:slim-buster'
    script: |
      pip3 install -r requirements.txt
      python3 main.py

And run the build;

gcloud builds submit

Oh no, Insufficient scopes!! But this time, you can’t scope the current account, because it is provided by the metadata server.

Let’s update the code to enforce that scope programmatically
Update only the creds variable definition.

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
creds, _ = google.auth.default(scopes=SCOPES)

And try again. Not better…

Impossible to scope the default service account. Let’s try something else!

The custom service account try

With some services, like Compute Engine, you can’t change the scope of the default service account at runtime.

You have to stop the VM, change the scopes and restart it. So boring

Or, and it’s the most interesting and my most ambitious option

You can use a customer-managed service account. Then you can scope the service account as you wish at runtime!

So, let’s use a customer-managed service account with Cloud Build. You have to create a service account, and grant the correct permissions on it.

#Create the service account
gcloud iam service-accounts create custom-sa#Grant the permission 
gcloud projects add-iam-policy-binding \
--member="serviceAccount:custom-sa@PROJECT_ID.iam.gserviceaccount.com" \
--role="roles/storage.objectViewer" PROJECT_IDgcloud projects add-iam-policy-binding \
--member="serviceAccount:custom-sa@PROJECT_ID.iam.gserviceaccount.com" \
--role="roles/logging.logWriter" PROJECT_ID

Replace the PROJECT_ID with your own project ID

And update your cloudbuild.yaml file like that

steps:
  - name: 'python:slim-buster'
    script: |
        pip3 install -r requirements.txt
        python3 main.py
serviceAccount: 'projects/PROJECT_ID/serviceAccounts/custom-sa@PROJECT_ID.iam.gserviceaccount.com'
options:
  logging: CLOUD_LOGGING_ONLY

Finally, have a final try… And fail again Insufficient scopes. Why??

Changing scope on runtime service account is not possible on Cloud Build. What’s the next option?

The impersonation solution

So, the metadata server can’t generate a token with a different scope. The issue is similar as this one we had with the user account at the beginning. Impossible to change the token scope at runtime.
The solution was to regenerate a token with the correct scopes, from the beginning.

Therefore, the next option is to do the same thing and to ask for a new token, on the service account, with the correct scopes at token creation time.

For that, we can use a feature named impersonation. The principle is to generate a token (access token or identity token) on behalf another account.
And, by the way, inherit of all its permissions

In the code, you have to change things:

You must know the service account email to impersonate (for production purpose, use environment variable to provide it)
You have to create the impersonated credential and to use it

import google.auth.impersonated_credentials

from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = 'YOUR_SHEET_ID'
SAMPLE_RANGE_NAME = 'DATA!A2:E'


def main():
    """Shows basic usage of the Sheets API.
    Prints values from a sample spreadsheet.
    """    SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
    creds, _ = google.auth.default(scopes="https://www.googleapis.com/auth/cloud-platform")
    icreds = google.auth.impersonated_credentials.Credentials(
        source_credentials=creds,
        target_principal=custom-sa@PROJECT_ID.iam.gserviceaccount.com,
        target_scopes=SCOPES,
    )


    try:
        service = build('sheets', 'v4', credentials=icreds)

        # Call the Sheets API
        sheet = service.spreadsheets()
        result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                    range=SAMPLE_RANGE_NAME).execute()
        values = result.get('values', [])

        if not values:
            print('No data found.')
            return

        print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s, %s' % (row[0], row[4]))
    except HttpError as err:
        print(err)


if __name__ == '__main__':
    main()

To allow the Cloud Build runtime service account impersonating the target service account, the runtime service account must be allowed to create a token on the target service account.

So, you have to grant the roles Service Account Token Creator on the runtime service account like that

gcloud projects add-iam-policy-binding \
--member="serviceAccount:custom-sa@PROJECT_ID.iam.gserviceaccount.com" \
--role="roles/iam.serviceAccountTokenCreator" PROJECT_ID

And have a try… Fail again!!

But the good news is that it’s not the same error! It’s an error from Google Sheet because the target service account hasn’t the permission to access to it!

Wonderful!! Go to your sheet document, click on the SHARE button and add the target service account email as VIEWER of the sheet document.

Have a new try and this time it works!!

Note: the customer-managed service account is not longer required. You can switch back to the Cloud Build default service account; Don’t forget to grant the correct role on it to let it impersonate the service accounts.

Note 2: you can’t impersonate the Cloud Build default service account itself. You can only impersonate a customer-managed service account (a service account that you create and which is attached to your project)

Inconsistent security solutions

Security is paramount and Google Cloud offers really strong and versatile security options.

However, those options are inconsistent from a service to another one and requires expertise, failure and hack to success in an elegant way.
Because of those difficulties, many users don’t spend time and go quickly to the fastest and the ugliest solution: They use a service account key file (which is the anti-pattern of security)!

I hope that article helps you to successfully and elegantly manage your security issues and stay safe in any circumstances!