Automate Google Sheets with Chat GPT Macros & Apps Script

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

Channel: Learn Google Sheets & Excel Spreadsheets

Views: 37,809

Keywords: Google Sheets, macros, Apps Script, Automate, Chat GPT

Id: HO1z4k0QuwQ

Length: 15min 24sec (924 seconds)

Published: Mon May 15 2023