jlord/sheetsee.js

:eyes: :chart_with_upwards_trend: Visualize Data from a Google Spreadsheet

Sheetsee.js is a client-side library for connecting Google Spreadsheets to a website and visualizing the information with tables and charts.

---

This repository is for the project website and provides a compiled version sheetsee.js. Use the compiled version if you want all of what is available in Sheetsee. If you want just the parts that you're going to use in your project and nothing more, you can build a custom version.

Each part of Sheetsee has its own repository and issues should be opened there. General issues/pull requests for the site are OK here.

Repositories for Sheetsee Components

Component	Description	Repo
sheetsee	Command line module for building a custom version of Sheetsee.	jlord/sheetsee
sheetsee-core	Included in every build. Has helpful working-with-your-data functions.	jlord/sheetsee-core
sheetsee-tables	Contains everything you'll need to create a table including sortable columns, pagination and search. Uses Mustache.js templating.	jlord/sheetsee-tables
sheetsee-maps	For making maps with your point, line or polygon spreadsheet data. Uses Leaflet.js and Mustache.js.	jlord/sheetsee-maps

sheetsee.js's Issues

Options in a Worksheet

Have a second worksheet in each spreadsheet file that contains some options for the generated page...

Marker Clustering

Sheetsee has been fantastic for mapping data I'm using but a lot of the datapoints have the exact same coordinates which will require me to apply some sort of marker clustering.

I tried working with mapbox.js clustering alongside sheetsee but had no joy. Is marker clustering something that can be done with sheetsee or is it unsupported?

More functions to work with data

Not really an issue, but I think this would be a nice enhancement: any change there could be functions to get all rows where a column is not empty; and one to return all unique values from a column?

when addUnitsLabels() runs it changes it alters the original data, gData

If you type something into the table search filter, like 'coco', and then hit clear, the 'name' and 'cuddlability' columns are empty because when the page loads it has that info, then when addUnitsLabels is called to format data for one of the charts, instead of creating a new altered data, it alters gData and therefore screws up other stuff.

addUnitsLabels alters the data so that there is a units and labels key which is what the d3 charts need. but it shouldn't alter the orig data, it should make a new altered data.

not sure if this was a problem before i started building the client-side version.

Link to addUnitsLabels() in Sheetsee
https://github.com/jllord/sheetsee.js/blob/master/js/sheetsee.js#L178-L186

/ cc @maxogden

99000435671907

Pagination

Having a simple, non-library dependent pagination for tables would be awesome.

Sheetsee-bubble comes up with empty page

Hi,

I tried the bubble chart at https://github.com/maxogden/sheetsee-d3bubble (which btw, I hosted at http://www.wyzpubs.com/dataviz/sheetsee/sheetsee-d3bubble/) and it results in an empty page. If I use div: 'body' instead of div: '#stuff' in index.html, it works. How do I make the svg element be contained within the div 'stuff'?

Thanks,
Jayaram

Sheetsee.getKeyword not working

no data shown from the query either using my data or the one from your spreadsheet.

It doesn't work in your demo page either.

adding sheetsee to bower repo

any chance this will soon be distributed in bower?

Static Site Generator

Create a site that lets you choose some options, enter your spreadsheet key and press a button to get the html generated for you.

get rid of all the global functions

hi! some advice:

right now when people load https://github.com/jllord/sheetsee.js/blob/master/sheetsee.js into their browser it creates a bunch of global functions and other things (like getCurrentYear, YEARS, etc)

one thing a lot of people do is wrap their code in a function so that their internal library functions don't leak into everyones page.

var Module = (function(window) {
  // in here things are private
  // unless you do something like window.hello = "world" then 'hello' becomes a global variable
  // but if you just do var hello = "world" hello will only be available inside here
}(window))

ideally for sheetsee you should only create 1 new global variable: sheetsee. people using the library would load sheetsee.js into their page and they would use it by doing things like sheetsee.getCurrentYear() or sheetsee.YEARS. you can look at underscore.js for an example of this pattern

the thing with globals is that if people already have a function on their page called getCurrentYears then yours might clobber theirs and cause bugs, so it is a good practice to only create 1 global variable that all of your functions and stuff will live under

so you could do something like this:

var Module = (function(window) {
  var sheetsee = window.sheetsee = {} // this line makes a new variable inside this function called sheetsee but also at the same time makes a sheetsee variable that is available to the whole page globally
  sheetsee.YEARS = [1,2,3,4]
  sheetsee.getCurrentYears = function() { // blah blah }
}(window))

Cache?

Hi, Is there a way to cache results?

Is there a way to return the cell name/ID?

I didn't see this reflected in the help documentation, but I notice there are sort of undocumented features like using {{rowNumber}}. Is there also a way to return the specific cell ID (i.e. "A1" or "G7")? If this feature did exist, it'd then be possible to use that Cell ID to make an AJAX call or something to the Google Sheet and update fields.

Multiple Instances of Tabletop

Set it up so that you can run multiple instances of Tabletop.js to multiple spreadsheets (tabletop.js already supports this).

Why is the Pie Chart so terrible - makeColorArrayOfObject()

The colors aren't showing up and the labels on the right are jank ( but I think that's an issue with my d3 for this pie chart).

The colors are jank it seems because makeColorArrayOfObject() isn't working correctly.. even though in my other server-side working version there hadn't been a problem.

It is suppose to be taking the array of hexcolors it's given and then inserting them into a new object inside of an array that will be passed into a d3.

When you return the data from makeColorArrayOfObject, you can see it's not placing the color.

it is called here: https://github.com/jllord/sheetsee.js/blob/gh-pages/index.html#L388-L390

and defined here: https://github.com/jllord/sheetsee.js/blob/gh-pages/js/sheetsee.js#L205-L218

I think it has something to do with the number of colors it's given? When I gave it a larger array of colors it placed one color in the output array position 1, but was still undefined in position 0. Hmm.

/ cc @maxogden

More functions to work with data

Not really an issue, but I think this would be a nice enhancement: any change there could be functions to get all rows where a column is not empty; and one to return all unique values from a column?

Fix double ids in HTML

Leaflet Panel Layers

My goal is to simply add points from a google spreadsheet to an existing leaflet map. Using sheetsee I was able to add everything except the layer control using the leaflet panel layers plugin. The error comes up in the panel layers js - .disableScrollPropagation(container); - undefined not a function, but since it works fine in leaflet/cartodb/mapbox I thought I would post here. See - http://jsfiddle.net/gLyb1uzc/1/ - if you replace sheetsee with mapbox js it works fine. The normal layer control seems to work ok with sheetsee.

Provide better demo that could be accessed without running it locally.

Even in the showcases that are listed in the readme is not very clear how it is used or where..

support dynamic units and labels

so you dont have to change the data

Add tests

For all the bits...finally!

Two markers with the same lat/long - one hides the other.

I'll have to dig into some mapbox.js/leaflet.js docs to see if there is any way to handle this.

My Sheetsee table blank (sometimes)?

jlord can you help?

see screenshot below. for some reason sheetsee and tabletop 1.3.4 produce a blank table from my google sheet or only a single row of data. Other times, it displays perfectly without making any changes. I have tried deleting my browser cache. The chrome console shows no errors. It is a fairly large sheet with 20k rows. Is this too large for sheetsee? any idea what is causing these empty tables?

https://www.dropbox.com/s/68spf4vy20mwusg/Screenshot%202015-03-12%2016.07.22.png?dl=0

Popup Content Formatting

I finally found a way, and am so very proud, to let users send in custom content to add to the geoJSON (that they'll later call upon when formatting marker popup content).

I can't, however, figure out a good way to have users pass in the format for popup content since it will depend on feature.opt and so forth which won't be defined outside of sheetsee.js. I had one sort of loose idea but it involved regular expressions and seemed not ideal.

I was thinking, maybe I should just have users bring the entire addPopup() function into their HTML instead of having them pass something from the HTML into addPopup() in sheetsee. It's not a huge function and at this point seems like the easiest way to handle things.

/ cc @maxogden

[Notice] Pagination Broken

I've opened an issue on the sheetsee-tables repo but wanted to put a note here too. Will try and get to it asap.

jlord/sheetsee-tables#3

Undefined function "map" in module.exports.d3BarChart

I'm trying to get a barchart running and I can't seem to find the fix here. I get

Uncaught TypeError: undefined is not a function 

Thrown on line 1419 of my sheetsee compilation (compiled with all options).

Specifically, it's the map function throwing the error. It seems like map is loaded properly via d3.

module.exports.d3BarChart = function(data, options) {
    // format data into units and labels
    var data = data.map(function(r) {
      var labels = options.labels
      var units = options.units
      return {units: r[units], labels: r[labels], hexcolor: r.hexcolor}
    })

I can't figure out what's going on...probably something simple.

When working with multiple sheets sorting doesn't work

I have a spreadsheet with two sheets Sheet1 and Sheet2

The code looks something like this

gData = data
        sheet1 = gData.Sheet1.elements
        sheet2 = gData.Sheet2.elements
        Sheetsee.makeTable(sheet1, "#siteTable")
        Sheetsee.initiateTableFilter(sheet1, "#tableFilter", "#siteTable")
        Sheetsee.makeTable(sheet2, "#userTable")
        Sheetsee.initiateTableFilter(sheet2, "#userTableFilter", "#userTable")

after doing this

Tabletop.init( { key: URL, callback: showInfo, simpleSheet: false })

when trying to sort the table I get this exception

Uncaught TypeError: Object #<Object> has no method 'sort' sheetsee.js:38
sortThings sheetsee.js:38
sendToSort sheetsee.js:68
v.event.dispatch jquery.js:2
o.handle.u

but when i change the code to simpleSheet: true and use gData for the table data it works.

Make build script

Multiple Category Filters

Hi! I'm relatively new to this, but is there a way to enable filtering across multiple categories rather than doing a getMatches() for each separate thing you want to filter?

It would be great if this could be done across multiple columns as well. For instance, say I have a spreadsheet with 3 columns - name of a place, type of establishment, and city. I want to be able to filter and choose multiple types of establishments, but also within a specific city. Would it be possible to do this in one getMatches() call so I could return the names of the places that match those specific filters?

Right now, I'm doing multiple getMatches() calls, and still haven't found a way to filter down across columns. I can choose a couple different types of establishments, and combine those returned objects to create one complete object of matching data results, but then if I want to filter by city, I don't see a way to do that - using my method above with combining objects, it just adds the additional rows to my data object, rather than only selecting the ones from the data set that match the chosen city.

Thanks for any help on this, and really great job on the project - I appreciate that something like this exists!

Why do I open the index.html is very slow

Import CSS or JS is the external?

Polygons

Not really an Issue but I tried for a long time and could not figure out how to make my spreadsheet for polygons. I can't seem to find the documentation for it but it does say that Sheetsee has supported this feature for a while. It would be great if I could get some help with this! This is for GeoJson.

Support polygons in maps

Add geojson support for this so that they render in the map.

sheetsee should wait until page is loaded to load icanhaz templates

this would make issues like #56 not be a problem

demo files missing

assets.css and tabletop1.3.4.js are not found in the repo, so you cannot run the demos locally without making those files up yourself

No more map background

Hi Jlord !

It seems that sheetsee-map doesn't work anymore : markers are ok but the background of the map has disappeared !

Have a look here -> https://jlord.github.io/hack-spots/

And on my page same issue -> http://www.newsresources.org/journalism-events-calendar/

I tried to find a fix but I can't :/

Thanks !

Cédric

The default sorting option is acting weird.

Hello and thanks for a simple framework.
It was really easy to setup.

However the sorting function of the table data is misbehaving a bit. When you have data formated as percentage (e.g. 10,1%17,1%, -5%, -6%) and regular decimal values it dosen't get sorted correctly. They are sorted randomly it seems. But Alphabetical data names, years works great.
The problem seems to appear when you add a coma or a dot in between the numbers.

Anyone have the same problem or a suggested solution to this problem?

Typo in styles.css

should be: visited:

This line:
p a:visted { color: #ff00ff; }

Upgrading Sheetsee on existing project & "Undefined" function

I've been using Sheetsee.js to publish a list of upcoming tourdates for my band. I noticed the the plugin was updated, and now I'm having trouble getting things to display properly.

Advance warning: I am not a coder (but rather a designer by trade). I am good at plugging in javascript when necessary and tweaking little bits here and there, but by no means do I understand the code itself.

In my particular use, I don't have Sheetsee output into a <table> but rather a bunch of divs. I suppose the example that would best match what I've done is the "pretty pennies" section of the table demo. I guess this uses elements of ICanHaz.js per the "tips!" page, but I don't particularly know how it's doing that.

I tried copying and pasting the demo code to work with my own stuff, but no luck. I'm getting an "undefined is not a function" error, which I don't know how to fix. Help?

You can view the code here:

http://no-other.org/shows/new.php

One final comment: Kudos on making Sheetsee easy enough to use that someone as clueless as myself could figure it out! :)

Add scatter plots

As an engineer/scientist, the vast majority of the charts I make are scatter plots.

Multiple Worksheets

Set it up to make this possible and document it (it's already possible with Tabletop.js).

custom marker icons in map

is it possible to use custom marker icons (not color or size, but whole image) while reading data from sheetsee.js? smth like this guy made here with markers loaded from url geoJSON: http://bl.ocks.org/geografa/8743008

Fix links in gh-pages

Some links in http://jlord.github.io/sheetsee.js/ are wrong: in the "Demos" section, in the table in the "Modules" section and in some text. Simply search for all href="/ in the index.html :)

Unexpected token

Hello, I'm getting an "Syntax error unexpected token '<'" at servicelogin. I've tried to use G spreadsheet with single sheets in case it was that.

Sheetsee To-do

I have a wee bit of time (and an imperative: Mozfest) to get going on some work I've been wanting to put into Sheetsee.

Modularize

Get these things on NPM and Browserify them together.

• sheetsee-core should provide basic functions for working with the data.
• sheetsee-maps should provide all the functions for setting up maps
• sheetsee-tables should provide all of the functions for making tables
• sheetsee-charts should provide all of the functions for the 3 basic d3 charts
• sheetsee-cache should get spreadsheet data and return it, add file writing/caching capabilities for offline development and skipping waiting on responses from Google. This is intended to work for server-side projects, on the command line and wouldn't not a part of the Browserify-ing stack.

For more advanced users, this allows you to build with just the parts of Sheetsee you intend to use (no need to have the stuff for charts if you're just mapping). For people who are newer, or don't care, you can still download the full (core + maps + tables + charts) source code from the repo and include it the same way you would any other library in your HTML head.

Will be digging into @remixz's PR (which is on this branch) since I think he covers a lot of this. YAY!

Enhancements

Good things.

• pagination for tables so they don't have to be so long (#12)
• polygons in maps instead of just points (#17)

BONUS Static Generator

Create a website that will generate the static code for you!

Niceties

Clean up and improve documentation, highlight Fork-n-Go examples in the wild and Tips-n-Bits.

---

Fingers crossed it ends up easier than expected :)
Thoughts on the outline are 😻

Support request re: 404 error - am I using the wrong Google doc key?

Hi,

Apologies for the n00b question, but hopefully you can help :)

Here is the repository where I am testing sheetsee:
https://github.com/radiocontrolled/gh-pages

When I open index.html, I am getting 404 error (testing in localhost):

NetworkError: 400 Bad Request - https://spreadsheets.google.com/feeds/list/1GcTwGGYmFxC63fDH8beLXXIjODd3yKL4Txn0XpOKnAU/csv/public/values?alt=json-in-script&sq=&callback=Tabletop.callbacks.tt139952295330739644

Do you know what I am doing wrong?

Thanks
Alison

License Type?

client-side offline development

at some point, while online, user exports their spreadsheet data as ___ and saves it into their directory. client-side sheetsee should check if browser is online, if not online, bypass tabletop method and run using the local data. it may not be up to date but should be a good sample set for which to develop the page with.

MajorP

Simplify license compliance

The chosen license mandates that all code distribution must indicate its license. However, if someone picks the needed .js from here to use in their project, they might not remember to edit it and add the needed license text.
This issue is a request for you to mitigate that by already including the license text on the beginning of the .js files.

Demo files are duplicated

Hi, the demo files inside /demo folder (at root) don't work.
(The ones inside /site work fine).

Just noticed there are working demo files inside /site/demo