Resources

Spreadparser turns a JSON endpoint from Google Spreadsheets into a easier to understand list of objects.

View the Demo on CodePen →

So, using Spreadparser could be useful for some goals such as:

• ✅ Small web projects
• ✅ Static generated websites
• ✅ Projects consuming government generated data
• ✅ Project prototyping
• ✅ Generate and share pages for visualizing third party data
• ✅ Studying project for frontend and backend developers
• ✅ Small companies online stores
• ✅ Allow non tech teams to provide data for tech teams
• ✅ Use Spreadsheet to create and maintain app config files

But it's probably not appropriate for:

• 🛑 Automatic spreadsheet update
• 🛑 Complex project based on relational database relations
• 🛑 Project with a huge amount of data
• 🛑 Google Spreadsheet data that can't be published to the web

Features

• 📦 Lightweigth: less than 2kb and zero dependencies
• 🧰 Versatile: Can be used for ES6/web projects, node project and as script tag
• 👍 Tested: Highly covered with unit testing, written with typescript
• 🎯 Understands your data as it is:
  • ➡️ "TRUE", "FALSE" strings and checkboxes become boolean values true and false
  • ➡️ Integers and floats become real numbers like 10 e 0.33
  • ➡️ Repeated title columns are parsed as arrays
  • ➡️ Allow spreadsheet data to be parsed as nested objects with inner properties and value
  • ➡️ Translate your data to your desired pattern like camel case or snake case

Getting Started

Compiled and production-ready code can be found in the dist directory. The src directory contains development code.

Publishing your Google Spreadsheet to web

First of all you need to have a Google Spreadsheet with your data and publish it to the web.

Now that you have your spreadsheet JSON running let's use Spreadparser.

1. Include Spreadparser on your site.

Direct Download

You can download the files directly from GitHub.

<script src="path/to/spreadparser.min.js"></script>

CDN

You can also use the jsDelivr CDN. I recommend linking to a specific version number or version range to prevent major updates from breaking your site. Spreadparser uses semantic versioning.

<!-- Always get the latest version -->
<!-- Not recommended for production sites! -->
<script src="https://cdn.jsdelivr.net/npm/spreadparser/dist/spreadparser.min.js"></script>

<!-- Get minor updates and patch fixes within a major version -->
<script src="https://cdn.jsdelivr.net/npm/spreadparser@1/dist/spreadparser.min.js"></script>

<!-- Get patch fixes within a minor version -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/spreadparser.min.js"></script>

<!-- Get a specific version -->
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/spreadparser.min.js"></script>

NPM

You can also use yarn (or your favorite package manager).

yarn add spreadparser

2. Add the markup to your HTML.

No extra configuration needed—just standard JavaScript.

fetch(Spreadparser.getSpreadsheetUrl("1Rr2y3ljAJPApYXcPyLXwxLciUCxz8XCu1Q0OnWH1l-U", 2))
    .then(response => response.json())
    .then(json => Spreadparser.parse(json, {titleCase: 'camelCase'}))
    .then(spreadsheet => {
        console.log(spreadsheet.data[0]); // You can see this console.log output bellow
    });

Giving us the following output:

{
   "nome":"Água",
   "hex":"#00FFFF",
   "rgb":{
     "red":0,
     "green":255,
     "blue":255
   },
   "hsv": {
     "hue":"180°",
     "saturation":"100%",
     "value":"100%"
   },
   "nomeWeb":"aqua"
 }

Installation (for web projects)

Install it as a dependency:

yarn add spreadparser # or npm install spreadparser

Now you can import Spreadparser as following:

import Spreadparser from "spreadparser";

Installation (for node projects)

It is possible to use Spreadparser for server side projects using node. For instance you can create a 11ty blog or a command line tool with Spreadparser:

Install it as a dependency:

yarn add spreadparser # or npm install spreadparser

Now you can import Spreadparser as following:

const Spreadparser = require("spreadparser/dist/umd/spreadparser.js");

Using it as a third party library

Spreadparser is available at jsDelivr CDN, for using it all you have to do is add proper url as script src to your HTML page:

<script src="https://unpkg.com/spreadparser">

Spreadparser is also available on unpkg.com

In the next topic we provide live examples of Spreadpaser as thirdy party library combined with other frontend tools.

Live examples

Here are some live examples for Spreadparser:

• A list of brazilian named colors - VueJS + Bulma + Spreadpaser

Documentation: getSpreadsheetUrl method

Spreadparser.getSpreadsheetUrl is a static method that receives two parameters: spreadsheetId and sheetNumber.

| Parameter | Type | Default | Required | |-|-|-|-| |spreadsheetId|string|undefined|true| |sheetNumber|number|1|false|

Sample usage:

Spreadparser.getSpreadsheetUrl("1Rr2y3ljAJPApYXcPyLXwxLciUCxz8XCu1Q0OnWH1l-U")
// https://spreadsheets.google.com/feeds/cells/1Rr2y3ljAJPApYXcPyLXwxLciUCxz8XCu1Q0OnWH1l-U/1/public/full?alt=json

Spreadparser.getSpreadsheetUrl("1Rr2y3ljAJPApYXcPyLXwxLciUCxz8XCu1Q0OnWH1l-U", 2)
// https://spreadsheets.google.com/feeds/cells/1Rr2y3ljAJPApYXcPyLXwxLciUCxz8XCu1Q0OnWH1l-U/2/public/full?alt=json

This method does exists because Spreadparser intentionally doesn't have a method to fetch data. So you can get your Spreadsheet JSON url and fetch data in your own way.

Documentation: parse method

Spreadparser.parse is a static method that receives two parameters: originalData and options.

| Parameter | Type | Required | |-|-|-| |originalData|object|true| |options|object|false|

Right bellow theres documentation for Spreadparser.parse options.

Parse Options

The second parameter for Spreadparse.parse is an object with properties. These properties are described below:

| Option | Type | Default| Description| |-|-|-|-| | separator | string | '__' | String portion used to separate nested objects | | titleCase | string | 'none' | Selected case style for data keys | | headerRow | number | 1 | Row number for title row | | includeEmptyRows | boolean | false | Use to include empty rows as part os data array |

Here are some complete options examples. Let's say we have a Google Spreadsheet as the following, we are going to asume that row column represents a real row number for each row:

|row|Person > Name | Adress > Street > Name | Adress > Street > Number | Hobbies | Hobbies| |-|-|-|-|-|-| |5|Benjamin|Yancy| 32 | Gym | Reading| | | | | | | | |7|Peter|Queens Street| 62| Climb walls | Science |

For the above Google Spreadsheet, the title row is number 4 not 1 as usual. So, using the following options:

const persons = Spreadparser.parse(originalData, {
  separator: '>',
  titleCase: 'camelCase',
  headerRow: 4,
  includeEmptyRows: true
})

The persons.data array will be:

[{
  "row": 5,
  "person": {
    "name": "Benjamin"
  },
  "adress": {
    "street": {
      "name": "Yancy",
      "number": 32
    }
  },
  "hobbies": ["Gym", "Reading"]
}, {}, {
  "row": 7,
  "person": {
    "name": "Peter"
  },
  "adress": {
    "street": {
      "name": "Queens Street",
      "number": 62
    }
  },
  "hobbies": ["Climb Walls", "Science"]
}]

How can you contribute?

• Give a :star: to this project if you like it
• Trust it as npm dependency for some of your projects
• Help this project opening an issue, you may suggest a feature, documentation or share a bug
• Improve this project by creating a pull request
• Create a new live example using Spreadparser, maybe using codepen
• Spread the idea!

License

MIT - Jota Teles - 2021