5 ms·

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

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.

rafaquintanilha 11y ago
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.
bobfunk 11y ago
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 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 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]
- michaeloblak 11y ago
  That's a great use case!
- wale 11y ago
  Hey @bobfunk how do i contact you concerning your app - netlify. Really great use case
  - bobfunk 11y ago
    You can shoot me a mail at matt@netlify.com
deleted 11y ago
[deleted]
michaelbuckbee 11y ago
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 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
ForrestN 11y ago
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?
franciscop 11y ago
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 https://github.com/franciscop/drive-db [2] http://www.makersupv.com/ http://www.makersupv.com/
tmarthal 11y ago
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 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).
fenomas 11y ago
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?
RachelF 11y ago
We had a similar experience. Google started giving us "Sharing quota for this file has been exceeded" errors.

rafaquintanilha 11y ago

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.

bobfunk 11y ago

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 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 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]

michaeloblak 11y ago
That's a great use case!
wale 11y ago
Hey @bobfunk how do i contact you concerning your app - netlify. Really great use case
- bobfunk 11y ago
  You can shoot me a mail at matt@netlify.com

deleted 11y ago

[deleted]

michaelbuckbee 11y ago

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 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

ForrestN 11y ago

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?

franciscop 11y ago

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 https://github.com/franciscop/drive-db [2] http://www.makersupv.com/ http://www.makersupv.com/

tmarthal 11y ago

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 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).

fenomas 11y ago

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?

RachelF 11y ago

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