Google Sheets

Single Sheet Extraction

To ingest data from a public Google Sheet, you first need to enable sharing on the file. Open the sheet in Google Sheets, click Share → Change to anyone with the link, and set the role to Viewer.

The share link will look like this:
https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit?usp=sharing

Google Sheets supports a direct CSV export URL. Extract the spreadsheet ID from the link and use the following export format:

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/export?format=csv

If your sheet has multiple tabs, you can target a specific one by appending the gid parameter (found in the URL when the tab is selected):

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/export?format=csv&gid=0

Following is the full code:

import polars as pl
import os

spreadsheet_id = os.environ['SPREADSHEET_ID']
url = f'https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?format=csv'

def transform():
    df = pl.read_csv(url)
    return df

Note

Typically, a spreadsheet URL will contain an access token. It is advised not to hard-code the ID into the string but rather use the secrets store to inject the ID during the build.

---

Single Sheet Extraction (Private Sheets via Service Account)

If your Google Sheet is private, you will need to authenticate using a Google Service Account.

Create a Google Cloud Project

• Go to https://console.cloud.google.com/.
• Click the project selector (top left) and choose “New Project.”
• Give it a descriptive name, for example DataSpace Sheets Access.
• Click Create.

Enable the Google Sheets API

• In the left sidebar, go to APIs & Services → Library.
• Search for Google Sheets API.
• Click Enable.
• Optionally, also enable the Google Drive API if you need to look up spreadsheets by name.

Create a Service Account

• Go to APIs & Services → Credentials.
• Click Create Credentials → Service Account.
• Enter a name like dataspace-sheets-access.
• Leave Permissions and Principals with access empty — no roles or users are needed.
• Click Done.

Create a Key File

• In the service account list, click your new account.
• Open the Keys tab.
• Click Add Key → Create New Key → JSON.
• Save the downloaded .json file (for example, mcp.json).

You’ll store this key in DataSpace’s secrets store later. Do not add the file to the workspace: everyone who can view the workspace can read its files.

Share Your Google Sheet with the Service Account

• Open the Google Sheet.
• Click Share.
• Copy the client email from the service account JSON file (it looks like dataspace-sheets-access@your-project-id.iam.gserviceaccount.com).
• Add that email as a Viewer.
• Copy the spreadsheet ID from the URL — it’s the long string between /spreadsheets/d/ and the next /.

Example:

https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit
→ Spreadsheet ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms

Prepare Your DataSpace Workspace

Declare the dependencies in _config.json:

{
  "packages": [
    "google-api-python-client",
    "google-auth"
  ]
}

In the secrets store, add a secret named GOOGLE_SERVICE_ACCOUNT_JSON and paste the entire contents of the key file as its value. It reaches the build as an environment variable.

Write the Transformation

import polars as pl
from google.oauth2 import service_account
from googleapiclient.discovery import build
import json
import os

# Spreadsheet ID and target sheet/tab name
SPREADSHEET_ID = "<REPLACE_WITH_SPREADSHEET_ID>"
SHEET_NAME = "Sheet1"

# The service account key, from the secrets store
SERVICE_ACCOUNT_INFO = json.loads(os.environ["GOOGLE_SERVICE_ACCOUNT_JSON"])

# Sheets API scope
SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

def transform():
    # Authenticate using the service account
    creds = service_account.Credentials.from_service_account_info(
        SERVICE_ACCOUNT_INFO, scopes=SCOPES
    )
    service = build("sheets", "v4", credentials=creds)

    # Read all values from the target sheet
    result = (
        service.spreadsheets()
        .values()
        .get(spreadsheetId=SPREADSHEET_ID, range=SHEET_NAME)
        .execute()
    )
    rows = result.get("values", [])

    # First row is treated as the header
    headers = rows[0]
    data = rows[1:]

    df = pl.DataFrame(data, schema=headers, orient="row")
    return df

Note

All cell values returned by the Sheets API are strings by default. Use Polars cast() to convert columns to the appropriate data types after loading.

Summary

You’ve now successfully configured your DataSpace workspace to:

• Authenticate securely via a Google service account
• Access a private Google Sheet using the Sheets API
• Load sheet data directly into a Polars DataFrame