google-spreadsheet-api
1 Initializing the connection
init-api
2 Reading a spreadsheet
get-cells
3 Inserting a row
insert-row
4 Modifying a row
update-row
5 "Predicates"
valid-sheet-id?
valid-sheet-url?
valid-range?
7.8

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]*$"