23 ms·

Google Spreadsheets and Python

811 points by happy-go-lucky 10y ago

f_allwein 10y ago
I think there's a bit of a gap in the software ecosystem today in that there's no tool that lets semi-technical people like myself create simple applications, e.g. to read and write to databases. Bit like MS Access used to do. Google Spreadsheets actually goes a long way (I've done some stuff using the IF function etc. in it), but obviously has its limits. So combining it with Python sounds interesting. Not sure if it's what I'm looking for, but I'll check it out.
- tyingq 10y ago
  Quickbase does that, but the pricing model doesn't work well for all use cases. Edit: DabbleDB was awesome, and easier to learn than quickbase. But Twitter acquihired them and shut it down.
- simonw 10y ago
  Have you seen AirTable? It's definitely the best modern alternative to Access I've seen - easy to create tables, great web UI and a phenomenal iOS mobile app. https://www.airtable.com https://www.airtable.com
  - f_allwein 10y ago
    thanks, did not know this.
  - startupdiscuss 10y ago
    It is the fastest growing productivity app of the year (I read somewhere based on a metric I cannot recall). I didn't realize they had an API. I just looked it up and the documentation is so good it makes me tear up. The trick with the documentation is that it lets you pick one of your own databases and it then makes all the examples relevant to that database! Why doesn't everyone do that?
    - simonw 10y ago
      I'm actually a bit disappointed by their API as it stands at the moment. Say for example you want to retrieve a record with a bunch of nested records in one go. Currently you have to do that by pulling the first record, then looping through the array of nested IDs for the other records and retrieving each of them in turn. I want to be able to say "get me back record 56 and all of the linked records in a single request". I ended up having to iterate over their "fetch all records of type X" endpoints and then gluing the data back together in-memory, which is a pretty nasty workaround.
      - howsta 10y ago
        Cofounder of Airtable here, I hear you and that's on the roadmap :).
        phillc73 10y ago
        Could you spend a couple of minutes to describe the advantages of Airtable over Fieldbook or Ragic? I've been gripped by indecision regarding which tool to use, and hence use none of them.
        howsta 10y ago
        To be honest, I haven't spent a lot of time using Fieldbook or Ragic. That being said, we spend an insane amount of time/effort making sure that Airtable delivers a first-class product experience. In fact, the first couple years of Airtable's existence involved myself and my cofounder Andrew--who previously PMed the redesign of Google Maps--building countless prototype variants to get the UX right, doing a literal hundred user studies with people from all sorts of industries/roles to understand how the full range of how they used spreadsheets, databases, and other products, and researching the prior art by talking to the creators of past products in the space (i.e. MS Access, Excel, Coghead, Podio, Dabbledb, etc). We think Airtable is to spreadsheets/Access/Filemaker what Slack is to email (and fwiw, Slack itself uses Airtable http://bit.ly/2m58l4U http://bit.ly/2m58l4U ). Specifically, our product offers the following (which to my knowledge Ragic/Fieldbook do not): x IMO a much more intuitive design x Native Android, iPhone, & iPad apps, as well as an electron desktop app ( airtable.com/downloads ) x Ability to create multiple views on the same table, each of which preserves its own filter/sort/visibility settings Multiple view types, including grid, grouped records, calendar, kanban, public forms, and gallery: http://bit.ly/2ky3WLB http://bit.ly/2ky3WLB . x Inline collaboration i.e. @mentions in text fields, record-level comments x Many more useful field types x Visual revision history and snapshots x More integrations, such as with Zapier (see Zapier's writeups http://bit.ly/2lfGr79 http://bit.ly/2lfGr79 http://bit.ly/2l3dZqG http://bit.ly/2l3dZqG ), Slack, native calendars via an iCal feed, and Dropbox/Box/Gdrive/Evernote. x Greater capacity and smoother performance. x Full realtime sync for all changes, including schema modifications. x Lots of little things, like the ability to perform date calculations relative to today (i.e. a filtered view or formula that shows all projects due within 7 days from today and is automatically updated as time goes by), private share-links and embeds to give people access to a read-only view of Airtable without making them sign up for an account, inline document previews (e.g. view a text-copyable inline version of a .DOCX file) Over 30,000 organizations already use Airtable--this includes tech cos like Airbnb, Box, Wework, and Tesla; non-tech cos like Atlantic Records and Penguin Randomhouse; educational institutions like CMU, Rice, and Stanford. We're growing our team and continuously releasing new enhancements to improve the product experience for all users.
  - wffurr 10y ago
    Why are the row limits so tight?! 1200 rows for the free tier, and only 5000 rows for the first paid tier! That's crazy low, given that Google Sheets gives you 200,000 cells, or 10,000 20-column rows, for free. EDIT: That was from 2009. The current limit is 2,000,000 cells per sheet: https://support.google.com/drive/answer/37603?hl=en https://support.google.com/drive/answer/37603?hl=en
    - j_s 10y ago
      Probably because they are in business to make money? Customers whose first priority is cost usually aren't good customers.
      - rattray 10y ago
        Right, but even their more expensive paid plan has a 50k row limit, which is low enough to preclude whole classes of applications. They do claim a "custom number" of rows in their "contact us for pricing" plan, but the 50k number suggests that anything higher than that would have serious perf downsides with their current architecture.
        mthoms 10y ago
        I read somewhere that AirTable is deliberately only targeting "human scale" data use cases.
      - hueving 10y ago
        That's a daft statement. Every customer cares about cost. The only difference is the price points. If that saying meant anything meaningful, Walmart, Safeway, Ford, etc would not exist. I think what you meant to say was, "don't target customers not willing to pay enough for a profit margin." Much less catchy, but at least it's not bullshit.
        j_s 10y ago
        I said: "Customers whose first priority is cost" (emphasis added) You said: "Every customer cares about cost" There's a world of difference right there! Appreciate you taking the time to comment but starting with "That's a daft statement" and ending with "bullshit", throwing in "I think what you want to say" for good measure? I must have hit a sore spot!
  - vijayr 10y ago
    Is there a downloadable version of this, paid or free?
    - simonw 10y ago
      No, it's entirely cloud based. You can install their iOS and Android apps but they'll only work if you have an internet connection.
    - howsta 10y ago
      In addition to native mobile apps, we do have a desktop app. It does require internet connectivity though (similar to Slack's app, it's built on electron). Airtable.com/downloads
- buildbuildbuild 10y ago
  Salesforce often ends up filling this gap in organizations that can afford it, but often results in a technical debt of layman-designed schemas persisting long into production. (cleaning up or directly working with SF data is an expensive nightmare in my experience even post-Heroku acquisition) You are correct that a modern MS Access alternative would certainly find its market.
  - snuxoll 10y ago
    For simple needs Salesforce isn't even that expensive, we have around ~38 "App Cloud" licenses - that adds up to $950/mo to not deal with data scattered everywhere, not needing to waste my time designing a full CRUD app with security, managing a database server, etc. It helps to have an experienced Salesforce admin though, I love the "citizen developer" that Salesforce always preaches but you can run into a mess if you don't think your data model through like any other database. > cleaning up or directly working with SF data is an expensive nightmare in my experience even post-Heroku acquisition Plenty of decent sync products to make this not stink. We happily pay for DBAmp every year to keep all our data on-site so we can expose it to other users and to keep a backup of pruned data.
    - dzhiurgis 10y ago
      Salesforce sells a lot of "best practices" that sometimes aren't, but so many young developers(and even architects) buy into. You are leaving out storage costs. There's a minimum record size and then per licence billing, something like $250/GB/month. If anyone needs some info/consulting - PM me via email in profile.
      - snuxoll 10y ago
        Storage costs are certainly something to be weary of, and something I have always hone through great efforts to minimize. We have just over 5GB of total storage in our org and are only using ~45MB after a year because we planned out model around both storage efficiency and a sane data model. You don't use fully normalized database designs in Salesforce if you can avoid it.
- PhilipRawson 10y ago
  Perhaps Bubble is close to what you may be looking for: https://bubble.is https://bubble.is I know several people using this as a quick backend database solution since they make it so easy to interact with data and expose a GET and POST API.
- ghostly_s 10y ago
  Microsoft has a new suite of web-based tools called Sway, PowerApps, Flow and Dynamics365. I gather this are trying to meet this need...somewhere in there?
- arthurfm 10y ago
  I think there's a bit of a gap in the software ecosystem today in that there's no tool that lets semi-technical people like myself create simple applications Google App Maker? https://blog.google/products/g-suite/customize-your-g-suite-experience-app-maker-and-recommended-apps/ https://blog.google/products/g-suite/customize-your-g-suite-...
- dragonwriter 10y ago
  > I think there's a bit of a gap in the software ecosystem today in that there's no tool that lets semi-technical people like myself create simple applications, e.g. to read and write to databases. Bit like MS Access used to do. Isn't the tool that does what MS Access used to do, and that is available now, called "MS Access"?
- beojan 10y ago
  MS Access hasn't gone anywhere, why not use it?
rickcnagy 10y ago
Having run an (internal) app that is deeply integrated with Google Sheets for about a year, I think that Sheets is good and bad. It's _extremely_ powerful for allowing business users to easily get access to and manipulate data manually. Downside is that it really struggles past a few thousand rows when you've got more than 20 or so columns. Definitely good for a quick and dirty way to expose data to internal users, but it essentially can't scale. Engineers need to be ready to move off of it if the system works.
- iamdave 10y ago
  I've found that it also chokes when you throw Google Apps Scripts at it; or at least it did when I was developing a mail merge app for a Real Estate friend. By "chokes" I mean the application frequently locked up, cells would stop responding and I had to constantly reload the page, in a spreadsheet with only a couple of hundred rows, and maybe 12 columns.
- startupdiscuss 10y ago
  If anyone from Google is reading this: please add a database (yes, like a cloud version of MS ACCESS) in the Google Docs/Drive suite. Has anyone looked at "Fusion Tables?" A google search reveals that is what it is supposed to be.
  - bognition 10y ago
    They already have one its called CloudSQL https://cloud.google.com/sql/ https://cloud.google.com/sql/
    - skj 10y ago
      That's not part of the drive/docs suite.
      - pg_is_a_butt 10y ago
        there is no more drive/docs suite.... it's all "Google Cloud" now. you're all idiots.
mixmastamyk 10y ago
Interesting to choose this over simpler storage, such as sqlite. I suppose you get offsite backups for free.
- gregorymichael 10y ago
  (author here) Main advantage in our usecase is getting the CUD interface for free.
  - imcoconut 10y ago
    This is a big advantage. Especially for smaller personal projects. I'm tracking various things I eat and do each day (exercise, art, read, etc.) as well as how I'm feeling (energy levels, sleep quality, etc.) in google spreadsheet. Sheets is great because I can edit from any device anywhere I am. I like looking at simple things like time series' of what I'm doing, eating, and how I'm feeling and then exploring correlations, and regression betas/t-stats. Stuff that's just a little bit to onerous to do exclusively in sheets. At the moment, I have to download the spreadsheet then open with pandas in a jupyter notebook and then run everything. I'm definitely going to use the python api from now on - thanks a lot for writing this! Next step is a simple python-based web dashboard to view all the results - but keep sheets for CUD instead of having to build a custom one and use sql/sqlite. This really is super helpful - thanks again!
  - schwarrrtz 10y ago
    This is one of the reasons why I liked working with Parse; the data browser was very handy. This seems like a great alternative.
  - jwmoraes 10y ago
    For free ? Where would the python code run ?
    - andrewguenther 10y ago
      He said just the CUD part (Create Update Delete). The assumption is that your data can be entered manually through the Sheets UI and your code only needs to do the Read part.
xelathebela 10y ago
Potentially, putting the python function into a AWS Lambda function (or any other serverless function) + attaching an API gateway, you could make a RESTful endpoint for all the CRUD operators for google sheets. Could be a extremely light weight way of storing data and exposing it through REST :)
btown 10y ago
https://gspread.readthedocs.io/en/latest/ https://gspread.readthedocs.io/en/latest/ is great, but it's limited in what kinds of things it can set (formatting, notes, etc.). One other option is to create an endpoint on script.google.com which can access a much richer SpreadsheetApp API: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app https://developers.google.com/apps-script/reference/spreadsh... . You can POST to it using an auth token from the same service account oauth creds (though you need to add drive and drive.scripts to your scopes), and it can run arbitrary JS to translate reads and writes from the JSON payload/response into API calls. As another note, we realized that far more useful than using Google Spreadsheets as the canonical backing database, was to be able to bidirectionally synchronize it with our primary database. That way, users who wanted to annotate entities in spreadsheet form could do so in GSheets, always working with up-to-date data, and keeping track of "I updated a.x in the spreadsheet, but a.y was updated upstream, so merge the two." Here were the semantics of our integration: Returns a list of updates between last_synced_data and sheet. Subsequently, if upstream_data is provided, then load it into the sheet, adding rows on the end as needed, or merging if there is a match in the merge_key column (note that any updates to the live sheet data since the last sync override any upstream data, and those live updates are returned without changing the live sheet). The caller would then be responsible for taking the returned list of updates and cleaning it for the database, as well as maintaining a record of what the state of the last sync was. Essentially we maintain enough information to do a three-way merge. We've since built internal applications that allow real-time spreadsheet-like interactions in a much more domain-specific manner, but it definitely did the job for quite a while. If there's interest in seeing open-source code for all of this, we could definitely extract from our corporate repo (we're https://www.belstone.com/ https://www.belstone.com/ ). Let me know!
- brian_herman 10y ago
  Yes, please post to github, gitlab, or bitbucket!
- gregorymichael 10y ago
  (author here) Would love to see the code for this!
agildehaus 10y ago
Just because you can doesn't mean you should. You're putting the entire Internet between you and your "database". You'll receive all the latency and general reliability problems that go with doing that. In the v3 API, the first blank row terminated the column data set. Which meant you couldn't access rows after that blank row, which may have been accidentally inserted by a user. They may have addressed this in v4, not sure. You have almost no ability to restrict the values the user enters. They can be literally any string and your app has to handle every possibility. Restricting type on a Sheet is not really possible. What if a user is in the middle of editing the spreadsheet when your app attempts to access it? Since the sheet is constantly saving itself, and the user may not have completed their edits, are you getting incomplete or inaccurate data?
kleebeesh 10y ago
It's worth mentioning this service that turns Google Sheets into a RESTful API for you: https://sheetsu.com/ https://sheetsu.com/ Also sheetrock, which is JS-specific: http://chriszarate.github.io/sheetrock/ http://chriszarate.github.io/sheetrock/ I'm not the creator and have only used these for trivial tinkering.
ecesena 10y ago
I build HasGluten [1], fully backed by a spreadsheet. Code is at [2], unfortunately not very up to date... it was my first react project. [1] http://hagluten.com http://hagluten.com [2] https://github.com/hasgluten/hasgluten https://github.com/hasgluten/hasgluten
- marcc 10y ago
  You have a small typo in the link to your site. [1] should be http://hasgluten.com http://hasgluten.com
zachruss92 10y ago
This is super cool. I will probably use this in the future when sketching out ideas!
georgeaf99 10y ago
A friend who works at the Dallas Animal Shelter (a non-profit) asked me to help automate their process of generating reports from data gathered via Google Forms, which they were doing by hand every month... As a developer who is used to high technical investment into complex systems, I had to take a completely different approach to storing their information. Google Forms + Spreadsheet + Python works perfectly for use cases like this: it is free, scaling isn't a concern, users can view and modify data at any time, and users can create Google Forms at any time. If you are trying to help a non-profit and aren't planning on maintaining their systems long-term, this is a great approach. Code for the project: https://github.com/georgeaf99/das-care-contact-forms https://github.com/georgeaf99/das-care-contact-forms
- neeljpatel 10y ago
  Need to dump a SQL query into a spreadsheet? That's what I made the script below for. Runs now on a cron job and now I have a makeshift analytics platform :) http://pastebin.com/ACwqc5um http://pastebin.com/ACwqc5um
- fragmede 10y ago
  Really curious in the "low" technical investment answer: where/how does the Python end of this system get run?
  - jotux 10y ago
    >which they were doing by hand every month I would assume they just installed python on whatever machine they were originally using to hand-generate reports and the job of that person now is to just run the python script and wait for it to finish.
    - tyingq 10y ago
      Google's AppEngine would likely be free or extremely low cost in this situation. Gspread appears to work there fine: https://github.com/burnash/gspread/issues/39 https://github.com/burnash/gspread/issues/39
gmisra 10y ago
I'm also a big fan of this approach, especially for sharing data responsibilities with non-technical colleagues. If you work in nodejs, the node-google-spreadsheet library is pretty good: https://www.npmjs.com/package/google-spreadsheet https://www.npmjs.com/package/google-spreadsheet
- alexjv89 10y ago
  thanks.. this is useful.. tried it out and perfectly matches my needs.
NicoJuicy 10y ago
I have a CMS where the client adjusts their website in Google Spreadsheets. Easy and simple. Multiple auths and roles and it's cached, so it's not queried every minute ( pressing a link clears the cache)
- foxhound6 10y ago
  I use something similar, but not quite as involved to let a restaurant update their daily specials on their website.
tonylucas 10y ago
Worth also looking at Airtable here as well, becoming a big fan of it.
sasaram 10y ago
Theres one easy to use google spreadsheet api here built on gspread https://github.com/shkr/GoogleDocsAPI https://github.com/shkr/GoogleDocsAPI
Lightbody 10y ago
I love Google Sheets. It is so much more powerful than 99.9% of the users appreciate. Here is how I use it at my day job as head of PM at New Relic: - Fetch deals closed and lost hourly from Salesforce - Fetch for each of our 14k+ paid accounts usage metrics using our Insights product - Pull both items into a nice color-coded business dashboard that is near realtime - Send said dashboard out as a PDF to a bunch of stakeholders daily/weekly - Save PDF snapshot into Google Drive so I can easily pull up historical reports If you haven't tried out Google App Script and supercharged your spreadsheets, give it a shot. It's also basically the only way the engineer in me gets to code much these days, so I have fun with it too :) PS: Google Sheets is definitely much slower for pure spreadsheets than Excel, no question about that. But by using cloud-based JavaScript + custom code, I can often work around that issue by writing functions that process the data quickly and report on what I need. Ultimately I end up getting much more out of it than Excel.
- vegabook 10y ago
  Ever since I learned R and Python, I have dumped Excel. All my heavy lifting is done in a proper programming language, and for all the spreadsheet things that I need, Google Sheets is perfect. There's also something pretty magical about the sharing functionality when two people work on a document at the same time. Not to mention that all my sheets are a browser shortcut away, anywhere.
  - hjek 10y ago
    For a free software spreadsheet people can work on at the same time, there's EtherCalc: https://ethercalc.net/ https://ethercalc.net/
- machbio 10y ago
  This Blogger has lot of Google App Script that might be helpful for anyone interested in Google App Script - https://digitalinspiration.com/google-addons https://digitalinspiration.com/google-addons
  - VirgilShelton 10y ago
Entangled 10y ago
Exactly for cases like this I came up with DataText, a simple text format for tables with an editor just like an excel sheet. https://github.com/kuyawa/DataText https://github.com/kuyawa/DataText
rob-olmos 10y ago
Like another commenter said, the built-in Apps Script is pretty powerful as it is that it could probably handle requirements more easily and portably. For example, I've created a simple Apps Script for a Google Spreadsheets that uses OAuth to sync the list of client accounts from QuickBooks Online allowing us to keep track of more structured notes and segmentation, which QBO is still sorely lacking. The sync allows someone to easily update the spreadsheet with any new clients not in the spreadsheet. However, if I was going to be doing any in-depth calculations and/or a lot of data then I'd probably go the same route with using Python externally. Forgot to mention: The only thing I'm wary of, and have seen other comments in other threads about, is using it for anything more crucial like a core Excel/Access app because of the potential future change that'll break things.
samwillis 10y ago
We use google spreadsheets and gspead/Python for analysis of our AdWords. The AdWords api is a PITA and so we just have a sceduled export to a google spreadsheet that we can then easily access. Works very well!
- boulos 10y ago
  Have you tried exporting to BigQuery (https://developers.google.com/adwords/scripts/docs/solutions/bigquery-exporter https://developers.google.com/adwords/scripts/docs/solutions...) instead? Depending on your analysis (and analysts!) it's probably easier to write SQL. As a reminder, there's also a generous free-tier for BigQuery! Disclosure: I work on Google Cloud.
jonknee 10y ago
Google Forms got a big re-do in the fairly recent past and it's an excellent product. My partner uses it a lot and she calls the results her "apps" becaues they are saved to her homescreen on iOS. She has some pretty sophisticated forms going because there's a "Go to section based on answer" feature that lets you build conditions. If you haven't checked it out in a while it's worth a shot.
eldavido 10y ago
I'm using Python and Google Sheets to manage my wedding invitation list. Lob for cards, google contacts for contact data, gsheets is the main db. It works well.
stephen-mw 10y ago
I never published it, but I actually wrote a service that uses google sheets to manage SSH access on your servers. Users simply submit a google form with their SSH key, and it's automatically pulled by a daemon on each host. It comes complete with admin/sudo access toggling. https://github.com/stephen-mw/gdoc_ssh_manager https://github.com/stephen-mw/gdoc_ssh_manager
dy 10y ago
I went pretty far down the path of trying to build applications with Google Spreadsheets and Apps Scripts (we still have some major business processes running on it). It's definitely made me feel the need for something between Spreadsheets and custom development or Salesforce - something like what I imagine MS Access solved in the earlier days. Here's what it'd need: - Spreadsheet like UI - Validations and field data types (major liability of spreadsheets) - Good forms integration - Custom code like Apps Script capability - Something around workflow and business logic I think Airtable gets pretty close but I haven't been willing to switch cause of some limitations but I do like where they're going. I'm considering pulling the trigger for Salesforce but keep hearing that it's really easy to shoot yourself in the foot.
- boulos 10y ago
  Depending on what you're doing, App Maker (https://developers.google.com/appmaker/ https://developers.google.com/appmaker/) might be what you're after. Disclosure: I work on Google Cloud, but not App Maker.
adam-p 10y ago
I used Google Sheets as the DB backend for a membership management site for my local neighbourhood organization. So far it has worked great (for few users, but that was part of the spec). Here's the project, and there's more rationale/investigation in the README: https://github.com/adam-p/danforth-east https://github.com/adam-p/danforth-east (And... it looks like I need to update from Sheets API v3 to v4.)
deleted 10y ago
[deleted]
kawsper 10y ago
I would love if I could use Ruby in Excel to program styling and transform cells, I wonder why I haven't seen anything like that.
- mistermann 10y ago
  There's something about Excel, I think there are certain aspects of it (programmability, object model) that I think Microsoft is just never going to improve - not that they lack manpower, or that users don't desperately want improvements, it feels more like a matter of principle. My best guess is that they don't want it to be too good lest it cannibalize their other product lines; I would say the very exact thing was the downfall of MS Access. Of course this sounds like a silly conspiracy theory, but if you're very familiar with Excel, and especially the pile of smoldering crap that is VBA, I'd be surprised if you don't see at least a glimmer of truth. That google now has a modern, capable language that can be used with google sheets, perhaps now MS will actually wake up and begrudgingly give their faithful users something they've been asking for, for literally over a decade.
z3t4 10y ago
you could also store the data in a json file an let a static web app consume it
maciekmm 10y ago
I wrote about it a year ago as well: https://mionskowski.pl/using-google-apps-scripts-for-a-simple-backend https://mionskowski.pl/using-google-apps-scripts-for-a-simpl...
cyberpanther 10y ago
The big downside I've found using this a lot in the past is that Google has no SLA for sheets. Sure its Google, so it has high availability but things can quite often fail with no support from Google. So just be careful of building anything mission critical with this. The API can be wonky every once in a while. At one point it even took them like 6 months to fix an issue I had with the Apps Script API. If my business relied on that functionality, I would have been screwed. The Jupyter Notebook use case sounds great but if you move to something that is more critical use a real data store.
- victorhooi 10y ago
  Regarding your claim about SLAs - there actually is an SLA for all of the paid Google Cloud products - e.g. the following would apply for GSuite, which covers Google Sheets: https://support.google.com/work/answer/6056635?hl=en https://support.google.com/work/answer/6056635?hl=en https://gsuite.google.com/terms/reseller_sla.html https://gsuite.google.com/terms/reseller_sla.html If you mean the free products e.g. Google Sheets with a consumer account - AFAIK, there isn't a contractual SLA per-se, but the uptime is pretty good =). You can also see a status dashboard with historical status here: https://www.google.com/appsstatus#hl=en&v=status https://www.google.com/appsstatus#hl=en&v=status RSS feed link is at the bottom of that page. (Disclaimer: I work for Google Cloud, on the Drive/Docs/Sheets side of things).
  - cyberpanther 10y ago
    But does that cover the API and if devs break something during a release? That's where I see the problem often. New API gets released that breaks something but the broken thing doesn't get fixed quickly.
    - victorhooi 10y ago
      Yes, the SLA does cover the API - these fall under the main product. The status dashboard (https://www.google.com/appsstatus#hl=en&v=status https://www.google.com/appsstatus#hl=en&v=status) lists what's covered or not covered - table at the top is all covered services (e.g. GMail, Google Drives/Docs/Sheets/Slides, Google+, Google Groups, Hangouts, Calendar etc.) The table at the bottom lists things that aren't covered (e.g. Blogger, Google Voice, Google Analytics, Google Realtime API) by the SLA. I believe you can find the list of covered/non-covered service on the support site as well (https://support.google.com/ https://support.google.com/). And what do you mean by devs break something during the release? Do you mean if we introduce a bug in the code, and it affects the availability of the API? Yes, that is covered as well. You mention you've seen this before - I can't promise anything, but if you're hitting any issues here, or roadblocks, and you're a GSuite customer (i.e. you are paying us some kind of money, even if it's $5 a month), I can certainly try looking into it for you - ping me your details and I can reach out.
migoden 10y ago
nice
eob 10y ago
Cloudstitch founder here (YC S15). We wrap Google Sheets and Excel 365 in an API you can use to GET/PUT data along with a number of other goodies: - security policies (e.g., row-based auth) - file upload support - email triggers upon upload - a notion of "frozen" releases versus live dev data (released but yet undocumented -- email hello@ for details) Very happy to provide support for anyone looking to use us as an API into their sheets.
untilHellbanned 10y ago
Cool but it's a non-starter if you go beyond a few thousand rows.
dpweb 10y ago
Did a NodeJS read only Sheets recently no auth needed. Gets array of objects, way simple. https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300158dfcf6 https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300... https://github.com/digplan/gsheet-web https://github.com/digplan/gsheet-web
szopa 10y ago
Huh. I bet that in the Docs SRE team at Google there's a senior SRE who's having a "I felt a great disturbance in the Force moment." Google Sheets is awesome, people at Google are smart, but they are not capable of magic. In the case of Sheets, the most reasonable sharding scheme for the database backing it would be based on some collection of spreadsheets (in the extreme case it would be just one spreadsheet, but that probably wouldn't be practical). The important assumption here is that all the traffic pertaining to one spreadsheet will ultimately go to one server. So, if there's too much traffic coming related to one spreadsheet, you cannot really scale horizontally by adding more servers – you have to give it more resources. A single Google Sheet is sometimes used by lots of users at the same time, but they are able to limit it from the frontend (for example by making it degrade gracefully). If the traffic comes from the API, it's going to be a little bit more tricky – especially that IIRC the current rate limiting for the API uses a daily quota, leaving room for some really nice spikes. So, as soon as a website built using this hack get somehow popular someone is going to have a really rotten day with a lot of pages. (Of course, this is pure speculation on my side, I have no idea how Google Sheets is actually built – I would be very curious if there's a smart way of overcoming this sort of issue.)
- nl 10y ago
  One of the good things to come out of Google Wave was a better understanding of how to scale using (eg) operational transform. Even without that, scaling a mostly read only spreadsheet backend isn't as hard as you are making out.
  - szopa 10y ago
    Well, a spreadsheet used as intended is mostly read-only, that's no necessarily true if it's backing a website :)
    - nl 10y ago
      Based on the read-write characteristics of every website I've ever built I'd say "mostly read-only" is a pretty good characterization. Even things like forums have many, many page loads than new posts.
bredren 10y ago
I did a major integration with Google sheets API recently and found the documentation sorely lacking. Docs and Sheets could be so much better.
johnsmith21006 10y ago
Our school district has moved to Google sheets for kids grades and I am blown away how well it fits with this use case.
vgt 10y ago
Google Sheets fully integrates with Google BigQuery as well! [0] You can create a BigQuery table that's powered by a Google Sheet, or export data straight to Sheets for quick and dirty data wrangling. Some folks use Google Forms or Google Sheets to update ledgers, SKUs, or what have you, with data flowing straight to BigQuery for analysis against clickstream data, server logs, Google Analytics data, weather, etc. Love AppScript - so many possibilities for a "serverless" framework. How about ODBC? [0] https://cloud.google.com/blog/big-data/2016/05/bigquery-integrates-with-google-drive https://cloud.google.com/blog/big-data/2016/05/bigquery-inte... (worked on BigQuery, work in Google Cloud)
Keverw 10y ago
I remember seeing people in Second Life talking about using Google Spreadsheets as database using LSL so people didn't have to run external databases. Pretty interesting stuff even though I don't think it's really meant for this use case.
ristic 10y ago
I have built something similar that works with LibreOffice Calc as the backend instead of Google Sheets: https://github.com/robsco-git/spreadsheet_server https://github.com/robsco-git/spreadsheet_server
hackdroid 10y ago
With google spreadsheet appscript, i've built a dashboard with bootstrap, implemented a database with dopost and doget, downloaded 8000+ webpages for data scraping overnight. You can overcome the 5 minutes limit by using this http://patt0.blogspot.in/2014/08/continuous-batch-library-update-for.html http://patt0.blogspot.in/2014/08/continuous-batch-library-up...
nithinm 10y ago
For a port of gspread which uses API v4 - supports new features like formats, notes etc. Checkout https://github.com/nithinmurali/pygsheets https://github.com/nithinmurali/pygsheets
- relinqmost 10y ago
  how long the maintenance of this library will last after gspread moves to v4?
tjdalaska 10y ago
I am interested in the opposite use case where Google Spreadsheets/Google Apps Script calls an existing python script... possibly using Google Compute Engine? Anyone have any experience and/or suggestions? https://developers.google.com/apps-script/articles/appengine#section4 https://developers.google.com/apps-script/articles/appengine... is the closest thing I have found searching myself but it states that it is no longer maintained.
classybull 10y ago
I'm going to have to be a contrarian on this one. On my team, we absolutely loathe Google Spreadsheets with a passion. Not necessarily the product itself. It actually is a truly capable and nice spreadsheet program. However, it encourages some extremely bad uses. Because of the low friction of creating a spreadsheet versus, say, a database, business users have started using it as a substitute for something that should probably live in a database. For instance, I'm thinking of a particular portion of our sales organization that kept their business hierarchy in a Sheet, ie x person is on y team. Now, when you want to do analysis on the data you now have to use something similar to this library to retrieve the data and return it in a dataframe, which then pipes into the rest of your analysis and you end up with a finished product, be it a dashboard or some job. What always happens is that you write your analysis, set it up to run regularly, and all of the sudden, three weeks later, you start receiving exception emails on the script. When you investigate it, inevitably some jackass business user upstream altered the "schema" of the document, breaking your downstream analysis. In addition, once you have hundreds or thousands of these scripts running repeatedly, all reaching out to the Google API and in some cases retrieving 100s of MBs of data, Google very quickly rate limits you and you end up with dozens of scripts that broke. Sheets is great for analysis and one offs, but you have to push back on your business users who will constantly try to use it as an operations platform because they don't know any better. - "I need to see an analysis of xyz data." - "Ok, where does the data live?" - "Well, all of the facts come from this SQL table, and we pull from this web service, and finally we run it through this lookup table we maintain in Google Sheets." - "Nope. Come back to me when you put that data somewhere else. Spreadsheets are not databases."
rohanprabhu 10y ago
Back at my previous company, which was a travel startup, there was a list of curated vacation packages that were created for every weekend. So, the itineraries team used to spend 5 days designing the packages and getting vendors on board, and then they used to fill up a google sheet with the details in it and the devs used to run a script, which used to populate this rows in the database and make any other updates required and it used to show up on the website. The reasons why this worked so well for us: 1. There were at the most 30-40 rows. Google Spreadsheets works amazing for small table sizes (although not as small as 30-40, I'd assume upto 1500-5000 should be fine as well). 2. All non-dev teams were EXTREMELY comfortable with Google Spreadsheets. For people with non-engineering backgrounds, a spreadsheet is an amazing, low-barrier entry to structured data which I believe is what made this solution amazing. 3. The dev team was completely removed as a dependency, and we had staging environments where they would run it first to ensure it was working properly, so a dev could be engaged only if their spreadsheet run wasn't working as expected or due to some other issue. The previous method was a huge email sent out to the devs, who would handcraft it into a JSON, which would then be passed to a script and then written to the database. This required every run to be effectively final.. there were only so many times one could engage a dev. 4. While we had to engage a dev to execute the script, it actually is very easy to integrate a menu option within the google sheets interface itself (if you are using Google Apps) for your domain, which would say something like 'Deploy to Staging', although we never got around to actually building this. For startups where product turnaround time is required to be short, this works as an amazing solution as it makes so easy for non-tech guys to input data into the system. While there is no doubt that a fully developed panel for these operations would be the best solution, one doesn't always have the luxury of time.
stpe 10y ago
In a project I worked on the implementation of a very complex form (insurance company) with lots of conditions. Given the number of stakeholders and people involved having a say (legal, product owner, sales people, customer care, copywriter, etc) normal iteration (gathering feedback => implement => deploy => repeat) would simply take too long. I very successfully used Google Spreadheets as a backend for this. Putting all text and the conditional logic in the spreadsheet, and allowing the form to be built as a web page based on the Google Spreadsheet data for instant preview. This allowed the different members in the project to even work in parallel (thanks to collaboration features) with stakeholders - and implementing the feedback immediately themselves DURING the meeting - to see if the results was what they expected. I wrote a small blog post about this, unfortunately in Swedish, but hey - there's a video at least :) http://www.rebelandbird.com/hyperiterativ-prototypning-med-google-spreadsheets/ http://www.rebelandbird.com/hyperiterativ-prototypning-med-g... Another powerful way I use personally is to use Google Spreadsheet as a data backend for Jekyll based static websites. Here is a Grunt plugin I did to deal with this https://github.com/stpe/grunt-gss-to-json https://github.com/stpe/grunt-gss-to-json - example usage; my retro games collection http://games.stpe.se/ http://games.stpe.se/
SnowingXIV 10y ago
The problem with google sheets is row limits. Excel has them too but it's more. Eventually only manageable way to deal with larger datasets is in SQL and managing CSVs with bash to split.

811 points by happy-go-lucky 10y ago

I think there's a bit of a gap in the software ecosystem today in that there's no tool that lets semi-technical people like myself create simple applications, e.g. to read and write to databases. Bit like MS Access used to do. Google Spreadsheets actually goes a long way (I've done some stuff using the IF function etc. in it), but obviously has its limits. So combining it with Python sounds interesting. Not sure if it's what I'm looking for, but I'll check it out.

tyingq 10y ago
Quickbase does that, but the pricing model doesn't work well for all use cases. Edit: DabbleDB was awesome, and easier to learn than quickbase. But Twitter acquihired them and shut it down.
simonw 10y ago
Have you seen AirTable? It's definitely the best modern alternative to Access I've seen - easy to create tables, great web UI and a phenomenal iOS mobile app. https://www.airtable.com https://www.airtable.com
- f_allwein 10y ago
  thanks, did not know this.
- startupdiscuss 10y ago
  It is the fastest growing productivity app of the year (I read somewhere based on a metric I cannot recall). I didn't realize they had an API. I just looked it up and the documentation is so good it makes me tear up. The trick with the documentation is that it lets you pick one of your own databases and it then makes all the examples relevant to that database! Why doesn't everyone do that?
  - simonw 10y ago
    I'm actually a bit disappointed by their API as it stands at the moment. Say for example you want to retrieve a record with a bunch of nested records in one go. Currently you have to do that by pulling the first record, then looping through the array of nested IDs for the other records and retrieving each of them in turn. I want to be able to say "get me back record 56 and all of the linked records in a single request". I ended up having to iterate over their "fetch all records of type X" endpoints and then gluing the data back together in-memory, which is a pretty nasty workaround.
    - howsta 10y ago
      Cofounder of Airtable here, I hear you and that's on the roadmap :).
      - phillc73 10y ago
        Could you spend a couple of minutes to describe the advantages of Airtable over Fieldbook or Ragic? I've been gripped by indecision regarding which tool to use, and hence use none of them.
        howsta 10y ago
        To be honest, I haven't spent a lot of time using Fieldbook or Ragic. That being said, we spend an insane amount of time/effort making sure that Airtable delivers a first-class product experience. In fact, the first couple years of Airtable's existence involved myself and my cofounder Andrew--who previously PMed the redesign of Google Maps--building countless prototype variants to get the UX right, doing a literal hundred user studies with people from all sorts of industries/roles to understand how the full range of how they used spreadsheets, databases, and other products, and researching the prior art by talking to the creators of past products in the space (i.e. MS Access, Excel, Coghead, Podio, Dabbledb, etc). We think Airtable is to spreadsheets/Access/Filemaker what Slack is to email (and fwiw, Slack itself uses Airtable http://bit.ly/2m58l4U http://bit.ly/2m58l4U ). Specifically, our product offers the following (which to my knowledge Ragic/Fieldbook do not): x IMO a much more intuitive design x Native Android, iPhone, & iPad apps, as well as an electron desktop app ( airtable.com/downloads ) x Ability to create multiple views on the same table, each of which preserves its own filter/sort/visibility settings Multiple view types, including grid, grouped records, calendar, kanban, public forms, and gallery: http://bit.ly/2ky3WLB http://bit.ly/2ky3WLB . x Inline collaboration i.e. @mentions in text fields, record-level comments x Many more useful field types x Visual revision history and snapshots x More integrations, such as with Zapier (see Zapier's writeups http://bit.ly/2lfGr79 http://bit.ly/2lfGr79 http://bit.ly/2l3dZqG http://bit.ly/2l3dZqG ), Slack, native calendars via an iCal feed, and Dropbox/Box/Gdrive/Evernote. x Greater capacity and smoother performance. x Full realtime sync for all changes, including schema modifications. x Lots of little things, like the ability to perform date calculations relative to today (i.e. a filtered view or formula that shows all projects due within 7 days from today and is automatically updated as time goes by), private share-links and embeds to give people access to a read-only view of Airtable without making them sign up for an account, inline document previews (e.g. view a text-copyable inline version of a .DOCX file) Over 30,000 organizations already use Airtable--this includes tech cos like Airbnb, Box, Wework, and Tesla; non-tech cos like Atlantic Records and Penguin Randomhouse; educational institutions like CMU, Rice, and Stanford. We're growing our team and continuously releasing new enhancements to improve the product experience for all users.
- wffurr 10y ago
  Why are the row limits so tight?! 1200 rows for the free tier, and only 5000 rows for the first paid tier! That's crazy low, given that Google Sheets gives you 200,000 cells, or 10,000 20-column rows, for free. EDIT: That was from 2009. The current limit is 2,000,000 cells per sheet: https://support.google.com/drive/answer/37603?hl=en https://support.google.com/drive/answer/37603?hl=en
  - j_s 10y ago
    Probably because they are in business to make money? Customers whose first priority is cost usually aren't good customers.
    - rattray 10y ago
      Right, but even their more expensive paid plan has a 50k row limit, which is low enough to preclude whole classes of applications. They do claim a "custom number" of rows in their "contact us for pricing" plan, but the 50k number suggests that anything higher than that would have serious perf downsides with their current architecture.
      - mthoms 10y ago
        I read somewhere that AirTable is deliberately only targeting "human scale" data use cases.
    - hueving 10y ago
      That's a daft statement. Every customer cares about cost. The only difference is the price points. If that saying meant anything meaningful, Walmart, Safeway, Ford, etc would not exist. I think what you meant to say was, "don't target customers not willing to pay enough for a profit margin." Much less catchy, but at least it's not bullshit.
      - j_s 10y ago
        I said: "Customers whose first priority is cost" (emphasis added) You said: "Every customer cares about cost" There's a world of difference right there! Appreciate you taking the time to comment but starting with "That's a daft statement" and ending with "bullshit", throwing in "I think what you want to say" for good measure? I must have hit a sore spot!
- vijayr 10y ago
  Is there a downloadable version of this, paid or free?
  - simonw 10y ago
    No, it's entirely cloud based. You can install their iOS and Android apps but they'll only work if you have an internet connection.
  - howsta 10y ago
    In addition to native mobile apps, we do have a desktop app. It does require internet connectivity though (similar to Slack's app, it's built on electron). Airtable.com/downloads
buildbuildbuild 10y ago
Salesforce often ends up filling this gap in organizations that can afford it, but often results in a technical debt of layman-designed schemas persisting long into production. (cleaning up or directly working with SF data is an expensive nightmare in my experience even post-Heroku acquisition) You are correct that a modern MS Access alternative would certainly find its market.
- snuxoll 10y ago
  For simple needs Salesforce isn't even that expensive, we have around ~38 "App Cloud" licenses - that adds up to $950/mo to not deal with data scattered everywhere, not needing to waste my time designing a full CRUD app with security, managing a database server, etc. It helps to have an experienced Salesforce admin though, I love the "citizen developer" that Salesforce always preaches but you can run into a mess if you don't think your data model through like any other database. > cleaning up or directly working with SF data is an expensive nightmare in my experience even post-Heroku acquisition Plenty of decent sync products to make this not stink. We happily pay for DBAmp every year to keep all our data on-site so we can expose it to other users and to keep a backup of pruned data.
  - dzhiurgis 10y ago
    Salesforce sells a lot of "best practices" that sometimes aren't, but so many young developers(and even architects) buy into. You are leaving out storage costs. There's a minimum record size and then per licence billing, something like $250/GB/month. If anyone needs some info/consulting - PM me via email in profile.
    - snuxoll 10y ago
      Storage costs are certainly something to be weary of, and something I have always hone through great efforts to minimize. We have just over 5GB of total storage in our org and are only using ~45MB after a year because we planned out model around both storage efficiency and a sane data model. You don't use fully normalized database designs in Salesforce if you can avoid it.
PhilipRawson 10y ago
Perhaps Bubble is close to what you may be looking for: https://bubble.is https://bubble.is I know several people using this as a quick backend database solution since they make it so easy to interact with data and expose a GET and POST API.
ghostly_s 10y ago
Microsoft has a new suite of web-based tools called Sway, PowerApps, Flow and Dynamics365. I gather this are trying to meet this need...somewhere in there?
arthurfm 10y ago
I think there's a bit of a gap in the software ecosystem today in that there's no tool that lets semi-technical people like myself create simple applications Google App Maker? https://blog.google/products/g-suite/customize-your-g-suite-experience-app-maker-and-recommended-apps/ https://blog.google/products/g-suite/customize-your-g-suite-...
dragonwriter 10y ago
> I think there's a bit of a gap in the software ecosystem today in that there's no tool that lets semi-technical people like myself create simple applications, e.g. to read and write to databases. Bit like MS Access used to do. Isn't the tool that does what MS Access used to do, and that is available now, called "MS Access"?
beojan 10y ago
MS Access hasn't gone anywhere, why not use it?

rickcnagy 10y ago

Having run an (internal) app that is deeply integrated with Google Sheets for about a year, I think that Sheets is good and bad. It's _extremely_ powerful for allowing business users to easily get access to and manipulate data manually. Downside is that it really struggles past a few thousand rows when you've got more than 20 or so columns. Definitely good for a quick and dirty way to expose data to internal users, but it essentially can't scale. Engineers need to be ready to move off of it if the system works.

iamdave 10y ago
I've found that it also chokes when you throw Google Apps Scripts at it; or at least it did when I was developing a mail merge app for a Real Estate friend. By "chokes" I mean the application frequently locked up, cells would stop responding and I had to constantly reload the page, in a spreadsheet with only a couple of hundred rows, and maybe 12 columns.
startupdiscuss 10y ago
If anyone from Google is reading this: please add a database (yes, like a cloud version of MS ACCESS) in the Google Docs/Drive suite. Has anyone looked at "Fusion Tables?" A google search reveals that is what it is supposed to be.
- bognition 10y ago
  They already have one its called CloudSQL https://cloud.google.com/sql/ https://cloud.google.com/sql/
  - skj 10y ago
    That's not part of the drive/docs suite.
    - pg_is_a_butt 10y ago
      there is no more drive/docs suite.... it's all "Google Cloud" now. you're all idiots.

mixmastamyk 10y ago

Interesting to choose this over simpler storage, such as sqlite. I suppose you get offsite backups for free.

gregorymichael 10y ago
(author here) Main advantage in our usecase is getting the CUD interface for free.
- imcoconut 10y ago
  This is a big advantage. Especially for smaller personal projects. I'm tracking various things I eat and do each day (exercise, art, read, etc.) as well as how I'm feeling (energy levels, sleep quality, etc.) in google spreadsheet. Sheets is great because I can edit from any device anywhere I am. I like looking at simple things like time series' of what I'm doing, eating, and how I'm feeling and then exploring correlations, and regression betas/t-stats. Stuff that's just a little bit to onerous to do exclusively in sheets. At the moment, I have to download the spreadsheet then open with pandas in a jupyter notebook and then run everything. I'm definitely going to use the python api from now on - thanks a lot for writing this! Next step is a simple python-based web dashboard to view all the results - but keep sheets for CUD instead of having to build a custom one and use sql/sqlite. This really is super helpful - thanks again!
- schwarrrtz 10y ago
  This is one of the reasons why I liked working with Parse; the data browser was very handy. This seems like a great alternative.
- jwmoraes 10y ago
  For free ? Where would the python code run ?
  - andrewguenther 10y ago
    He said just the CUD part (Create Update Delete). The assumption is that your data can be entered manually through the Sheets UI and your code only needs to do the Read part.

xelathebela 10y ago

Potentially, putting the python function into a AWS Lambda function (or any other serverless function) + attaching an API gateway, you could make a RESTful endpoint for all the CRUD operators for google sheets. Could be a extremely light weight way of storing data and exposing it through REST :)

btown 10y ago

https://gspread.readthedocs.io/en/latest/ https://gspread.readthedocs.io/en/latest/ is great, but it's limited in what kinds of things it can set (formatting, notes, etc.). One other option is to create an endpoint on script.google.com which can access a much richer SpreadsheetApp API: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app https://developers.google.com/apps-script/reference/spreadsh... . You can POST to it using an auth token from the same service account oauth creds (though you need to add drive and drive.scripts to your scopes), and it can run arbitrary JS to translate reads and writes from the JSON payload/response into API calls. As another note, we realized that far more useful than using Google Spreadsheets as the canonical backing database, was to be able to bidirectionally synchronize it with our primary database. That way, users who wanted to annotate entities in spreadsheet form could do so in GSheets, always working with up-to-date data, and keeping track of "I updated a.x in the spreadsheet, but a.y was updated upstream, so merge the two." Here were the semantics of our integration: Returns a list of updates between last_synced_data and sheet. Subsequently, if upstream_data is provided, then load it into the sheet, adding rows on the end as needed, or merging if there is a match in the merge_key column (note that any updates to the live sheet data since the last sync override any upstream data, and those live updates are returned without changing the live sheet). The caller would then be responsible for taking the returned list of updates and cleaning it for the database, as well as maintaining a record of what the state of the last sync was. Essentially we maintain enough information to do a three-way merge. We've since built internal applications that allow real-time spreadsheet-like interactions in a much more domain-specific manner, but it definitely did the job for quite a while. If there's interest in seeing open-source code for all of this, we could definitely extract from our corporate repo (we're https://www.belstone.com/ https://www.belstone.com/ ). Let me know!

brian_herman 10y ago
Yes, please post to github, gitlab, or bitbucket!
gregorymichael 10y ago
(author here) Would love to see the code for this!

agildehaus 10y ago

Just because you can doesn't mean you should. You're putting the entire Internet between you and your "database". You'll receive all the latency and general reliability problems that go with doing that. In the v3 API, the first blank row terminated the column data set. Which meant you couldn't access rows after that blank row, which may have been accidentally inserted by a user. They may have addressed this in v4, not sure. You have almost no ability to restrict the values the user enters. They can be literally any string and your app has to handle every possibility. Restricting type on a Sheet is not really possible. What if a user is in the middle of editing the spreadsheet when your app attempts to access it? Since the sheet is constantly saving itself, and the user may not have completed their edits, are you getting incomplete or inaccurate data?

kleebeesh 10y ago

It's worth mentioning this service that turns Google Sheets into a RESTful API for you: https://sheetsu.com/ https://sheetsu.com/ Also sheetrock, which is JS-specific: http://chriszarate.github.io/sheetrock/ http://chriszarate.github.io/sheetrock/ I'm not the creator and have only used these for trivial tinkering.

ecesena 10y ago

I build HasGluten [1], fully backed by a spreadsheet. Code is at [2], unfortunately not very up to date... it was my first react project. [1] http://hagluten.com http://hagluten.com [2] https://github.com/hasgluten/hasgluten https://github.com/hasgluten/hasgluten

marcc 10y ago
You have a small typo in the link to your site. [1] should be http://hasgluten.com http://hasgluten.com

zachruss92 10y ago

This is super cool. I will probably use this in the future when sketching out ideas!

georgeaf99 10y ago

A friend who works at the Dallas Animal Shelter (a non-profit) asked me to help automate their process of generating reports from data gathered via Google Forms, which they were doing by hand every month... As a developer who is used to high technical investment into complex systems, I had to take a completely different approach to storing their information. Google Forms + Spreadsheet + Python works perfectly for use cases like this: it is free, scaling isn't a concern, users can view and modify data at any time, and users can create Google Forms at any time. If you are trying to help a non-profit and aren't planning on maintaining their systems long-term, this is a great approach. Code for the project: https://github.com/georgeaf99/das-care-contact-forms https://github.com/georgeaf99/das-care-contact-forms

neeljpatel 10y ago
Need to dump a SQL query into a spreadsheet? That's what I made the script below for. Runs now on a cron job and now I have a makeshift analytics platform :) http://pastebin.com/ACwqc5um http://pastebin.com/ACwqc5um
fragmede 10y ago
Really curious in the "low" technical investment answer: where/how does the Python end of this system get run?
- jotux 10y ago
  >which they were doing by hand every month I would assume they just installed python on whatever machine they were originally using to hand-generate reports and the job of that person now is to just run the python script and wait for it to finish.
  - tyingq 10y ago
    Google's AppEngine would likely be free or extremely low cost in this situation. Gspread appears to work there fine: https://github.com/burnash/gspread/issues/39 https://github.com/burnash/gspread/issues/39

gmisra 10y ago

I'm also a big fan of this approach, especially for sharing data responsibilities with non-technical colleagues. If you work in nodejs, the node-google-spreadsheet library is pretty good: https://www.npmjs.com/package/google-spreadsheet https://www.npmjs.com/package/google-spreadsheet

alexjv89 10y ago
thanks.. this is useful.. tried it out and perfectly matches my needs.

NicoJuicy 10y ago

I have a CMS where the client adjusts their website in Google Spreadsheets. Easy and simple. Multiple auths and roles and it's cached, so it's not queried every minute ( pressing a link clears the cache)

foxhound6 10y ago
I use something similar, but not quite as involved to let a restaurant update their daily specials on their website.

tonylucas 10y ago

Worth also looking at Airtable here as well, becoming a big fan of it.

sasaram 10y ago

Theres one easy to use google spreadsheet api here built on gspread https://github.com/shkr/GoogleDocsAPI https://github.com/shkr/GoogleDocsAPI

Lightbody 10y ago

I love Google Sheets. It is so much more powerful than 99.9% of the users appreciate. Here is how I use it at my day job as head of PM at New Relic: - Fetch deals closed and lost hourly from Salesforce - Fetch for each of our 14k+ paid accounts usage metrics using our Insights product - Pull both items into a nice color-coded business dashboard that is near realtime - Send said dashboard out as a PDF to a bunch of stakeholders daily/weekly - Save PDF snapshot into Google Drive so I can easily pull up historical reports If you haven't tried out Google App Script and supercharged your spreadsheets, give it a shot. It's also basically the only way the engineer in me gets to code much these days, so I have fun with it too :) PS: Google Sheets is definitely much slower for pure spreadsheets than Excel, no question about that. But by using cloud-based JavaScript + custom code, I can often work around that issue by writing functions that process the data quickly and report on what I need. Ultimately I end up getting much more out of it than Excel.

vegabook 10y ago
Ever since I learned R and Python, I have dumped Excel. All my heavy lifting is done in a proper programming language, and for all the spreadsheet things that I need, Google Sheets is perfect. There's also something pretty magical about the sharing functionality when two people work on a document at the same time. Not to mention that all my sheets are a browser shortcut away, anywhere.
- hjek 10y ago
  For a free software spreadsheet people can work on at the same time, there's EtherCalc: https://ethercalc.net/ https://ethercalc.net/
machbio 10y ago
This Blogger has lot of Google App Script that might be helpful for anyone interested in Google App Script - https://digitalinspiration.com/google-addons https://digitalinspiration.com/google-addons
- VirgilShelton 10y ago

Entangled 10y ago

Exactly for cases like this I came up with DataText, a simple text format for tables with an editor just like an excel sheet. https://github.com/kuyawa/DataText https://github.com/kuyawa/DataText

rob-olmos 10y ago

Like another commenter said, the built-in Apps Script is pretty powerful as it is that it could probably handle requirements more easily and portably. For example, I've created a simple Apps Script for a Google Spreadsheets that uses OAuth to sync the list of client accounts from QuickBooks Online allowing us to keep track of more structured notes and segmentation, which QBO is still sorely lacking. The sync allows someone to easily update the spreadsheet with any new clients not in the spreadsheet. However, if I was going to be doing any in-depth calculations and/or a lot of data then I'd probably go the same route with using Python externally. Forgot to mention: The only thing I'm wary of, and have seen other comments in other threads about, is using it for anything more crucial like a core Excel/Access app because of the potential future change that'll break things.

samwillis 10y ago

We use google spreadsheets and gspead/Python for analysis of our AdWords. The AdWords api is a PITA and so we just have a sceduled export to a google spreadsheet that we can then easily access. Works very well!

boulos 10y ago
Have you tried exporting to BigQuery (https://developers.google.com/adwords/scripts/docs/solutions/bigquery-exporter https://developers.google.com/adwords/scripts/docs/solutions...) instead? Depending on your analysis (and analysts!) it's probably easier to write SQL. As a reminder, there's also a generous free-tier for BigQuery! Disclosure: I work on Google Cloud.

jonknee 10y ago

Google Forms got a big re-do in the fairly recent past and it's an excellent product. My partner uses it a lot and she calls the results her "apps" becaues they are saved to her homescreen on iOS. She has some pretty sophisticated forms going because there's a "Go to section based on answer" feature that lets you build conditions. If you haven't checked it out in a while it's worth a shot.

eldavido 10y ago

I'm using Python and Google Sheets to manage my wedding invitation list. Lob for cards, google contacts for contact data, gsheets is the main db. It works well.

stephen-mw 10y ago

I never published it, but I actually wrote a service that uses google sheets to manage SSH access on your servers. Users simply submit a google form with their SSH key, and it's automatically pulled by a daemon on each host. It comes complete with admin/sudo access toggling. https://github.com/stephen-mw/gdoc_ssh_manager https://github.com/stephen-mw/gdoc_ssh_manager

dy 10y ago

I went pretty far down the path of trying to build applications with Google Spreadsheets and Apps Scripts (we still have some major business processes running on it). It's definitely made me feel the need for something between Spreadsheets and custom development or Salesforce - something like what I imagine MS Access solved in the earlier days. Here's what it'd need: - Spreadsheet like UI - Validations and field data types (major liability of spreadsheets) - Good forms integration - Custom code like Apps Script capability - Something around workflow and business logic I think Airtable gets pretty close but I haven't been willing to switch cause of some limitations but I do like where they're going. I'm considering pulling the trigger for Salesforce but keep hearing that it's really easy to shoot yourself in the foot.

boulos 10y ago
Depending on what you're doing, App Maker (https://developers.google.com/appmaker/ https://developers.google.com/appmaker/) might be what you're after. Disclosure: I work on Google Cloud, but not App Maker.

adam-p 10y ago

I used Google Sheets as the DB backend for a membership management site for my local neighbourhood organization. So far it has worked great (for few users, but that was part of the spec). Here's the project, and there's more rationale/investigation in the README: https://github.com/adam-p/danforth-east https://github.com/adam-p/danforth-east (And... it looks like I need to update from Sheets API v3 to v4.)

deleted 10y ago

[deleted]

kawsper 10y ago

I would love if I could use Ruby in Excel to program styling and transform cells, I wonder why I haven't seen anything like that.

mistermann 10y ago
There's something about Excel, I think there are certain aspects of it (programmability, object model) that I think Microsoft is just never going to improve - not that they lack manpower, or that users don't desperately want improvements, it feels more like a matter of principle. My best guess is that they don't want it to be too good lest it cannibalize their other product lines; I would say the very exact thing was the downfall of MS Access. Of course this sounds like a silly conspiracy theory, but if you're very familiar with Excel, and especially the pile of smoldering crap that is VBA, I'd be surprised if you don't see at least a glimmer of truth. That google now has a modern, capable language that can be used with google sheets, perhaps now MS will actually wake up and begrudgingly give their faithful users something they've been asking for, for literally over a decade.

z3t4 10y ago

you could also store the data in a json file an let a static web app consume it

maciekmm 10y ago

I wrote about it a year ago as well: https://mionskowski.pl/using-google-apps-scripts-for-a-simple-backend https://mionskowski.pl/using-google-apps-scripts-for-a-simpl...

cyberpanther 10y ago

The big downside I've found using this a lot in the past is that Google has no SLA for sheets. Sure its Google, so it has high availability but things can quite often fail with no support from Google. So just be careful of building anything mission critical with this. The API can be wonky every once in a while. At one point it even took them like 6 months to fix an issue I had with the Apps Script API. If my business relied on that functionality, I would have been screwed. The Jupyter Notebook use case sounds great but if you move to something that is more critical use a real data store.

victorhooi 10y ago
Regarding your claim about SLAs - there actually is an SLA for all of the paid Google Cloud products - e.g. the following would apply for GSuite, which covers Google Sheets: https://support.google.com/work/answer/6056635?hl=en https://support.google.com/work/answer/6056635?hl=en https://gsuite.google.com/terms/reseller_sla.html https://gsuite.google.com/terms/reseller_sla.html If you mean the free products e.g. Google Sheets with a consumer account - AFAIK, there isn't a contractual SLA per-se, but the uptime is pretty good =). You can also see a status dashboard with historical status here: https://www.google.com/appsstatus#hl=en&v=status https://www.google.com/appsstatus#hl=en&v=status RSS feed link is at the bottom of that page. (Disclaimer: I work for Google Cloud, on the Drive/Docs/Sheets side of things).
- cyberpanther 10y ago
  But does that cover the API and if devs break something during a release? That's where I see the problem often. New API gets released that breaks something but the broken thing doesn't get fixed quickly.
  - victorhooi 10y ago
    Yes, the SLA does cover the API - these fall under the main product. The status dashboard (https://www.google.com/appsstatus#hl=en&v=status https://www.google.com/appsstatus#hl=en&v=status) lists what's covered or not covered - table at the top is all covered services (e.g. GMail, Google Drives/Docs/Sheets/Slides, Google+, Google Groups, Hangouts, Calendar etc.) The table at the bottom lists things that aren't covered (e.g. Blogger, Google Voice, Google Analytics, Google Realtime API) by the SLA. I believe you can find the list of covered/non-covered service on the support site as well (https://support.google.com/ https://support.google.com/). And what do you mean by devs break something during the release? Do you mean if we introduce a bug in the code, and it affects the availability of the API? Yes, that is covered as well. You mention you've seen this before - I can't promise anything, but if you're hitting any issues here, or roadblocks, and you're a GSuite customer (i.e. you are paying us some kind of money, even if it's $5 a month), I can certainly try looking into it for you - ping me your details and I can reach out.

migoden 10y ago

nice

eob 10y ago

Cloudstitch founder here (YC S15). We wrap Google Sheets and Excel 365 in an API you can use to GET/PUT data along with a number of other goodies: - security policies (e.g., row-based auth) - file upload support - email triggers upon upload - a notion of "frozen" releases versus live dev data (released but yet undocumented -- email hello@ for details) Very happy to provide support for anyone looking to use us as an API into their sheets.

untilHellbanned 10y ago

Cool but it's a non-starter if you go beyond a few thousand rows.

dpweb 10y ago

Did a NodeJS read only Sheets recently no auth needed. Gets array of objects, way simple. https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300158dfcf6 https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300... https://github.com/digplan/gsheet-web https://github.com/digplan/gsheet-web

szopa 10y ago

Huh. I bet that in the Docs SRE team at Google there's a senior SRE who's having a "I felt a great disturbance in the Force moment." Google Sheets is awesome, people at Google are smart, but they are not capable of magic. In the case of Sheets, the most reasonable sharding scheme for the database backing it would be based on some collection of spreadsheets (in the extreme case it would be just one spreadsheet, but that probably wouldn't be practical). The important assumption here is that all the traffic pertaining to one spreadsheet will ultimately go to one server. So, if there's too much traffic coming related to one spreadsheet, you cannot really scale horizontally by adding more servers – you have to give it more resources. A single Google Sheet is sometimes used by lots of users at the same time, but they are able to limit it from the frontend (for example by making it degrade gracefully). If the traffic comes from the API, it's going to be a little bit more tricky – especially that IIRC the current rate limiting for the API uses a daily quota, leaving room for some really nice spikes. So, as soon as a website built using this hack get somehow popular someone is going to have a really rotten day with a lot of pages. (Of course, this is pure speculation on my side, I have no idea how Google Sheets is actually built – I would be very curious if there's a smart way of overcoming this sort of issue.)

nl 10y ago
One of the good things to come out of Google Wave was a better understanding of how to scale using (eg) operational transform. Even without that, scaling a mostly read only spreadsheet backend isn't as hard as you are making out.
- szopa 10y ago
  Well, a spreadsheet used as intended is mostly read-only, that's no necessarily true if it's backing a website :)
  - nl 10y ago
    Based on the read-write characteristics of every website I've ever built I'd say "mostly read-only" is a pretty good characterization. Even things like forums have many, many page loads than new posts.

bredren 10y ago

I did a major integration with Google sheets API recently and found the documentation sorely lacking. Docs and Sheets could be so much better.

johnsmith21006 10y ago

Our school district has moved to Google sheets for kids grades and I am blown away how well it fits with this use case.

vgt 10y ago

Google Sheets fully integrates with Google BigQuery as well! [0] You can create a BigQuery table that's powered by a Google Sheet, or export data straight to Sheets for quick and dirty data wrangling. Some folks use Google Forms or Google Sheets to update ledgers, SKUs, or what have you, with data flowing straight to BigQuery for analysis against clickstream data, server logs, Google Analytics data, weather, etc. Love AppScript - so many possibilities for a "serverless" framework. How about ODBC? [0] https://cloud.google.com/blog/big-data/2016/05/bigquery-integrates-with-google-drive https://cloud.google.com/blog/big-data/2016/05/bigquery-inte... (worked on BigQuery, work in Google Cloud)

Keverw 10y ago

I remember seeing people in Second Life talking about using Google Spreadsheets as database using LSL so people didn't have to run external databases. Pretty interesting stuff even though I don't think it's really meant for this use case.

ristic 10y ago

I have built something similar that works with LibreOffice Calc as the backend instead of Google Sheets: https://github.com/robsco-git/spreadsheet_server https://github.com/robsco-git/spreadsheet_server

hackdroid 10y ago

With google spreadsheet appscript, i've built a dashboard with bootstrap, implemented a database with dopost and doget, downloaded 8000+ webpages for data scraping overnight. You can overcome the 5 minutes limit by using this http://patt0.blogspot.in/2014/08/continuous-batch-library-update-for.html http://patt0.blogspot.in/2014/08/continuous-batch-library-up...

nithinm 10y ago

For a port of gspread which uses API v4 - supports new features like formats, notes etc. Checkout https://github.com/nithinmurali/pygsheets https://github.com/nithinmurali/pygsheets

relinqmost 10y ago
how long the maintenance of this library will last after gspread moves to v4?

tjdalaska 10y ago

I am interested in the opposite use case where Google Spreadsheets/Google Apps Script calls an existing python script... possibly using Google Compute Engine? Anyone have any experience and/or suggestions? https://developers.google.com/apps-script/articles/appengine#section4 https://developers.google.com/apps-script/articles/appengine... is the closest thing I have found searching myself but it states that it is no longer maintained.

classybull 10y ago

I'm going to have to be a contrarian on this one. On my team, we absolutely loathe Google Spreadsheets with a passion. Not necessarily the product itself. It actually is a truly capable and nice spreadsheet program. However, it encourages some extremely bad uses. Because of the low friction of creating a spreadsheet versus, say, a database, business users have started using it as a substitute for something that should probably live in a database. For instance, I'm thinking of a particular portion of our sales organization that kept their business hierarchy in a Sheet, ie x person is on y team. Now, when you want to do analysis on the data you now have to use something similar to this library to retrieve the data and return it in a dataframe, which then pipes into the rest of your analysis and you end up with a finished product, be it a dashboard or some job. What always happens is that you write your analysis, set it up to run regularly, and all of the sudden, three weeks later, you start receiving exception emails on the script. When you investigate it, inevitably some jackass business user upstream altered the "schema" of the document, breaking your downstream analysis. In addition, once you have hundreds or thousands of these scripts running repeatedly, all reaching out to the Google API and in some cases retrieving 100s of MBs of data, Google very quickly rate limits you and you end up with dozens of scripts that broke. Sheets is great for analysis and one offs, but you have to push back on your business users who will constantly try to use it as an operations platform because they don't know any better. - "I need to see an analysis of xyz data." - "Ok, where does the data live?" - "Well, all of the facts come from this SQL table, and we pull from this web service, and finally we run it through this lookup table we maintain in Google Sheets." - "Nope. Come back to me when you put that data somewhere else. Spreadsheets are not databases."

rohanprabhu 10y ago

Back at my previous company, which was a travel startup, there was a list of curated vacation packages that were created for every weekend. So, the itineraries team used to spend 5 days designing the packages and getting vendors on board, and then they used to fill up a google sheet with the details in it and the devs used to run a script, which used to populate this rows in the database and make any other updates required and it used to show up on the website. The reasons why this worked so well for us: 1. There were at the most 30-40 rows. Google Spreadsheets works amazing for small table sizes (although not as small as 30-40, I'd assume upto 1500-5000 should be fine as well). 2. All non-dev teams were EXTREMELY comfortable with Google Spreadsheets. For people with non-engineering backgrounds, a spreadsheet is an amazing, low-barrier entry to structured data which I believe is what made this solution amazing. 3. The dev team was completely removed as a dependency, and we had staging environments where they would run it first to ensure it was working properly, so a dev could be engaged only if their spreadsheet run wasn't working as expected or due to some other issue. The previous method was a huge email sent out to the devs, who would handcraft it into a JSON, which would then be passed to a script and then written to the database. This required every run to be effectively final.. there were only so many times one could engage a dev. 4. While we had to engage a dev to execute the script, it actually is very easy to integrate a menu option within the google sheets interface itself (if you are using Google Apps) for your domain, which would say something like 'Deploy to Staging', although we never got around to actually building this. For startups where product turnaround time is required to be short, this works as an amazing solution as it makes so easy for non-tech guys to input data into the system. While there is no doubt that a fully developed panel for these operations would be the best solution, one doesn't always have the luxury of time.

stpe 10y ago

In a project I worked on the implementation of a very complex form (insurance company) with lots of conditions. Given the number of stakeholders and people involved having a say (legal, product owner, sales people, customer care, copywriter, etc) normal iteration (gathering feedback => implement => deploy => repeat) would simply take too long. I very successfully used Google Spreadheets as a backend for this. Putting all text and the conditional logic in the spreadsheet, and allowing the form to be built as a web page based on the Google Spreadsheet data for instant preview. This allowed the different members in the project to even work in parallel (thanks to collaboration features) with stakeholders - and implementing the feedback immediately themselves DURING the meeting - to see if the results was what they expected. I wrote a small blog post about this, unfortunately in Swedish, but hey - there's a video at least :) http://www.rebelandbird.com/hyperiterativ-prototypning-med-google-spreadsheets/ http://www.rebelandbird.com/hyperiterativ-prototypning-med-g... Another powerful way I use personally is to use Google Spreadsheet as a data backend for Jekyll based static websites. Here is a Grunt plugin I did to deal with this https://github.com/stpe/grunt-gss-to-json https://github.com/stpe/grunt-gss-to-json - example usage; my retro games collection http://games.stpe.se/ http://games.stpe.se/

SnowingXIV 10y ago

The problem with google sheets is row limits. Excel has them too but it's more. Eventually only manageable way to deal with larger datasets is in SQL and managing CSVs with bash to split.