Sync-Google-Sheet

A simple Node-js library to download Sheet from Google Drive and parse it to structured JSON.

Preparation

Enable the Google Sheet API

Google developers console is at this address:

https://console.developers.google.com/apis/

• From your Google developer account, go to the Library menu (left side) and find the Google Sheet API.
• Activate the API by clicking the ACTIVATE button.

Get a Google access Key

The library needs one of the following access keys to download the spreadsheet document:

• Service Account Key: a JSON file.
• API Key: Simplier to create and use, but less secure.

Service Account Key

• From the same Google developers console, go to the Credentials menu.
• Clic the Create credentials dropdown button and select Service account key
• select default as Service account, and JSON as Key type
• Create and download the key

You get a .json file that contain a private key to access documents in your Google drive.

(Be carrefull with this secret key, don't commit it in your code!)

API Key

• From the developer console, go to the Credentials menu.
• Clic the Create credentials dropdown button and select API key

Copy and paste the provided string as the apiKey parameter. See the "Usage" section below

Prepare the spreadsheet

Set the Google Sheet as readable

In the Google sheet you want to export, clic on the SHARE button (top-right corner) and check that it's shared as Anyone with the link can view.

Get its file id

The file id is a 44 characters hash string inside the shareable link. For instance, consider the spreadsheet at the following address: https://docs.google.com/spreadsheets/d/1tDyLD2f_P2n9etVESzx-_CJdiH1VgXL8B9VYmEVA6pQ/edit?usp=sharing

its file id is: 1tDyLD2f_P2n9etVESzx-_CJdiH1VgXL8B9VYmEVA6pQ

Install the library

npm install sync-google-spreadsheet -save

Usage

You need to specify two things:

• fileId the file id of the Google sheet you want to download
• An access key:
  • either: clientSecretPath the path to the json file that contain your Google secret key.
  • or: apiKey the API key string

var syncSpreadsheet = require('sync-google-sheet');

var params = {
    fileId: '1tDyLD2f_P2n9etVESzx-_CJdiH1VgXL8B9VYmEVA6pQ',
    clientSecretPath: './keys/google-secret.json',
    // apiKey: 'XXxxXxXXX_XXxxxXXXxxxXXXXxxXXxxxXXXX',
    metaTableName: 'meta' // optional
};

syncSpreadsheet(params, function onResult(error, result) {
    if (error) return console.error(error);
    console.log(result);
});

Workbook and sheet formats

Meta table

The workbook should contain one meta table. The meta table define the sheets to export and how these sheets should be exported. The format of this meta table is as follow:

| name | format | key | headerLine | ------------- | ------------- | ----- | ----------- | SheetName | dictionary | id | line number

available formats are:

• array
• dictionary
• dictionary*
• mappedList
• mappedList*
• keyvalue
• arrayvalue
• mappedvalue

The key column is only used for dictionary and mappedlist tables, to specify which attribute should be used for key. If not specified, id is used as key name. Keys can be chained (colon separated) to obtain a recursively structured object.

headerLine is optional, set it if your title line is not the first one (e.g. 2).

Array and dictionary tables

Column defines one attribute of the objects. The two first row are used to define the attribute name and type. The name can be a dot formatted path used to structure the object.

| name | <- property name | ---------- | --------------------- | type | <- type of the values | value | <- value for item 1 | value | <- value for item 2 | value | <- value for item 3

For instance the following table:

| id | stats.atk | stats.def | isBoss | --------- | ----------- | ---------- | ----------- | string | int | int | bool | bouli | 280 | 430 | TRUE | spiky | 310 | 240 | FALSE | mekka | 120 | 510 |

Will result in the following JSON array:

[
    { id: 'bouli', stats: { atk: 280, def: 430 }, isBoss: true },
    { id: 'spiky', stats: { atk: 310, def: 240 }, isBoss: false },
    { id: 'mekka', stats: { atk: 120, def: 510 } }
]

or the following JSON dictionary with id used as key:

{
    bouli: { id: 'bouli', stats: { atk: 280, def: 430 }, isBoss: true },
    spiky: { id: 'spiky', stats: { atk: 310, def: 240 }, isBoss: false },
    mekka: { id: 'mekka', stats: { atk: 120, def: 510 } }
}

If you want the key to not be included in the dictionary entries, set the sheet type to dictionary* (with an asterisk *) in the meta table.

On the same example, the result would become:

{
    bouli: { stats: { atk: 280, def: 430 }, isBoss: true },
    spiky: { stats: { atk: 310, def: 240 }, isBoss: false },
    mekka: { stats: { atk: 120, def: 510 } }
}

Mapped List

A mapped list is a dictionary that maps to an array of items.

Consider the following table where id is used as key.

| id | x | y | width | height | ----- | ----- | ----- | ----- | ------ | int | int | int | int | int | 1 | 0 | 0 | 15 | 20 | 1 | 15 | 2 | 12 | 14 | 1 | 17 | 1 | 8 | 6 | 2 | 2 | 13 | 10 | 14 | 3 | 0 | 0 | 11 | 7 | 3 | 5 | 3 | 9 | 5 | 3 | 4 | 8 | 4 | 4 | 3 | 8 | 5 | 7 | 7 | 4 | 0 | 0 | 16 | 11 | 4 | 7 | 2 | 20 | 22

The items that share the same key will be grouped in an array:

{
    "1": [
        { x: 0,  y: 0, width: 15, height: 20 },
        { x: 15, y: 2, width: 12, height: 14 },
        { x: 17, y: 1, width: 8,  height: 6 }
    ],
    "2": [
        { x: 2, y: 13, width: 10, height: 14 }
    ],
    "3": [
        { x: 0, y: 0, width: 11, height: 7 },
        { x: 5, y: 3, width: 9,  height: 5 },
        { x: 4, y: 8, width: 4,  height: 4 },
        { x: 8, y: 5, width: 7,  height: 7 }
    ],
    "4": [
        { x: 0, y: 0, width: 16, height: 11 },
        { x: 7, y: 2, width: 20, height: 22 }
    ]
}

Keyvalue tables

The keyvalue table should define 3 columns:

• key
• type
• value

For instance, the following table:

| key | type | value | --------------- | --------------- | ----------- | player.speed | float | 1.4 | player.power | int | 120 | player.strength | int | 209 | area | string | "tutorial" | entries | array.int | [3, 5, 6]

will produce the following object:

{
    player: {
        speed: 1.4,
        power: 120,
        strength: 209,
    },
    area: 'tutorial',
    entries: [3, 5, 6]
}

You can define several values column with an identifier. Each value column will be extracted as a separate object.

| key | type | value:en | value:fr | value:ja | --------------- | --------------- | ----------- | ----------- | ------------- | cookie | string | Cookie | Biscuit | クッキー | key | string | Key | Clé | 鍵 | gem | string | Gem | Diamant | 宝石

Will produce the following dictionary:

{
    en: { cookie: "Cookie", key: "Key", gem: "Gem" },
    fr: { cookie: "Biscuit", key: "Clé", gem: "Diamant" },
    ja: { cookie: "クッキー", key: "鍵", gem: "宝石" }
}

Recursively structured table and chained keys

To structure dictionary or mappedlist to more that one depth level, you can chain the keys in the meta table by appending them together with a colon character :

For instance, given the following table:

| world | map | id | name | ---------------------- | -------------------- | ----- | ------------ | string | string | int | string | Clearwater Harbor | The Flour Tower | 1 | Leoril | Clearwater Harbor | Valenstrong Mansion | 1 | Viccoril | Clearwater Harbor | Shipwreck Edge | 1 | Panneak | Clearwater Harbor | Shipwreck Edge | 2 | Jamcoril | Clearwater Harbor | The Smelt Belt | 1 | Yenqarim | Clearwater Harbor | The Smelt Belt | 2 | Dorxiron | Clearwater Harbor | The Smelt Belt | 3 | Alydove | Crewth Piers | Valor Brewery | 1 | Wolgwynn | Crewth Piers | Valor Brewery | 2 | Oldove | Crewth Piers | Valor Brewery | 3 | Safrila | Crewth Piers | Riddlecloud | 1 | Colynn | Crewth Piers | Riddlecloud | 2 | Kyslynn | Crewth Piers | Narrow Bridge | 1 | Deltheris | Crewth Piers | Dawnton Castle | 1 | Aroborin | Crewth Piers | Dawnton Castle | 2 | Sarovar | Rainbow Ridge Orchard | Ert Grotto | 1 | Dorfinas | Rainbow Ridge Orchard | Rivershire Rampart | 1 | Brennoa | Rainbow Ridge Orchard | Rivershire Rampart | 3 | Alyyra

By chaining the keys world:map:id in the meta table, we obtain the following structured JSON object:

{
    "Clearwater Harbor": {
        "The Flour Tower":     {
            "1": { name: "Leoril"}
        },
        "Valenstrong Mansion": {
            "1": { name: "Viccoril" }
        },
        "Shipwreck Edge":      {
            "1": { name: "Panneak" },
            "2": { name: "Jamcoril" }
        },
        "The Smelt Belt": {
            "1": { name: "Yenqarim" },
            "2": { name: "Dorxiron" },
            "3": { name: "Alydove" }
        }
    },
    "Crewth Piers": {
        "Valor Brewery":  {
            "1": { name: "Wolgwynn" },
            "2": { name: "Oldove" },
            "3": { name: "Safrila" }
        },
        "Riddlecloud":    {
            "1": { name: "Colynn" },
            "2": { name: "Kyslynn" }
        },
        "Narrow Bridge":  {
            "1": { name: "Deltheris"}
        },
        "Dawnton Castle": {
            "1": { name: "Aroborin" },
            "2": { name: "Sarovar" }
        }
    },
    "Rainbow Ridge Orchard": {
        "Ert Grotto": {
            "1": { name: "Dorfinas" }
        },
        "Rivershire Rampart": {
            "1": { name: "Brennoa" },
            "3": { name: "Alyyra" }
        }
    }
}

Array value

The arrayvalue format is for exporting a single column to an array.

| value | ------- | string | sword | shield | ring

["sword", "shield", "ring"]

Mapped value

Mapped value let you extract arrays of values, grouped by a key. The value column needs to be named value.

For instance, extrating the following table:

| id | value | -------- | ----------- | string | int | star | 9 | star | 12 | star | 20 | luna | 3 | luna | 7

Produces the following JSON object:

{
    "star": [9, 12, 20],
    "luna": [3, 7]
}

Available types

The following types can be defined to tell the script how to parse the values in the spreadsheet. If no type is specified, the column (or row when spreadsheet is defined as keyvalue) is ignored. If a value doesn't match with the type defined, an error is returned in the callback.

Basic types

• int Base 10 integer. Default to 0.
• float Floating point number. Default to 0.
• string Text string. Default to the empty string.
• bool Boolean value. Note that these values are displayed as TRUE or FALSE in Google Sheet. If the value is not defined (i.e. empty cell) the attribute is optimized out and removed completely.

Arrays

• array JSON encoded array. default to an empty array.
• array.int JSON encoded array of integer.
• array.float JSON encoded array of number.
• array.string JSON encoded array of string.
• array.bool JSON encoded array of boolean.

Wildcard symbols

For some of the basic types (int, float and string), the following wildcard symbols are available:

• ? if the cell is empty, don't add the attribute (no default value)
• + allow the value to be an array of this type.
• * allow the value to be empty or an array.

JSON Data

• json string that can be parsed as a valid JSON object. If the value is not defined, the attribute gets optimized out.

Reference types

Reference type let you point data from another sheet that has been extracted (i.e. the sheet needs to be defined in the meta table before where it is referenced). You define which sheet (and optionaly wich attribute) to point in the type field itself. Empty cell are optimized out and attribute removed completely.

• ref:<sheet.path> single reference
• array.ref:<sheet.path> array of references.

Consider the following table, in which one field is a reference to the zone sheet we previously extracted:

| name | zones | bgm | --------------- | ----------- | --------------- | string | ref:zone | string | tuto | | wild | plain | 1 | wild | cave | 2 | underground | mountain | 3 | wild | volcano | 4 | fire

Produce the following JSON object:

{
    tuto: { name: "tuto", bgm: "wild" },
    plain: {
        name: "plain",
        zones: [
            { x: 0,  y: 0, width: 15, height: 20 },
            { x: 15, y: 2, width: 12, height: 14 },
            { x: 17, y: 1, width: 8,  height: 6 }
        ],
        bgm: "wild"
    },
    cave: {
        name: "cave",
        zones: [
            { x: 2, y: 13, width: 10, height: 14 }
        ],
        bgm: "underground"
    },
    mountain: {
        name: "mountain",
        zones: [
            { x: 0, y: 0, width: 11, height: 7 },
            { x: 5, y: 3, width: 9,  height: 5 },
            { x: 4, y: 8, width: 4,  height: 4 },
            { x: 8, y: 5, width: 7,  height: 7 }
        ],
        bgm: "wild"
    },
    volcano: {
        name: "volcano",
        zones: [
            { x: 0, y: 0, width: 16, height: 11 },
            { x: 7, y: 2, width: 20, height: 22 }
        ],
        bgm: "fire"
    }
}

Acknowledgements

This tool is based on grille-downloader and grille-xlsx from Daigo Sato.