📊 Google Spreadsheet CLI
❤️ Shameless plug
📢 Features
- List worksheets
- Add worksheet
- Remove worksheet
- Append a row to a worksheet
- Automatically adds the header row if it's missing
- Permissive JSON format through JSON5
- Available as a docker image
🎩 Authentication
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')😇 Documentation
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:
Passing raw JSON
$ 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.
Passing base64 encoded JSON
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"}Setup (docker 🐳 )
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!Setup (NodeJS)
npm i google-spreadsheet-cli -g