Google Sheets as a REST API in Elixir

# mix.exs
defp deps do
  [
    {:httpoison, "~> 2.2"},
    {:jason, "~> 1.4"}
  ]
end

# lib/my_app/sheets/sheet_response.ex
defmodule MyApp.Sheets.Meta do
  @enforce_keys [:total, :limit, :offset]
  defstruct [:total, :limit, :offset]
 
  def from_map(%{"total" => t, "limit" => l, "offset" => o}),
    do: %__MODULE__{total: t, limit: l, offset: o}
end
 
defmodule MyApp.Sheets.Response do
  @enforce_keys [:data, :meta]
  defstruct [:data, :meta]
end
 
defmodule MyApp.Sheets.Product do
  defstruct [:name, :price, :category, :stock]
 
  def from_map(map) do
    %__MODULE__{
      name:     map["name"],
      price:    map["price"],
      category: map["category"],
      stock:    map["stock"]
    }
  end
end

# lib/my_app/sheets/client.ex
defmodule MyApp.Sheets.Client do
  alias MyApp.Sheets.{Meta, Response}
 
  @base "https://sheetsapi.gkit.mreshank.com/api/spreadsheets"
 
  def query(user_key, sheet, opts \\ []) do
    api_key = Keyword.get(opts, :api_key)
    params  = build_params(opts)
    url     = "#{@base}/#{user_key}/#{sheet}?#{URI.encode_query(params)}"
    headers = if api_key, do: [{"Authorization", "Bearer #{api_key}"}], else: []
 
    case HTTPoison.get(url, headers) do
      {:ok, %{status_code: 200, body: body}} ->
        {:ok, decode(body)}
 
      {:ok, %{status_code: status, body: body}} ->
        {:error, "HTTP #{status}: #{body}"}
 
      {:error, reason} ->
        {:error, reason}
    end
  end
 
  defp decode(body) do
    %{"data" => data, "meta" => meta} = Jason.decode!(body)
    %Response{
      data: Enum.map(data, &MyApp.Sheets.Product.from_map/1),
      meta: Meta.from_map(meta)
    }
  end
 
  defp build_params(opts) do
    Keyword.take(opts, [:limit, :offset, :search, :sort, :fields])
    |> Enum.reject(fn {_, v} -> is_nil(v) end)
    |> Map.new(fn {k, v} -> {Atom.to_string(k), v} end)
  end
end

{:ok, resp} = MyApp.Sheets.Client.query(
  "YOUR_USER_KEY",
  "Products",
  api_key: "sk_your_api_key_here",
  limit: 20,
  sort: "name"
)
 
IO.puts("Total: #{resp.meta.total}")
Enum.each(resp.data, fn p -> IO.puts("#{p.name} - #{p.price}") end)

# lib/my_app/sheets/cache.ex
defmodule MyApp.Sheets.Cache do
  use GenServer
 
  @ttl_ms 5 * 60 * 1000  # 5 minutes
 
  def start_link(_opts), do: GenServer.start_link(__MODULE__, %{}, name: __MODULE__)
 
  def fetch(key, fetch_fn) do
    GenServer.call(__MODULE__, {:fetch, key, fetch_fn})
  end
 
  @impl true
  def init(state), do: {:ok, state}
 
  @impl true
  def handle_call({:fetch, key, fetch_fn}, _from, state) do
    case Map.get(state, key) do
      {value, expires_at} when expires_at > System.monotonic_time(:millisecond) ->
        {:reply, {:ok, value}, state}
 
      _ ->
        case fetch_fn.() do
          {:ok, value} = result ->
            expires = System.monotonic_time(:millisecond) + @ttl_ms
            {:reply, result, Map.put(state, key, {value, expires})}
 
          error ->
            {:reply, error, state}
        end
    end
  end
end

# lib/my_app/application.ex
children = [
  MyApp.Sheets.Cache,
  # ...
]

MyApp.Sheets.Cache.fetch("products:all", fn ->
  MyApp.Sheets.Client.query("YOUR_USER_KEY", "Products",
    api_key: System.get_env("SHEETS_API_KEY"),
    limit: 200
  )
end)

# lib/my_app_web/controllers/products_controller.ex
defmodule MyAppWeb.ProductsController do
  use MyAppWeb, :controller
 
  def index(conn, params) do
    search = case Map.get(params, "q") do
      nil  -> nil
      ""   -> nil
      q    -> "name:#{q}"
    end
 
    {:ok, resp} = MyApp.Sheets.Cache.fetch("products:#{search || "all"}", fn ->
      MyApp.Sheets.Client.query(
        System.get_env("SHEETS_USER_KEY"),
        "Products",
        api_key: System.get_env("SHEETS_API_KEY"),
        limit: String.to_integer(params["limit"] || "20"),
        offset: String.to_integer(params["offset"] || "0"),
        sort: params["sort"] || "name",
        search: search
      )
    end)
 
    json(conn, %{data: resp.data, meta: resp.meta})
  end
end

def fetch_all(user_key, sheet, opts \\ [], acc \\ [], offset \\ 0) do
  page_size = 100
  {:ok, resp} = query(user_key, sheet, Keyword.merge(opts, limit: page_size, offset: offset))
  all = acc ++ resp.data
 
  if offset + page_size >= resp.meta.total do
    {:ok, all}
  else
    fetch_all(user_key, sheet, opts, all, offset + page_size)
  end
end