When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa

Esa is a beginner at Google Sheets. I discover the moment he discovered the deepest secrets of Google Sheets. And we wax poetic about AI in sheets, and data flow.

Watch on YouTube

Google Sheets has become an indispensable tool for many professionals and individuals alike. Its versatility and ease of use have made it a popular choice for data organization, analysis, and collaboration. In a recent episode of Sheet Talking on YouTube, Esa, a relatively new user of Google Sheets, shares his journey of discovering the secrets and power of this spreadsheet software.

This article delves into Esa's experience, highlighting the process of transitioning from a novice to a proficient user of Google Sheets. Based on the conversation I had with Eas which you can see on YouTube and read the full transcript below.

Result of necessity

Esa's introduction to Google Sheets came as a result of necessity. Faced with a daunting task that would have taken countless hours to complete manually, he turned to Google for a better solution. In the Sheet Talking episode, Esa explains how he embarked on his journey to learn the ins and outs of Google Sheets, starting from scratch and gradually gaining proficiency.

The Fascination of Fresh Learning

Esa's perspective as a new user of Google Sheets brings a unique perspective to the table. While some users have been using this software for a decade or more, Esa's fresh experience allows for a deeper exploration of the learning process. His curiosity and determination to unravel the secrets of Google Sheets are commendable, inspiring others to embark on their own learning journeys.

Discovering the Hidden Gems

During the interview, Esa discusses his initial knowledge and experience with spreadsheets, acknowledging his limited expertise. The conversation focuses on his current understanding, the areas where he lacks knowledge, and the hidden features and capabilities he has yet to discover. Esa's insights provide valuable insights into the vast potential of Google Sheets, highlighting its integration with various tools and its ability to handle complex tasks efficiently.

The Power of Integration

One of the reasons Esa prefers Google Sheets over other spreadsheet software is its seamless integration with numerous tools. He mentions using scraping tools, client registries, and other integrations to enhance his workflow. Esa also highlights the advantages of Google Sheets in terms of collaboration, allowing multiple users to work on lists, documents, and spreadsheets simultaneously.

Overcoming Challenges with Formulas

As Esa's knowledge grew, he encountered challenges that required him to explore advanced features of Google Sheets. He mentions using formulas like XLOOKUP and the integration of APIs to streamline processes and compile vast amounts of data effectively. Esa's journey exemplifies the power of learning new formulas and leveraging APIs to accomplish complex tasks efficiently.

The Role of Google Sheets in Business Management

Esa also sheds light on the significance of Google Sheets in business management. He explains how he uses Google Sheets as a central data space for managing inventory, creating comprehensive lists, and integrating them with management systems like ERP software. The flexibility and accessibility of Google Sheets enable businesses to streamline their operations and make data-driven decisions.

Exploring the Boundaries

During the interview, Esa and the host discuss various possibilities and potential use cases of Google Sheets. They delve into the challenges of structuring data, creating relational databases, and exploring alternative tools like Glide and SpreadSimple. Esa's exploration of different options demonstrates the endless possibilities for leveraging Google Sheets in various domains.

Conclusion

Esa's journey from a novice to a proficient user of Google Sheets offers valuable insights into the process of learning and mastering this powerful spreadsheet software. His story highlights the importance of curiosity, experimentation, and continuous learning in harnessing the full potential of Google Sheets. As more individuals and businesses recognize the power of this tool, the secrets of Google Sheets continue to unfold, empowering users to accomplish remarkable feats with their data.

When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa - YouTube
https://www.youtube.com/watch?v=FN78_1L4DMg

Transcript:
(00:00) Kind of, uh, out of necessity, when I had, , a huge, , job to do that I could spend like a thousand hours doing manually, I would just go out and, and just Google, tried to see, ask around if, if there was, , a better way to do it. Welcome. So this is a very special episode of sheet talking. Because it's someone who started using Google sheets literally a year ago.
(00:25) Now this might sound crazy to some who've been using Google sheets for 10 years, and I thought this was so interesting because it's talking with someone who is it's, they're fresh in their journey. In learning, literally all of the secret. Uh, parts of Google sheets, they are better sheets member.
(00:44) So they are going on a very rapid journey to find out way more. But I wanted to focus in on going from knowing nothing to knowing something and what that was like, what journey happened and what sparked that we'll find out that it was out of necessity out of doing this job they needed to do , figuring out how to compile a million cells.
(01:04) You'll see. This was so interesting to focus in with someone who is right there at the precipice of learning, who is starting that journey, who knows what it feels like to go from nothing to something. And I hope you will enjoy this journey as well. I wanted to sort of start with nothing. Uh, in the beginning.
(01:26) Nobody knows anything. , and you particularly Esa , you answered the call of people who are excited to talk or not excited, people who are going to talk about spreadsheets, uh, here on sheet talking and you prefaced it with like, I suck at spreadsheets and I don't really want to. Be on a thing to talk about spreadsheets, of which the thing is I feel like I suck at and I, I sort of dug into this and I, I sort of pinpointed out that like, everybody sucks.

(02:00) Like there is not a single person on earth that's like the, like there might be the best person at different aspects of spreadsheets. It's like spreadsheets are so wide. So I wanted to take this opportunity to talk to you and talk with you and find out what do you. Think you know about spreadsheets, what you don't, what you know, you don't know about spreadsheets.
(02:25) But then also I wanted to find out what you don't know that you don't know about spreadsheets and what you didn't, you do know about spreadsheets, but you didn't think you knew, so, right. There's like a lot of things. So that's what I think we, we can start at. Um, thank you for joining me.
(02:40) Yeah. Thank you for having me. Um, I think, uh, The, the things I know, I know is that there's a lot of tools that integrate with Google Sheets, which is, uh, basically one of the, one of the main reasons I, I prefer it over to Microsoft Excel, uh, which has, uh, longer history in, in, in all kinds of industries, uh, at least here in Finland.
(03:06) Uh, Google Sheets is kind of a new player in the, in the field Yeah. From my, uh, corporate point of view. Uh, but, um mm-hmm. What I really like is, is I can have a scraping tools. I, I can have all kinds of integrations that can push stuff in Google Sheets and, and maybe take stuff out of Google Sheets and utilize it.
(03:28) But, um, mm-hmm. But the, the, the part that, um, what happens in the Google Sheets in this pipeline is, has been kind of blurry for me for a while. So I know I can import and export stuff and I can do basic, uh, basic formulas like, uh, cell plus sbi. Yeah, yeah. Calculated together and stuff like that. Right away, right away you are probably, Hands down way above, way ahead of many people.
(04:01) Because it sounds like you have experience with Microsoft Excel. Well, that, that's, uh, started like a little over a year ago. Mm-hmm. Um, I had to, I had to learn to use it a a little bit, um, just to. We, we had, um, in, in the intercompany I work for, we actually switched out two e-commerce platforms and one e r P system and, um, and bookkeeping systems at the same time, uh, during the summer.
(04:33) So I had to move, uh, through the exports and imports, um, a little lower 6,000 products and. 10,000 client registry and stuff like that. So, so I had to figure out, uh, some formulas such as X lockup and, but this what you were doing in Google Sheets That, that I, I did in X Excel and then soon after had Google Sheets because we had, um, How to use Excel for the imports to the new, the odu, uh, e r P software because they, they have, um, they, they accept the Excel files, but they don't like the CSVs or sorry, say

(05:21) What's e r p? Um, it's a management system for a warehouse, uh, for e-commerce. Um, so inventory, yeah. Management. Yeah, yeah, yeah. Inventory and, and, uh, client base and. It has, uh, a lot of stuff. This, this, uh, particular one is odu.com. I think it's a Belgian mm-hmm. Software. If, if I remember you were sun setting that software and moving to No, it's the opposite.
(05:53) Yeah. Oh, you were inducing that software. Yeah, we, we started using it to, to Formula One we had was, uh, from the mid nineties. So, yeah. And you had never wait. So thi this is very interesting to me. So you, this was a year ago, you started using Microsoft Excel and Google Sheets. Yeah. Like you haven't used Google Sheets before a year ago.
(06:20) Well, a little bit, but for just, uh, just notes, basically just stuffing things to the cells and not, not really using it more like, um, using it as a notepad of sorts. And how long have you used it as a notepad? Um, Maybe like three years. We, we used it with, uh, with my team of, of 20 people and none of us actually knew how to, so we just put stuff in the cells just to keep separate sentences organized.
(06:53) And is this because you have, so you're using Google Sheets to like separate some notes? Um, because you had a Google Workspace account, like was your email, Gmail, and you had Google Docs and you sort of had these things available to you for free, right? Yeah. And so you were like, okay, spreadsheets are gonna be used for our notes.
(07:15) And then, uh, you were able to, I, I'm assuming here you were able to share. Like have the whole team on a single document or a, a single spreadsheet. Yeah. That's, that's one of the big, big advantages that we could share to shed the link. We, we used, um, Google Docs as well, but, um, with docs it's, it's like, um, more detailed in one place and with which sheets we could have like lists of, of stuff.
(07:49) And then, then maybe a link to a. Google Docs if, if there was a need for one. Yeah, like a Google Doc is just one big, it's almost like one big cell, right? Like Google Sheets. You have like lots of cells. You can put lots of different information all over the place. Right? Yeah. That's a great way to put it.
(08:07) Yeah. That's interesting because you know, quite honestly, I've heard it the other way where people are like, oh, I take notes in Google Docs and then when I have to like list out information or like compile information in different ways, I'll link to a spreadsheet. But you did it sort of the opposite.
(08:24) You're like, okay, we're gonna keep our notes in a spreadsheet and link to Google Docs. But the beauty of it is that it's possible to do it either way. Like you have all of these tools available. I'm like, Also slides and drawing Google drawings and oh my God, there's like so many, uh, things.
(08:43) Jamboard, did you ever use a Jamboard? Yeah, I, I tried it once, but I felt like it had a limited space, so we moved, moved to something else. But that's, that's thing because the tools, there are so many tools and they are so flexible that, that you can use them in, in so many ways. So sometimes I just feel like I'm, I'm smashing a screw with a rubber hammer and thinking there has to be a better way.

(09:09) Oh my God, yes. So this is one of those things where it's a good thing and a bad thing. Right? There's a curse and a blessing. There's a blessing that it's, these tools are very flexible. A, a new spreadsheet is completely blank and you have 26 columns. You have a hun a thousand rows. You have 26,000 cells.
(09:33) Like, great. But like also you have 26,000 cells. Like, oh my God, this is like, do I need all of these all the time? Um, yeah. And it's like unbelievably dense too. Right. Have you been now in the last year that you've been. Working with Google Sheets and it sounds like you're using Google Sheets a lot more, especially cuz you're a better Sheets member.
(09:56) You're watching videos. Yeah. And you're like seeing sort of, I don't know how to explain this, but like, there's a blank sheet and it's like the night sky and then like at, or like it's a daytime and it's cloudy and, and you see the sky and then at night you're like, oh. There's like another layer of stars behind this.
(10:17) Oh, I, there's some density to this. And then you like look through a telescope and you're like, oh wait. There's multiple levels of density and like these things that just go on forever and in an infinite number of possibilities, like app script, like formulas, there's a 500 or just over 500 formulas.
(10:36) But with app script, there's like an infinite number of things you can do, like. When did you find out these sort of pieces? Like you said, you know, you were taking notes at the beginning and then do you remember, like, how did you discover that formulas were a thing? You can put an equal sign in, like kind of, uh, out of necessity, when I had, um, a huge, uh, job to do that I could spend like a thousand hours doing manually, I would just go out and, and just Google, tried to see, ask around if, if there was, um, a better way to do it.
(11:12) And, and then that's basically when I found the X lookup function, which has now, um, I think saved, uh, my workplace and my own companies. Um, countless hours, like we are talking, um, maybe, maybe even millions of hours here. What were you doing? So, so the X lookup, the x lookup is pretty ballsy. It's like a, a pretty hunky kind of, Formula.

(11:42) And that was the first formula you were like, oh, what I can use this for, what I need to do? Yeah. I, I had to scratch my head for a while to understand how it works, but after that I, I taught it to like six other people in the company and they were all like, oh, I wish I knew this like 20 years ago. And then I just comforted them saying it wasn't around back then, but, Yeah.
(12:08) At least 20 years ago, people had to use V lookup only. They had V lookup and H lookup X lookup is pretty new. Yeah. Yep. But it's, it's amazing. Like when we had like, um, three different sources of information for the same products that we have to compile in one, uh, file to import it, that that really helped.
(12:32) We could just, uh, bundle them in. From all the sources filled by field, uh, like column by column, by just, uh, matching them with the stock keeping unit number. So it was three, it was really amazing. I, I had, uh, I, I had one, uh, this, this was, uh, an extra file, but, but, uh, I had, uh, a little over a million fields, uh, cells in one file.
(13:01) In a million cells. And you had to fill those out, or those were what? The raw data that you had to, I had to fill those. Pick it out. You'd fill them out. I, so you had more than a million out, like other three different pieces of information. Yeah. Three different sources. And you had to combine these into one, uh, spreadsheet.
(13:17) Yeah. Yeah. One spreadsheet with a mil, a little over a million cells. Wow. And it, and, and countless hours. You were, you were at the beginning just picking like, Going, finding this command F find this skew number, copy paste this information over to this other sheet. Yeah, I just went asking how they've done it before.

(13:44) And that's basically how it was the, the software that we were using before didn't really have any data of validation either. So every cell. That had any information, it was always hand put in, like manually. Wow. So, so you, you can imagine the, the number of ways one can type a product name, for example, if there's like a thousand repetitions and every single line is manually input.
(14:17) Wow. Yeah. That's an interesting thing that some software. Some proprietary software that, like you, you can use for inventory management might not have data validation because like the people who make that software have to literally code in data validation. But in addition to like having the ability to do data validation, you need to have the ability to do data validation on those specific fields and those inputs.
(14:43) Whereas with Google Sheets and Excel, you can like right click. Add data validation. Like you, like, you don't have to be a coder to code this sort of feature of saying, I only want, uh, URLs in this column, or I only want, uh, out of, I, I only want to be able to select out of like five options and have a click dropdown menu to like click and select this.
(15:10) Like, you don't need to code to add that to a spreadsheet, right? Yep. Wow. And so x lookup was your first, uh, formula, and then were you like, what else can I do here? Or was there another need that you're like, okay, I got this job done, I have now filled out these million cells, and you went off and like, you're like, I'm okay.
(15:38) I'm, I'm done with spreadsheets. I've learned enough, I'm good to go. Um, And then, oh wait, I did have another need. Or was it like, Hmm, what else is possible? What happened after that? I got, I got kind of excited on how powerful it was, and then I started thinking of more use cases and then more ways to use it.
(15:59) And then I just, when, whenever I came with, uh, came up with a problem that had to do with, with data sorting or, or any kind of modifications mm-hmm. I just went, um, Went out to, to look for solutions, uh, in the, in the world of formulas. And I found some, some really nice, uh, for example, one, one big revelation was that I could just put like a word in quotes and send plus sign and another word in quotes, and it would combine them in the one cell.
(16:34) Mm-hmm. And this kind of stuff. Um, actually let me. Uh, creating, uh, with, with some, some external tools, um, a way to create, um, Google Map, uh, URLs for, for scraping, uh, certain things on Google Maps, uh, according to, to a postal number in Finland. So right, basically right now I have, I have one bot, uh, running to.
(17:05) To make a complete list of all the schools in Finland, because they are one of our main clients in our supply sector. Mm-hmm. So, uh, that I, I couldn't believe that kind of lists, uh, actually never existed before, but I think, um, in two or three months, we might be the first ones to have it, and this is all, so, so you're creating a brand new list of schools in Finland? Yeah.

(17:31) But you're finding those schools by using maps and, and, and you are, you are. And the formula here created url. Yeah. You're, you're taking the URL of a Google Maps and you're like flipping through, you're changing some metric, which is the, uh, zip code, right? Yeah. I, I actually, uh, first, first made a full list of the zip codes.
(17:55) Mm-hmm. And then the. Names of the places, and then I combine them in the formula with the Google, uh, she Google Maps url. Mm-hmm. And now it can automatically, it says it, uh, generated url. Uh, it gives the maps map results limited to the schools in the area. And yeah. This is so cool. To the sheath. Yeah. This is so cool.
(18:23) Right? Like, um, I have used in a similar way, uh, Google Sheets to do what's called like spin text or spin Spin text or spin text. Oh yeah. Like I'll take a sentence. Uh, and, and I do this with sentences. I do this with prompts. Now with, with prompt engineering, uh, with AI and chat pt, I've been like, Creating a prompt and then flipping, taking one element and then going down a list of like, okay, I want this one prompt and just flip out this one element.
(18:55) Um, and then I'm. So the basic part of that right, is exactly what you said, sort of taking text and text and adding it together. Um, but I do that plus I'll integrate it with the a p I of OpenAI in the sheet so I don't have to like copy paste into chat G P T. So I can just see the result right away of that prompt with that specific wording and go down the list and just see all of the prompts and.

(19:23) Get all of these different things. Oh, that's brilliant. So did, did, did you get the sheets, uh, with the api straight to the open ai? Yeah. Yeah. I need to returns the results to the sheets. Yep. And there's a couple things you can do here, right? So you can like, and, and you think, you think, okay, this might save you from copy pasting.
(19:49) Like, just, just like you said before, right? You, you had this sort of massive amount of information across three different databases. You had to bring them together and like you could theoretically, with given enough time, you could do this. Piece by piece. You could say, go here, find this thing, command F, find this, cut and paste, and move this data here.
(20:11) Same thing with this idea, right? With enough time you can take this string of text, copy, paste it into chat u pt, get the result, copy, paste it back into a sheet, theoretically, right? But like being able to do that a hundred times in like moments or. What I am testing or, or not really testing, but like trying to play with right now, and I, at the time of this recording, I have not su successfully done this yet, but what I'm trying to do is figure out how to not just enter text to ai, API and then get back text.

(20:52) What I want to do is like, based on the text that I get back, then go back to AI with another prompt so that I can string. Multiple prompts together. For instance, um, this is gonna be a bad example, but, um, let's say I wanted to come up with weird ways to do marketing. Well, I wanna like write a blog post about weird ways to do marketing, but like, if you just type that into a chat p t you're gonna get just like a list and then a little bit of explanation.
(21:28) What I really wanna do is like, get. Get the results in a bullet point. I don't want, I don't want it to take the single prompt and come up with the entire blog post. I want to get like an individualized bullet list of these like weird ways. Then each one. Now I, I know. Okay, that's a weird way to do marketing.
(21:48) Then I want to send that one of those examples through again and say, given this, this weird way of doing, um, Marketing. Give me three examples of it. Then with those examples, I wanted to also say like, okay, describe how uh, someone doing, um, SEO might, uh, do this weird way of marketing. Uh, describe how someone doing paper click ads might do this weird way of marketing to like, have a prompt generate answers, but then have those answers then generate.
(22:25) Uh, multiple other prompts, not prompts answers. Then have those answers generate multiple answers. So then instead of getting like two words and getting an 8,000 word log article, that just like, sounds like AI wrote it, you know, is like, it comes out with these like little pieces of text that then you can piece together and say, okay, here's, you know, um, 50 weird ways to do marketing and the different ways.
(22:55) Like Right. You, you can come up with a list of say, 10 ways to do marketing, which is seo, paper, click, um, influencer marketing. You can do those in one of those prompts. Right. And now you have, with one prompt, you can come up with 50 weird ways to do marketing. And the examples. Right. Sorry that was a long explanation.
(23:16) No, it's, it's, it's really good. And I, I think I, I see, tell what. I think there was, uh, one tool that tried to implement same, uh, kind of mm-hmm. Take, call it, um, prompt chaining. Maybe that's okay. I've been trying to figure out what this is called. Okay. I'm gonna write that down cause I've never heard before.
(23:41) That's great. I gotta look for that. Yeah. I'll get back to you with the name when I remember it, but I, I just run into this the other day, so. Well, what I've seen so far is like people using Google Sheets to do the one step. What I sort of explained at the beginning is like, okay, you can spin this thing away around again and again and again, 50, a hundred, 200, 500 times.
(24:06) It doesn't matter cuz it's gonna just input and get a response. But I just think it'd be so much more interesting to like do now what's called Prompt Cheney. I figured out the name of it. Thank God. What, what, what's the main, uh, pain point in it? Like, is it, is it, uh, the answer is coming back to the Google sheets.
(24:28) Um, so there's two main pain points that I haven't been able to really overcome. And, and someone will know this, right? Even probably someone watching this might even have seen an, maybe I figured this out within the time, between the time of recording this end. Uh, publishing this, but, um, I'm sure somebody will figure this out.
(24:47) Okay, so the two main challenges that I have is, one, a single, a single trigger app script can only run if you do it in a sheet. Like if you do a custom function. And a sheet can really only run like 30 seconds or at times out. So like sometimes now my prompts time out because now it's like taking longer and longer to like, Send and get a response, but you can increase that to six minutes before it times out if you do a installed trigger.
(25:17) So instead of using just a custom function and app script to just send the result, send the prompt and get the response, you can do an installed trigger, which is fairly basic to do. It's sort of just going into triggers and adding a trigger and saying, uh, I want this to trigger at this point. Um, And so you can increase that to six minutes, but six minutes is still a limit.
(25:43) So even though 30 seconds is small and you're like, okay, I have overcome the 32nd limit, now you have a six minute limit. So with this kind of problem training that I want to do, I have a feeling that like you're gonna get to points where you wanna do like 5, 6, 10 times, and if it's already.
(26:03) If it's already timing out on 30 seconds regularly with just one that means at six minutes, I'm only, I'm limited to at most 12 things. Okay. So like are there other ways to, um, to get around this time limit? And there is, it's whi which is you create multiple different functions that operate in different ways.
(26:30) So like, W you trigger the trigger from another trigger instead of one trigger that then you have a six minute limit. You say, okay, I'm gonna run this app script. Then when it's done, it's gonna do this thing, which then triggers another app script. But, but Google Sheets is, and Google Sheets and Google itself.
(26:51) Like the company is pretty smart and they have these limits that some of these triggers are not triggerable by a, a trigger itself. You have like a user must execute this. So then I'm like thinking, oh, well then, then the sort of way to get around this is to have a operator where instead of saying, I'm gonna do this two word, uh, prompt and then get out 10,000 words or something, um, You sort of have an operator that walks through a few of those steps, which then you have unlimited number of triggers, right?
(27:28) If, if it's a human triggering these things. Um, so that's one problem, which brings me then to the second problem that I haven't been able to really, maybe, maybe I have overcome it, but not in my head yet, um, how this data flows. Is not like a normal, um, data set. In sheets. A spreadsheet, like most people in the most ways that you use spreadsheets is you have headers and your column is relational related to each other, and then rows are related to each other.
(28:08) But if you have, uh, some, some data that is, Like you have an in one input, but then that input has, let's say five results, and then each of those results has five results. Then each of those inputs have five results. You have a multiplying number of pieces of data, right? Yeah. So like talking over it, what, what you could theoretically do, and I've.
(28:43) Again, this is theoretical, not really, I haven't done it yet, is reverse engineer how many things you're gonna need. So if you have five pieces of data that create five, another five pieces of data, then another five. That's just five times. Five times five, which is 125, right? Is that, is that the correct math? Five times five is, I'm, I'm really bad at math, but, but I, I know it's a lot because that, there was a time I, I tried to do like a flash, uh, mobile game with mm-hmm.
(29:12) With each, uh, screen would have three options to choose from, and each of those would have three options and so forth. Yeah, I didn't quite get really far to, to just realize it was a done more work than I anticipated, and then I just changed the idea, so, so what ends up being is like, I. Okay, so you C you absolutely can reverse engineer this, right? You can say, okay, I'm gonna have a maximum number of five options, which have then five options, which then have five options.
(29:45) Okay? I will at maximum have th 125 pieces of distinct data, and so you can sort of. Create your data flow, knowing you're gonna have distinctly 125 things that you have to like combine here at the end into this whatever blog post or whatever you wanna make. Um, that's all well and good until you are like, well, in some cases I want three.
(30:11) In some cases I want four. In some cases I want to like, it doesn't. Theoretically, I sort of get to this point and I just get sort of tired. I just, I'm just like, is it worth it? Like, I literally ask myself this every time I go through. I'm like, is it worth it? Shouldn't I just like write a better prompt? Like, is it is okay? For instance, is prompt training going to result in something that is.
(30:40) 100 times better than if I just worked on this single prompt, like cuz now you can write really long prompts, whereas like, I guess, mm, a year ago or so, you couldn't, so there was this sort of limit of prompt you have to put in. Now if you now, um, we're, we're talking now May, 2023, but maybe in a year there will be no limit.
(31:07) If there is no limit to the amount of information that you can put into a prompt, then this sort of prompt chaining doesn't really matter, right? Yeah. But it's, it's, it's still like great learning experience and, uh, all that. But, um, ha have you, have you heard of the auto J P T. Right.
(31:30) Yeah. So I've heard of this. I haven't really like, looked much into it of, uh, is it, um, agents which are like it, it'll, it'll be some action it will take. Right. Well, like I, I'm, I'm the. Basically the wrong person to talk about, because all I have is sec secondhand knowledge from my smarter friends.
(31:50) But, uh, they, how they explain it to me is that you give it a goal and it figure out figures out the rest, like the way, and it basically, it should create, uh, multiple prompts, uh, itself to, to achieve results. Like it, it could basically, uh, create a, a full. Application of sorts or website with all the sections, uh, like basically chaining prompts mm-hmm.
(32:20) By itself. But, but yeah, again, I, I just talk with my friends about it. I, I don't really have any actual facts, so please don't, uh, whoever is listening, don't crucify me for this. I might have it all wrong. Right. So, so to sort of sum up what I. Think the problem is, is not necessarily the code itself, but rather like the information, um, construction.
(32:50) Like how is, how do, how do you, so the question that is unanswered is like, how do you design a sheet or a series of spreadsheets that have some actions that are going to take place without the human? In inter looping or that human is interloping, is it on one tab and then there's multiple tabs in the background? Or is like the human have to go from tab to tab to tab? Um, that, that's sort of the, the pain point for me at that point.
(33:24) And with or without, you know, autonomous agents doing some action, um, Sort of spreadsheets are again, a curse and a blessing. There is some basic structure to a sheet, a spreadsheet file, right is, yeah, you have one initial spreadsheet table or tab, and you can create more tabs. But then there's the other side of app script.
(33:54) There's the app script where you can write app script that is uh, bound to this file. So there's some good bounding things that we just can't get over. We won't ever have to get over. You don't have to do H T M L. You don't have to do c s css. You don't have to style it. Different, differently.
(34:14) But like structuring data is pretty difficult at times, right? Like. Um, spreadsheets don't seem to be good relational databases. You can create a relational database in a spreadsheet file. It's a little clunky, but you can do it. Um, and so the, the design of the information is, it's a pretty big pain point.
(34:42) Yeah. That, that is true. I, I actually had had one project idea. That I was, um, supposed to use Google Sheets as, as kind of a, a database, but, um, tomorrow I've thought about it. Tomorrow I've leaned towards, uh, to just having to Google Sheets as, as a place where I could, uh, import posts to WordPress from instead of using it as a, as a full database for our custom website.
(35:17) But the idea was that I could scrape in the information and, and then I could, uh, like dynamically create the website with that information for, for each, yeah. Like, uh, row being a, a, a card for, uh, the example I had in mind was listing all the cafes in Finland. So th this is, this happens a lot there.
(35:39) There is a. A sheet, a single database sheet, and then each row is a single page on a website. A lot of people do that. They, they'll use programs like Glide, uh, spread Simple. There's a few programs that take rows of data, and it's like the title, um, the head header one, text, some image. You'll have a U URL to some image or some theme of a, of a image file.
(36:09) Um, And, and, yeah, you can do that. I, I did without using Google Sheet. Okay. So I, I created a website, a a, a directory, a a searchable directory. But I used Google Sheets to design the data, and then I exported it as a CSV file and uploaded it to Algolia. Algolia is just like, just such, oh, yeah. Yeah, I know Algolia.
(36:34) Yeah. Yeah. And so that was like a really sort of a cheating way to use Google Sheets to gather the data, but then set it as a static set of data in Algolia, use this other program since then. There's sites like Glide and, uh, spread symbol that do all the technology for you, but, and you can still keep a Google sheet as your main data space.
(36:59) Um, yep. I, I running tried out, uh, spread simple as, as the mm-hmm. M v P prototyping, but it's cuz I want to scale it. It, it would get, uh, expensive really fast. Yes. Uh, then some other options are if you're like, okay, I just need to read the data in like, Like spread simple and glide in these, these things, they create the page for you.
(37:28) Oh, there's also sheet to site, which it'll, it'll create this, the website, and then it'll read off of the Google sheet. If you, however, have a website that you're like, I just need the data in the website to be in a Google sheet, and I just need to read the data from the sheet, then there's, uh, there's tools like, uh, sheet Sue.
(37:50) S e, oh, sorry, s h e t t s u or sheet C, um, or sheet two a p i, which turn your Google sheet into an p i. So then your, your database is just this, a p I call from your website. So you design your website, you create your website, and then all you do is, is get the data from the sheet instead of like glide and spread simple, sort of do all the business logic for you.
(38:17) You have to sort of do the logic yourself, and then it just reads the data from the sheet. That's really cool. But the coolest part about this is that those tools like have a price, right? There's gonna be like 20, 30, $40 a month, and then probably some increased amount. The more data that you use and the more bandwidth that you use.
(38:40) However, in app script. On in app, like in literally bound to a file itself. You can create a url, uh, well, you de you deploy Cript to a web app and then you get a URL that you can access that sheet. So you don't need to use a separate, um, uh, service. You can do this all from Google Sheets itself. And I even have videos, which, uh, there's videos called like embed a.
(39:13) A number or embed a text on your website from a Google sheet, and it uses this idea of a app script that you deploy as a web app and it's on Google servers and you get a URL and then you put that URL into your website to say, Hey, just read this piece of data and you get that data into your website.
(39:35) That sounds amazing. So, so basically, If, if I had a, if I had a use case where, where I have created, um, let's say a cafe website for one of my customers and, and they would like to, uh, they would like to change the opening hours, for example, or, or, or a lunch menu. I could give them, share them a Google sheet where they had, uh, input fields.
(40:03) For these things. Yep. And then, then the website could fetch the data from the sheet to the website and, and it fetches it live. So they make the edit and it's live. Like you refresh the page and it's done. It's there. I mean, it's incredibly insecure, right? Like, like, think about this, right? It's a u url.
(40:21) Your website is just like calling this u l. That has no password protection. Right. But like the Google sheet is not shared open. It's the, it's going through this url. So someone else who wants to read the operating time of your cafe could, but they can't access the sheet. Like they can't open the sheet cuz it has like only the users who are given access to it.
(40:46) So it's insecure in like general internet terms, but it is pretty secure because you can't access the sheet itself. Except unless you have edited access. So, so, so they basically gets us to read what's already public information in this example, right? Right. So that's what makes it insecure is like anyone can read it and then, because that URL is on your front end of your website, so anyone can go and grab that URL and use it somewhere else.
(41:18) So, In that sense, it's insecure, but like Yes, exactly. It's already public information, so like do you need to like hide it? No, not really. Yeah, I, I think I'm gonna, I'm gonna take a deeper look into this and I, I'm, I might already have, have a lot of use cases for it because I've, I've noticed it with, um, I've been creating websites for my customers for, I think, Six years now, and one of the most common things is people are afraid of logging into WordPress at making edits.
(41:53) Even if I really just create, create a custom menu for them at, at one, it's one time I, I spent like, um, almost 50 hours creating a dynamic menu for, for a customer that would, you could put, just put in. In input fields, it's a whole menus for, for like, uh, three weeks forward. Mm-hmm. And it would automatically change when the, when today changes.
(42:23) So it would always have to dynamic, uh, current menu for the day. And I had to plan that. We could just broadcast it on, on all kinds of screens to, to make sure people know what's for lunch today and. Stuff like that, but they, they never wanted to use it because it was in the WordPress and it just felt difficult for them for whatever reason.
(42:49) I never figured out why, why people are so afraid of it. But they, they, however, do know how to open up Google Sheet and put in information. So this could really solve a lot of problems, uh, with the client interaction. I could just send them the, share them, the sheet link and I would have some instructions and in fills ready.
(43:16) They would just, I mean, the biggest problem is going to be data validation, which you can bake into a sheet, right? You can right click data validation and say, you know, only put in numbers here. Only put in the wor, the only either select AM or pm. Uh, you, you can. Sort of, instead of saying, Hey, just type in the time here, like 9:00 AM or 9:00 PM you can give them the, like a clock and have them change the numbers and maybe even not type in anything, but select the numbers one through 12, select am pm select, uh, open, close.
(43:53) Um, And, and you don't necessarily have to say, you know, fill in this field where it could be anything. And they start typing N I N E A M and you're like, what? Yeah. Um, so yeah, with Google Sheets you can, again, the curse and the blessing, right? The curse is the, the blessing is, oh, here, I'll show you, share you this sheet, edit this document.
(44:17) The, the, the curse is, oh, you can type in anything you want here. Okay, well add some data validation. Okay, wait, fix that. But like, oh, now anyone who has access to their, you know, their, uh, email or their Google Drive can now access that. So, yeah, it's a little bit of a, Security, insecure, open kind of thing.
(44:40) Issue. But I don't know, I guess email is better than nothing. Yeah. And if, if they have to. If, if somebody gets the access to their Google Drive, it's more than likely that they find out, uh, like a bunch of stored passwords there anyways. So, yeah, I think you have more problems than updating your opening and closing times of your restaurant site.
(45:07) That's one. One of the, the happy things I've noticed is that people have started using the two factor verification in the logins, so, Yeah, that helps a bit. Also, you know what has helped a lot in the last few years is a lot of people are working from home, so they're not, uh, so there's not like work stations like, you know how there was open offices and, and like, oh yeah.
(45:35) Computers used to be like, oh, you left your computer on, was like a huge security risk. Like someone could just walk in and start using your computer. It's like, no, that doesn't happen anymore. Yep. And, and same with the, the password sharing and the notepads and, and sticky notes on the desks. Yeah, like there was, there was uh, one time where I worked in, uh, In a pretty, pretty big company in the financial sector.
(46:01) And, and it was, it was crazy how, how many people, just like, they, they didn't, um, when they had to import a code sequence for the BitLocker system and then they had to import the password and all that, uh, they did it every day, but they still had, uh, Sticky notes with the, here's my pin code.
(46:21) Mm-hmm. Here's my password. So basically no matter how, how locked the computer was or if it was turned off, anyone could just walk there and, oh, that's all I need. Exactly. Everybody's password is on a post-it note on, on their monitor. Yeah. It's, yeah. And right now, even, even if they have all the, the same stuff on the sticky notes when it's in their home.
(46:46) There's no thousands of people walking past their workspace nowaday days. Yeah. I, I, I, thankfully, you know, one thing is a lot of people use spreadsheets to list their passwords. They'll just have a spreadsheet of passwords. I, I don't have that. I, I've heard others will have that. Um, so this has been fun.
(47:06) I hope you learned that you know more about spreadsheets than you thought you did. Yeah, yeah, yeah. And you gave me one, one really great idea. I'm gonna definitely try out. Yeah. And there is a video on better sheets for that. There's actually two, there are, there's sort of three videos that have the word embed in them.
(47:30) One video where it, it says, embed a headline on a, uh, in a website. That's probably the best one to watch. There's another one called like, embed a number. If you just wanna like flip out a number, there's. Fairly the same. And the sheet is available. So you, you don't have to do the app script.
(47:47) You can watch me do the app script, but then just copy the app script from that sheet. Um, but then there's a third one, which is, has the word embed in it, but it's not necessarily what you want. It's embed a form in a website where someone can enter an email address and it gets added to a sheet.
(48:05) So it's sort of the other way around. So there's embed a headline. Embed a headline from a Google sheet, and then there's embed a form to capture emails to a Google sheet. So those are two separate videos, but they all have the word embed in them. All right. I I, I listed those down. I'm gonna take a look at them.
(48:29) Um, amazing. Yeah, and, and the best thing too is you don't have to be an expert at spreadsheets because the app script is there for you, like you'll see. It all there. And then you can co copy the sheet, copy the template and use it. Um, but you gave me, thank you for telling me about prompt chaining, at least the word.
(48:47) It's so hard sometimes knowing that like there's an action you want to do, but you don't know the word for it. And you're like, what I Google for didn't really, so this is gonna be. It is, this exact, uh, thing has been one of my struggles with the, with the sheet. Uh, because I, I have the idea that, uh, somebody must have done this some way, but when I don't know how, what it's called and what formula is, it's, it's really hard to look up.
(49:14) Well then, you know what AI is. AI is, it's also hard for me. So even though I know how to do something, I don't know how to name it. I don't know how to say like, This is the action that I'm taking. I'm doing this thing and I'm showing you how to do this thing, but like, what is the word that I need to use in the title so that you find this thing that I'm doing? It's hard to title things as much as it is as hard to find.
(49:44) W find the words to say, I want to do this thing in a spreadsheet. It's equally hard both ways. I had, um, yeah, I had a friend like email me and say, Hey, Here's like a video idea for you because like I was, I was looking for this thing to do and I found this video that does this, and he sent me a video that I was like, oh, I've done this in a video, and I sent him the video that I made.
(50:12) And, and I'm like, oh, here's how, uh, here's the video that you wanted, but you found some other video for it. And he's like, oh, you should probably name it the one that I was looking for. I'm like, yeah, but like so many people will search for so many ways. Like you can name it one way, but there's 10 ways to say how to do that thing.
(50:32) Like combining two, uh, two. Oh my God. It's like two columns into one column. Like do you say Mer? I'm gonna merge two columns into one column because merge is literally a different thing. It's like merging cells. But like, is anyone gonna use the word combined? Like, I wanna combine first name, last name, or I wanna put together my first name or the last name.
(50:57) Or they'll say, I, I want to make a full name from a first name and last name. Like, there's also other use cases for combining two columns into one, which is like, oh, I wanna combine multiple columns into one. I wanna, I wanna take all the parts of a street address and make it one street address.
(51:13) It's like, there's so many ways to say that. It's crazy. Yeah. The, and the. That's the thing, because the, those were exactly the first words. I, I, I thought in this context was like a merge. Well, it wasn't that and combined, well, not exactly that either. Can I find this, this, this word that I didn't know existed? This, uh, I don't know how to say it.
(51:34) So I'll just, uh, it in that chat. Yeah, that's the one. Yes. I, I had no idea how some art exists with who, who uses that work? I don't know where that word came from because, yeah. Now I've started to use concatenate to say, combine text together. Cause like, cuz that's the formula name in Google Sheets, concatenate just at, puts text together.
(52:01) There's also crazy enough, there's literally the, the, uh, formula called text join, which is like, okay, is that join? But like with the. Formula join. You can join together. Text. So why would you need another formula called text Join? To just join Text together? When you can join Text together with join.
(52:24) But if I'm wanting to join text together, isn't that the same as concatenate? Like those are three separate forms. Like why would you need all of these things? It, it sounds like it's for the same thing, but just can you find out just slight differences that, that actually make it clear to use one in one use case and the other in the next No, I don't think there's a clear use for any of, um, even worse is concat concat.
(52:58) So like Concat is a short version of concatenate where the formula literally takes only two arguments, but. Why do you need a separate formula for only two elements? If concatenate also concatenates together? Two elements or arguments like why, who? Who's the product manager who was like, I have two. I have spent so much time combining two pieces of text, and I don't want to spell out concatenate every single time, but they must have come up with Concat before.
(53:35) They came up with auto complete because now when you go into Google Sheets and you type in equal and you just say c o n, concat and concatenate come up, so you don't have to type in concatenate anymore. What is, is there a difference between the two? Yeah. Conka only combines text of two, two arguments, whereas concatenate is multiple AR arguments.
(54:01) But you can use concatenate to combine two el two arguments. Oh, no. So, so if somebody is just like Googling around and it's, find the concat first, but they need to string three or four arguments. Yeah. It's to stand up scratching their head because they never write the hole. Look at this, you can also string together concat.
(54:23) So like if you concat one argument and the next argument, the second argument could be a concat of two arguments. So like there's probably someone who is nested concat because they just like never figured out that concatenate is just like, cause nobody knows the word catey, right? Yeah. That's not a normal English word or or word at all.
(54:50) Yeah. That, that's, that's actually what I said to my brother when I first heard the word and, and he was like, uh, being a programmer, he was like, well, just, I used it every day. What do you mean? So there's just one person who use it. There's some languages. Yeah. Yeah. There's some languages that have it.
(55:08) Like programming languages. Yeah. But nobody else, uh, That, that I've met, uh, has had ever heard it before. I just go around the desk. Do you know what this word means? Yeah. So gen generally speaking, to wrap this all up, we are all on a spectrum, on a continuum of knowing and not knowing and like there is nobody that is knows more than another person.

(55:36) It's just like you've experienced these formulas and Google sheets in a different way. We're all different. Uh, unicorns. We're all e each individually a unicorn in Google Sheets. We're all the, the combination of all our past experiences. Yeah, and, and this also confirms my, uh, my thought of, uh, that when I'm open about not knowing, uh, basically anything, uh, about the subject, it's the, it's the best way to start learning something just like happened today.

(56:13) Yeah, just be open to learning and you'll learn if, um, thank you so much. Thank you so much for, for chatting, for talking about spreadsheets for so long. It's so much fun. Yeah, I had no idea, uh, that we had so much to talk about spreadsheets because I, I never find anyone to, to actually talk about spreadsheets.

(56:37) Thank you. Hey, thanks for watching that video. Watch here for all the episodes of sheet talking here on YouTube. And over here, watch the latest video from better sheets. Enjoy. Thanks for watching.