6 ms·

I've built quite a few dashboards while working on proof-of-concept feature/product engineering. Even though people often think I'm joking, almost always the ba

I've built quite a few dashboards while working on proof-of-concept feature/product engineering. Even though people often think I'm joking, almost always the backend database was a Google Sheet. Google Sheets has great API support, easy to write functionality, convenient read and file dumps, works well with Pandas/SQL, and has a universally appreciated UX. Data validation can be annoying if the "admin" directly enters data, but for building a proof of concept, nothing beats Google Sheets. The data from Google Sheets would be passed through an API to a web UI/dashboard. The dashboard I built for end users was a simple Bootstrap (Vue-Bootstrap) table from the API, with enough easy-to-use filters and views to work out of the box. If the data was too large, I would use a templated snippet to convert the JSON into a card view. Ignoring long-term maintainability, this was one of the most foolproof ways to build dashboards. After that, I'd slap Firebase Auth on top, and that was it. I haven't worked on these types of projects for nearly two years now. Folks I know use Retool or Softr with an API connectivity platform like Portable, Pipedream, or Zapier. If you're staring at a spreadsheet on a daily basis, the next step should be looking into an app builder combined with an API connection platform.

vishnugupta 2y ago
Currently I manage my startup’s accounting in Google sheet, and your comment gave me an idea to enhance it with Google Forms!
- ezst 2y ago
  That... Sounds scary.
  - ska 2y ago
    Clunky, but should be ok assuming a) a pretty simple structure and cashflow b) properly implemented double-entry, and c) backups.
  - jayd16 2y ago
    Accounting in a spreadsheet is scary?
    - ezst 2y ago
      Accounting of your holiday's expenses or hobby project? Sure. For a whole business and all the legal and practical obligation that entails? Nope. Not a chance. Just give me a proper accounting software so I don't have to second-guess myself at every turn as to whether my code or my interpretation of the law is correct.
mettamage 2y ago
Love it. I think my prototyping capabilities just extended by this comment
tonyhart7 2y ago
hey we do the same thing lol, google sheets is great and sometimes if I have or want more I can just simply use pocketbase for easy solution these 2 tools is great for easy/fast prototype various project and honestly its kinda hard to replace
smusamashah 2y ago
Monzo (online bank in UK) let you see all your transactions in a Google sheet (with some caveats e.g. interest earned is skipped). I wanted to make a total wealth chart over all years. It's so damn complicated in Google sheets. In MS Excel I could simply make a pivot chart, apply any aggregation on days/weeks and be done with it. But with Sheets I had to make a new aggregate column, filter data in another new column, and make a chart on that. Your comment does explain that API is why they export to sheets and not excel but Google sheets is way behind in ease of use.
- BillinghamJ 2y ago
  Google Sheets can do that - https://officewheel.com/group-dates-in-pivot-table-google-sheets/ https://officewheel.com/group-dates-in-pivot-table-google-sh...
  - smusamashah 2y ago
    This is the text book answer. Not something you can actually do on a live sheet with all transactions. If you want to use a different aggregate e.g. week instead of month or skip first year or something like that you will be making these columns all over again.
- miki123211 2y ago
  My bank just exports to CSV, which is trivially importable to Excel, Sheets, Pandas or whatever else you need.
  - maxique 2y ago
    As I understand it, the appeal of Monzo's way is it's live updating rather than having to log in to some horribly outdated online banking portal and manually export
    - smusamashah 2y ago
      Yes exactly. The Google sheet is live. I couldn't just add a column in live sheet either. I had to copy the sheet using a formula so that its always up to date. And still had to use to other sheets. Chart is in the 4th sheet. In excel I would not have to do any of that.
OxfordOutlander 2y ago
+1 for googlesheets. It is stable, and provided you don't exceed the API limits, you can run a lot of data through it. Appscript is also incredibly powerful if you work with tools that have decent APIs. With a few lines of js and the built in trigger functions, you can have an automatically updating dashboard with email notifications when certain conditions are met (e.g. balance too low, user engagement metric does X etc).
specproc 2y ago
The article mentions appsheets, which I absolutely loved in my last role. Brilliant for knocking up very simple internal tooling off the back of a Google Sheet. Most importantly, there was zero fucking around with IT on it, as it was already included in our workspace package.
dotancohen 2y ago
Firebase auth? Would it be possible for a savvy end user to replicate your API calls to Google Sheets directly to bypass the auth?
ramzis 2y ago
> for building a proof of concept, nothing beats Google Sheets Supabase is free and checks all the boxes in addition to having auth, vector search, logs, security, and not being ... Google
xtiansimon 2y ago
Slightly off-topic. In small business accounting, I have a persistent need for a FileMaker Pro like solution for invoices. FMP was conscious of on-screen layout and print. ReTool has been recommended as similar replacement for FMP but they don’t have the idea of creating print-ready documented receipts and invoices. You don’t even need to print it, you just need to keep it for the IRS. I love the idea of using Google Sheets and turnkey app building apps. But I still need documentation.
- carlosjobim 2y ago
  There's gotta be a ton of solutions for sale for small business accounting, no? Among them FileMaker pro?
  - xtiansimon 2y ago
    > "There's gotta be a ton...Among them FileMaker pro?" You'd think. I don't know what Claris did last week, but when I last dived into this small pool about 12 months ago their license + hosting was something like 2k/year (looking now it might have gone down, or their pricing page is hiding the fees?). Add on top you need to develop, maintain, support your project. These costs are not insignificant for a "small business"--and by the SBA definition my clients are in the bottom 2-18% by employee (size) & revenue.
ezst 2y ago
If I want a database set up in minutes just to focus on the business side and nothing else, I simply fire up a new Django project (I suspect any other framework with an ORM and auto-generated CRUD UIs would be equally competent for that). I have nothing against Google Sheets, I really haven't put much thought into it in this context, but I would need some convincing that it's a better and easier way to kick things off.
- sjsdaiuasgdia 2y ago
  That Django project needs a host to run on, even if it's just your local machine. If you want others to interact with it, you need to expose it to the internet through some means and think through the security implications of doing so. The Google Sheets approach doesn't need you to manage a process or web server. It is instantly shareable while letting Google worry about security, performance, availability, etc. The specifics of the "what's better?" ratio can shift depending on comfort level / experience with either product, or what infrastructure you may already have available to deploy to. But building on top of GApps does mean you get a lot of useful aspects "free".
  - ezst 2y ago
    Thanks, that's a valid point. I have never thought of publishing a PoC web app as more than pushing some files on a server and editing a config file, but now I see how not everyone can be familiar with that.
- liontwist 2y ago
  I like to just start with a SQLite UI editor. You can just query a database and work with the results directly . In a dynamic language like python there is little advantage to loading rows into classes. That Django query language is so painful and opaque.
  - ezst 2y ago
    Practically, Django's admin *becomes* the SQLite UI editor you are referring to, I suspect. Starting with it, the amount of UI code, backend code or SQL code to be written becomes a matter of how far you want to stretch the PoC in either direction (more effort on the views for a prettier/more unique front-end, more controller code for more business logic/cases covered, more database work to assess how the data model will scale).
maximinus_thrax 2y ago
Back in 2010, I was in a work meeting making small talk and we were discussing how cringe some advertising is nowadays. One of us, a program manager who was ~15 years my senior told me it's because we're not the target demographic and that millennials are targeted differently, mainly through word-of-mouth and generally Guerrilla marketing tactics. Whenever I see articles like these and comments like yours, I can't stop thinking about that meeting. Edit: to clarify, I am not accusing you of anything. But I do suspect the article to be part of a Google marketing campaign.
mosburger 2y ago
I wonder if anyone has ever made an ORM adapter that works with Google Sheets as its "Database"?
zem 2y ago
I've built a scrabble tournament management app on google sheets, because it was the easiest way I could think of to let multiple people edit it with all the others seeing live updates. From a developer's point of view it kind of sucks, having to maintain everything in one large javascript file and having to use spreadsheet tabs as input and output, not to mention manually attaching the script to a new sheet for every tournament. But the user experience has been excellent, modulo a few permissions glitches. I'm still trying to rewrite it as a self hosted web app this year because the aforementioned permissions glitches and the difficulty of doing versioned deployments has made me reluctant to continue relying on google, but overall sheets has been a huge boon and we have gotten years of use out of it.

vishnugupta 2y ago

Currently I manage my startup’s accounting in Google sheet, and your comment gave me an idea to enhance it with Google Forms!

ezst 2y ago
That... Sounds scary.
- ska 2y ago
  Clunky, but should be ok assuming a) a pretty simple structure and cashflow b) properly implemented double-entry, and c) backups.
- jayd16 2y ago
  Accounting in a spreadsheet is scary?
  - ezst 2y ago
    Accounting of your holiday's expenses or hobby project? Sure. For a whole business and all the legal and practical obligation that entails? Nope. Not a chance. Just give me a proper accounting software so I don't have to second-guess myself at every turn as to whether my code or my interpretation of the law is correct.

mettamage 2y ago

Love it. I think my prototyping capabilities just extended by this comment

tonyhart7 2y ago

hey we do the same thing lol, google sheets is great and sometimes if I have or want more I can just simply use pocketbase for easy solution these 2 tools is great for easy/fast prototype various project and honestly its kinda hard to replace

smusamashah 2y ago

Monzo (online bank in UK) let you see all your transactions in a Google sheet (with some caveats e.g. interest earned is skipped). I wanted to make a total wealth chart over all years. It's so damn complicated in Google sheets. In MS Excel I could simply make a pivot chart, apply any aggregation on days/weeks and be done with it. But with Sheets I had to make a new aggregate column, filter data in another new column, and make a chart on that. Your comment does explain that API is why they export to sheets and not excel but Google sheets is way behind in ease of use.

BillinghamJ 2y ago
Google Sheets can do that - https://officewheel.com/group-dates-in-pivot-table-google-sheets/ https://officewheel.com/group-dates-in-pivot-table-google-sh...
- smusamashah 2y ago
  This is the text book answer. Not something you can actually do on a live sheet with all transactions. If you want to use a different aggregate e.g. week instead of month or skip first year or something like that you will be making these columns all over again.
miki123211 2y ago
My bank just exports to CSV, which is trivially importable to Excel, Sheets, Pandas or whatever else you need.
- maxique 2y ago
  As I understand it, the appeal of Monzo's way is it's live updating rather than having to log in to some horribly outdated online banking portal and manually export
  - smusamashah 2y ago
    Yes exactly. The Google sheet is live. I couldn't just add a column in live sheet either. I had to copy the sheet using a formula so that its always up to date. And still had to use to other sheets. Chart is in the 4th sheet. In excel I would not have to do any of that.

OxfordOutlander 2y ago

+1 for googlesheets. It is stable, and provided you don't exceed the API limits, you can run a lot of data through it. Appscript is also incredibly powerful if you work with tools that have decent APIs. With a few lines of js and the built in trigger functions, you can have an automatically updating dashboard with email notifications when certain conditions are met (e.g. balance too low, user engagement metric does X etc).

specproc 2y ago

The article mentions appsheets, which I absolutely loved in my last role. Brilliant for knocking up very simple internal tooling off the back of a Google Sheet. Most importantly, there was zero fucking around with IT on it, as it was already included in our workspace package.

dotancohen 2y ago

Firebase auth? Would it be possible for a savvy end user to replicate your API calls to Google Sheets directly to bypass the auth?

ramzis 2y ago

> for building a proof of concept, nothing beats Google Sheets Supabase is free and checks all the boxes in addition to having auth, vector search, logs, security, and not being ... Google

xtiansimon 2y ago

Slightly off-topic. In small business accounting, I have a persistent need for a FileMaker Pro like solution for invoices. FMP was conscious of on-screen layout and print. ReTool has been recommended as similar replacement for FMP but they don’t have the idea of creating print-ready documented receipts and invoices. You don’t even need to print it, you just need to keep it for the IRS. I love the idea of using Google Sheets and turnkey app building apps. But I still need documentation.

carlosjobim 2y ago
There's gotta be a ton of solutions for sale for small business accounting, no? Among them FileMaker pro?
- xtiansimon 2y ago
  > "There's gotta be a ton...Among them FileMaker pro?" You'd think. I don't know what Claris did last week, but when I last dived into this small pool about 12 months ago their license + hosting was something like 2k/year (looking now it might have gone down, or their pricing page is hiding the fees?). Add on top you need to develop, maintain, support your project. These costs are not insignificant for a "small business"--and by the SBA definition my clients are in the bottom 2-18% by employee (size) & revenue.

ezst 2y ago

If I want a database set up in minutes just to focus on the business side and nothing else, I simply fire up a new Django project (I suspect any other framework with an ORM and auto-generated CRUD UIs would be equally competent for that). I have nothing against Google Sheets, I really haven't put much thought into it in this context, but I would need some convincing that it's a better and easier way to kick things off.

sjsdaiuasgdia 2y ago
That Django project needs a host to run on, even if it's just your local machine. If you want others to interact with it, you need to expose it to the internet through some means and think through the security implications of doing so. The Google Sheets approach doesn't need you to manage a process or web server. It is instantly shareable while letting Google worry about security, performance, availability, etc. The specifics of the "what's better?" ratio can shift depending on comfort level / experience with either product, or what infrastructure you may already have available to deploy to. But building on top of GApps does mean you get a lot of useful aspects "free".
- ezst 2y ago
  Thanks, that's a valid point. I have never thought of publishing a PoC web app as more than pushing some files on a server and editing a config file, but now I see how not everyone can be familiar with that.
liontwist 2y ago
I like to just start with a SQLite UI editor. You can just query a database and work with the results directly . In a dynamic language like python there is little advantage to loading rows into classes. That Django query language is so painful and opaque.
- ezst 2y ago
  Practically, Django's admin *becomes* the SQLite UI editor you are referring to, I suspect. Starting with it, the amount of UI code, backend code or SQL code to be written becomes a matter of how far you want to stretch the PoC in either direction (more effort on the views for a prettier/more unique front-end, more controller code for more business logic/cases covered, more database work to assess how the data model will scale).

maximinus_thrax 2y ago

Back in 2010, I was in a work meeting making small talk and we were discussing how cringe some advertising is nowadays. One of us, a program manager who was ~15 years my senior told me it's because we're not the target demographic and that millennials are targeted differently, mainly through word-of-mouth and generally Guerrilla marketing tactics. Whenever I see articles like these and comments like yours, I can't stop thinking about that meeting. Edit: to clarify, I am not accusing you of anything. But I do suspect the article to be part of a Google marketing campaign.

mosburger 2y ago

I wonder if anyone has ever made an ORM adapter that works with Google Sheets as its "Database"?

zem 2y ago

I've built a scrabble tournament management app on google sheets, because it was the easiest way I could think of to let multiple people edit it with all the others seeing live updates. From a developer's point of view it kind of sucks, having to maintain everything in one large javascript file and having to use spreadsheet tabs as input and output, not to mention manually attaching the script to a new sheet for every tournament. But the user experience has been excellent, modulo a few permissions glitches. I'm still trying to rewrite it as a self hosted web app this year because the aforementioned permissions glitches and the difficulty of doing versioned deployments has made me reluctant to continue relying on google, but overall sheets has been a huge boon and we have gotten years of use out of it.