Sign inSign up

fgribreau/google-spreadsheet-cli

By fgribreau

•Updated almost 9 years ago

CLI writing data into a Google Spreadsheet

Image
0

2.0K

fgribreau/google-spreadsheet-cli repository overview

⁠📊 Google Spreadsheet CLI

Build Status Coverage Status deps Version Docker hub available-for-advisory extra Twitter Follow

⁠❤️ 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

⁠Changelog⁠

Tag summary

Content type

Image

Digest

Size

262.3 MB

Last updated

almost 9 years ago

docker pull fgribreau/google-spreadsheet-cli