Google Spreadsheet to JSON

WARNING: This is an experimental package, and I don't recommend using this in a production environment.

This package is an ES6 module that helps you to fetch and process data from a public google spreadsheet.

Notes

1. Although improbable, it is possible that the way the Google API returns data is changed and, therefore, this package will break.
2. This package uses Fetch API and doesn't support legacy browsers without a polyfill.

Usage

Publish your spreadsheet

First, you must publish your spreadsheet to the web, using File -> Publish To Web in your Google Spreadsheet.

Getting spreadsheetId

You'll find your spreadsheetId in your spreadsheet URL

In the screenshot above the spreadsheet URL is

https://docs.google.com/spreadsheets/d/1vETd54ohHGEWPbpnqBdmW8vXnQViIhmnGxOMo62qRzM/edit#gid=0

and the spreadsheetId is 1vETd54ohHGEWPbpnqBdmW8vXnQViIhmnGxOMo62qRzM

Install package

npm

npm install gsheet-to-json --save

yarn

yarn add gsheet-to-json

Import and use package

import gsheetToJson from 'gsheet-to-json'

// gsheetToJson returns a Promise and can be used with async/await or with then/catch callbacks
const getGotCharacters = async function  () {
  const gotCharacters = await gsheetToJson({
    id: '1vETd54ohHGEWPbpnqBdmW8vXnQViIhmnGxOMo62qRzM'
  })

  console.log(gotCharacters)
}

getGotCharacters()

The result of gotCharacters is:

{
  "columns": {
    "id": [
      1,
      2,
      3
    ],
    "name": [
      "Robert Baratheon",
      "Jaime Lannister",
      "Catelyn Stark"
    ],
    "age": [
      38,
      20,
      35
    ],
    "email": [
      "[email protected]",
      "[email protected]",
      "[email protected]"
    ]
  },
  "rows": [
    {
      "id": 1,
      "name": "Robert Baratheon",
      "age": 38,
      "email": "[email protected]"
    },
    {
      "id": 2,
      "name": "Jaime Lannister",
      "age": 20,
      "email": "[email protected]"
    },
    {
      "id": 3,
      "name": "Catelyn Stark",
      "age": 35,
      "email": "[email protected]"
    }
  ]
}

Params

| Param | Options | Default | Description | |:--------:|--------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------------------------------| | id | string - required | none | The ID of your document. This is the big long aplha-numeric code in the middle of your document URL | | sheet | number - optional | 1 | The number of the individual sheet you want to get data from. Your first sheet is 1, your second sheet is 2, etc. If no sheet is entered then 1 is the default | | query | string - optional | none | A simple query string. This is case insensitive and will add any row containing the string in any cell to the filtered result. | | integers | boolean - optional | true | Setting 'integers' to false will return numbers as a string | | rows | boolean - optional | true | Setting 'rows' to false will return only column data. | | columns | boolean - optional | true | Setting 'columns' to false will return only row data

Credits

This project was thoroughly encouraged and based on gsx2json by Nick Moreton