Skip to content

download, read and parse a formatted Google Sheets to JSON object

License

Notifications You must be signed in to change notification settings

cstoquer/sync-google-sheet

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

52 Commits
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Install with NPM

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.

About

download, read and parse a formatted Google Sheets to JSON object

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published