Introduction: Turning Spreadsheet Pain Into an App

So, you all have probably been working in spreadsheets pretty much your entire careers. As an ops consultant, I have spent the last few months intermittently wanting to scratch my eyeballs out at the hideous mess that I contributed to, as well as helping to solve.

of.

If you take nothing else away from today, I would urge you just to keep in mind that yeah, you can turn that hideous mess of spreadsheets that you're dealing with into an app and make your life a huge amount easier.

It's that simple, you take your sheets, you figure out where the mess is at its worst and where it would benefit from being cleared up, you tell Claude in plain English to create a web app for you and you deploy it and you keep on tweaking.

What We’ll Cover Today

I'll go through hopefully three points today. I'll do a quick fairly simple demo of a live build using a fairly simple app and spreadsheet. I'll

then show you how hideous and complicated and what drove me to kind of decide on this as being a solution and I'll show you most of all that the great advantage of doing this is the software that you buy off the shelf, it's built for everyone, it's not built for you.

When you're building your own app you can create all of the weird and wonderful things to solve exactly the fecking problem that you're having. It is joyous and I would warn you it is bloody

addictive. If you haven't played much with vibe coding already, probably most of you have, but if you haven't it does get addictive and your partner will get very annoyed as you keep on saying, oh just one more, just one more prompt, I

just want to get Claude going on this one more thing. So it is all about the tweaking and let's see how we get on. That's a great start isn't it?

Anyway, so

The Messy Spreadsheet Problem

this spreadsheet is probably typical of a spreadsheet that everybody's come across at some point in some company. It's a bit pants, it's a very simplified

version of one, but you know the problem everybody fills in the data in their own particular way, data messes around with formats, yes you can solve this with drop -downs but you always wind up with weird and wonderful data not very well

updated by most of the people most of the time and yes you do always wind up with tab called old and some individual stuff and a brilliant initiative that everybody promptly forgot about.

Back to this, if I turned this into an app might be a little bit nicer to look at, might be a bit more usable to update and maybe I can integrate it with some other data.

Bringing in Other Data Sources (Trello, Jira, BigQuery)

Here's a fairly horrible basic Trello board, again this is just for demo purposes.

What you need to bear in mind here is this could be, I don't know, Jira pulling through all of your Jira statuses, sizes and due dates, it could be looking at BigQuery to pull out essential data that you then integrate with your other stuff.

This is just a very super simple demo so I'll just be mixing the data between spreadsheet and Trello together.

Prompting Claude to Build a Web App

This is the prompt I'm just going to start it going before I kind of talk you through it.

Here's my cloud I am going to change it down to a much hopefully faster model and hopefully it'll take much less time to run than it it did earlier.

The prompt I'm putting in here is bigger and way more complex than you actually need. I figured I'd put in a slightly more complicated one so there's half a hope of it building out reasonably usefully in this session.

All I did in my first prompt was just say okay I've got this Google Sheet. I want an output that looks kind of like this.

I said a little bit about the navigation. I said a little bit about the fact that I need to be able to edit, write it back, have a little bit of functionality but it's over to you what's most important

you definitely don't need to have as much information as I have here in this case because the data I'm inputting is a bit messy I figured I'd tell us how to deep work the data and how it should kind of translate correctly into

something a little bit more usable yeah this is going to take quite some time to run so while we leave Claude thinking I'm gonna hop over and here's one I made earlier.

A Real-World “Eyeballs Out” Spreadsheet Example

So you remember I mentioned that I wanted to scratch my eyeballs out. This is part of the reason why.

Obviously this is all anonymized data but yeah it does generally go out as far as column BQ which is quite lengthy.

I've only got 150 columns in this one but the real data that I was working with had about a thousand lines for about five different projects so five different variations of

this it was painful and like I said us humans we are not good at looking at these hideously long grids of data and figuring out where the little error is what little change needs to be made somewhere what's missing from all of this data and we much prefer web interfaces because they're kind of built

for humans to be able to make use of so I slowly over the course of a month or so evolved into a way more complex interface.

I would say that I probably got to something that was giving me value within a couple of hours of starting. I had something that was delivering

value to everybody else I was working with a few days later after a few more hours effort and now I've got a whole ton of people kind of all making a lot of use of this and generally saving myself a crazy amount of pain.

How the App Is Structured (Sheets + Script + HTML)

So the core of what you'd be looking at is really simple. When you're looking at a spreadsheet, you just go into extensions, you go into app sheet, create an app. That brings you into this interface, and mostly all you need to think about is two different files.

You'll have a GS file, that does the difficult stuff, HTML file, simple visual layer, dead easy.

In my case, I've got a couple of additional ones, which I'll explain to you in a moment. But here is the sucker that it turned into.

What the App Looks Like in Practice

Now you're probably thinking correctly, Lir, this doesn't look that great. True, it's pretty much a spreadsheet still.

This is the grid view. I've kept it really simple. I've done a few bits and pieces.

I can change what columns I'm looking at here. Add in maybe personal email and company status to this one. Job done.

I can choose if I want to filter. A bit more elegant than Google Sheets. Nothing here that's going to be blowing anybody's mind I don't think.

I can also set up my filters if I've got a view that I really like. These columns, these filters, yeah I can save it out and keep it for future reference. Nice but again probably the kind of stuff that you would do in Sheets.

Kanban View and Bulk Status Updates

What you can't really do so well in Sheets though is stuff like this Kanban view.

So all of that hideous complexity of data that I was showing you a lot of the columns were referring to what status is one of these people in this Kanban view just gives me quite a simple way of being able to look at all of the people

figure out who's in what status and move people around maybe these people get to move across to this stage I won't track that one again we can do things like if I've selected a bunch of people I can say okay I want to move them now into the active stage that means that

they're jumping through a few different stages I can say don't bother updating that one I can say set that start date to whatever job done nice okay that's a bit nicer than what you would normally get from Google sheets and here we get

into some of the weird and wonderful stuff that you can start to play with if you have your own set of unique needs.

Custom Actions: Copy-Paste Lists and Quick Emails

So for this one maybe I want to grab the details of all of the active people. I have the selected details column. I can say okay I want their name, their ACME email, give me the data, one per line, copy it to clipboard and then I can send it off to whoever is looking for it.

Maybe I want to email all of these people using their personal email. Grab Grab all of that, semicolon separated, copy to clipboard, job done.

Again, slightly weird and wonderful use cases. Don't know if this would be useful to any of you guys, but it's the kind of thing that tends to come in. Useful, you'll be familiar with your own use cases.

Built this out because if people send me a list of people, I want to be able to just do a very quick filter to have them all come back here.

Role-Based Views for Employees and Clients

Built out a few bits and pieces like an employee view. So if I'm Ethan or Sophia or whatever, I have my own view on, you know, what is in the weekly statement, what are my timesheets looking like, etc, etc.

I built out a client version of that, so if I'm one of the clients on this project, well, here's a list of all of the people that I have working on my stuff. Have I actioned these guys? I need to action these guys, job done.

Will I reject that person? Yes, I will. I get an error, not to worry, this is demo mode, I did warn you guys. So, that gives you a few ideas for things you can do.

I'm not going to show you the rest of the bells and whistles I'll just hope to hell that Claude is very slowly getting there okay we're slightly running low on time so what I'll do instead show you the one I made earlier I guess sods law

isn't it anyway so like I said you go into your extensions you go into app sheet you say you create an app this one like I said much simpler I've just done code one GS file, one HTML file.

Claude will, in the fullness of time, give me the HTML and the GS files and tell me, step -by -step if needed, I'm not a developer, I need step -by -step guidance when I'm doing this for the first time, tell me exactly what to do and how to get it done.

It will debug and fix things along the way if you run into issues but I did find it worked pretty smoothly from the get -go.

Deployment and Built-In Access Control

I had to do my very first deployment there a couple of months ago. Exciting times, but yeah, all you do is click on new deployment. You say initial build or something

similar and oh, I'd just like to linger on this thing for a moment. Because this is all built from Google Sheets, it will be tied to your domain. If I was doing this from my

lirr .acme .test email address signed in correctly into my browser I'll be able to see the sheet and therefore I can see the app. If I'm signed in with a different email address then I can't see the app unless I've given permission for

everyone to see the app. What I love about this is you just got a bit of security built in by default people from outside won't be able to see it it's the same security that you have on Google sheets so if you're happy with that you

you can be happy with the app as well. In this case though, I'm just gonna be brave and say anybody can access it, dammit, I am generous.

That'll tell you the URL, you can ignore the deployment ID for most purposes, and then we just click it out and hope for the best.

Results, Limitations of the Live Demo, and Next Tweaks

Oh yes, okay, so we had this, it's done a bit of deep working of the data and turned it into this.

well done Lear, you just reinvented Trello, you will definitely make some money on this one.

What I have also done in a previous build was think well this is the kind of data where quite often I might need to be able to do more useful things like present it back to the board.

Maybe a few of these items but not all of them will sometimes need a status update to the board that that I have to put together a slide for.

And every time I'm making that slide, I have to cherry pick out each of those pieces of data. That is a painful thing to do.

I would much rather, ah, give up the will to live. Okay, let's give up on Claude for the moment.

What I would do and what normally works much faster than this is just tell Claude,

I'd like to be able to add an icon to each card to say whether it's part of the exec view or not. And then add an extra button to the navigation to give me that exact view and make it into something that I can screen grab and put into a slide and it comes out with something that actually works

Wrap-Up and Q&A

believe it or not so that is more or less the end of the part that I was going to demo so over to you folks for any questions my apologies that Claude was misbehaving a little bit slowly today it does work faster than that that usually.

Demo mode, always fun. Any questions?