Hacker News Reader

Comment by rubbingalcohol | Hacker News Reader

I've used Google Spreadsheet as an "API" before (manually, not using this wrapper), and it is definitely not made to be used as such. Under heavy load, it slows down or times out, and even under the best circumstances it's not exactly fast.

It's great in a pinch. My use-case was a spreadsheet that others in my organization wanted to edit, then we'd pull the data onto a web page and display nicely. It worked, but eventually we had to set up a secondary server to cache the spreadsheet result because pulling directly from Google in realtime was too unreliable.

View on HN

One thing you can do to make this use-case work better is to combine the spreadsheet as API with a static site generator.

I made this very basic plugin for Jekyll:

https://github.com/netlify/jekyll-gdrive

That'll let you use a Google spreadsheet as a datasource and expose the data to your liquid templates.

Combine it with something like https://www.netlify.com that'll run the builds for you and let you trigger builds via webhooks, and you have a Google spreadsheet based publishing engine where the final sites are completely static and live straight on a CDN.

Used it for one site where we used the scripting options of Google spreadsheets to add a "Publish Now" image button to the sheet, so in the end user could edit the the sheet, and then press "Publish" to trigger a build+deploy.

If the users are already used to spredsheets, it can be a really handy little anti CMS :)

[disclaimer: I'm a founder of netlify]

That's a great use case!

Hey @bobfunk how do i contact you concerning your app - netlify.

Really great use case

You can shoot me a mail at matt@netlify.com

Admittedly naive question: if the advantage of having Google Sheets store the data is that you can use the Sheets editor when you want to, could something like Sheetsu store the data separately, much faster, for the API, and then just sync any changes between the Google Spreadsheet and their data? So you could still access via Google when needed, but these problems don't matter?

I made a node.js package that does exactly this, a local cache in json with few hooks for data modification, and you can control when to update it to avoid timeouts [1]

Then we used it as backens for a blog for my University team as we were already using Drive, being each entry a google docs document [2]

[1] https://github.com/franciscop/drive-db [2] http://www.makersupv.com/

Thanks for sharing. It's clear this approach works for small datasets and if you want something more reliable under heavy load just go for a proper database. Might work well with prototypes though.

I also implemented something like this very recently [1] and I would recommend building in caching from the start as I was consistently getting throttled by the Google API even in development (just live reloading the page as I styled it).

Secondly, while the load page and then load the data in as JS approach definitely works, I found that just pushing the data in as HTML on the server side was easier to cache and faster to load.

1 - https://gist.github.com/mbuckbee/0ad3bd150e705c769c50

This was sufficient to handle thousands of requests per hour with a page load time of less than 250ms

There are a bunch of 'fork-n-go' github jekyl sample applications that wrap a webpage with a Google Spreadsheet datasource.

Here is a sample (from a github employee) that I've used in the past. Source: https://github.com/jlord/hack-spots and live site: jlord.us/hack-spots/#info - not sure how they are caching the spreadsheet, but I've never seen it not perform (not sure about concurrent users or whatever though).

I've been looking at workflows like this, but the problem is being completely cut off from editing or testing locally when offline.

That is, it would be great to keep my data in a google sheet and transform it into a static json file that lives in my app source, but then if I need to work offline, all I can do is edit the json and remember to reflect the changes into the sheet later.

I don't suppose anyone has found a workflow that solves this?

We had a similar experience. Google started giving us "Sharing quota for this file has been exceeded" errors.

Reply on news.ycombinator.com