Google Sheets

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());
}

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();
}