google-spreadsheet-api

8.12

google-spreadsheet-api🔗ℹ

Carmine Margiotta <car.margiotta@icloud.com>

(require google-spreadsheet-api)
	package: google-spreadsheet-api

A simple interface for Google Sheets API, supporting authentication.

You will need a Google Service Account: https://support.google.com/a/answer/7378726?hl=en And the relative JSON containing the private key.

1 Initializing the connection🔗ℹ

procedure
(init-api #:json-path json-path) → api-connection?
json-path : path-string?

This procedure parses the JSON (at json-path) provided by Google and uses the private key for signing requests. The produced api-connection will be needed by every other procedure.

Examples:

> (require google-spreadsheet-api)
> (define conn (init-api #:json-path "data/google-auth.json"))

2 Reading a spreadsheet🔗ℹ

procedure
(get-cells conn sheet coordinates)
→ (listof (listof (or/c number? string?)))
  conn : api-connection?
  sheet : (or/c valid-sheet-id? valid-sheet-url?)
  coordinates : valid-range?

This procedure downloads cells at the given coordinates and places them in a list of lists (a list of rows). The conn must be initialized with init-api.

Examples:

> (require google-spreadsheet-api)
> (define conn (init-api #:json-path "data/google-auth.json"))
> (get-cells conn "1Gvw2x2ygZ5YYcGCxDiq4ZaD-1qdvX31NkX1ZkgmFYvE" "A2:C3")
'(("snacks", 540, 10.1), ("lasagna", 140, 15.5))

3 Inserting a row🔗ℹ

procedure
(insert-row conn sheet range values) → any
  conn : api-connection?
  sheet : (or/c valid-sheet-id? valid-sheet-url?)
  range : valid-range?
  values : (listof (or/c number? string?))

This procedure appends a new row to the subtable delimited by range, for example "A2:D".

Examples:

> (require google-spreadsheet-api)
> (define conn (init-api #:json-path "data/google-auth.json"))
> (insert-row conn "1Gvw2x2ygZ5YYcGCxDiq4ZaD-1qdvX31NkX1ZkgmFYvE" "A2:D")

4 Modifying a row🔗ℹ

procedure
(update-row conn sheet coordinates values) → any
  conn : api-connection?
  sheet : (or/c valid-sheet-id? valid-sheet-url?)
  coordinates : valid-range?
  values : (listof (or/c number? string?))

This procedure modifies an existing row identified by the given coordinates.

Examples:

> (require google-spreadsheet-api)
> (define conn (init-api #:json-path "data/google-auth.json"))
> (update-row conn "1Gvw2x2ygZ5YYcGCxDiq4ZaD-1qdvX31NkX1ZkgmFYvE" "A2:C2" '("lasagna", 520, 100))

5 "Predicates"🔗ℹ

procedure
(valid-sheet-id? id) → boolean?
id : any/c

A valid sheet ID is a string made of an arbitrary number of alphanumeric characters, a - and another arbitrary number of alphanumeric characters.

procedure
(valid-sheet-url? url) → boolean?
url : any/c

A valid sheet URL is a string made of "https://docs.google.com/spreadsheets/d/" and a valid-sheet-id?.

procedure
(valid-range? range) → boolean?
range : any/c

A valid range is a string that matches the regular expression "^[A-Z]{1}[0-9]*:[A-Z]{1}[0-9]*$"

1	Initializing the connection
2	Reading a spreadsheet
3	Inserting a row
4	Modifying a row
5	"Predicates"