google-spreadsheet-cli

    2.0.1¬†‚Äʬ†Public¬†‚Äʬ†Published

    ūüďä 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

    Install

    npm i google-spreadsheet-cli

    DownloadsWeekly Downloads

    1

    Version

    2.0.1

    License

    MIT

    Last publish

    Collaborators

    • fgribreau