grandcru / googlesheets Goto Github PK

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.

> 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/...

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!

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

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?