📊 Google Spreadsheet CLI

❤️ Shameless plug

• Open-Source self-hostable or fully-managed webhook as a service
• Charts, simple as a URL. No more server-side rendering pain, 1 url = 1 chart
• Looking for a managed Keycloak IAM ?

📢 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

Changelog