Data connectivity & integrationAvailable connectorsGoogle Sheets

Google Sheets

The Google Sheets connector is a Palantir-provided driver for Google Sheets.

To create a new Google Sheets source, follow the standard setup flow for Palantir-provided drivers, then use the sections below for Google Sheets-specific configuration and networking. For the complete property reference, see the official Google Sheets driver documentation ↗.

For bulk reads, use the source explorer and batch syncs. To read or update a fixed spreadsheet range, use the Google Sheets API code examples.

Supported capabilities

Capability	Status
Exploration	🟢 Generally available
Batch syncs	🟢 Generally available
Incremental	🟢 Generally available

Networking

The table below lists the domains that the source must access to run.

For Foundry worker connections, add an egress policy for each domain.

If the domains are on another network and you use agent proxy egress policies (preferred) or an agent worker (not recommended), the agent must be able to reach them. The systems on those domains must also allow connections from the agent. Learn more about agent networking.

Domain	Required
accounts.google.com	Always. Required for OAuth
`www.googleapis.com`	Always

Use Google Sheets from code

To read or update a spreadsheet range with the Google Sheets API v4 ↗, choose the source type based on the calling identity:

Service account: Use a Google Sheets source from a Python pipeline. See Prepare a service-account source for a Python pipeline.
Signed-in user: Use a REST API source from a Python or TypeScript v2 Function. See Prepare delegated OAuth for Functions.

Prepare a service-account source for a Python pipeline

Set AuthScheme to OAuthJWT, configure the Google Sheets source with a Google service-account JSON credential, and share the target spreadsheet with the service account. Grant read access for the read example or edit access for the write example. For the complete connection properties, see the CData service-account guidance ↗.

Follow the external transforms setup to install transforms-external-systems, enable code imports for the source, and import it into the transforms repository. Also add google-auth, requests, and pandas to the repository. Replace the pipeline placeholders with the source RID, output dataset path, spreadsheet ID, worksheet, and replacement value. The source must expose its Google JSON credential through the OAuth_Jwt_Certification secret alias.

Add direct network egress policies for oauth2.googleapis.com:443 and sheets.googleapis.com:443. When the source supplies a proxy URI, the examples use it for both the token exchange and the Sheets API request. Otherwise, both calls use the configured direct route.

Prepare delegated OAuth for Functions

For Function examples, create a REST API source for https://sheets.googleapis.com and configure it with an OAuth 2.0 outbound application. Use https://accounts.google.com/o/oauth2/v2/auth as the authorization endpoint and https://oauth2.googleapis.com/token as the token endpoint. Request the https://www.googleapis.com/auth/spreadsheets scope and offline access. Run Functions that rely on token refresh in serverless mode; deployed mode cannot automatically refresh an expired calling-user token. Configure the client ID, client secret, and the redirect URL shown by Foundry. Add direct network egress policies for accounts.google.com:443, oauth2.googleapis.com:443, and sheets.googleapis.com:443.

Follow Configure access to external APIs to enable exports and code imports for the REST API source, then import it into the Functions repository. Replace the Function placeholders with its RID, the spreadsheet ID, and worksheet. Before invoking a Function, authorize the outbound application as the calling user through an interactive workflow such as a function-backed action. These examples retrieve the user's session credential and inject the access token into the API request. See Use OAuth 2.0 with outbound applications for runtime and authorization constraints.

Read a fixed range

Replace every angle-bracket placeholder before adapting an example. No TypeScript v1 Function example is provided, because that runtime does not expose the delegated OAuth credential required by this workflow. Use the Python Function or TypeScript v2 Function examples for delegated access.

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import json
from urllib.parse import quote

import pandas as pd
import requests
from google.auth.transport.requests import Request
from google.oauth2 import service_account
from transforms.api import Output, transform_pandas
from transforms.external.systems import Source, external_systems

SOURCE_RID = "<GOOGLE_SHEETS_SOURCE_RID>"
OUTPUT_DATASET = "<OUTPUT_DATASET_PATH>"
SPREADSHEET_ID = "<SPREADSHEET_ID>"
READ_RANGE = "'<WORKSHEET_NAME>'!A1:C3"


@external_systems(google_sheets=Source(SOURCE_RID))
@transform_pandas(Output(OUTPUT_DATASET))
def compute(google_sheets) -> pd.DataFrame:
    service_account_info = json.loads(
        google_sheets.get_secret("OAuth_Jwt_Certification")
    )
    session = requests.Session()
    proxy_uri = google_sheets.get_https_proxy_uri()
    if proxy_uri:
        session.proxies.update({"http": proxy_uri, "https": proxy_uri})

    credentials = service_account.Credentials.from_service_account_info(
        service_account_info,
        scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"],
    )
    credentials.refresh(Request(session=session))

    response = session.get(
        "https://sheets.googleapis.com/v4/spreadsheets/"
        f"{SPREADSHEET_ID}/values/{quote(READ_RANGE, safe='')}",
        headers={"Authorization": f"Bearer {credentials.token}"},
        timeout=30,
    )
    response.raise_for_status()

    values = response.json()["values"]
    return pd.DataFrame(values[1:], columns=values[0])

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import json
from urllib.parse import quote

import requests
from functions.api import function
from functions.sources import get_source

SOURCE_RID = "<GOOGLE_SHEETS_DELEGATED_SOURCE_RID>"
SPREADSHEET_ID = "<SPREADSHEET_ID>"
READ_RANGE = "'<WORKSHEET_NAME>'!A1:C3"


@function(sources=[SOURCE_RID])
def read_google_sheet_values() -> str:
    source = get_source(rid=SOURCE_RID)
    credentials = source.get_session_credentials().get()

    session = requests.Session()
    proxy_uri = source.get_https_proxy_uri()
    if proxy_uri:
        session.proxies.update({"http": proxy_uri, "https": proxy_uri})

    response = session.get(
        "https://sheets.googleapis.com/v4/spreadsheets/"
        f"{SPREADSHEET_ID}/values/{quote(READ_RANGE, safe='')}",
        headers={"Authorization": f"Bearer {credentials.access_token}"},
        timeout=30,
    )
    response.raise_for_status()
    return json.dumps(response.json())

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import { getFetch, getSource } from "@palantir/functions-sources";

const SOURCE_RID = "<GOOGLE_SHEETS_DELEGATED_SOURCE_RID>";
const SPREADSHEET_ID = "<SPREADSHEET_ID>";
const READ_RANGE = "'<WORKSHEET_NAME>'!A1:C3";

export const config = { sources: [SOURCE_RID] };

export default async function readGoogleSheetValues(): Promise<string> {
  const source = await getSource({ rid: SOURCE_RID });
  const credentials = await source.sessionCredentials?.get();
  if (!credentials || credentials.type !== "oauth") {
    throw new Error("Authorize the outbound application before running this Function");
  }

  const sourceFetch = await getFetch(source);
  const url =
    `https://sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}/values/` +
    encodeURIComponent(READ_RANGE);
  const response = await sourceFetch(url, {
    headers: { Authorization: `Bearer ${credentials.accessToken}` },
  });
  if (!response.ok) {
    throw new Error(`Google Sheets returned HTTP ${response.status}`);
  }
  return JSON.stringify(await response.json());
}

Update one cell

The examples below overwrite cell B2 in the configured worksheet with the supplied value using one spreadsheets.values.update request ↗ with valueInputOption=RAW.

Replace every angle-bracket placeholder and review the target cell. Set the confirmation flag to enable the write. Otherwise, the example returns without contacting Google Sheets.

Run only one writer against the target cell at a time. If a request's outcome is unclear, check the cell before retrying.

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
import json
from urllib.parse import quote

import pandas as pd
import requests
from google.auth.transport.requests import Request
from google.oauth2 import service_account
from transforms.api import Output, transform_pandas
from transforms.external.systems import Source, external_systems

SOURCE_RID = "<GOOGLE_SHEETS_SOURCE_RID>"
OUTPUT_DATASET = "<OUTPUT_DATASET_PATH>"
SPREADSHEET_ID = "<SPREADSHEET_ID>"
TARGET_RANGE = "'<WORKSHEET_NAME>'!B2"
REPLACEMENT_VALUE = "<REPLACEMENT_VALUE>"
CONFIRM_WRITE = False


@external_systems(google_sheets=Source(SOURCE_RID))
@transform_pandas(Output(OUTPUT_DATASET))
def compute(google_sheets) -> pd.DataFrame:
    if not CONFIRM_WRITE:
        return pd.DataFrame([{"status": "dry_run", "mutated": False}])

    service_account_info = json.loads(
        google_sheets.get_secret("OAuth_Jwt_Certification")
    )
    session = requests.Session()
    proxy_uri = google_sheets.get_https_proxy_uri()
    if proxy_uri:
        session.proxies.update({"http": proxy_uri, "https": proxy_uri})

    credentials = service_account.Credentials.from_service_account_info(
        service_account_info,
        scopes=["https://www.googleapis.com/auth/spreadsheets"],
    )
    credentials.refresh(Request(session=session))

    url = (
        "https://sheets.googleapis.com/v4/spreadsheets/"
        f"{SPREADSHEET_ID}/values/{quote(TARGET_RANGE, safe='')}"
        "?valueInputOption=RAW"
    )
    response = session.put(
        url,
        headers={"Authorization": f"Bearer {credentials.token}"},
        json={"range": TARGET_RANGE, "majorDimension": "ROWS", "values": [[REPLACEMENT_VALUE]]},
        timeout=30,
    )
    response.raise_for_status()
    return pd.DataFrame([response.json()])

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
import json
from urllib.parse import quote

import requests
from functions.api import function
from functions.sources import get_source

SOURCE_RID = "<GOOGLE_SHEETS_DELEGATED_SOURCE_RID>"
SPREADSHEET_ID = "<SPREADSHEET_ID>"
TARGET_RANGE = "'<WORKSHEET_NAME>'!B2"


@function(sources=[SOURCE_RID])
def write_google_sheet_value(new_value: str, confirm: bool = False) -> str:
    if not confirm:
        return json.dumps({"status": "dry_run", "mutated": False})

    source = get_source(rid=SOURCE_RID)
    credentials = source.get_session_credentials().get()

    session = requests.Session()
    proxy_uri = source.get_https_proxy_uri()
    if proxy_uri:
        session.proxies.update({"http": proxy_uri, "https": proxy_uri})

    url = (
        "https://sheets.googleapis.com/v4/spreadsheets/"
        f"{SPREADSHEET_ID}/values/{quote(TARGET_RANGE, safe='')}"
        "?valueInputOption=RAW"
    )
    response = session.put(
        url,
        headers={"Authorization": f"Bearer {credentials.access_token}"},
        json={"range": TARGET_RANGE, "majorDimension": "ROWS", "values": [[new_value]]},
        timeout=30,
    )
    response.raise_for_status()
    return json.dumps(response.json())

Copied!1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import { getFetch, getSource } from "@palantir/functions-sources";

const SOURCE_RID = "<GOOGLE_SHEETS_DELEGATED_SOURCE_RID>";
const SPREADSHEET_ID = "<SPREADSHEET_ID>";
const TARGET_RANGE = "'<WORKSHEET_NAME>'!B2";

export const config = { sources: [SOURCE_RID] };

export default async function writeGoogleSheetValue(
  newValue: string,
  confirm: boolean = false,
): Promise<string> {
  if (!confirm) {
    return JSON.stringify({ status: "dry_run", mutated: false });
  }

  const source = await getSource({ rid: SOURCE_RID });
  const credentials = await source.sessionCredentials?.get();
  if (!credentials || credentials.type !== "oauth") {
    throw new Error("Authorize the outbound application before running this Function");
  }

  const sourceFetch = await getFetch(source);
  const url =
    `https://sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}/values/` +
    `${encodeURIComponent(TARGET_RANGE)}?valueInputOption=RAW`;
  const response = await sourceFetch(url, {
    method: "PUT",
    headers: {
      Authorization: `Bearer ${credentials.accessToken}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      range: TARGET_RANGE,
      majorDimension: "ROWS",
      values: [[newValue]],
    }),
  });
  if (!response.ok) {
    throw new Error(`Google Sheets returned HTTP ${response.status}`);
  }
  return await response.text();
}

←

PREVIOUSGoogle Search

NEXTGoogle Spanner

→