📊 CLI for reading and writing data into Google Spreadsheet
First thing first, you need your Google credentials, follow the authentication instructions there. Then save the JSON file somewhere, e.g. ~/myproject-8cbb20000000.json
.
Locate the spreadsheet you want to work with, take the id from Google spreadsheet URL, e.g. 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw
.
If you wish to directly pass the base64 stringified JSON as --credentials
parameter you might first want to only keep client_email
and private_key
using jq.node like so:
export CREDENTIALS=$(cat ~/myproject-8cbb20000000.json | jq -r btoa 'pick(["client_email", "private_key"]) | JSON.stringify | btoa')
worksheets list
List spreadsheet document worksheets:
google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials ~/myproject-8cbb20000000.json \
worksheets list
{"id":"od6","title":"my first worksheet"}
{"id":"od7","title":"my second worksheet"}
{"id":"ad7","title":"oh oh oh the last one"}
... or you could also pass the credential as a JSON base64 encoded string:
google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials $CREDENTIALS \
worksheets list
{"id":"od6","title":"my first worksheet"}
{"id":"od7","title":"my second worksheet"}
{"id":"ad7","title":"oh oh oh the last one"}
worksheets add <title>
Add a worksheet to the spreadsheet document:
$ google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials $CREDENTIALS \
worksheets \
add my_awesome_worksheet
{"id":"oy7n5ch","title":"my_awesome_worksheet","rowCount":50,"colCount":20,"url":"https://spreadsheets.google.com/feeds/worksheets/2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw/oy7n5ch"}
Other options:
--spreadsheetId, --id spreadsheet id, the long id in the sheets URL [required]
--credentials, --creds json credential path (use environment variable to specify a JSON stringified credential in base64) [required]
--rowCount, --row number of rows [default: 50]
--colCount, --col number of columns [default: 20]
-h, --help Show help [boolean]
worksheets remove <worksheetId>
Remove a worksheet from the spreadsheet document:
$ google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials $CREDENTIALS \
worksheets \
remove od6
{"status": "success"}
worksheets get --worksheetId {worksheetId} append --json
Append a row to a worksheet. Once you got the worksheetId
it's really simple to append a row:
$ google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials $CREDENTIALS \
worksheets \
get --worksheetId od6 \
append --json '{a:1, b:2, c:3}'
{"content":"b: 2, c: 3","title":"1","updated":"2017-04-26T21:46:22.201Z","id":"https://spreadsheets.google.com/feeds/list/2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw/od6/cpzh4"}
Note that the JSON data we passed was not strictly valid still it worked thanks to JSON5.
As soon as you will have quotes or special characters inside your JSON, things are going to be messy. Fortunately you can also pass a base64 encoded JSON to --json
.
$ JSON=$(echo '{a:1, b:2, c:3}' | base64)
$ echo $JSON
e2E6MSwgYjoyLCBjOjN9Cg==
$ google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials $CREDENTIALS \
worksheets \
get --worksheetId od6 \
append --json $JSON
{"content":"b: 2, c: 3","title":"1","updated":"2017-04-26T21:46:22.201Z","id":"https://spreadsheets.google.com/feeds/list/2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw/od6/cpzh4"}
Use this approach if you don't know/want to setup your NodeJS environment, that's what containers are good for.
# open ~/.bashrc (or equivalent)
nano ~/.bashrc
# edit it
function google-spreadsheet-cli(){
docker run -i --rm fgribreau/google-spreadsheet-cli:latest $@
}
# save it
# source it
source ~/.bashrc
# run it!
google-spreadsheet-cli \
--id 2CVmfghQmkMdLct11Tfo0aqv1WtnPA-chuYDUMEvoVPw \
--credentials $CREDENTIALS \
worksheets \
get --worksheetId od6 \
append --json '{a:1, b:2, c:3}'
# done!
npm i google-spreadsheet-cli -g