Google-spreadsheet-to-json NPM

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 <spreadsheet-id> [file] [options]

Options:

  -h, --help                   output usage information
  -V, --version                output the version number
  -b, --beautify               Beautify final JSON
  -s, --credentials <s>        Service Account credentials JSON data or file path
  -t, --token <token>          Auth token acquired externally
  -y, --tokentype <tokentype>  Type of the informed token (defaults to "Bearer")
  -w, --worksheet <n>          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>          Column to hash the final object
  -m, --property-mode <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 <n>           Header start line (auto-detected by default)
  --header-size <n>            Header lines quantity (defaults to 1)
  --ignore-col <n>             Column name (Excel-like labels) to be ignored (can be repeated, number are also supported)
  --ignore-row <n>             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).

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);
});

Notes

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:

gsjson({
    spreadsheetId: 'abc123456789'
})

Output:

[
    {
        "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:

gsjson({
    spreadsheetId: 'abc123456789',
    hash: 'id',
    propertyMode: 'pascal'
})

Output:

{
    "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:

gsjson({
    spreadsheetId: 'abc123456789',
    listOnly: true,
    worksheet: 1
})

Output:

[
    [
        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:

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

Output:

[
    [
        {
            "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:

[{"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}]