[HN Gopher] Show HN: Query Your Sheets with SheetSQL
___________________________________________________________________
Show HN: Query Your Sheets with SheetSQL
Hello HN! I've developed a tool named SheetSQL that allows you to
query, join, export, and schedule your queries to Google Sheets
through a straightforward SQL interface in the browser. This tool
is a simple, first iteration of the idea, so I'm eager to receive
feedback on it. You can contact me at taras [at] sheetsql.io :D.
I'd love to find out if it could be useful for folks out there! As
someone working in fintech, I constantly deal with sheets and often
find it challenging to perform seemingly simple tasks like JOINs
natively. However, I'm familiar with SQL, which inspired the
creation of SheetSQL. It's designed to assist those who use sheets
daily and have some SQL knowledge, making operations across
multiple sheets and worksheets as easy as if they were interacting
with a Postgres database. For those interested in the technical
details, the engine powering the queries in the background is
DuckDB. Therefore, you can expect support for all syntax from the
latest version of DuckDB :) Cheers,
Author : tarasyarema
Score : 103 points
Date : 2024-03-13 10:02 UTC (12 hours ago)
(HTM) web link (sheetsql.io)
(TXT) w3m dump (sheetsql.io)
| rvba wrote:
| I would make the mid tier limited to 10k (100k?) queries and add
| one more plan.
| blowski wrote:
| Why's that? I'm terrible at making decisions on pricing, so I'm
| curious about people's thought processes.
| itsoktocry wrote:
| There's some weird nudging psychology that says when when
| people see an expensive and free option, the mid-tier seems
| even more appealing, which makes it easier to get them
| paying.
|
| Honestly, I wouldn't worry about it. If your tool is useful
| to people, I think your query limit on the free-tier will be
| enough.
| rrr_oh_man wrote:
| Probably some kind of psychological pricing strategy.
|
| However, charging for queries would be like ChatGPT charging
| in tokens -- yes, it's understandable, but non-intuitive. And
| very product-, not user-driven.
|
| I'd rather keep it simple in terms that a user can
| understand. Really like how you set it up right now -- makes
| it easy to say yes.
|
| The _enterprise_ game is the one where you will make the real
| money: Teams, Management, Integrations, Access Rights,
| Billing cycles, Onboarding, etc.
| rvba wrote:
| There will be that one user who makes a gazillion queries
| just because they are unlimited.
|
| Or the second tier should still be capped. But that looks
| wrong feo marketing point of view.
| tarasyarema wrote:
| That is an interesting thing you are raising here. I think
| right now the point of the paid plan is the scheduled queries,
| which are the more compute expensive, that's why the pricing
| like this.
|
| Now, I do believe that I may just increase the free allowance,
| and maybe if I see people that want to power use normal queries
| and have maybe a few scheduled ones have an mid tier plan (or
| even on demand) that allows more queries and a few schedules.
|
| And eventually move the PRO to be a really PRO plan.
| wanderingstan wrote:
| I've made it used an interface to Google sheets at every company
| I've worked at, and in every case it's made me a hero to all the
| non-tech employees. Finally a UI they understand!
|
| Often previous tech people had to built internal CRUD apps with
| Byzantine interfaces that trafficked in JSON or YAML files.
|
| But being able to make changes to our app, or import/export data,
| and all via making changes to a google sheet--this feels like
| magic.
| tarasyarema wrote:
| Nice, actually I think the best target for this would be people
| that poweruse sheets and have some SQL knowledge. I see teams
| that eventually would develop tools or use data lakes etc
| instead of sheets, but as a first tool to quickly operate over
| the initial startup sheet drama I think it could be powerful :D
| hobs wrote:
| How do you deal with the validation problems that crop up, the
| data entry problems that crop up, and the impedance mismatch
| between a traditional datastore and the sheet?
| wanderingstan wrote:
| Google sheets actually has a pretty robust data validation
| setup (up to regular expressesions), and most business users
| are fairly good at handling detailed instructions.
|
| But yes, for the inevitable times when they get it wrong, you
| just need some robust alerting system. Having it email/text
| me when anything goes wrong turned out to still be easier
| than writing our own CRUD app or fancy interfaces.
| hobs wrote:
| I am really glad that worked! I am pretty paranoid about
| the downstream breakage of things but I probably just need
| to let go a little more.
| nunez wrote:
| it's almost always required. the API for Sheets and its
| documentation isn't great. when I dug into sheets to make my
| receipts tracker, I found myself having to transpile their HTTP
| REST API docs into Golang, which was difficult since Golang has
| special objects for many kinds of requests you'd make in
| Sheets.
| hashhar wrote:
| Very interesting. Seems conceptually (and implementation wise
| too) quite similar to
| https://trino.io/docs/current/connector/googlesheets.html.
|
| Where are you storing the structure of the sheet though? Is it
| re-evaluated on each query? I am thinking about cases where a
| column header is renamed or the data types in a column are
| changed?
| tarasyarema wrote:
| Oh, that's actually cool and thanks for the reference.
|
| It's using a temporary cache that re-uses the latest data from
| the sheet (checking if it was updated) so that's why the
| subsequent queries should be much faster than the initial one.
|
| Now about the headers, it's indeed an issue that I need to
| figure out, as there are some sheets that have weird structures
| and it would be nice to find an easy UXsy way to preview the
| sheet data and say "select from this range" or "these are the
| headers".
| hashhar wrote:
| > it would be nice to find an easy UXsy way to preview the
| sheet data and say "select from this range" or "these are the
| headers".
|
| Indeed. Defining the UX when the only UI you have is a SQL
| query is a bit challenging.
|
| Very impressive project indeed and solves a real problem a
| lot of people and companies have. I wish you luck.
| tarasyarema wrote:
| Will keep the site posted on this, but definetly is one of
| the things that worries me the most as of now.
|
| Thanks for the comment!!
| cldellow wrote:
| Google Sheets has an extensibility system: you write a React
| app that renders as a sidebar inside the sheets UI. This
| exposes a few additional API endpoints vs the vanilla sheets
| API. For example, you can determine the selected range, when
| a user has edited a value, changed sheets, etc. We use this
| in our add-on to help people select a range. Sounds like it
| might help for you, too!
|
| The extensibility system also lets you show a modal dialog,
| which we use for previewing API calls to third party HTTP
| servers -- sounds like a similar thing could work well for
| you, too.
|
| You could also look at supporting named ranges if you don't
| already, so people can refer to a range as `FinanceData`
| instead of `Sheet 1!A3:F90`
| numlocked wrote:
| Cool! How do you deal with irregularly shaped data? For instance
| if I have two levels of headers, the first of which spans
| multiple columns via merged cells?
| tarasyarema wrote:
| As I replied to @hashhar, the header thing it's indeed an issue
| that I need to figure out, as there are some sheets that have
| weird structures and it would be nice to find an easy UXsy way
| to preview the sheet data and say "select from this range" or
| "these are the headers".
| tarasyarema wrote:
| Note tho, that vertically merged cells I think should not be
| an issue probably, but would need to QA jaja
| untech wrote:
| Very cool! I am often frustrated by the lack of SQL features in
| Google Sheets.
| mdavid626 wrote:
| I get a certicicate error - invalid authority. When I proceed:
| FortiGuard Intrusion Prevention - Access Blocked
| tarasyarema wrote:
| Could you reach out to taras@sheetsql.io with a screenshot or
| something, so I could have a look at what could be going on?
| rad_gruchalski wrote:
| Talk to your corporate IT. They are not mitm-ing you
| transparently enough.
| tarasyarema wrote:
| If someone want to try the PRO version here's a promo code to use
| it for free the first month "PGRAHAM" it's limited to the first
| 50 uses :D
| brap wrote:
| This would be interesting to use not as an external UI but rather
| completely inside of Sheets. Maybe as a function or something
| like that.
| tarasyarema wrote:
| Yeah actually I'm thinking on making an extension that you
| could use from the sheet page. Do you think that would make
| sense?
| ramraj07 wrote:
| Google sheets already has an inbuilt QUERY function that'll do
| basic sql..
| gpderetta wrote:
| it is better than messing around with vlookup (what isn't),
| but only a tiny subset of sql is supported (with dedicated
| syntax). Also it claims to support actual human readable
| column names but as far as I know it doesn't work and you
| have to use column labels. Also no joins.
|
| Still, if it were to be extended to a full query language
| with good ergonomic it would be amazing.
| tarasyarema wrote:
| Indeed, you can run queries in a sheet, but
|
| 1. It's writing a query in a single row cell which is hard
| from a UX perspective 2. SheetSQL offers you a way to cross
| join sheets as if those where tables, even between
| worksheets, which is something that is not supported
| natively 3. Full DuckDB SQL support, so you can use
| advanced functions like PIVOT or window functions
| throwaway295729 wrote:
| Looks great!
|
| Is there a rate limit?
|
| Will changed to the sheet instantly go live? Or is that a manual
| process to clear cache?
| tarasyarema wrote:
| I have the GCP sheet basic read quota limit, which is something
| I need to investigate if I can make it higher.
|
| Also, in theory right now once you use a sheet once it will
| cache the version so that subsequent queries are faster, and in
| theory if you change the sheet it will check for that and use
| the live data if it got updated in the meantime.
|
| Nevertheless, it might take a minute or so sometimes.
| niutech wrote:
| You can just import your CSV file to SQLite without any limits,
| e.g.: sqlite3 :memory: -cmd '.import -csv
| file.csv table'\ 'SELECT col, COUNT(*), AVG(total) FROM
| table GROUP BY col'
|
| More: https://www.sqlitetutorial.net/sqlite-import-csv/
| tarasyarema wrote:
| It is indeed an option, I guess the problem with this is that
| its static and and you need to re-create the tables each time.
|
| For someone more techie probably could work, as you could dump
| the data to sqlite and then query it. But if you want to have
| it dynamically and from sheets in real-time probably it would
| be harder to handle this way.
| shubhamjain wrote:
| The problem is you need to create a table with the correct data
| types first, which can be time-consuming. Additionally, using
| CLI can be a bit cumbersome to use, especially if it involves
| quering multiple tables, or using joins. I don't want to point
| to the infamous "dropbox" comment [1], but it's important to
| understand that many folks just want a friendly interface where
| they can put in a URL/file, and start querying.
|
| This is the exact reason I built TextQuery [2], which solves a
| similar problem as OP's. I just wanted a simple app where I
| could drop a CSV and start SQL querying it, without creating
| schemas or writing code/commands.
|
| [1]: https://news.ycombinator.com/item?id=8863
|
| [2]: https://textquery.app/
| niutech wrote:
| In the link I posted there is a GUI way of importing CSV
| files by using the open source SQLiteStudio:
| https://sqlitestudio.pl. Your TextQuery also looks nice,
| although it's a pity it isn't open source.
| blondin wrote:
| great work on the first iteration!
| tarasyarema wrote:
| Thanks! First time deploying a project like this on my own, so
| nervous to see how the users use it, or if they do it at all
| jeje
| replwoacause wrote:
| This looks awesome, nice work!
| diordiderot wrote:
| Is the site MKDocs?
|
| edit: looks like sphinx
| tarasyarema wrote:
| :D Actually completely accidental, as I'm using the chakra ui
| for the frontend part
| mikeyinternews wrote:
| That's pretty cool. So do they load your sheet into their
| database from which you write SQL?
| tarasyarema wrote:
| No, it's done dynamically over the data, no loading nor
| anything. In fact it's streaming the queries with the power of
| DuckDB :D
| nathanwallace wrote:
| Readers may also enjoy Steampipe [1], an open source CLI to live
| query Google Sheets [2] and 140+ other services with SQL (e.g.
| AWS, GitHub, etc). It uses Postgres Foreign Data Wrappers under
| the hood and supports joins etc across the services. (Disclaimer
| - I'm a lead on the project.)
|
| 1 - https://github.com/turbot/steampipe 2 -
| https://github.com/turbot/steampipe-plugin-googlesheets
| tarasyarema wrote:
| Oh wow, I did not know about this, thanks for adding. Curious,
| do you handle live updates of the data upstream, it would
| refetch the data each time?
| nathanwallace wrote:
| Steampipe does live queries against the API endpoint and
| stores results in an in-memory cache. You can modify the
| cache expiration to adjust that behavior across queries [1].
| If you want to do change data capture to act on results then
| check out the query trigger [2] in our Flowpipe open source
| project [3].
|
| 1 - https://steampipe.io/docs/guides/caching 2 -
| https://flowpipe.io/docs/flowpipe-hcl/trigger/query 3 -
| https://github.com/turbot/flowpipe
| mritchie712 wrote:
| Very cool! I built a company around SQL and Google Sheets
| (https://www.seekwell.io/, acquired by ThoughtSpot in 2022).
| Plenty of opportunity there.
|
| You get a lot of this for free in duckdb. e.g. if you run the
| query below from the duckdb CLI, it'll query the sheet[0]:
| SELECT * FROM read_csv_auto('https://docs.google.com/spre
| adsheets/export?format=csv&id=1GuEPkwjdICgJ31Ji3iUoarirZNDbPxQj_k
| f7fd4h4Ro', normalize_names=True);
|
| We use duckdb under the hood at my new company
| (https://www.definite.app/), so we get this same sort of
| functionality for free.
|
| 0 - https://twitter.com/thisritchie/status/1767922982046015840
| tarasyarema wrote:
| Yeah, that is actually super nice, the potential DuckDB offers
| to operate with data in such an easy way. With a bit of tuning
| on the auth side it's that easy indeed.
|
| I think it is a pain point for some people, and it's great to
| see other teams working on similar products and being based on
| the same tech.
| andybak wrote:
| Needs more docs and more examples. Either I'm dim or I'm getting
| the syntax on a simple SELECT..WHERE wrong.
| tarasyarema wrote:
| Could you email me maybe some of the issues you encountered so
| I could help and find a good way to add the docs in the page?
| andybak wrote:
| I'd rather do it here so everyone can join in.
|
| I'm not getting a quota error despite not using it all day:
|
| > Could not run query
|
| > Error: Monthly free usage of 100 exceeded,
|
| It's saying I've used 188 out of 100 which is odd. I haven't
| used 188 - more like 15.
|
| Earlier I was trying a query like:
|
| > SELECT * from <table> where Status="Complete";
|
| And it was complaining that Complete wasn't a valid column...
| (rather than Status...) I also had weird issues trying to
| replace * with an actual column name. The autocomplete went
| crazy.
|
| Not sure if it makes a difference but I'm a stubborn Firefox
| user.
| qixxiq wrote:
| I've been using Google Sheets as a data store for some of my
| latest projects, and it's really (mostly) delightful.
|
| https://www.wheremoneygo.com barely has a database, and bedsides
| protecting the Google access keys has minimal privacy/security
| risk.
|
| The main risk, and something I've only seen once, is a corrupted
| database on the Google Sheets side. Since I don't want to keep
| backups of peoples sheets, if something goes wrong on the Google-
| side the entire database could be lost.
| x2bool wrote:
| There is similar open-source project for Google Sheets:
| https://github.com/0x6b/libgsqlite (SQLite extension), which is a
| fork of https://github.com/x2bool/xlite (same idea but for
| Microsoft Excel spreadsheets)
| tobilg wrote:
| Nice! I built https://sql-workbench.com, which is based on DuckDB
| WASM as well, and can also query Google Sheet directly from the
| browser.
|
| Here's an example for querying a Google Sheet:
|
| https://sql-workbench.com/#queries=v0,SELECT-*-FROM-read_csv...
| tarasyarema wrote:
| Indeed the tool is great, and the performance is amazing in the
| browser. I believe DuckDB has a ton of potential!
|
| The same query in SheetSQL
|
| https://ibb.co/dDdZ0vC
| jimnotgym wrote:
| Can you tell me why this is better than using Power Query in
| Excel?
| tarasyarema wrote:
| I guess this is more focused on Google sheets and the
| limitations of it, i.e. people that do not operate with Excel
| files basically
| eezing wrote:
| Can do this for free via GCP BigQuery console.
| tarasyarema wrote:
| Is this something that can be done without being in Google? And
| it would not be free in the end right? As BQ is not free in the
| end either...
___________________________________________________________________
(page generated 2024-03-13 23:02 UTC)