So from talking to people I see a need to quickly turn a spreadsheet of information into something visually pleasing. SheetUI is my response to that need. You give it a Google Sheet URL, pick a template, and tap in the columns where you want them to show up. In return you get a visually pleasing and responsive web page that you can embed on your site, or link to in your newsletter or chat with a client.
Created with React and Material-UI, plus some spreadsheet fetching and parsing code.
This looks great! I made a while back a library[1] to do this, but of course you still have to code on top of it (which is what I wanted). Your project makes it very easy for everyone to get started and not only those who know how to code, props on launching it.
Are you using Node.js? Would love some feedback on my lib since it seems we are doing the same thing for different user types!
I use Next.js, but the sheet is loaded client side :) My next iteration would switch to Cloud sheets API though.
That said, I wish I had stumbled upon your library when I first started out. It looks better designed than the one I initially used (which I promptly abandoned because it couldn't handle certain errors properly, and I decided to roll my own).
Thanks! I've been thinking of renaming it. Everyone who uses it loves it, but I have to first nag people into actually trying it and I think it's because it'd not very discoverable.
What names did you use to try to find it if you don't mind me asking?
I missed it even after your changes until I read this thread. Maybe modifying the CSS is too much of an ask, but I personally think the link color should be a bit differentiated, the link should be underlined, or the link should be moved since I didn't expect a link there.
There's lots of platforms in that space and yet I am surprised that pretty much all of them are "read-only". I have yet to see one that allows writing to the sheet so users browsing the generated site can submit their own content or even use like counters or other dynamic functions that would update in the sheet.
I wrote an article about how to set up a platform like those mentioned + with write functionality (https://blog.usejournal.com/how-to-use-google-sheets-as-a-cm...) but I've always wondered if there would really be a market for such a thing. There clearly is anyway for read-only Google Sheet generated sites.
I think with the exception of Sheet2Site (and SheetUI, of course), the aforementioned platforms do provide write to sheet as well. Judging by the fact that they've been around for a while, I believe there is a market for platform with write functionality.
Your article is pretty well written! More people should look to using sheets before hand-rolling a heavy weight solution.
This looks really cool. I have a potential use case I'd consider using this for, but I don't want to make the sheet (or resulting page) public. Have you considered support for OAuth to retrieve private sheets?
Yes, definitely. I'm hard at work building Google Sheets API integration, so you wouldn't have to make your sheet public. DM me (@tjchear on Twitter) or email me to discuss your use case.
"Even with the nonprofit pricing it would cost $900 per year."
"Pricing for singe user with one "app" is $60 per month. The "free" version only allows 100 "views" per month. Everytime you go to the page with the app and interface with the tools it counts as a "view". Everytime you refresh the page it is another "view"."
very nice... curious if you could embed it easily into another site or even wordpress page? I have had an HOA membership page that is backed by a Google Spreadsheet with some crap Sheets plugin that's always having trouble.
That is definitely possible. I can make some changes to generate an iframe embed code. Email or DM me on Twitter (@tjchear) if you want something customized :)
I have a personal website backed by a Google sheets page; however, it started not handing out proper CORS headers on the request, so it no longer works for me.
Have you run across this? How did you solve it? Thanks!
From the File menu, choose "Publish to the web", and then choose ".csv" format. This gives you a URL that (up until recently) you could pull with either "curl" (etc.) or from a web page (e.g., an SPA). Now the SPA thing fails with a CORS error. Other people on the web have seen it too.
Thank you! I have yet to use the Cloud GSheets API; there's a much simpler approach, but it requires the user to publish their sheet on the web. I'm definitely working on GSheets API integration so users would not have to publish their sheet though.
Ah yes. SheetUI turns a spreadsheet into a simple but pleasing web page, while Glide turns them into an app. That said, adding interactivity, crud capabilities, and just generally making a web app out of it is definitely part of my feature roadmap.
Yeah definitely! They generate an entire site complete with beautiful template and search functionalities. Really amazing work. My niche is smaller in comparison - SheetUI generates a single webpage for each sheet. That said, generating a complete website/webapp is definitely part of my feature roadmap.
But that being said I'm not sure this is static HTML either. You're going to need someway to parse and display that sheet data. Even if its server rendered as static HTML React isn't a bad way to go.
Sheet is retrieved and parsed client-side. React lets me iterate faster. I also use Next.js, so where possible I can do server-side rendering before sending the rendered bits down the wire.
They are currently used as private use webpages for either private communities or to clients. I do not have a publicly viewable website I can show you, because frankly SheetUI is fairly new and no one has used it for that yet.
A subscription model on more template choices, read/write capabilities, and external integrations. Complete site/app generation from sheets is not out of the question :)
Services like this need an easy way to allow people to use their own domain names, and we need to lower the barrier of entry to individuals registering domains and setting up HTTPS.
Imagine a SaaS that sells domains, then provides and easy (and open) API for delegating control over subdomains using OAuth2.
So I sign up for sheetui, and during the registration process they redirect me to a namecheap.com (where anderspitman.net is registered) OAuth2 server, I grant them control over sheetui.anderspitman.net, and they can now host there on my behalf.
sheetui.com also shouldn't have to deal with TLS certs. Why doesn't namecheap.com handle that for me?
HTTP requests can be proxied through something like ngrok which again uses OAuth2 tokens for auth. Now we have a stack where people can securely host websites from there phone.
Hahaha oh wow, I had a design document for this last year, down to the details. OAuth2 doesn't quite fit the bill without a little modification, unfortunately.
Would love to discuss it. I think this could be a solid business. I have little interest in building it myself, but may have to in order to make my other projects easier to self-host. I keep bringing it up hoping to find out it already exists.
What issues did you run into with OAuth2? Are you open to sharing your design doc?
If you want to contact me, I'm shell@alterednarrative.net on email and XMPP. I'll try and find whether I still have the design notes, but doubt it; I've been through a couple of laptops since and lost a lot.
One of my goals was that the flow could start from a service provider - you type in the domain name you want the service to be bound to on the service provider's website, and it goes from there. If you were to use OAuth2 at this point, there's no existing client ID/password, and allowing public dynamic client registration leads to DoS potential as someone could easily register a practically infinite amount of clients and fill up disk space. So you want a custom flow which winds up, at the end, with the consent of the domain owner, returning OAuth2 credentials to the service provider. This flow is fairly complex to spec out in a way that doesn't have subtle security issues (e.g. this initial flow can't trust much info from the service provider which could be something akin to a phishing scam, returning to the service provider on error has to be done carefully because of risk of being used in an open redirect chain, etc).
After that it's mostly a case of supplying auth keys for RFC2136 so the service can manage its delegated domain, handling credential rotation, and properly handling edge cases like either the service provider or the domain management system losing track of state, or the domain being lost/transferred to another entity.
Honestly I'd like to build something like this for my own projects, and might do so as a weekend project soon, esp since it's just been validated as something useful. :D
I'll shoot you an email, and we can talk more if you do end up working on this.
I'm not a big fan of pre-registered clients in the first place. Ideally any client should be able to talk to any OAuth2 server that speaks the same profile, ie endpoints and scopes. Aaron Parecki describes a way to allow "anonymous" clients here[0]. As I understand that's how IndieAuth is implemented. But I'm still relatively new to OAuth, and I don't doubt there could be subtle security issues. Do you have any thoughts on that?
Rather than leaving all this to the many registrars, what about a SaaS (let’s call it usemycustomdomain) that allows you to point a CNAME record for a subdomain to it, then when you sign up to an app that supports custom domains, you can oAuth2 to usemycustomdomain. Then usemycustomdomain can do the SSL management and termination and proxy the traffic to different SaaS products. It could even support custom URLs on a domain.
That would make it more flexible, but in my mind the whole point of this thing existing is to take the pain out of having a custom domain, and making the process accessible to more people. Part of that would include handling registration and hiding all the DNS nonsense unless you really want to fiddle with it.
This is very interesting. I'm trying to understand the use case a little better. Does it seem related to the emerging standard on delegated credentials for TLS (https://blog.cloudflare.com/keyless-delegation/)?
This is an interesting use case (the delegation of subdomain). Sounds like something that can be built atop Amazon Route 53. Have you seen this need come up a lot?
I think it would be helpful for decentralizing things, ie less necessary for the web we have than the web I want.
For example I'm working on a project right now that's ideal for self-hosting, but I really want to encourage people to use their own domain names, and registering them and handling HTTPS is a rather large barrier.
Personally I would love the ability to show data on my website using a Google sheet where others less technical can contribute to (open source projects) as opposed to editing json files. It would only make sense for me to do it if it's still hosted under my domain though.
This looks really cool! I love your quick video that shows how to use the product and build a great looking UI so quickly.
I'm guessing you guys use Liquid based on the curly braces? It looks like you have a pretty nice UI for manipulating the dynamic data, but if you're interested, my product helps people write Liquid: https://www.dropkiq.com/
Let me know if you're interested. Otherwise, best of luck with your product!
I implemented the parsing part! This is the first I've heard of Liquid though. When SheetUI has gained sufficient traction that people need advanced Liquid support, I'll look to your product for that :) There is no price quoted on your page though; do you mind telling us here a ballpark figure?
Awesome! Yea, Liquid is a pretty robust language built by Shopify that is implemented in most languages to help with this exact problem. It might come in handy some day as you scale your app. :)
For Dropkiq, your app looks simple enough (for the Liquid part) that you could likely use our free version. For apps that have complex Liquid, we have our Pro version, which I'm currently offering beta pricing at around 1K per year.
I also recently built a URL shortener on top of Sheets API (https://github.com/ahmetb/sheets-url-shortener) and I don't have to worry about setting up a database or other form of storage. All I needed to do was set up credentials, and it's good to go.
A lot of folks don't realize Google Sheets is like a free small database with a decent API. :)
Precisely! A lot of folks have already built their businesses and processes atop Google Sheets, so it seems a no-brainer to me that any SaaS dealing with data should have a way to bootstrap from that.
Great idea and execution! I've been using Liftoff[1] with Airtable as a DIY solution for this and while it's been easy to set it up and deploy it on Netlify, I still had to resort to writing a custom watch for changes daemon and forking the repo for adjusting the components layout.
I like that SheetUI has the things above built-in and more functionalities like adding buttons and a customisation UI.
Is a custom-CSS feature going to be available anytime soon? It would be great to be able to add some original styling to a website.
SheetUI is built using Material UI, which I'm still getting the hang of. It certainly has support for parameterized themes. As for custom CSS, I'm going to have to look into how to do that technically while making sure there's no security implications.
Thanks for showing me Liftoff! The generated websites look great. I'll see what I can learn from it.
I think just allowing a CSS textarea input that can be injected inside a <style> HTML tag is enough. If the input is HTML escaped (replace <, > and other dangerous characters) then nothing should escape out of that tag.
In college I would make primitive webpages for my college newspaper to use for live feeds and such content publishing. I built something like this (although vastly less polished) - the writers could just fill in details in a sheet & add image URLs, and my static page could retrieve the spreadsheet contents via API and essentially have a super cheap data backend with a super fancy collaborative editor. Fun times.
I used to drive a restaurant website's menu using a shared Google sheet. It was an easy way for multiple people to have access to update the content. I'd cached the recent versions in-case anyone made a breaking formatting change or Google was unavailable. Worked very well for our use-case.
Wonderful product! I was looking to build a page on a team that I am working with, but wanted a simple integration like this. Kudos!
I would like to know if you are going to add more features later. For example, informal 'About us' pages would have links to social media in their bios. So can we have more than primary and secondary buttons?
Also, it would be lovely to have profile photo frame customizable. It looks stylish to have the photo embedded in a circular frame for example.
These are great ideas! I'll add them to SheetUI's feature roadmap. Definitely reach out to me at tjchear (twitter/linkedin/gmail) and we can talk about what you need.
I think the greatest challenge will be creating enough elements/visualizations and customization to please most customers.
Our team at Sheet.Best (https://sheet.best) was just talking about reducing the entry barrier for our services and one of the ideas was something like this.
Nice to see more competition on the "no code" approach to use spreadsheets as databases :).
Very cool! I can see this being especially useful for communicating status of some process status, like for example, # of tests run today, outcome, etc.
Crazy I know: why not just link them to the sheet? If the goal is for one person to convey information to another, why do we need to “decorate” it?
I kind of like some of the Brutalism takes on web design. Just throw away all decoration and live with the ugly world, “ugly” being subjective. I think excel sheets are actually quite beautiful.
Challenge the status quo :-) at the expense of coolness, dignity and comfort.
Ugliness aside, there are some things that don't work very well in a plain spreadsheet vs a UI. For example large paragraphs and images.
Part of conveying information should be about making the information easy to digest. Sometimes a spreadsheet fits that bill. Other times a crafted UI works better.
It does if you create large cells and stick paragraphs in. Make sure the width of the cell is enough for 12-15 words. Images also work just fine. It truly boils down to "uglyness" and not functionality. "Easy to digest" is a subjective term.
Excel sheets are essentially rectangular grid with borders. Grids have been used since the 60's in graphic design, primarily championed by the Swiss Grid System (Josef-muller brockmann, et. al).
So if you say that excel sheets are not a good instrument to convey information, you're saying that a grid like structure is not good at laying out information in a logical, understandable fashion. Which is provably false - everything from Tax forms to road signs, every bit of graphic design professionally conducted uses the grid system.
Think a bit deeper than just aesthetics and ask yourself, what exactly is an excel sheet? It is a bunch of boxes. Whether those are div elements or excel cells, what's the difference? Padding and margins?
I agree with this to some extent. The aesthetics, even if it does almost nothing for functionality, unfortunately does influence people's impression and opinion, and ultimately, their decision to open their wallet. When the SaaS world is filled with players who each bring something to the table, aesthetics sometimes becomes a tie-breaker, so to speak.
Ooh I have some opinions about this, and I largely agree with you (crazy, I know).
One idea I have is to go back to the simpler days of MSDOS GUI. We can generate such a UI using some custom markdown, and for the most part it does simple CRUD, with plugin support for complicated stuff. The focus will be on speed (keyboard navigable with Windows alt+<letter> shortcuts), and brutalist UI in the form of MSDOS GUI.
It's a Chrome extension called Screen Recorder. Very nifty! I've used it for my other projects and I'm happy with it. Only downside is it generates webm only - I had to put it through an encoder.
FYI to folks who are trying it out now - on step 3, you'll have to click on an element on the preview UI so you could customize it. You can customize it to show a particular column by clicking the + button. Without customization, the resulting page will not show anything.
I'll fix this in the next iteration by providing defaults.
Love the execution of your idea. Having played with google sheet too (using it as my storage for https://nooze.world actually) and having followed sheet2site story, I am left wondering if there is still money to make for services like this in 2020.
There is always room for improvement! For now, SheetUI doesn't do the same thing as Sheet2Site (they generate an entire website, whereas SheetUI generates only single web page per sheet).
Considering how many people across so many industries use Google Sheets as part of their processes, I'm convinced there is some money to be made.
Thanks! These products are great (I can see myself using a couple of them). I think when we look across all industries that use spreadsheets, we'll find a market that's so big that any player can find their own profitable niche. I hope to find mine.
"Even with the nonprofit pricing it would cost $900 per year."
"Pricing for singe user with one "app" is $60 per month. The "free" version only allows 100 "views" per month. Everytime you go to the page with the app and interface with the tools it counts as a "view". Everytime you refresh the page it is another "view"."
Never heard of it, but it looks really well done! It also mirrors Google Sheet's UI closely for easier onboarding. I guess at this point you can say SheetUI is a pale copy of it and I'd have nothing to counter. That said, their margin is my opportunity, and I can definitely find opportunities amongst users whom AwesomeTable has not fully satisfied. Thanks for bringing this to my attention - I'll learn from them and improve my craft.
This product is great... But it makes me feel sad to think about all the webpages that hide a small amount of tabular data in an oversized web UI.
I want the reverse of this service - I just want the raw data from any web page in a spreadsheet ready to filter and sort and define new columns as I please...
This is one of the reasons I really like that some webpages have taken to using JSON REST endpoints to provide the data for their (usually React-based) webpage. It makes it fantastically easy to circumvent all the complicated stuff and just directly access the data.
It's somewhat of a shame that HTML never quite managed to split data from representation. It managed to separate content and styling, but the content is still treated as some kind of text document, not a textual representation of some data.
Like it or not, it's the reality we're in. It's not going away anytime soon, but we can always achieve incremental improvements towards that ideal through HTML standardizations. React and GraphQL (both great products from fb) really make that possible.
Yes, but currently the process of writing a scraper for a given page is usually half an hour with beautiful soup... Or more if you have to handle multiple pages, Auth cookies that expire, and rate limits.
Example usecase:. eBay search results, I want to sort by "Price plus $0.50 per mile from me", since I'm going to have to drive my van to pick up the item.
That would be super easy with a spreadsheet of the search results, and I could probably have the data sorted in under 60 seconds on a column of "=B5+C5*0.5"
It's the complete opposite of what SheetUI hopes to achieve, but this is such an interesting use case I'll think more about it :) I can see how there might be a legitimately strong demand for it. Feel free to connect with me (tjchear on twitter/linkedin/gmail) to discuss this further.
I can see another variation of this being useful as well. Instead of loading live data all the time, it loads the data at time of publishing and creates a static html page with an option to auto-publish to github pages or netlify or something like that.
Sheets itself does a remarkable job of pasting HTML into a sheet. I just tried it with English Wikipedia's list of the world's largest cities and it worked perfectly, except the column of photographs is blank instead, which is a reasonable choice.
There was one other comment that mentioned the same thing. I'll look into this. How can I chat with you further about this? My email's tjchear at gmail if you want to reach out.
Far from it! The backend is provided by Google Sheets. Much of what I do with SheetUI is to provide an easy-to-use editor and a beautiful front-end to visualize the data coming from the sheets.
I don't think so. I just need to procure an API key and some credentials from Cloud Console to begin using Sheet API. The rest is just a lot more backend development for me.
Would you build a livelihood on that? I have heard of that but it sounds like the kind of loophole that got the industry into a situation where you needed to pay tens of thousands to use an API.
Yes? Google is notorious for deprecating and changing things, so I would have a backup plan but companies like Streak have built large businesses on much more restricted APIs like Gmail.
> kind of loophole
This is not a loophole, this is 2-legged Oauth. This is much safer than the typical 3-legged Oauth where the user gives blanket permission to read and write from their Google Drive. The user has to explicitly share the sheet with this service account email. Google is trying to protect people from clicking a phishing link and getting all their email and drive content stolen or hijacked.
Yes, Google can shut this down tomorrow or demand thousands of dollars to use it. It's a inherent risk building on top of someone's platform, the trade off being you get the scaffolding and user base the platform already has.
You might be mixing up access to GMail APIs with general access to Google APIs? Reading people's emails comes with tons of red tape, other things not so much.
Okay, it's wider than just GMail, I had missed the addition of Drive, but according to that link an audit is just required for
> Drive - Any Drive API scope that permits an application to read, modify, or manage the content or metadata of a user’s Drive files, without the user individually granting file-by-file access.
I could be wrong, but to me this suggests you can use the Sheets API against a user-specified document without it. It has it's own namespace of scopes that's not under drive.X
Created with React and Material-UI, plus some spreadsheet fetching and parsing code.
Glad to answer any questions!