Disclaimer

This uses undocumented features of Google’s Publish feature. This may change in the future, which will break this library. Don’t use this for a missile launch system.

In addition, the Publish feature requires that a sheet be made public. You probably shouldn’t use this to store missile launch codes, or any other sensitive information.

Usage

To use this library, you need a sheet-id that can be found in the URL for your google sheet: docs.google.com/spreadsheets/d/THIS-IS-YOUR-SHEET-ID/edit#gid=0.

You will also need to Publish your google sheet, which can be done in the google sheets web interface: File > Publish to the web. Make sure to publish the entire document as a web page. These options should be set by default.

This example uses this Google Sheet.

(ns example.core
  (:require [clojure-sheets.core :as sheets]
            [clojure-sheets.key-fns :as sheets.key-fns]
            [clojure.core.async :as a]))

(a/<!! (sheets/sheet->map "1EOWjYGWIzf8i7rcnlhvqWtRL5Ke2V2vz7FgYGYL8EBo"
                          {:page  1
                           :key-fn sheets.key-fns/idiomatic-keyword}))
;; => [{:clojure-sheets.core/row 2,
;;      :person/first-name "Matilda",
;;      :person/last-name "Jones",
;;      :person/address "123 Some Road",
;;      :person/address1 "Apt 4",
;;      :person/address2 nil,
;;      :person.address/city "Somewhereville",
;;      :person.address/state "MA"}
;;     {:clojure-sheets.core/row 3,
;;      :person/first-name "Michael",
;;      :person/last-name "Jones",
;;      :person/address "321 Another Ave",
;;      :person/address1 "Apartment Complex",
;;      :person/address2 "Unit 32",
;;      :person.address/city "Somewhereville",
;;      :person.address/state "MA"}
;;     {:clojure-sheets.core/row 4,
;;      :person/first-name "Another",
;;      :person/last-name "Person",
;;      :person/address "1234 Some Lane",
;;      :person/address1 nil,
;;      :person/address2 nil,
;;      :person.address/city "Boston",
;;      :person.address/state "MA"}
;;     {:clojure-sheets.core/row 5,
;;      :person/first-name "Nobody",
;;      :person/last-name "Noname",
;;      :person/address nil,
;;      :person/address1 nil,
;;      :person/address2 nil,
;;      :person.address/city "Noplace",
;;      :person.address/state "NO"}]

(a/<!! (sheets/sheet->map "1EOWjYGWIzf8i7rcnlhvqWtRL5Ke2V2vz7FgYGYL8EBo"
                          {:page  2
                           :key-fn keyword}))
;; => [{:clojure-sheets.core/row 2,
;;      :DataPoint "Alpha",
;;      :Data1 1.0,
;;      :Data2 123.0,
;;      :Data3 32.5}
;;     {:clojure-sheets.core/row 3,
;;      :DataPoint "Beta",
;;      :Data1 2.0,
;;      :Data2 188.12,
;;      :Data3 122.4,
;;      :Data4 33.0}
;;     {:clojure-sheets.core/row 4,
;;      :DataPoint "Epsilon",
;;      :Data1 1.4,
;;      :Data2 290.0,
;;      :Data3 5.5,
;;      :Data4 123.0,
;;      :Data5 11.0}
;;     {:clojure-sheets.core/row 5,
;;      :DataPoint "Monkey",
;;      :Data1 33.0,
;;      :Data2 1.0,
;;      :Data3 33.0,
;;      :Data4 0.0}]

:key-fns

The :key-fn option to the sheet->map function takes a header and returns an appropriate map key. Any function that takes a string can be used, but here are some examples of :key-fn that will be most useful. These examples are taken from the Usage section.

Original Header	clojure.core/keyword	clojure-sheets.key-fns/idiomatic-keyword
“person/first-name”	:person/first-name	:person/first-name
“person_last-name”	:person_last-name	:person/last-name
“Person.Address”	:Person.Address	:person/address
“Person.Address.City”	:Person.Address.City	:person.address/city
“DataPoint”	:DataPoint	:datapoint

As you can see, idiomatic-keyword consistently gives the best results. You should probably prefer this :key-fn, unless you have a good reason for writing your own.

Query with EQL

If you want to query sheets data with EQL, check out EQLizr, which supports clojure-sheets as a backend.