Google Spreadsheets and Python(twilio.com)
twilio.com
Google Spreadsheets and Python
https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html
142 comments
This Blogger has lot of Google App Script that might be helpful for anyone interested in Google App Script - https://digitalinspiration.com/google-addons
Thank you for this, amazing!
For all the non-programmers out there that are comfortable with spreadsheets, this site - http://codingisforlosers.com/ -is pretty epic for building out a ton of dashboards and data pipelines with Google Sheets.
You might like a small library I made then, it's really similar to Twillio one but in Node.js but a lot easier to set-up (read-only):
- https://github.com/franciscop/drive-db
- https://github.com/franciscop/drive-db
I get all the good things Google Apps do. It's wonderful. Yet, until Google has a "contract with our users" that includes a level of customer service and human conflict resolution I would not touch them with a ten foot pole.
Why? Because they could take it all away --and I do mean ALL-- overnight. Email, apps, etc.
I've seen many people run into the Google ban for reasons not more complicated than not being experts and using one of Google's tools in a way that triggers an algorithmic ban. And this doesn't have to have anything to do with fraud or scams.
Had one client, probably ten years ago, who moved all of his 200+ domains to a service Google introduced at the time called "Google for Domains". A domain parking service with ads inserted into the parked domains auto-magically by Google. Prior to that all of his domains were parked at GoDaddy, who used Google to stuff ads into the domains. As soon as the service was offered he figured it was a good idea to remove GoDaddy as the middle-man in that equation.
He transferred all of his domains. There was an approval process. All domains were approved overnight. Done deal. Right?
Wrong! Two days later Google sent him an email informing him that all of his Google accounts were now permanently suspended (aka: closed) with no recourse. Reason given: Unusual click activity on some of the domains parked with Google for Domains.
This guy ran (runs) a multi-million dollar manufacturing business. The very idea that he would sit there clicking on ads to earn a quarter of a cent per click is, well, stupid.
Anyhow, this one event cost him all of his Google tools. His email, calendar, contacts, apps and whatever else he was using at the time. No recourse. Not conversation with a human being. Nothing. Done. Puff. Evaporated.
Since that one event I have been of the opinion that using any of these Google tools for a business is, to put it plainly, stupid and dangerous. You could be betting the farm on a petulant algorithm and no customer service of any kind to protect you from it.
I still have Excel files from decades ago that I can open and manipulate today. Microsoft could go out of business and I could still open these files. And the software has never lacked functionality, either directly, through add-ins or programmability.
The bottom line for me is that if something is business-critical it isn't a good idea to rely on a service that could be yanked overnight (I do mean that literally). If Google had customer service and a human (and humane) process to deal with issues it would be a very different story. I don't want free stuff. We have tens of thousands of dollars invested in all kinds of software. I want stuff I can rely on because my businesses depend on it.
The only way you are going to have to security is if you pay for well supported software that has a team behind it who understand how important it is for a business to have the ability to wake up every morning knowing that the tools they come to rely on for their daily work won't evaporate overnight.
I truly don't care how many shinny new things, candy and chocolate Google throws on the table. Until they prove they understand this one point they represent a business-killing risk nobody should be willing to accept.
BTW, this is a problem with nearly all Internet giants. Facebook, Amazon and others are horrible companies from the standpoint of how they deal with their business customers.
Why? Because they could take it all away --and I do mean ALL-- overnight. Email, apps, etc.
I've seen many people run into the Google ban for reasons not more complicated than not being experts and using one of Google's tools in a way that triggers an algorithmic ban. And this doesn't have to have anything to do with fraud or scams.
Had one client, probably ten years ago, who moved all of his 200+ domains to a service Google introduced at the time called "Google for Domains". A domain parking service with ads inserted into the parked domains auto-magically by Google. Prior to that all of his domains were parked at GoDaddy, who used Google to stuff ads into the domains. As soon as the service was offered he figured it was a good idea to remove GoDaddy as the middle-man in that equation.
He transferred all of his domains. There was an approval process. All domains were approved overnight. Done deal. Right?
Wrong! Two days later Google sent him an email informing him that all of his Google accounts were now permanently suspended (aka: closed) with no recourse. Reason given: Unusual click activity on some of the domains parked with Google for Domains.
This guy ran (runs) a multi-million dollar manufacturing business. The very idea that he would sit there clicking on ads to earn a quarter of a cent per click is, well, stupid.
Anyhow, this one event cost him all of his Google tools. His email, calendar, contacts, apps and whatever else he was using at the time. No recourse. Not conversation with a human being. Nothing. Done. Puff. Evaporated.
Since that one event I have been of the opinion that using any of these Google tools for a business is, to put it plainly, stupid and dangerous. You could be betting the farm on a petulant algorithm and no customer service of any kind to protect you from it.
I still have Excel files from decades ago that I can open and manipulate today. Microsoft could go out of business and I could still open these files. And the software has never lacked functionality, either directly, through add-ins or programmability.
The bottom line for me is that if something is business-critical it isn't a good idea to rely on a service that could be yanked overnight (I do mean that literally). If Google had customer service and a human (and humane) process to deal with issues it would be a very different story. I don't want free stuff. We have tens of thousands of dollars invested in all kinds of software. I want stuff I can rely on because my businesses depend on it.
The only way you are going to have to security is if you pay for well supported software that has a team behind it who understand how important it is for a business to have the ability to wake up every morning knowing that the tools they come to rely on for their daily work won't evaporate overnight.
I truly don't care how many shinny new things, candy and chocolate Google throws on the table. Until they prove they understand this one point they represent a business-killing risk nobody should be willing to accept.
BTW, this is a problem with nearly all Internet giants. Facebook, Amazon and others are horrible companies from the standpoint of how they deal with their business customers.
The experience described is no longer relevant. I've gotten (phone!) calls from Google within minutes of posting for help, and I live in a tiny nation in the middle of the Pacific Ocean.
In my experience Google provides amazing service for tools that you are paying for. If you're using Google Suite/Apps to access your sheets, you should be just fine.
> I want stuff I can rely on because my businesses depend on it.
Well we're talking about using Google spreadsheets as a backend for an app. It should go without saying that this wouldn't be a mission-critical use.
In my experience Google provides amazing service for tools that you are paying for. If you're using Google Suite/Apps to access your sheets, you should be just fine.
> I want stuff I can rely on because my businesses depend on it.
Well we're talking about using Google spreadsheets as a backend for an app. It should go without saying that this wouldn't be a mission-critical use.
In terms of your anecdote about your client's experience - I can't speak to this, as I'm not directly familiar with this "Google for Domains" service and I don't have enough details from your post to comment.
However, I can assure you that Google definitely has a customer support department - I work with them every day =).
Also - it's important to distinguish between the free products we offer - and the paid products - where you actually give us money. In the latter case, there is nearly always a customer support hotline, and in many cases, a binding SLA agreement.
I work in Google Cloud, on the support organisation side of things - and I can confirm there is definitely phone support (along with chat and email), we do have SLAs on all of our products and you are definitely speaking to a human. It's not particularly hard to reach a person - either go through your admin panel (https://admin.google.com - click the big question mark), or all of our 24/7 support numbers for each country are right there on the website:
https://support.google.com/a/table/3247295?hl=en
That's on the business side. On the consumer side, the phone numbers shouldn't be too hard to find either on a search e.g.:
https://support.google.com/googleplay/answer/7100415?hl=en&r...
My own anecdote - several years ago before I started with Google, I subscribed to Google Play Music - I accidentally un-favourited a track once, and I couldn't find a way to find it again. So on a whim I rang Google Play Music support, and went straight to a person within seconds. She was very friendly, and tried to help me find the history - unfortunately it wasn't there, but the customer service was pretty damn good - a heck of a lot better than all of my experiences with several un-named mobile phone and broadband companies. (Hint: I'm in Australia)
Disclaimer: I work for Google.
However, I can assure you that Google definitely has a customer support department - I work with them every day =).
Also - it's important to distinguish between the free products we offer - and the paid products - where you actually give us money. In the latter case, there is nearly always a customer support hotline, and in many cases, a binding SLA agreement.
I work in Google Cloud, on the support organisation side of things - and I can confirm there is definitely phone support (along with chat and email), we do have SLAs on all of our products and you are definitely speaking to a human. It's not particularly hard to reach a person - either go through your admin panel (https://admin.google.com - click the big question mark), or all of our 24/7 support numbers for each country are right there on the website:
https://support.google.com/a/table/3247295?hl=en
That's on the business side. On the consumer side, the phone numbers shouldn't be too hard to find either on a search e.g.:
https://support.google.com/googleplay/answer/7100415?hl=en&r...
My own anecdote - several years ago before I started with Google, I subscribed to Google Play Music - I accidentally un-favourited a track once, and I couldn't find a way to find it again. So on a whim I rang Google Play Music support, and went straight to a person within seconds. She was very friendly, and tried to help me find the history - unfortunately it wasn't there, but the customer service was pretty damn good - a heck of a lot better than all of my experiences with several un-named mobile phone and broadband companies. (Hint: I'm in Australia)
Disclaimer: I work for Google.
You might not know the answer to this question. If you don't, it might be interesting to see if you can get to the answer internally.
Let's say I setup a site and have AdSense place ads on it. I don't touch any of the ads. I never click any of them. Yet, for some reason, the ads see a bunch of click activity. The reasons can be many, from sheer randomness to a competitor using it as an attack vector (to deny revenue and more).
I have seen this precise scenario lead to the absolute shutdown of a Google account. And that means every single service, including email and docs. Gone. Done. No recourse. No way to even recover the data.
Has this changed?
Do you have a way for people affected by such algorithmic issues to engage in a conversation with something that does not resemble a totalitarian regime?
The paid vs. free part is immaterial. People are relying on your products and offerings. Free is a strategy for dominance. I can understand free products not having a human in the loop. What I can't understand is the dictatorial/totalitarian relationship with your users, who, free or not, have come to rely on these tools.
Let's say I setup a site and have AdSense place ads on it. I don't touch any of the ads. I never click any of them. Yet, for some reason, the ads see a bunch of click activity. The reasons can be many, from sheer randomness to a competitor using it as an attack vector (to deny revenue and more).
I have seen this precise scenario lead to the absolute shutdown of a Google account. And that means every single service, including email and docs. Gone. Done. No recourse. No way to even recover the data.
Has this changed?
Do you have a way for people affected by such algorithmic issues to engage in a conversation with something that does not resemble a totalitarian regime?
The paid vs. free part is immaterial. People are relying on your products and offerings. Free is a strategy for dominance. I can understand free products not having a human in the loop. What I can't understand is the dictatorial/totalitarian relationship with your users, who, free or not, have come to rely on these tools.
I believe you're referring her to Adwords clickfraud - is that right? I don't work on that team, so can't comment on their policies (and even if I did, I probably can't comment publicly).
However, my understanding is normally AdWords accounts are suspended for billing/fraud related reasons (e.g. you don't pay your bills) - e.g.:
https://support.google.com/adwords/answer/2375414?hl=en-AU
If you AdWords account does get suspended, there's a form to appeal it here:
https://support.google.com/adwords/contact/pf_suspended
I haven't seen it lead immediately to the actual Google login itself being suspended. However, if your the actual Google account was suspended - there is a set procedure you can follow to un-suspend it - there should have been a link in the notification email.
Also - paid or free isn't immaterial - it actually does make a difference.
If it's a free account (E.g. @gmail.com), you can fill in the online forms to appeal the suspension.
If it's a paid account (i.e. part of a GSuite domain - or it's old name, Google for Work), then what often happens are individual accounts are suspended, and you talk to your own domain administrator to get that resolved. There are ways to get a domain suspended (e.g. you don't pay your bills) but they're usually fairly obvious.
(Disclaimer: I work for Google, but the above comments are my own opinions).
However, my understanding is normally AdWords accounts are suspended for billing/fraud related reasons (e.g. you don't pay your bills) - e.g.:
https://support.google.com/adwords/answer/2375414?hl=en-AU
If you AdWords account does get suspended, there's a form to appeal it here:
https://support.google.com/adwords/contact/pf_suspended
I haven't seen it lead immediately to the actual Google login itself being suspended. However, if your the actual Google account was suspended - there is a set procedure you can follow to un-suspend it - there should have been a link in the notification email.
Also - paid or free isn't immaterial - it actually does make a difference.
If it's a free account (E.g. @gmail.com), you can fill in the online forms to appeal the suspension.
If it's a paid account (i.e. part of a GSuite domain - or it's old name, Google for Work), then what often happens are individual accounts are suspended, and you talk to your own domain administrator to get that resolved. There are ways to get a domain suspended (e.g. you don't pay your bills) but they're usually fairly obvious.
(Disclaimer: I work for Google, but the above comments are my own opinions).
You are confusing your own products. AdSense. Not AdWords.
The various forms Google makes available for appealing suspensions are pretty much useless.
I urge you not to believe what I am saying here as well as not feeding within your own internal echo chamber. Use your own search product to search the 'net for the thousands of stories of various forms of algorithmic account suspensions, the damage they cause and how frustrating it is to get anywhere with Google.
Based on what I've witnessed personally and what I've read online the idea of relying on Google products for anything business critical terrifies me.
You --Google-- needs to truly care for it's clients and, as I said in a prior post, issue a guarantee. I am NOT talking about a guarantee of uptime for a service, that's irrelevant here.
What I am referring to are guarantees of service longevity as well as protection and recourse from algorithmic account suspensions. A business person needs to have the ability to address and discuss misunderstandings or problems and not have their entire Google-provided infrastructure evaporate overnight with nowhere to go.
To me the matter of free vs. paid is immaterial. Google and companies like Google use free services to gather and monetize audiences. For example, nobody would pay for search. Nobody would pay for a Facebook account. So you use free to bait the hook and capture audiences. This is a technique as old as the Internet, with browsers such as Netscape and earlier being the first land-grab-through-free-products.
If you are going to have a virtual monopoly by resorting to free products you also have to have the responsibility of not causing your user base irreparable damage by pulling those product either by early termination of the services or termination or suspension of account without recourse. At the moment you are large enough to not have to care about this one bit and nobody has challenged you in court for this terrible issue.
Start here and read through a few pages of links:
https://www.google.com/webhp?sourceid=chrome-instant&ion=1&e...
The various forms Google makes available for appealing suspensions are pretty much useless.
I urge you not to believe what I am saying here as well as not feeding within your own internal echo chamber. Use your own search product to search the 'net for the thousands of stories of various forms of algorithmic account suspensions, the damage they cause and how frustrating it is to get anywhere with Google.
Based on what I've witnessed personally and what I've read online the idea of relying on Google products for anything business critical terrifies me.
You --Google-- needs to truly care for it's clients and, as I said in a prior post, issue a guarantee. I am NOT talking about a guarantee of uptime for a service, that's irrelevant here.
What I am referring to are guarantees of service longevity as well as protection and recourse from algorithmic account suspensions. A business person needs to have the ability to address and discuss misunderstandings or problems and not have their entire Google-provided infrastructure evaporate overnight with nowhere to go.
To me the matter of free vs. paid is immaterial. Google and companies like Google use free services to gather and monetize audiences. For example, nobody would pay for search. Nobody would pay for a Facebook account. So you use free to bait the hook and capture audiences. This is a technique as old as the Internet, with browsers such as Netscape and earlier being the first land-grab-through-free-products.
If you are going to have a virtual monopoly by resorting to free products you also have to have the responsibility of not causing your user base irreparable damage by pulling those product either by early termination of the services or termination or suspension of account without recourse. At the moment you are large enough to not have to care about this one bit and nobody has challenged you in court for this terrible issue.
Start here and read through a few pages of links:
https://www.google.com/webhp?sourceid=chrome-instant&ion=1&e...
You ask for guarantees that an account will never be suspended, or that a service will never be discontinued.
I don't think that's even possible.
For one - abuse is a real thing.
Just ask Amazon about abuse and spam on their AWS platform. There is a reason that you can't just type in any credit card, and spin up 100 EC2 instances, or send 100,000 emails - depending on signals, Amazon seem to manually verify some accounts. But on top of that, they take proactive steps to suspend accounts acting in suspicious ways.
We do much the same.
It is a hard problem, and undoubtedly there are false positive and false negatives. That is regrettable - but I don't think allowing AWS or GCP to become a free haven for spammers or abusers is the answer.
You are correct that we need good procedures to deal with appeals of these things.
Regarding never deprecating a product - Google has started many products, and some of those have been shutdown. I know there's a lot of angst on HackerNews about Google Reader in particular - I myself used it a lot in fact. However, taking off my Google hat - of all companies, they are usually the best about letting you export your data, or migrate it away. When a product gets shutdown, you usually have 6 months, a year etc. to export your data. Even before I started there, they had Google Takeout which basically lets you export your data from any Google product in portable formats e.g. mbox, JSON files etc. Open standards are important to us - e.g. see our work on Kubernetes.
If there's a specific are you think we're failing on - please let me know, and I'll see if there's anything I am able to share, or if there's somebody I can link you up with.
I don't think that's even possible.
For one - abuse is a real thing.
Just ask Amazon about abuse and spam on their AWS platform. There is a reason that you can't just type in any credit card, and spin up 100 EC2 instances, or send 100,000 emails - depending on signals, Amazon seem to manually verify some accounts. But on top of that, they take proactive steps to suspend accounts acting in suspicious ways.
We do much the same.
It is a hard problem, and undoubtedly there are false positive and false negatives. That is regrettable - but I don't think allowing AWS or GCP to become a free haven for spammers or abusers is the answer.
You are correct that we need good procedures to deal with appeals of these things.
Regarding never deprecating a product - Google has started many products, and some of those have been shutdown. I know there's a lot of angst on HackerNews about Google Reader in particular - I myself used it a lot in fact. However, taking off my Google hat - of all companies, they are usually the best about letting you export your data, or migrate it away. When a product gets shutdown, you usually have 6 months, a year etc. to export your data. Even before I started there, they had Google Takeout which basically lets you export your data from any Google product in portable formats e.g. mbox, JSON files etc. Open standards are important to us - e.g. see our work on Kubernetes.
If there's a specific are you think we're failing on - please let me know, and I'll see if there's anything I am able to share, or if there's somebody I can link you up with.
I'm sure there are ways to automated the backup procedure. Most of these files are exported to known formats (doc/docx, xls, etc.). A quick Google search gave me[1], but I'm sure that this can be made via API calls and a lambda function running freely on AWS.
[1] http://www.driveexport.com/
[1] http://www.driveexport.com/
...when I think that Microsoft is trying to kill scripting in office...
There’s also a newer thing called Google Data Studio, which creates neat visualizations directly from Sheets with even less effort.
It’s a no-brainer to feed all sorts of metrics (business or otherwise) from let’s say a Django app into Sheets, and with Data Studio decision makers can have all the freedom to combine them into complicated dashboards in accordance with current business landscape, pinging engineers only if new things need to be tracked.
It’s a no-brainer to feed all sorts of metrics (business or otherwise) from let’s say a Django app into Sheets, and with Data Studio decision makers can have all the freedom to combine them into complicated dashboards in accordance with current business landscape, pinging engineers only if new things need to be tracked.
Is it currently possible to authenticate to Salesforce without Oauth? Like just obtain a token?
I use App Scripts to run API calls from a Google spreadsheet which is easy when the API has simple basic auth. But I've been stumped with Salesforce.
FYI: https://developers.google.com/apps-script/guides/sheets
I use App Scripts to run API calls from a Google spreadsheet which is easy when the API has simple basic auth. But I've been stumped with Salesforce.
FYI: https://developers.google.com/apps-script/guides/sheets
I have OAuth working. It's a pain and I'm sure I've kludged it because it requires going to a _specific_ sheet and re-authenticating every few months. But it works! Hit me up at [email protected] and I'd be happy to share my work. Maybe we can polish it up and get it published to Github.
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.
For a free software spreadsheet people can work on at the same time, there's EtherCalc: https://ethercalc.net/
Totally agree on Google Sheets. I love the connectivity of sheets and the best application I have seen is grades at the school district when you have a lot of kids. It is so easy for me and my wife
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
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
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
Really curious in the "low" technical investment answer: where/how does the Python end of this system get run?
>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.
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.
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://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/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:
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/ ). Let me know!
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).
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/ ). Let me know!
One thing to note is that the new version (v4) of the Sheets API can access the same fancy functionality that Apps Script can. So if gspread moves to that (which e.g. https://github.com/burnash/gspread/issues/435 alludes to), some of those limitations will go away.
(And the sync thing is neat!)
(And the sync thing is neat!)
https://github.com/nithinmurali/pygsheets uses APIv4; someone couldn't wait!
I have also used Google Spreadsheets to act as a frontend of a database that people can dynamically update. I use the onEdit() function in google apps script and have found that it is not always reliable if edits are made in quick succession. I would definitely be interested on how you created your bidirectional synced system.
Sign me up for your product or service, would definitely be interested in seeing code for this.
I would love to take a look at the source code for this!
(author here) Would love to see the code for this!
(gspread's author here) Thank you for the comprehensive tutorial!
Yes, please post to github, gitlab, or bitbucket!
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.
The Jupyter Notebook use case sounds great but if you move to something that is more critical use a real data store.
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://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
RSS feed link is at the bottom of that page.
(Disclaimer: I work for Google Cloud, on the Drive/Docs/Sheets side of things).
https://support.google.com/work/answer/6056635?hl=en 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
RSS feed link is at the bottom of that page.
(Disclaimer: I work for Google Cloud, on the Drive/Docs/Sheets side of things).
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.
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) 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/).
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.
The status dashboard (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/).
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.
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.
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.
Has anyone looked at "Fusion Tables?"
A google search reveals that is what it is supposed to be.
Check out airtable.com for something like this. Great product, lots of use cases.
Isn't that what Firebase is for?
They already have one its called CloudSQL
https://cloud.google.com/sql/
That's not part of the drive/docs suite.
Yep, this should be accessible to users. Someone needs to bridge that gap from spreadsheets to real apps. One click to convert your Spreadsheet into a database. Or hell, just silently migrate it when (NUMROWS + NUMCOLUMNS) > BIGNUMBER .
Honestly, if clippy suggested to excel users something similar, I'd be extremely happy.
Sheets integrates with BigQuery!
There's also ODBC in AppScript (although I haven't tested it to tell you how well it works).
(work on Google Cloud)
There's also ODBC in AppScript (although I haven't tested it to tell you how well it works).
(work on Google Cloud)
Something a la Microsoft Access? (but obviously integrated in their online suite)
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.
I had a lot of mileage avoiding row count actually in the sheet buy using an API to retrieve data directly when a user is interested in a particular record. Then the only think you need to keep in the sheet is an index which is lighter because (as far as I can tell) it's cell count that really affects performance.
I had the same experience. I had to write a layer to slice my data up and submit no more than 50 rows, and retry on connection timeouts. Very frustrating, and so weird that a global giant like Google releases an API that's so mediocre.
One issue I ran into quite a bit was timeouts.
I had a spreadsheet with a few hundred rows I was using to create Docs and send emails. I was running from scripts.google.com. Any operation running for longer than about five minutes would time out so I had to batch my work into smaller buckets and hit run a bunch of times.
I had a spreadsheet with a few hundred rows I was using to create Docs and send emails. I was running from scripts.google.com. Any operation running for longer than about five minutes would time out so I had to batch my work into smaller buckets and hit run a bunch of times.
hear hear! this was my experience too. I was ready to switch to using gsheets as my entire application UI layer when the scaling problems and desyncing started hitting me.
still fantastic for quick-and-dirty tools and prototypes though.
still fantastic for quick-and-dirty tools and prototypes though.
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
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.
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.
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
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
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
Probably because they are in business to make money? Customers whose first priority is cost usually aren't good customers.
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.
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.
I read somewhere that AirTable is deliberately only targeting "human scale" data use cases.
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.
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.
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!
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!
My silly little hobby spreadsheets that would benefit a lot from this are easily >1500 rows.
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?
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?
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.
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.
Cofounder of Airtable here, I hear you and that's on the roadmap :).
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.
Fieldbook co-founder here, thought I would weigh in. I agree with Howie that he doesn't spend much time using Fieldbook, since we already have many of the features he mentioned.
IMHO Fieldbook has the best user experience anywhere for building and working with a real relational data model, including many-to-many relationships, sophisticated queries, and aggregated reports. We've done a ton of iteration with real users and hundreds of usability tests to make these features natural and intuitive.
Fieldbook customers use it on a daily basis for their core business information and processes, and they're very happy. Read our reviews here: http://www.capterra.com/database-management-software/spotlig...
But there's no need to be gripped by indecision! Just try them both, it's free. That will tell you which one works best for you. Message us in-app if you have any questions, we're happy to help you get set up!
IMHO Fieldbook has the best user experience anywhere for building and working with a real relational data model, including many-to-many relationships, sophisticated queries, and aggregated reports. We've done a ton of iteration with real users and hundreds of usability tests to make these features natural and intuitive.
Fieldbook customers use it on a daily basis for their core business information and processes, and they're very happy. Read our reviews here: http://www.capterra.com/database-management-software/spotlig...
But there's no need to be gripped by indecision! Just try them both, it's free. That will tell you which one works best for you. Message us in-app if you have any questions, we're happy to help you get set up!
It seems cool. Have you consider letting us trying the demo without asking for credential? The login form have stop me since I do not want to be contacted by your sales.
I had heard of airtable and checked it out and thought: this is exactly what I have needed this whole time. It is a much slicker version of SmartSheet.
And then I just found out about Fieldbook through your comment and it looks really good too. It's the only other product I've seen that is in the ballpark.
And then both founders are right here on HN pointing out more features.
And then I just found out about Fieldbook through your comment and it looks really good too. It's the only other product I've seen that is in the ballpark.
And then both founders are right here on HN pointing out more features.
Thank you to both founders for taking the time to write such lengthy responses. I really appreciate the more detailed insight.
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 ).
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 . 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/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.
We think Airtable is to spreadsheets/Access/Filemaker what Slack is to email (and fwiw, Slack itself uses Airtable 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 . 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/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.
Is there a downloadable version of this, paid or free?
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.
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
thanks, did not know this.
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.
You are correct that a modern MS Access alternative would certainly find its market.
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.
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.
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.
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.
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.
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.
> 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"?
Isn't the tool that does what MS Access used to do, and that is available now, called "MS Access"?
Perhaps Bubble is close to what you may be looking for: 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.
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.
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.
Edit: DabbleDB was awesome, and easier to learn than quickbase. But Twitter acquihired them and shut it down.
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?
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-...
Google App Maker?
https://blog.google/products/g-suite/customize-your-g-suite-...
MS Access hasn't gone anywhere, why not use it?
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?
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?
It's worth mentioning this service that turns Google Sheets into a RESTful API for you: https://sheetsu.com/
Also sheetrock, which is JS-specific: http://chriszarate.github.io/sheetrock/
I'm not the creator and have only used these for trivial tinkering.
Also sheetrock, which is JS-specific: http://chriszarate.github.io/sheetrock/
I'm not the creator and have only used these for trivial tinkering.
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 :)
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-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 - example usage; my retro games collection http://games.stpe.se/
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-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 - example usage; my retro games collection http://games.stpe.se/
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.
If you haven't checked it out in a while it's worth a shot.
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."
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."
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:
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)
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
[2] https://github.com/hasgluten/hasgluten
[1] http://hagluten.com
[2] https://github.com/hasgluten/hasgluten
You have a small typo in the link to your site. [1] should be http://hasgluten.com
Interesting to choose this over simpler storage, such as sqlite. I suppose you get offsite backups for free.
(author here)
Main advantage in our usecase is getting the CUD interface for free.
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!
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!
This is one of the reasons why I liked working with Parse; the data browser was very handy. This seems like a great alternative.
For free ? Where would the python code run ?
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.
How would the django/flask admin app compare?
multi-user UI, sharing/access control already there, versioning of data, backups. those are all really nice to have
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-inte...
(worked on BigQuery, work in Google Cloud)
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-inte...
(worked on BigQuery, work in Google Cloud)
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.)
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.)
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.
Well, a spreadsheet used as intended is mostly read-only, that's no necessarily true if it's backing a website :)
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.
Even things like forums have many, many page loads than new posts.
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.
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.
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.
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.
Depending on what you're doing, App Maker (https://developers.google.com/appmaker/) might be what you're after.
Disclosure: I work on Google Cloud, but not App Maker.
Disclosure: I work on Google Cloud, but not App Maker.
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.
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.
I wrote about it a year ago as well:
https://mionskowski.pl/using-google-apps-scripts-for-a-simpl...
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... is the closest thing I have found searching myself but it states that it is no longer maintained.
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
(And... it looks like I need to update from Sheets API v3 to v4.)
Here's the project, and there's more rationale/investigation in the README: https://github.com/adam-p/danforth-east
(And... it looks like I need to update from Sheets API v3 to v4.)
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-up...
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.
Did a NodeJS read only Sheets recently no auth needed. Gets array of objects, way simple.
https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300... https://github.com/digplan/gsheet-web
https://runkit.com/58a77f7a86c06300158dfcf5/58a77f7a86c06300... https://github.com/digplan/gsheet-web
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.
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.
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.
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
thanks.. this is useful.. tried it out and perfectly matches my needs.
[deleted]
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
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.
For a port of gspread which uses API v4 - supports new features like formats, notes etc. Checkout https://github.com/nithinmurali/pygsheets
how long the maintenance of this library will last after gspread moves to v4?
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
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!
Have you tried exporting to BigQuery (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.
Disclosure: I work on Google Cloud.
Theres one easy to use google spreadsheet api here built on gspread https://github.com/shkr/GoogleDocsAPI
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)
Easy and simple. Multiple auths and roles and it's cached, so it's not queried every minute ( pressing a link clears the cache)
I use something similar, but not quite as involved to let a restaurant update their daily specials on their website.
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.
Worth also looking at Airtable here as well, becoming a big fan of it.
I did a major integration with Google sheets API recently and found the documentation sorely lacking. Docs and Sheets could be so much better.
you could also store the data in a json file an let a static web app consume it
This is super cool. I will probably use this in the future when sketching out ideas!
Cool but it's a non-starter if you go beyond a few thousand rows.
nice
Our school district has moved to Google sheets for kids grades and I am blown away how well it fits with this use case.
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.