in this video let's try to automate Google Sheets using chat GPT so we'll start with some basic
actions and then we'll try to build upon that and make it more complicated so I have this little
table here let's start with something basic let's try to just copy the data from here and maybe
paste it here something basic like that so I'll just go ahead and first of all open the script
editor so extensions apps script here I have this function I'm going to rename this to Main
and basically we'll just grab some code from chat GPT and paste it here so the first thing is going
to be we're going to ask chat GPT to generate some code to basically copy the data from here
and paste it here something basic like that so I'm going to open this and I'm going to be using
3.5 GPT on this one I do have access to four but I'll be using 3.5 let's just ask a question
create apps script code
that will copy data
from sales tab range A2 through C let's just go see what the range was A2 through C8
and paste in G2 so let's see if that works so I'm going to go ahead and run this
okay here we go so I'm going to go ahead and copy this code so notice I'm not copying the whole
thing I just copy what's in the function so not including this last bracket and this first line
I'm going to go to my code editor and we'll just paste it right here within our function
so as you can see it's grabbing the sales tab it's doing Target sheet as the sheet were to
paste that's fine this is the range A2 through C8 so that's that so then it's gonna go to here
and paste so let's see if this works and then we'll see what to do next so I'll just run this
so as you can see that copy paste worked just fine very good so I'm going to get rid of this
so let's try to go back and make sure that it automatically finds the last row here instead
of us telling it has to go to line eight so I'm going to go back so let's just say
instead of A2 through C8 find the last row automatically
so as you can see it was here concatenated as the last row
that should work so I'm going to copy this we'll go back to our
script and I'll basically just replace this it's probably worth to mention that you
need to have some understanding what your code does when you do something like this
because this potentially could generate any code right so that could be some code that
goes and deletes some files in your Google Drive who knows so you have to have some understanding
what your license could do before you start copy pasting the script but this looks fine so
this should work so this point E5 for example add a couple more lines here and rerun this
that should hopefully grab see this data from here and paste it here and it did find these
new lines and it automatically has this so the next thing I want to do I want to now instead of
copy pasting this here let's copy paste this in a new tab so let's add another tab here
let's call this backup or something
and I'll just copy paste this names
so now let's say we want to paste it in this backup tab in a column so I'm going to go back
foreign so I'm going to say instead of pasting in the same tab I want to paste
in backup tab let's capitalize this back up
starting in a column
in the first empty row
something like this
so there's our code so let's go ahead and copy this I'm going to go back to our script editor
paste it here so okay now it gets sales tab it's going to get backup tab
that's good we're going to get our data in the last row so far so good that's our source range
then we have our target range which is basically going to be from that Target sheet it's going to
start from a column and it finds the last row in that column and does plus one and basically that
will be one line after the last row and then we basically just grab the data and move it
over this should work let's just give it a shot see this is this I'm going to rerun this thing
so this should now put that data
see here now assuming I run this code again let's actually get rid of a few lines here
so now it should grab this much and put it below Jennifer here let's see if that works
and as you can see it worked see below Jennifer we got from Isabella through Jane which is this
stuff so it goes right below the last available line and it's going to add those looks very good
so next let's make sure we clear this data after it does the copy paste so I'm gonna go back
so I'm going to say after pasting clear the data
in original tab or sales tab let's just call it this way
all right so I'm going to copy this let's go ahead and go back to our
script and I'm going to paste it here
so again it's going to grab sales tab it's going to grab backup tab from our active spreadsheet
which is the same spreadsheet then it's going to find the last row in our source tab which is
this one and it's going to create this range from A2 through C whatever the last row ends up being
and then we go to our Target tab and paste the data one line before
that line and once we do that then we got this Source range which is what we're going to clear
this looks good this should work just fine so I'm gonna go ahead and again let's just start
over by deleting this so assuming this is our original data we should be able to run the script
and that should grab that data see clear it from here and now it should be in this backup
with all this data so now if I for example create a few more lines
in our sales tab let's change these numbers a little bit
as a matter of fact let me change it in a way so it's easier to spot
so now if I run it again hopefully it's going to grab all of this and put it below here
and then it will clear this data here so let's go ahead and rerun the same script
go back and take a look it's come from here if I go to backup you
can see these are the new lines for this
so now if we run it again now the sales is empty let's see what happens when there's nothing here
so it did run
and as you can see what happened here we cleared these headers
and we moved them over here so I'm gonna undo this Ctrl Z command Z and let's actually
add a check in our code to make sure that there is information in sales before we copy paste
before running this code make sure there is data in sales tab
I'm not sure if we have to be more specific and say
data start in line two and after or will it just figure it out let's see what happens
see there it is this that handling it says if the last row is less than two
then return meaning it will stop and not run the code this should work just
fine but maybe instead of just return let's also add some message for the user
let's add a message
for our user when there is no data
I'm not even going to mention what the actual message should be let's just see what it does
okay there it is so it didn't alert that looks good so I'm going to copy this go back to our
worksheet here and we'll just go ahead and grab this paste it here save it
so this point I don't have anything here so this would be the case when it should
basically not do anything and give us an error message so let's try to run this
and what's Happening Here the message is actually showing up right now but I'm in the code so we're
gonna have to go here see it says no data found in sales tab if I press OK it will stop and not run
any of the code so at this point if I have some information let's actually try with a single line
so this time there should be no message
see the line is gone and Jack is here good so let's finish this by asking it to add
a menu for us so that we don't have to run it from here but we can run it from here
create a menu called custom macros
and add this
function to that menu
so this is that function and this is the menu so I'm going to copy this go back to our
script go below this and paste it here
now you can see here it says copy sales data that's because the function it made here is called
copy sales data now in our case that function is called main here so I'll just go ahead and
change that and let's just save this so because this is going to happen on open
we have to refresh this to see that menu here but I don't think we need
this anymore so I'll just save this close this and we'll just refresh this
custom menus load slowly so there it is custom macros if I open this
see there's that copy sales data and basically if we do have something here
no data found in sales tab
it gives us this there's nothing here so we get the error now if we have something
then it should actually run and add that to our backup
here seems to work there it is so I started from basically scratch as you can see the entire code
in this particular case was created by chatgpt and again this was the version 3.5 so I was not
using version 4. and basically I started by this basic starting point and then I kept asking for
new things to be added to this code or modified and as you can see it works just fine and that
should do it for this video thanks for watching please subscribe and I'll see in the next one