Google Spreadsheets input plugin for Embulk
Embulk input plugin to load records from Google Spreadsheets.
Overview
- Plugin type: input
Configuration
name | type | requirement | default | description |
---|---|---|---|---|
auth_method | string | optional | authorized_user |
service_account , authorized_user , compute_engine , or application_default
|
json_keyfile | string | optional | keyfile path or content
|
|
spreadsheets_url | string | required | ||
worksheet_title | string | required | worksheet title | |
start_column | integer | optional | 1 |
|
start_row | integer | optional | 1 |
|
end_row | integer | optional | -1 |
-1 means loading records until an empty record appears. |
max_fetch_rows | integer | optional | 10000 |
Load data from a worksheet for each numerical value specified by this option. |
null_string | string | optional | '' |
Replace this value to NULL
|
value_render_option | string | optional | 'FORMATTED_VALUE' |
'FORMATTED_VALUE' , 'UNFORMATTED_VALUE' , 'FORMULA' are available. See the value_render_option document. |
stop_on_invalid_record | boolean | optional | true |
|
default_timestamp_format | string | optional | '%Y-%m-%d %H:%M:%S.%N %z' |
|
default_timezone | string | optional | 'UTC' |
|
default_typecast | string | optional | 'strict' |
|
columns | array | required |
about keyfile
-
if
auth_method
iscompute_engine
orapplication_default
, this option is not required. -
if
auth_method
isauthorized_user
, this plugin supposes the format is the below.{ "client_id":"xxxxxxxxxxx.apps.googleusercontent.com", "client_secret":"xxxxxxxxxxx", "refresh_token":"xxxxxxxxxxx" }
-
if
auth_method
isservice_account
, set the service account credential json file path.
about columns
- name: column name
- type: boolean, long, double, string, timestamp, json
- format: timestamp format like
'%Y-%m-%d %H:%M:%S.%N %z'
- timezone: timezone
- typecast: you can choose
strict
,loose
,minimal
(default:strict
)-
strict
: raise TypecastError if typecasting is failed. -
loose
: setNULL
value if typecasting is failed. -
minimal
: typecast minimally.
-
Prepare JSON file for auth_method: authorized_user
You may use example/setup_authorized_user_credentials.rb to prepare OAuth token.
Go to GCP console > API Manager > Credentials > Create 'OAuth Client ID'. Get the client id and client secret.
Run setup_authorized_user_credentials.rb
to get refresh_token
.
bundle --path vendor/bundle
bundle exec ruby example/setup_authorized_user_credentials.rb
Development
Run example:
- Execute
example/setup_authorized_user_credentials.rb
if you don't have credentials, then
$ embulk bundle install --path vendor/bundle
$ embulk run -b . -l trace example/config_authorized_user.yml
Run test:
$ bundle exec rake test
To run tests which actually connects to Google Spreadsheets such as test/test_google_spreadsheets_client.rb
,
prepare a json_keyfile at example/service_account_credentials.json
, then
$ bundle exec ruby test/test_google_spreadsheets_client.rb
$ bundle exec ruby test/test_example.rb
Release gem:
Fix gemspec, then
$ bundle exec rake release