Share your code. npm Orgs help your team discover, share, and reuse code. Create a free org »

    spreadsheet-to-jsonpublic

    spreadsheet-to-json

    npm license github-issues Circle CI build status

    nodei.co

    Convert Google Spreadsheets to JSON using Javascript

    Uses the google-spreadsheet library to fetch data.

    The final JSON is based on sheets names and column titles and finally looks like this :

    {
        "Customers": [{
                "name": "Sony",
                "country": "Japan"
            },{
                "name": "Renault",
                "country": "France"
            }
        ],
        "Invoices":  [{
                "id": "F0001",
                "totalAmount": "12367.12"
            },{
                "name": "F0002",
                "totalAmount": "4398.2"
            }
        ]
    }

    This is useful when you let people edit spreadsheets and need to work with the data.

    Install

    npm i --save spreadsheet-to-json

    QuickStart

    var extractSheets = require('spreadsheet-to-json');
     
    // optional custom format cell function
    var formatCell = function(sheetTitle, columnTitle, value) {
        return value.toUpperCase();
    };
     
    extractSheets({
        // your google spreadhsheet key
        spreadsheetKey: 'abch54Ah75feBqKGiUjITgE9876Ypb0yE-abc',
        // your google oauth2 credentials
        credentials: require('./google-generated-creds.json'),
        // names of the sheet you want to extract (or [] for all)
        sheetsToExtract: ['Customers', 'Invoices'],
        // custom function to parse the cells
        formatCell: formatCell
    }, function(data) {
        console.log('Customers: ', data.Customers);
        console.log('Invoices: ', data.Invoices);
    });
     

    Authentification

    Create a credentials.json file for your app here : https://console.developers.google.com/

    • create a new project
    • enable the Drive API
    • in credentials, select create new client id then service account and save the generated JSON. (privately)
    • the just paste the JSON contents as credentials in the extractSheets call.

    Share the target google spreadsheet with the client_email from the credentials.json.

    Tests

    extractSheet should produce correct data
    
    ✓ sheet should have 5 rows
    ✓ row should have 4 properties
    ✓ row should have correct properties
    ✓ name should be Johnny
    ✓ id should be 1
    
    formatCell
    
    ✓ names should not be equal
    ✓ name should be uppercased
    ✓ id should be 1
    
    extractSheets should produce correct data
    
    ✓ data should have 3 sheets
    ✓ sheets should have correct names
    ✓ Private sheet should not be exported
    ✓ Customers should have 5 rows
    ✓ Customers properties names should equal id,name,location,Exotic_ Col-NAME
    ✓ Invoices should have 9 rows
    ✓ Invoices properties names should equal ref,amount
    ✓ Leads should have 9 rows
    ✓ Leads properties names should equal id,estimate,ref
    
    open invalid spreadsheet should return empty data
    
    ✓ invalid should have no rows
    
    columns with exotic names should be handled correctly
    
    ✓ Exotic column name should exist in output
    ✓ Exotic column name should be renamed in data
    ✓ Exotic column name should be handled correctly
    
    
    total:     21
    passing:   21
    duration:  1.9s
    

    Author

    Julien Bouquillon julien@bouquillon.com http://github.com/revolunet

    License

    install

    npm i spreadsheet-to-json

    Downloadsweekly downloads

    45

    version

    1.2.0

    license

    MIT

    repository

    githubgithub

    last publish

    collaborators

    • avatar