Git Product home page Git Product logo

googlesheets's Introduction

Google Sheets

Build Status Hex.pm Version

Google Sheets is an OTP application for fetching Google spreadsheet in CSV format, optionally parsing and converting into application specific data structures and storing each loaded version into ETS table with unique key. The host application can query latest or specific version of stored data using provided API.

Main use case for the library is a game server, where game configuration is edited in a Google spreadsheet. By polling changes and using the latest version for each new client connection, it is possible to rapidly tweak configuration without needing to deploy or restart server.

The library can also be used as a command line tool to fetch spreadsheet data and save it into local directory.

Quick start

# Make sure you have published spreadsheet to be accessible without 
# authorization, see Publishing Google spreadsheet for instructions.

# In your mix.exs file
defp deps do
  [ {:google_sheets, "~> 2.0"} ]
end
def application do
  [applications: [:logger, :google_sheets]]
end

# In your `config/config.exs` file:
config :google_sheets, spreadsheets: [
  config: [
    dir: "priv/data",
    url: "https://spreadsheets.google.com/feeds/worksheets/" <>
          "19HcQV5Z-uTXaVxjm2jVJNGNFv0pzA_cgdBTWMe4a77Y/public/basic"
  ]
]

# In your application code
defmodule MyApp do
  def func do
    # Get the latest version and data for :config spreadsheet
    {:ok, version, data} = GoogleSheets.latest :config
    {version, data} = GoogleSheets.latest! :config

    # Get just the data for latest :config spreadsheet
    {:ok, data} = GoogleSheets.latest_data :config
    data = GoogleSheets.latest_data! :config

    # Get just the version for latest :config spreadsheet 
    {:ok, version} = GoogleSheets.latest_version :config
    version = GoogleSheets.latest_version! :config

    # Use fetch to get specific version
    {:ok, data} = GoogleSheets.fetch version
    data = GoogleSheets.fetch! version
  end
end

# The library expects that initial data is loaded from a local directory.
# Therefore before starting your application, use the mix gs.fetch task
# to save data into local directory.
mix gs.fetch -u https://spreadsheets.google.com/feeds/worksheets/1k-N20RmT62RyocEu4-MIJm11DZqlZrzV89fGIddDzIs/public/basic -d priv/data

How it works

When the application starts, the supervisor creates an ETS table named :google_sheets and starts an updater process for each configured spreadsheet. Each updater process monitors one spreadsheet and if changes are noticed, it will load the new data, pass it into parser and store updated data into ETS table.

During genserver init callback CSV data is loaded from local file system. Therefore you must fetch data using the gs.fetch mix task to fetch data before starting application. This requirement means that application can always successfully start - even if Google services are down!

Using the library

After the application has started, you can query loaded data using the public API defined in GoogleSheets module. For the full documentation see http://hexdocs.pm/google_sheets/index.html

Configuration

  • :spreadsheets - A keyword list of spreadsheet configurations. The key is an atom uniquely identifying a spreadsheet.

Each :spreadsheets list entry is a keyword list:

  • :sheets - List of worksheet names to load. If empty, all worksheets in spreadsheet are loaded.
  • :ignored_sheets - List of sheet names to ignore from :sheets, can be used to filter sheets based on mix env.
  • :poll_delay_seconds - How often changes the monitored spreadsheet are polled. If 0, no polling is done. If not defined, the default is 30 seconds.
  • :loader - Module implementing GoogleSheets.Loader behavior. If nil, the default is to use GoogleSheets.Loader.Docs which loads data form a google spreadsheet. In this case the :url parameter must be specified.
  • :parser - Module implementing GoogleSheets.Parser behavior. If nil, the raw CSV data is stored into ETS table.
  • :url - URL of the Google spreadsheet to load.
  • :dir - Local directory relative to application root where CSV files fetched before are located. For example priv/data

For a complete example configuration, see config.exs.

Publishing Google Spreadsheet

The default way to share a spreadsheet using Google Sheets API is to use OAuth. It might be possible to use two legged OAuth to support serverside authentication, but no effort has been spent investigating whether this works or not. Therefore it is required that the spreadsheet has been publicly published.

For the library to work correctly, spreadsheet must published to web and shared. Publishing allows fetching worksheet feed containing URLs to individual worksheets and sharing allows us to access the actual CSV content.

Publish to web is found in the File menu and it opens a dialog shown below:

Publish to Web

Sharing link is on the top right corner of the worksheet document and it opens following dialog:

Sharing dialog

Mix gs.fetch task

The mix task gs.fetch loads a Google spreadsheet and saves worksheets in specified directory. If no parameters are given, it fetches all spreadsheets specified in the applications :google_sheets configuration and writes data into corresponding directory. You can also provide -u and -d arguments to manually specify parameters.

mix gs.fetch \
-u https://spreadsheets.google.com/feeds/worksheets/1k-N20RmT62RyocEu4-MIJm11DZqlZrzV89fGIddDzIs/public/basic \
-d priv/data

More information

Credits

Credits for the original C# implementation goes to Harri Hätinen https://github.com/hhatinen and to Teemu Harju https://github.com/tsharju for the original Elixir implementation.

googlesheets's People

Contributors

jannekai avatar muhmi avatar zigomir avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

googlesheets's Issues

Make library generic

We also use AirTable at our org, so was thinking it would be cool to add another data source.

I don't mind taking a stab at making this lib generic.

Thoughts?

Mix task to update sheet by name

I made my own helper mix task so I don't have to duplicate information about the google sheet id.

defmodule Mix.Tasks.Turtle.Config.Update do

  def run(_args) do
    dir = google_sheets_config(:dir)
    url = google_sheets_config(:url)
    Mix.Task.run "gs.fetch", ["-u", url, "-d", dir]
  end

  def google_sheets_config(key) do
    Application.get_env(:google_sheets, :spreadsheets)
    |> get_in([:config, key])
  end

end

What's your opinion of making the mix task use the values of the config file instead of requiring someone to paste them into the command line?

Cheers!

P.S. Really well written library. It has allowed me to transition from hardcoding config values without having to build an admin interface!

mix gs.fetch fails with a MatchError

I'm trying to use the GoogleSheets library. I followed the documentation and published my spreadsheets to the web and try to feed the initial information with the mix gs.fetch task.

mix gs.fetch -u https://spreadsheets.google.com/feeds/worksheets/1k-N20RmT62RyocEu4-MIJm11DZqlZrzV89fGIddDzIs/public/basic -d priv/data

Loading spreadsheet from url "https://spreadsheets.google.com/feeds/worksheets/1k-N20RmT62RyocEu4-MIJm11DZqlZrzV89fGIddDzIs/public/basic" and saving to /home/evert/dev/sp/priv/data

** (MatchError) no match of right hand side value: {:error, %HTTPoison.Error{id: nil, reason: :closed}}
lib/google_sheets/loader/docs.ex:48: GoogleSheets.Loader.Docs.load_spreadsheet/3
lib/google_sheets/loader/docs.ex:39: GoogleSheets.Loader.Docs.load/3
lib/mix/tasks/gs.fetch.ex:47: Mix.Tasks.Gs.Fetch.fetch_spreadsheets/1
(mix) lib/mix/task.ex:296: Mix.Task.run_task/3
(mix) lib/mix/cli.ex:58: Mix.CLI.run_task/2

I tried both with "your" url in the example, as my own. Both give me a valid xml feed when getting them with curl. Sadly as an elixir newbie I can't find out how to fix the issue and add a solution.

Problem with comma when get a CSV from API

Problem: When a field have a comma inside like "description, is, this!" The google Sheets Api load the field as ""description,is,this!"", as the Standard implementation of CSV Escape is ", this is a problem when loading and parse using the sheets api Elixir, because the parser truncate with comma inside the field

How to test: load a google spreadsheet, the fields need to have comma inside, use the google_sheets Elixir Lib to do this and csv library(nimble_csv was used).

This can be fixed changing the escape character when download the CSV from google API, but I can't figure out to change from comma to other escape in API.

Link to download from api is like this: https://docs.google.com/spreadsheets/d//export?gid=0&format=csv

mix gs.fetch not working with new Google Sheet URLs

> mix gs.fetch -d _data/gs -u https://docs.google.com/spreadsheets/d/1sPWEV-fXQC9zsgW829nbUaiY-htpFyH8ImAVlWJuv14/edit\?usp\=sharing                [11:54:25]
Loading spreadsheet from url "https://docs.google.com/spreadsheets/d/1sPWEV-fXQC9zsgW829nbUaiY-htpFyH8ImAVlWJuv14/edit?usp=sharing" and saving to _data/gs

11:58:03.753 [error] 3005- fatal: {:invalid_name, '?{done'}

** (exit) {:fatal, {{:invalid_name, '?{done'}, {:file, :file_name_unknown}, {:line, 4}, {:col, 97}}}
    xmerl_scan.erl:4124: :xmerl_scan.fatal/2
    xmerl_scan.erl:2148: :xmerl_scan.scan_element/12
    xmerl_scan.erl:2605: :xmerl_scan.scan_content/11
    xmerl_scan.erl:2133: :xmerl_scan.scan_element/12
    xmerl_scan.erl:2605: :xmerl_scan.scan_content/11
    xmerl_scan.erl:2133: :xmerl_scan.scan_element/12
    xmerl_scan.erl:2605: :xmerl_scan.scan_content/11
    xmerl_scan.erl:2133: :xmerl_scan.scan_element/12

The URL above is pointing to a doc that has been published and shared.

Not sure if it's related, but it looks like current google sheets sharing URLs are different from your README:

https://docs.google.com/spreadsheets/d/1/...

vs. your README:

https://spreadsheets.google.com/feeds/worksheets/...

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.