google-spreadsheet-apiπ i
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.
1Initializing the connectionπ i
( init-api#:json-pathjson-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:
>(requiregoogle-spreadsheet-api)
>(defineconn(init-api#:json-path"data/google-auth.json"))
2Reading a spreadsheetπ i
( get-cellsconnsheetcoordinates)
→(listof(listof(or/cnumber?string?)))
conn:api-connection?
sheet:(or/cvalid-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:
>(requiregoogle-spreadsheet-api)
>(defineconn(init-api#:json-path"data/google-auth.json"))
>(get-cellsconn"1Gvw2x2ygZ5YYcGCxDiq4ZaD-1qdvX31NkX1ZkgmFYvE""A2:C3")
'(("snacks",540,10.1),("lasagna",140,15.5))
3Inserting a rowπ i
( insert-rowconnsheetrangevalues)→any
conn:api-connection?
sheet:(or/cvalid-sheet-id?valid-sheet-url?)
range:valid-range?
values:(listof(or/cnumber?string?))
This procedure appends a new row to the subtable delimited by range, for example "A2:D".
Examples:
>(requiregoogle-spreadsheet-api)
>(defineconn(init-api#:json-path"data/google-auth.json"))
>(insert-rowconn"1Gvw2x2ygZ5YYcGCxDiq4ZaD-1qdvX31NkX1ZkgmFYvE""A2:D")
4Modifying a rowπ i
( update-rowconnsheetcoordinatesvalues)→any
conn:api-connection?
sheet:(or/cvalid-sheet-id?valid-sheet-url?)
coordinates:valid-range?
values:(listof(or/cnumber?string?))
This procedure modifies an existing row identified by the given coordinates.
Examples:
>(requiregoogle-spreadsheet-api)
>(defineconn(init-api#:json-path"data/google-auth.json"))
>(update-rowconn"1Gvw2x2ygZ5YYcGCxDiq4ZaD-1qdvX31NkX1ZkgmFYvE""A2:C2"'("lasagna",520,100))
5"Predicates"π i
( 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.
( 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?.
( 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]*$"