Keep your SQL database in sync with Google Sheets using googlesheets-sql-sync.
Let users manually insert data using Google Sheets while having the power of all available SQL tooling for further processing.
googlesheets-sql-sync uses JDBC and bundles the PostgreSQL driver. Additional drivers can be added any time. If you would like to add support for SQLite, MySQL or any other SQL database, open an issue and it can probably be added in no time.
To simplify the task of synchronisation, the following assumptions are made:
This allows for a few simplifications in the implementation:
java -version
googlesheets-sql-sync.jar
from Github.spreadsheets.readonly
.java -jar googlesheets-sql-sync.jar --init
Now fill out the missing information in the config file.
Use your Google credentials from above.
Specify at least one target and one sheet using that target.
You can find more DB options in the JDBC docs.
Name the table
as you wish for it to appear in your database.
To get a spreadsheet_id
, open one of your Google Sheets and copy the part between /d/
and /edit
from the URL bar in your Browser.
Specify the range
using the A1:Z10
. Skip the number to select all rows - like A:ZZ
. You can also specify a sheet if your spreadsheet contains multiple sheets by prefixing th range like SomeSheet!A:ZZ
.
For example, the spreadsheet_id
for https://docs.google.com/spreadsheets/d/1q5BNyL7-FnApmkjq45HlKPK-W-pdEmTrtpz0iaHm8p0/edit#gid=0
is 1q5BNyL7-FnApmkjq45HlKPK-W-pdEmTrtpz0iaHm8p0
.
Start the program with:
java -jar googlesheets-sql-sync.jar
You will be prompted to visit an OAuth URL to authorize and connect your Google Account.
After successful authorization, a first sync is triggered and further ones will occur in the specified interval.
Often you don't want to open up another port just for OAuth of a small sync tool.
To work around this you can run java -jar googlesheets-sql-sync.jar --auth-only
on your local machine, then copy the generated googlesheets_sql_sync.auth.json
file to your server and on the server run java -jar googlesheets-sql-sync.jar --no-server
The program can be configured using command line flags. To see available options, run:
java -jar googlesheets-sql-sync.jar --help
.auth.json
file and try to re-authenticate, Google for some reason will only send you access_token
and expires_in
, no refresh_token
. To fix this go to https://myaccount.google.com/permissions remove the app's permission and try again.Let me know if you run into any issues or if you have any suggestions for improvements.
'#googlesheets-sql-sync.config/generate
'#googlesheets-sql-sync.options/defaults
and '#googlesheets-sql-sync.options/validate
'#googlesheets-sql-sync.core/start
'#googlesheets-sql-sync.log/info
, '#googlesheets-sql-sync.log/warn
, #googlesheets-sql-sync.log/error
to modify or disable logging.lein test
Run testslein run
Run the whole systemlein repl
Start in dev mode with REPL enabled
lein uberjar
Can you improve this documentation?Edit on GitHub
cljdoc is a website building & hosting documentation for Clojure/Script libraries
× close