google-spreadsheet-to-json

google-spreadsheet-to-json
==========================


A simple tool to export Google Spreadsheets to JSON files. Can be used though Node API or CLI.

Installation

Command-line:
``
$ npm install -g google-spreadsheet-to-json
`

Node API:
`
$ npm install --save google-spreadsheet-to-json
`

Help

`
$ gsjson --help

Usage: gsjson [file] [options]

Options:

-h, --help output usage information
-V, --version output the version number
-b, --beautify Beautify final JSON
-s, --credentials Service Account credentials JSON data or file path
-t, --token Auth token acquired externally
-y, --tokentype Type of the informed token (defaults to "Bearer")
-w, --worksheet Worksheet index (zero-based) or title (defaults to first worksheet, can be repeated)
-a, --all-worksheets Return all worksheets (worksheet option is ignored)
-c, --hash Column to hash the final object
-m, --property-mode How to handle property names: "camel" (default), "pascal", "nospace" or "none"
-i, --vertical Use the first column as header
-l, --list-only Just list the values in arrays
-0, --include-header Include header when using "list-only" option
--header-start Header start line (auto-detected by default)
--header-size Header lines quantity (defaults to 1)
--ignore-col Column name (Excel-like labels) to be ignored (can be repeated, number are also supported)
--ignore-row Row number to be ignored (can be repeated)
`

Usage (CLI)

Public spreadsheets:
`
$ gsjson abc123456789 data.json
`

Private spreadsheets:
`
$ gsjson abc123456789 data.json -s creds.json
$ gsjson abc123456789 data.json -t authtoken
`

You can also redirect the output if you omit the filename:
`
$ gsjson abc123456789 >> data.json
`

Usage (Node API)

With the exception of beautify, the same options from the CLI applies here (options like include-header becomes includeHeader).

`javascript
var gsjson = require('google-spreadsheet-to-json');

gsjson({
spreadsheetId: 'abc123456789',
// other options...
})
.then(function(result) {
console.log(result.length);
console.log(result);
})
.catch(function(err) {
console.log(err.message);
console.log(err.stack);
});
`

$3

- A spreadsheet ID can be extracted from its URL.
- If an array is passed on the worksheet option (in CLI, this can be done by repeating the argument) or the allWorksheets option is used, the output from each worksheet is returned inside an array (the order is not guaranteed).

About authentication

Since Google enforces OAuth 2.0, this module offers arguments for Service Account JSON credentials or an auth token.

The credentials option can receive a file path, the JSON data (string) or an object (on Node API).

For quick tests, there's a method to acquire a temporary token:
- Access Google OAuth 2.0 Playground: https://developers.google.com/oauthplayground/
- Enter the scope: https://spreadsheets.google.com/feeds/
- Authorize and retrieve your access token

For more detailed information regarding auth methods: https://github.com/theoephraim/node-google-spreadsheet

Known issues

- Public spreadsheets can only be used without authentication if the option "File > Publish to the web" is used in the Google Spreadsheets GUI, even if the spreadsheet is visible to everyone. This problem won't occur when authenticated.

Example 1 (array of objects):

|Id |Name |Age |Newsletter |
|---|-----|----|-----------|
|1|Joisse Wendell|25|TRUE|
|2|Brand Katelin|16|FALSE|
|3|Gloriana Goldie||TRUE|

Command-line:
`
$ gsjson abc123456789 data.json -b
`

Node API:
`javascript
gsjson({
spreadsheetId: 'abc123456789'
})
`

Output:
`json
[
{
"id": 1,
"name": "Joisse Wendell",
"age": 25,
"newsletter": true
},
{
"id": 2,
"name": "Brand Katelin",
"age": 16,
"newsletter": false
},
{
"id": 3,
"name": "Gloriana Goldie",
"newsletter": true
}
]
`

Example 2 (hashed object, with pascal case properties):

|id |first name |last name |age |newsletter |
|---|-----------|----------|----|-----------|
|1|Joisse|Wendell|25|TRUE|
|2|Brand|Katelin|16|FALSE|
|3|Gloriana|Goldie||TRUE|

Command-line:
`
$ gsjson abc123456789 data.json -b -c id -m pascal
`

Node API:
`javascript
gsjson({
spreadsheetId: 'abc123456789',
hash: 'id',
propertyMode: 'pascal'
})
`

Output:
`json
{
"1": {
"Id": 1,
"FirstName": "Joisse",
"LastName": "Wendell",
"Age": 25,
"Newsletter": true
},
"2": {
"Id": 2,
"FirstName": "Brand",
"LastName": "Katelin",
"Age": 16,
"Newsletter": false
},
"3": {
"Id": 3,
"FirstName": "Gloriana",
"LastName": "Goldie",
"Newsletter": true
}
}
`

Example 3 (list of values, selecting the second worksheet):

|Id |Name |Age |Newsletter |
|---|-----|----|-----------|
|1|Joisse Wendell|25|TRUE|
|2|Brand Katelin|16|FALSE|
|3|Gloriana Goldie||TRUE|

Command-line:
`
$ gsjson abc123456789 data.json -b -l -w 1
`

Node API:
`javascript
gsjson({
spreadsheetId: 'abc123456789',
listOnly: true,
worksheet: 1
})
`

Output:
`json
[
[
1,
"Joisse Wendell",
25,
true
],
[
2,
"Brand Katelin",
16,
false
],
[
3,
"Gloriana Goldie",
null,
true
]
]
`

Example 4 (selecting multiple worksheets by title):

|Id |Name |Dead |
|---|-----|-----|
|1|Jon Snow|FALSE|
|2|Hodor|FALSE|

|Id |Saint |Main Attack |
|---|------|------------|
|1|Seiya|Pegasus Ryu Sei Ken|
|2|Ikki|Hoyoku Tensho|

Command-line:
`
$ gsjson abc123456789 data.json -b -w "Game Of Thrones" -w "Saint Seiya"
`

Node API:
`javascript
gsjson({
spreadsheetId: 'abc123456789',
worksheet: ['Game Of Thrones', 'Saint Seiya']
})
`

Output:
`json
[
[
{
"id": 1,
"name": "Jon Snow",
"dead": false
},
{
"id": 2,
"name": "Hodor",
"dead": false
}
],
[
{
"id": 1,
"saint": "Seiya",
"mainAttack": "Pegasus Ryu Sei Ken"
},
{
"id": 2,
"saint": "Ikki",
"mainAttack": "Hoyoku Tensho"
}
]
]
`

Example 5 (vertical data, without beautifying):

|Id |1 |2 |3 |
|---|---|---|---|
|Name|Joisse Wendell|Brand Katelin|Gloriana Goldie|
|Age|25|16||
|Newsletter|TRUE|FALSE|TRUE|

Command-line:
`
$ gsjson abc123456789 data.json -i
`

Output:
`json
[{"id":1,"name":"Joisse Wendell","age":25,"newsletter":true},{"id":2,"name":"Brand Katelin","age":16,"newsletter":false},{"id":3,"name":"Gloriana Goldie","newsletter":true}]
``

Change log

See specific file.

TO-DO

- Improve options documentation (especially header size)
- Create more test cases

License