ProductPromotion
Logo

Elixir

made by https://0x3d.site

GitHub - GrandCru/GoogleSheets: Elixir library for fetching Google Spreadsheet data in CSV format
Elixir library for fetching Google Spreadsheet data in CSV format - GrandCru/GoogleSheets
Visit Site

GitHub - GrandCru/GoogleSheets: Elixir library for fetching Google Spreadsheet data in CSV format

GitHub - GrandCru/GoogleSheets: Elixir library for fetching Google Spreadsheet data in CSV format

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.

Articles
to learn more about the elixir concepts.

Resources
which are currently available to browse on.

mail [email protected] to add your project or resources here 🔥.

FAQ's
to know more about the topic.

mail [email protected] to add your project or resources here 🔥.

Queries
or most google FAQ's about Elixir.

mail [email protected] to add more queries here 🔍.

More Sites
to check out once you're finished browsing here.

0x3d
https://www.0x3d.site/
0x3d is designed for aggregating information.
NodeJS
https://nodejs.0x3d.site/
NodeJS Online Directory
Cross Platform
https://cross-platform.0x3d.site/
Cross Platform Online Directory
Open Source
https://open-source.0x3d.site/
Open Source Online Directory
Analytics
https://analytics.0x3d.site/
Analytics Online Directory
JavaScript
https://javascript.0x3d.site/
JavaScript Online Directory
GoLang
https://golang.0x3d.site/
GoLang Online Directory
Python
https://python.0x3d.site/
Python Online Directory
Swift
https://swift.0x3d.site/
Swift Online Directory
Rust
https://rust.0x3d.site/
Rust Online Directory
Scala
https://scala.0x3d.site/
Scala Online Directory
Ruby
https://ruby.0x3d.site/
Ruby Online Directory
Clojure
https://clojure.0x3d.site/
Clojure Online Directory
Elixir
https://elixir.0x3d.site/
Elixir Online Directory
Elm
https://elm.0x3d.site/
Elm Online Directory
Lua
https://lua.0x3d.site/
Lua Online Directory
C Programming
https://c-programming.0x3d.site/
C Programming Online Directory
C++ Programming
https://cpp-programming.0x3d.site/
C++ Programming Online Directory
R Programming
https://r-programming.0x3d.site/
R Programming Online Directory
Perl
https://perl.0x3d.site/
Perl Online Directory
Java
https://java.0x3d.site/
Java Online Directory
Kotlin
https://kotlin.0x3d.site/
Kotlin Online Directory
PHP
https://php.0x3d.site/
PHP Online Directory
React JS
https://react.0x3d.site/
React JS Online Directory
Angular
https://angular.0x3d.site/
Angular JS Online Directory