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

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.

1

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.

2

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.

3

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.

4

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 need to upload this file to your DataSpace workspace.

5

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 [email protected]).

• 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

6

Prepare Your DataSpace Workspace

Declare the dependencies in _config.json:

:config.json

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

Make sure your service account key (mcp.json) is uploaded to the workspace root.

7

Write the Transformation

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

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

# Path to your service account credentials
SERVICE_ACCOUNT_FILE = "./mcp.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_file(
        SERVICE_ACCOUNT_FILE, 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

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