Elixir library to read and write data of Google Spreadsheets.
This library is based on Google Cloud API v4 and uses Google Service Accounts to manage it's content.
Check ecto_gss if you need to integrate your Google Spreadsheet with Ecto changesets for validation and other features.
- Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
- On the Add credentials to your project page, create Service account key.
- Select your project name as service account and JSON as key format, download the created key and rename it to service_account.json.
- Press Manage service accounts on a credential page, copy your Service Account Identifier: [projectname]@[domain].iam.gserviceaccount.com
- Create or open existing Google Spreadsheet document on your Google Drive and add Service Account Identifier as user invited in spreadsheet's Collaboration Settings.
- Add
{:elixir_google_spreadsheets, "~> 0.1.9"}
to mix.exs underdeps
function, add:elixir_google_spreadsheets
in your application list. - Add service_account.json in your
config.exs
or other config file, likedev.exs
orprod.secret.exs
. config :goth, json: "./config/service_account.json" |> File.read! - Run
mix deps.get && mix deps.compile
.
For the non-default Google API limits, you can tune the following settings locally:
config :elixir_google_spreadsheets, :client,
request_workers: 50,
max_demand: 100,
max_interval: :timer.minutes(1),
interval: 100
Currently (01.07.2017) there is an issue in OTP 20, because :crypto.mpint/1
was moved to another module and it's used in :goth
dependency :json_web_token
, you can use this temporary hack as a resolution, by adding this in your deps: {:json_web_token, git: "https://github.com/starbuildr/json_web_token_ex.git", override: true}
Initialise spreadsheet thread with it's id which you can fetch from URL:
`{:ok, pid} = GSS.Spreadsheet.Supervisor.spreadsheet("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXXX")`
Or if you wish to edit only a specific list:
`{:ok, pid} = GSS.Spreadsheet.Supervisor.spreadsheet("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX-XXXXXXXXX", list_name: "my_list3")`
Sample operations:
GSS.Spreadsheet.id(pid)
GSS.Spreadsheet.rows(pid)
GSS.Spreadsheet.read_row(pid, 1, column_to: 5)
GSS.Spreadsheet.read_rows(pid, 1, 10, column_to: 5, pad_empty: true)
GSS.Spreadsheet.read_rows(pid, [1, 3, 5], column_to: 5, pad_empty: true)
GSS.Spreadsheet.read_rows(pid, ["A1:E1", "A2:E2"])
GSS.Spreadsheet.write_row(pid, 1, ["1", "2", "3", "4", "5"])
GSS.Spreadsheet.write_rows(pid, ["A2:E2", "A3:F3"], [["1", "2", "3", "4", "5"], ["1", "2", "3", "4", "5", "6"]])
GSS.Spreadsheet.append_row(pid, 1, ["1", "2", "3", "4", "5"])
GSS.Spreadsheet.clear_row(pid, 1)
GSS.Spreadsheet.clear_rows(pid, 1, 10)
GSS.Spreadsheet.clear_rows(pid, ["A1:E1", "A2:E2"])
Last function param of GSS.Spreadsheet
function calls support the same Keyword
options (in snake_case instead of camelCase), as defined in Google API Docs.
We also define column_from
and column_to
Keyword options which control range of cell which will be queried.
Default values:
column_from = 1
column_to = 26
major_dimension = "ROWS"
value_render_option = "FORMATTED_VALUE"
datetime_render_option = "FORMATTED_STRING"
value_render_option = "USER_ENTERED"
insert_data_option = "INSERT_ROWS"
- Recommended columns 26 (more on your own risk), max rows 1000;
- This library is in it's early beta, use on your own risk. Pull requests / reports / feedback are welcome.