Turn Google Sheets + ChatGPT into your SEO automation machine (no coding needed)

What if you could save hours every week, every single day by automating some of the most boring tasks that you do in your daily workflow? Well, that's actually exactly what we're talking about today. Hi, I'm Daniel Lightna. I'm an SEO consultant based here in Tur Switzerland and I'm specialized on the German speaking market. And to be honest, I rather work on an SEO strategy than coping some documents from one folder to another in drive or doing some repetitive boring stuff. And that's why I actually started automating some really cool things. Two of them we will see today. I brought with me two cases that I actually automated things that I really use on a daily basis and that save me well every day a few minutes, but in a year that's a lot of time. Then you look at this video. Really important. Don't just look at my cases and think like, well, I don't need that and I need that. Really think about your dayto-day. Think about what are the tasks that you are doing all the time

that are really boring. Maybe they're small, maybe just 1 minute, 2 minutes. But if you could save two, 3 minutes every day, that's a lot over here. So look at your day today and always think like, well, could I automate that? And then just try it out. So that's what I actually want you to learn today. And how I said I poked you to case that we will actually look into we will also look into how to use appcript which sounds really complicated but it's really really easy and really before you think like well that's too much for me I can't code you don't need to code JBD is doing all the coding you just need to know how to prompt and even that you could just let JPD write the prompt and how to use this app script and everything and how to press a button I'm sure you can press a button without further ado let's jump into the tools we are actually using today. What we need today is Chipt. I assume Chip doesn't need any explanation. The only thing, do you need the pro version or the free version? I use the pro version. I think it's better because with programming, you're going back and forth a lot and

you might get to your limit. But to be honest, I haven't used the free version for one and a half years. So, I don't know what's included in the free version. Now, then we will need the Google Drive environment. We will need Google Docs. We will need Google Sheets. We will need Google Drive. In there we need the appcript. Appcript is a coding environment in Google Drive. And you find it under extension appcript. You find it in the Google Docs document. You also find it in the same place in the Google Sheets. The good thing about it, it's like you have the macros, you had them also in Excel, but there is a big different mar and the appcript. Maros in actual were just made to program in the document. So you could program something in the Axel or in I think it was in word as well but you couldn't communicate between word and excel. The great thing about appcript is it's in the whole Google drive environment. So you could actually communicate you can move talk

commands. You can do a lot of with it. Um, as long as you're in the drive environment and if you have a look at what it looks like, it's actually if you go on AppScript, you really enter directly into the coding environment. And don't be afraid, you don't need a lot here. You just need two buttons. You just need to copy paste here. Delete and paste. You need the save button. Really easy. No, and the run button. There are other functions like the debug button, which would be if you have an error to find out what the error is. I don't even use it to be honest. If there's an error, I copy the error and tell J to be PD there's an error and he finds out the error. Maybe it's a little bit back and forth that you need to go. But almost in all of the cases, he found the error, he corrected the error and it worked fine. Never needed to debug anything. And that's actually everything. The rest you can there are functions where you can program it, but that's just more advanced. You can play with them like program some functions that work like every on midnight or something but for now we don't look at this for every case

that I have. You will actually get access to the drive where you have the two cases. Every case has a Google sheet with the prompt where you can actually just copy the prompt like it is the one that I used and you get a link to the conversation. The conversation is really you see it here. I start with the prompt. Then it's writing the code. At the beginning in this case, he answered some things in German. Although my prompt was in English, but HBT knows very well that I speak German, English, and Spanish. So sometimes he's just mixing up the languages. But later I tell him to chess also in English. Here you get the code that I copied. Then there was an error there. I tell him to switch to English. And you can just read through it and see how it actually worked and how I told communicated with him. Well, there's an error. Or might it be like this? That's my thing. So you don't even need to think to be honest. And you have this for both examples. Always prompt and the link. And with that we are actually good. So let's start and have a look at the first case

I pro. The first one we look into is templates. What's the case here? The thing is I have a template for the keyword research. I have template for the content audit. I have a template for the snippet audit. I have a template for the technical audit. I have a lot of templates on my drive. Always when I need to do it with for my client, I need to copy the template and move it to the folder of the client and then to be then able to work with the template. This sounds like really easy, but it's still right click, copy, wait until the copy is done, move it to the client folder. In the client folder, rename it and maybe fill out some stuff. So, I was like, that's really, really annoying. Shouldn't I automate it? And that's what I did. In this case, I made a template, an example, so you could actually see it and didn't give you all of my 100 templates that I have, but it actually looks like that. I have a Google sheet that's called automation poppy templates. And then I have a folder, in this case, it's just here that has every

template in there. And then obviously somewhere on my drive, I have a client folder. In the automation sheet, what I have now is three tabs. A overview, a lock, and templates. Let's start with the templates. In this case, I deleted the template ID, but I have all these templates for example, and in this case now, I have the test template, the version that's just for me, the name I gave it to see which one I select. You will see that in a short time. And then the template it. That's something that's really great in Google Drive because every document has its own ID which looks like this. And I'll show you now how you get this it. If you go to the template example, that's this one. If you look up here, you have from D slash and here you have a number until/edit and that's the document ID and that's unique for every document that you have on your Google Drive and that's the number you need. So I just copied it there to have the template ID and then

in the overview I have my client. So I have actually one row for every client. Then the client folder ID also every folder on drive has its own ID. So in this case the client folder if you go there you have here the ID and that's the ID. I just cop it here. Then I have a drop down that I actually made where I can choose which of the templates this is connected to here. So I can just select this one. If I want to have a new template I need to add it there. And so in this case we just use the test template because everything else would make an error. And then I fill in the date which is for me important for the naming of the document. Then I have a lock where I actually don't do anything. ChibiT is filling this lock up. So I actually see what happened and can actually because there is in details there will be the link or the error message. So I could actually go directly to the document. Now we have this prompt where I actually say you should write a appcript that automatically copies the

pamplate document in the correct client folder and renames it. We should be in the active row in the road where I am with the cursor. It should take the client the client folder ID the template type and the date and move the template. That's where we have the ID in the template sheet. And you can actually read through this on your own. I want the lockdown. So everything I just explained you, I want him to do. So that's what actually is explained in the prompt. I already made the prompt. I already tested the code to know to see the errors. But I'll show you now how you actually add the code to your project. And there we are in this chat. And the code is up there. There are two parts of the code because I wanted to have a button in my menu. And the button in the menu is always programmed like this. And you edit separately. So now we go here to the document and we say extensions appcript. The app script is opening. We are ending up here and we just copy this part. So that's adding the menu item. But we don't have the code still. So we need to go up there

and copy this. And that's the code that's actually copying the template. We put this underneath. So we actually have we can close here. Have this functions. We have the menu button. We have the actual copy template function and the log results. You don't need to understand what's happening here. Let's save. The menu button won't be visible automatically. You see, we don't have a menu button here. But if we reload this, the app script will be closed. And now waiting a little bit. And there we have the button. From now on, every time I open this document, I will have the menu button. But for the first time when I open it, I won't have the menu button. You need to tell him you want the menu button because if not, you could just go to appcript and run the code. you will need to choose this function because we don't just want to add a menu and run it. But I think it's much nicer especially if it's not just you using the app script but someone else if it's a menu item. So now we don't need to go back to the code if there's everything

good. If there's an error we need to go back and now we have this client we just go in the row. We can click anywhere but we need to be in this row. If we are in the next row, it will get the information that we could try because it should bring an error message required field in row three. The thing is it needs the name of the client, it needs the folder, it needs the template type and it needs the date. So that's now an error that came because we are in an empty row. If we go now on the right row and press the button, the script is running. Finished. That's everything. We just copied a document to the client folder and renamed it. Let's see if it actually work. Let's go back to the client folder. And there it is. Today that's not the date. So we actually need to change something there. Ah, yeah, because I didn't. We can try it again now with the actual date. Month day. So you see that I didn't just invent something. I'm going to copy it again. Running the script. And what it's also doing is writing in the lock that we just did these things and also the error message. We will have a look at

the log in a minute. Finish script. So now in the lock we have oh the errors message was not written. Um you could say them to him that he should write the error message. We see the time we see the client the template that it was a success and that's actually the link to the client. And in the client folder we now have this and this template and that's the copy of the file that I just showed you. And that's everything. And with this I could just go well I need a new content briefing. So I go there like well content briefing press a button add the date and then it's in the client folder and that just saves me a lot of time to be honest. So that's everything to this example. The next use case now is the content preview. We have a content plan and we have a content brief template. If you look at the content plan, it's just I made it for my travel blog where we normally have the URL if it's a new post or an updated post. the keywords. In this case, I just added some main keywords, add the search intent, I added some competitors, why

are we writing it, and what CTA we actually want in the blog post. And then we have the content briefing, the template that we would actually give to the writer where we have to fill in the information. All this information is already in the content plan, but you have to fill it in there. The topic, the search, the URL, the page type, we have some additional information like who are we writing for, the writing style normally, and then we have the keywords and we add the competitors. This would be the document that we sent to the content writer. Now, if you don't have an automatization, you need to go there. Well, that's the URL. Copy paste. That's the recommendation. Copy paste. But you can do this with a button. In this case, I already put the code in there. You find prompt here, which is really detailed. What's really important what you see in this prompt is that I is that I give him everything like every one of these fields where I see that some of them are German but that not important. I tell him all these is are the placeholders. I tell him exactly up here these are all the columns of my content

plan. So he actually really knows what actually is in the document to write the script. If you now have a look at the script, I have it open here already because I had some errors before trying it. We also have on open, we have the menu button, we have the content prefing, we have the wrestled and that was just for testing. I can delete it now. And if you go to the conversation that I actually linked in the prompt, you see how I have the prompt, the code, that's actually the one that's in the document now. I had some problems and some errors. Now, you can see I really just give him the error and then try to solve the error. What it's now doing if we are in this row and we press the button, it should copy the content briefing template in the same folder as the content plan is like you see here. I already tested it and fill in all the information. So let's try now the inter [Music] script again like in the other example with the menu item that we added and the content brief is created successfully.

If you see go to the lock now we see like this was before when I had some errors because it seems like or at least that's what I understood talking to catch that there is a limit on how many documents you can copy in a certain amount of time. I now made a break of half an hour and it worked again. So, just that you know, if you ran into a service error drive, it's that drive is actually blocking you for half an hour to copy a file because it's like, well, you did some crazy Let's just stop it here. But it's working now perfectly fine. You have here the document linked and here they are document. If you go now to the two documents, that's the one I just generated now. We have all the information in there. the topic, the search intent, the URL, type of post that it is, why we are writing this, the call to action, we have the keywords. We didn't have secondary keywords, that's why I didn't fill it out. We have the competitors, which in this case, I just filled in my own uh URL. And in the Scandinavian roots, we have the same. All of this is filled in like you saw

with one click. If you have more secondary keywords, also them, all the competitors. And it didn't take me any time. It's really easy. you need to set it up once and it's really easy to program it if you have a nice prompt to just set it up once and then you can always just fill it out. In this case, if I want to do it for each of them, I need to be in the row and press the button. But of course, you could also say like, well, create me for every row that's filled out content briefing. In this case, I didn't wanted it to do because normally you work with the content plan. You add new stuff. You don't have to make the briefing of everyone at the same time. But that's just how you and your case is. As always, you get everything with the prompt. Here I have the template, the document ID, and I copied all the fields just so that it's easier to copy to JPT instead of a document. But that's everything. These have been two examples and use cases that I really use on a daily basis. You can do a lot of These were just examples to show you a little bit how you can actually interact

from one file to another one. And some things I think a lot of agency and a lot of photoreneurs are doing on a daily basis. Of course, you can go more advanced. There is something called appies and all tools normally have appies. For example, SE ranking has an upy where you could, for example, in your keyword research with a button get in the new search volume. I have this implemented in my keyword research. Um, but also like last week, I connected to the app of my newsletter tool because I'm writing a daily newsletter and I just exported every email that I've written in the two years into a Google part. So now I could reuse it for a blog post or a LinkedIn post. Really just get creative what you have, what you want to do. And the great thing with appies, it sounds really hard to program, but the only thing that you need is the code. And normally JetB knows the upy because there's always upy documentation out there. He doesn't know. You can copy the upy documentation. You can look for it and give him the link and everything. But normally he already has read have

been reading it. You can really do a lot of things with it. It's just the limit actually is your creativity and getting to the ideas what to automate. So I really hope you go out there and try to automate stuff. And it would be amazing if you let me know what you automate because as I said the limit is the creativity and we are all more creative if we learn from each other. You can find me on danny lightener.st. I actually have three newsletters in German, English and Spanish but the most active one is the German one. You can find me on LinkedIn. Just connect there and I hope to hear from you and happy automating.