1.0.4 • Published 1 year ago
gsheet-cmd v1.0.4
gsheet
CLI for google sheets (just like rclone or gdrive)
$ npm i -g gsheet-cmd
or checkout this repo and run
$ npm link
Setting up a service account
- Create a project in https://console.cloud.google.com
- Enable the google sheets API via https://console.cloud.google.com/apis/library
- Create a service account in https://console.cloud.google.com/iam-admin/serviceaccounts/
- Download the file and store (ideally in .gsheet/...json)
(per se, one could also do this via a personalized token - just not implemented yet)
Usage
$ gsheet --help
Access a gsheet from the command line
gsheet read|append|update --service-account <file> [--csv] [--format-json]
--sheet <id> [--worksheet <name>] [--firstCol=A] --lastCol=<B or right>
[--lookup-cols <col>[,<col>]] --file <file>|-
Commands:
gsheet read Read gsheet and return as JSON (or CSV)
gsheet append Apend JSON (or CSV) to gsheet - proprety names = column names
gsheet update Update gsheet from JSON (or CSV) - proprety names = column
names; needs --lookup-cols to be given
Options:
--help Show help [boolean]
--version Show version number [boolean]
--service-account File name of service account file - either path or in
~/.gdrive/ (same as gdrive) [string] [required]
--csv Read output / append/update input is CSV (not JSON)
[boolean]
--format-json Format the JSON output (JSON only) [boolean]
--sheet ID of the gsheet (last part of URL) [string] [required]
--worksheet Name of the worksheet (defaults to first worksheet)[string]
--first-col First column in the worksheet to look at (defaults to A)
[string]
--last-col Last column in the worksheet to look at (B or right of it)
[string]
--lookup-cols Name(s) of columns to perform lookup on, need to be defined
in JSON (or CSV) [string]
--file Read output / append/update input file name; - for stdout
(only read) [string] [required]
Examples
Reading JSON
$ gsheet read --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol C --file test.json
Reading formatted JSON to stdout
$ gsheet read --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol C --file - --format-json
Reading CSV
$ gsheet read --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol C --file test.csv --csv
Reading CSV to stdout
$ gsheet read --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol C --file - --csv
Append from a JSON file
$ gsheet sebastianrothbucher$ gsheet append --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol D --file test.json
Append from a CSV file
$ gsheet append --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol D --file test.csv --csv
Update from a JSON file
$ gsheet update --service-account some-12345-123456789abc.json --sheet 1aXf_jiHPPu1vbMRlrAYNAI2nTRWEdg1P7HLZXzagKB8 --worksheet Sheet2 --lastCol D --file test.json --lookup-cols Name