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



npm license github-issues Circle CI build status

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.


npm i --save spreadsheet-to-json


var extractSheets = require('spreadsheet-to-json');
// optional custom format cell function
var formatCell = function(sheetTitle, columnTitle, value) {
    return value.toUpperCase();
    // 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);


Create a credentials.json file for your app here :

  • 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.


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


✓ 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


Julien Bouquillon