google-spreadsheet-cli: A JavaScript repository from FGRibreau

📊 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

FGRibreau/google-spreadsheet-cli