revolunet / spreadsheet-to-json

Convert Google Spreadsheets to JSON using Javascript

spreadsheet-to-json

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"
    },
    {
      "id": "F0002",
      "totalAmount": "4398.2"
    }
  ]
}

This can be useful when you want people edit spreadsheets and need to work with the data.

Install

npm i --save spreadsheet-to-json

QuickStart

extractSheets can use node callback pattern or async/await.

const { extractSheets } = require("spreadsheet-to-json");

// optional custom format cell function
const formatCell = (sheetTitle, columnTitle, value) => value.toUpperCase();

extractSheets(
  {
    // your google spreadhsheet key
    spreadsheetKey: "abch54Ah75feBqKGiUjITgE9876Ypb0yE-abc",
    // your google oauth2 credentials or API_KEY
    credentials: require("./google-generated-creds.json"),
    // optional: names of the sheets you want to extract
    sheetsToExtract: ["Customers", "Invoices"],
    // optional: custom function to parse the cells
    formatCell: formatCell
  },
  function(err, data) {
    console.log("Customers: ", data.Customers);
    console.log("Invoices: ", data.Invoices);
  }
);

see ./example.js

Authentification

The credentials key can either be a API_KEY string or a service account object.

API Key

You can create an API key here : https://console.developers.google.com/apis/credentials

Be sure to restrict it to Google Drive API

Google service account

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 credentials then service account and save the generated JSON. (privately)
• then give the JSON contents to the credentials parameter 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 [email protected] http://github.com/revolunet

License

• MIT : http://opensource.org/licenses/MIT

spreadsheet-to-json's Issues

How to ignore first row?

I have a spreadsheet table where I want to ignore the first row, as my header start from 2nd row. How do I do this?

Returns null

Using the format in the example. No errors but I just get null from the returned object when running extractSheets function.

throw new Error-No values in the header row

I have a form with multiple tabs that users need to maintain. Some users have started adding blank forms, and I'm not sure how to skip this error. Can you help me? Thank you!

throw new Error('No values in the header row - fill the first row with header values before trying to interact with rows');
            ^
Error: No values in the header row - fill the first row with header values before trying to interact with rows
    at GoogleSpreadsheetWorksheet.loadHeaderRow 

WARNING! You must upgrade to the latest version of google-spreadsheet!

Google's deprecation date for the v3 sheets API is March 3rd 2020
Bad news - this version of this module will stop working on that date :(
Good news - the new version of the module uses the newer v4 api :)
However, there are breaking changes, so please see the docs site
https://theoephraim.github.io/node-google-spreadsheet

when using on now.

Severe vulnerabilities in package 😅

https://snyk.io/test/github/revolunet/spreadsheet-to-json?severity=high&severity=medium&severity=low

I would like to use the package 😀 I'll give it a shot at updating the project

New column if same name

Hello,

I think it would also be nice to have the avability to retrieve the renamed version delivered by the theoephraim/node-google-spreadsheet.

This would allow one to get a JSON that would like:

data: {
  wrong: "a",
  wrong_2: "b",
  wrong_3: "c"
}

For thus I propose to add an option key that would indicate the base field name for which we want to create a new objet. For instance :
newColumn:["wrong", "tags"]

Not all columns if same name

Hello,
I have a spreadsheet with five columns: 'id', 'artist name', 'wrong', 'wrong' and 'wrong'.
And when I use this module, I obtain a JSON but I just have the first column 'wrong' (and also the 'id' and 'artist name' columns).
Is it on purpose?

TypeError: extractSheets is not a function

I am following the example in the docs using my own credentials and google doc ID and I am getting this error: TypeError: extractSheets is not a function

Do you have any roadmap for this module?

I though about other features like:

• streaming (in case of very large spreadsheet)
• conversion to specific type (boolean, integer, etc.) for each cell
• access of sheets by integer instead of name

Do you plan anything similar for this module?

I find it one of the best to read from Google Spreadsheet, and I was also thinking about a browser usage of Google Spreadsheet API to use Google Spreadsheet as a database (of course not comparable to databases like mysql, mongodb, etc.).

Header symbol parsing issue

When a colon (:) or slash (/) is present in the first row of a sheet, the value for the column containing the symbol is null. Oddly only the value comes back null but the field name itself containing the symbol displays correctly.