Chat GPT vs Google Bard for JavaScript, Apps Script Code

in this video let's compare chat GPT and Google bard I guess that's how we say it for writing some JavaScript code and I'll be generating some code for apps script for this so I'll go ahead and open the spreadsheet go to extensions apps script and basically I'll try to explain what I need in both and we'll see how they do so in this case I have the spreadsheet this tab called sales and this is my data so let's start with something simple here let's try to create a new column that will have first and last names combined I'll start here I'm going to say I have a spreadsheet let's actually say Google sheet spreadsheet with a worksheet called sales I think there it is and then let's mention that we have first names in column A and last names in column B let's actually capitalize this last names part data starts in row 2. and ends in row 4. see 2 4. I need Apps scripts code that will combine first and last names and output results in column let's see I alright so that's that so let's see what happens so that's our chat GPT code let's go ahead and copy this put it in our function right here let's see how well this worked so it's getting that sales tab then it gets our data from A2 through B4 so that's our A2 B4 so that's what our names are it gets values so that's going to be an array and then it creates a new array to Output the results and then it basically just runs a loop through that data right here and it does some what seems to be concatenation of first and last names zero being column one for first names one being column two for last names and then we push our results to this full name column right here as an array so that's a good sign because we need array of arrays and then we write our values from I2 to I4 so I think this code should work let's just go ahead and run this and let's take a look so as you can see we have our results John Doe Jane Smith Peter Brown in column I exactly how we want it no problem so let's try the same exact thing in Google bard so we'll copy this go back here and paste the same exact question let's see what we get okay so this is the answer so I'm going to go ahead and copy this and let's go ahead and paste it here and see if this code makes any sense right away I'm getting syntax error let me just make sure I did copy things right Yep this is the closing bracket for this and that's how far I went so I did copy this correctly so let's take a look and see what happened here so it started by getting the spreadsheet that should be fine then from that spreadsheet we get the sheet called sales that's fine too then from that worksheet we get range well what it did it got first names range from a column and last names from B column but that should be fine and then it did this combined range and this is where our final data is supposed to go I2 through I4 so so far that should be fine now let's see what happened then so we do this Loop then and we do this first names range which is this get values which is going to be an array of our first names basically and then get the length of that that is correct and then we go I plus plus to basically go one up on each iteration of this Loop and we start from zero so I suppose that should be fine and then what it does here it does this VAR and that is why we're probably getting that syntax error so let's go ahead and remove that VAR shouldn't be here and then what it does it does is combined names range that's this and then sets the value and then it's going to get from the first names range the value in that position I don't know if that's going to work because get value that doesn't accept an index as far as I know so let's just test this so if we do dot get value yeah this doesn't accept an index so this is not going to work so this whole thing is not correct so let's go back and try to ask a follow-up question because starting here this whole thing is not correct so let's just ask this didn't work I get syntax error let's start with that okay so I got a new piece of code and here when I look here see it does seem to have removed that VAR so that's a good sign but it's still doing this get value in this index position which that function doesn't accept so this is again even though it's not going to give us syntax error it's not going to work now let's see what error we get this is pretty much the same code that I made I guess after removing that VAR I believe so let's just go back and paste this now this is uh uh did I copy it right there should be another bracket here yeah I didn't copy this okay fair enough so let's just run this it's going to give us some sort of error let's then give that error back to The Prompt and see what happens okay so I'm going to copy this error paste it here okay let's see what happens okay so it seems to still do this get value thing in the position I this is not going to work again so I'm going to copy this go back here and paste save this so now what it does it did the same thing up here here it goes again the same length of the first names range which should not be a big problem I mean you could do this even though it doesn't make a lot of logical sense to code this this way but then it tries to get the first name and it does this get value get value if you have like a single cell you could get value otherwise this is a range of cells here so it should be get values so you could do something get values here and then do I over here which is that position and then I guess do this here too and get the I in this position something like that and then at this point you could set those values but again you cannot do this like this because this is a whole range here and you're setting just one value when you do set value therefore this again you have to figure out the right place to actually set the value which is why you should probably create an array and place that in Array but it doesn't do that so what I'm going to do I'm gonna ask if it can do it by outputting the values in an array so can we generate an array and then output the results to the spreadsheet so see what it did it created an answer that is completely disconnected from my previous questions so what it's doing it's just creating this array and then it's trying to Output the results but even this is not going to work because it's doing this array not in the right format it should be an array of arrays instead it's just a regular array and that's not going to work so again it's not generating any of this right this is not working out with chat GPT as you can see with a single question I got the results I wanted right away it did this let's try a couple of more things here I'm going to now ask it to get this beginning part of this emails before the email sign and put that in a separate column too so let me just copy this paste it here and then I'll make some adjustments so spreadsheet with worksheet called first names first names I'm not going to mention anymore so I'm just going to say emails are in column C and then data starts in two ends in four we don't want to combine that anymore extract the part of emails before character and output the results in column where do we want to put this let's do J now all right let's try that so I'm going to copy this so by the way this is 3.5 for anybody who's interested this is not four so I'll just go ahead and paste it here okay so sales tab we get the values from the range we create a new array output and then we'll look through that and then emails are in that column in the first position because we're just getting that range for C column which makes sense and then we split using this email character get the first part that makes total sense and then we'll get that what we called apparently username and then we push it to an array output results this looks perfect this should work so I'm going to run this let's go back and take a look there it is see we get the results no problem again assuming that it failed the previous question I had it's pretty much guaranteed to fail this too but let's just give it a shot so let's just copy this question go back to this and paste it here and try to run this and see what we get so I'll just copy the whole function this time and paste it below this as a separate function and that's what it got us this time so that's the function so again it gets the tab it gets the data so far that seems to be fine it gets the range and then this is the part where it's going to Output the range but again we get that same problem right it gets this Loop and then it's going to try to get this in this position I which doesn't exist now this part is fine it will get the email it will split and get the first part of the email that line is okay but the way it's interpreting the actual apps script environment code where it has to generate an array and put the results in an array all of that is missing from here so if we save this and try to rerun this you'll see we're going to get an error because get value cannot have positions like this I cannot have that now if I for example copy and give this exception back based on our previous example it was not able to fix this problem so there's my new code so I'm just going to copy this now and paste it in this new function that we just did starting from here finishing here and then again the same thing persists it's still doing this trying to get the value in that position that's not going to work there it is so let me get rid of this let's try to maybe do something else let's try to now do something with this dates let's grab these dates and then output the results like month year day in separate columns in some particular format so let's go back to our chat GPT and then I'll try maybe to create like a generic function with Google bard to see how it does compared to like Apps scripts code but let's first just generate some code using chatgpt to just maybe get this data from this date column and extract the month the year day to separate columns and see how it goes so again I'll copy this question I'll paste it here I'll just change that dates are in column d ATA starts in Row 2 and row four need apps script code that will extract well it's not going to extract emails anymore so let's get rid of that that will extract months in format June and years from that date and output our results in columns let's see K and let's actually delete this let's do J and K again all right let's see what happens it went to utilities format date interesting very nice I thought it would go with JavaScript to do this but it went with apps script functions actually so I'm going to copy this and let's just put that up here so again let's see what it did so it's going to grab the sales tab get the data all of that is the same so it gets the output array Loops through this data and then it's gonna get that date which is that date in that column that's fine this should work and then it's going to get this utilities format date pass that date from here and then it gets a Time Zone from our parents spreadsheet to get this information in that time zone and then we get that in this format mmm and then we got yyy which is for the year so that's going to get us year and month and then it's going to push it in this array format again all of this looks exactly right so let's just go ahead and run this so that ran no problem let's go take a look and as you can see we got this this is May 2023 2023 this is correct this looks right now let's just go ahead and change so this dates let's just try to rerun that just to make sure we get things the way they should be yep so full month looks good and we got our full year so the formatting was right we got our results looks excellent so what I'm going to do at this point I'm going to go back to chatgpt and ask if it can do this without using this utilities class so basically just using JavaScript so let's ask this can you get the same without using utilities class and that's our JavaScript dates let's just copy this code go back here and paste again this looks good assuming apps script actually does support this method here JavaScript would for sure so let's go ahead and run this yep so that ran no problem we got the same exact results let's just remove this and rerun this just to confirm there we go we got our results and now let's just ask one more question can you move date formatting to a separate function so it did this in this array format let's just ask can you make a separate function actually instead of separate function what we want is one function that will get us those formats based on a parameter let's do can you update the function that will give us different date format based on a parameter okay so let's see what it did so it's going to create this format date function right here it's going to pass the date and the format here it doesn't mention what that format actually is but then it's going to say if it's month year then give us this if it's year month then give us that and then otherwise give us this and again this is an example where chat GPT didn't do very well so this is not going to work first of all it's not going to pass this format here in the correct way and then the actual formats that it made it doesn't seem to be correct so we wanted one to do the month separately and then the year separately but instead it gets us this month year year month so that's not gonna work so let's just say this didn't work can you try again so again it's not passing the format here when I look at this code and then going here it did correct this to do month year that part is good so seems like it did fix some of the parts in this function but it's not using that here let's just mention you need to pass the format needed to format date function to get year and the month so that I think looks better see now it did pass that to the function month and year and then it does push this so I think this should work I didn't read everything here but looks fairly good so let's just copy and paste it and then we'll see if this actually works so let's just save this so this part here see it gets the data then it's going to look through the data then it's going to use this format date function which is this separate function down here first it's going to pass the date with this month option to get the month and then the year and then we're gonna do this so it did this interesting like output length in a different way but it should be fine from what I can tell let's just scroll down and look at this format date so it's going to get the date and the format so first the date what it does it converts it to a JavaScript date here now this is something that it shouldn't do because this is already a JavaScript date so there's no need to create a JavaScript date so it's not good so then we have our formatted date which is a variable apparently and then if that format is month it's going to do this to get the month and then it's a year it's going to do this and then in all the other cases it does that so most of this seems good the only thing that it shouldn't have is pretty much this this shouldn't be here it should just pass this date directly here as a JavaScript date that being said let's run this and see what that output here and also this one see it does like format it shouldn't pass the format here so let's just get rid of this and let's run this function on top let's see what it says does it give us an error I'm curious here or does it still just give us some sort of output so it did run without errors let's go take a look seems to still works so apparently JavaScript date here accepting a date was able to make another date now that can create some probably time zone issues but that being said hey it works so we'll just live with that now what I want to do now I want to go back to Google bard and see if it can actually create this format date function just the function to basically just give us the partial month or year we need so let's just say need a JavaScript function that will accept a date as a date object and a parameter called format and depending on the parameter will return full year or return month in format like January so now we're not doing apps script code at this point this is just plain JavaScript let's see what it does so it called it get year or month that's the format so the format will be either year or month and if it's not correct that's going to give us error so that's nice that we have now error checking that was cool now then we get the year out of it we get the month out of it I don't know why we need to get both of them because we need to return just one but then if the format is year it returns year else it returns month names where did this come from yeah there is no array month names it just basically made it up assuming there's an array that it can grab this month out of it this is not going to work so basically what this is going to do it's going to say month names is undefined so let's just go ahead and say month names is undefined and let's see what happens so now what it did see it made this array of month names from January through December and then it created this function and it puts this array outside of the function but then it's going to do these two things it's going to get us the year it's going to get us a month and the way this months work it's basically if you do get month right here in JavaScript for January it gives you zero for February 1. so something like that so what it's going to do it's gonna add one here so January is going to become one because it would give you zero the problem with that since it's getting that from the array one from the array would be actually February so instead of January it would give you February instead of February it's going to give you March so it's always going to be off by a month so let's just say instead of January I get February so let's see what happens okay so what it did essentially the same thing it's still doing this plus one and it's giving me the same problem so it's still going to be off so this plus one essentially shouldn't be here if this plus one wasn't here this would have worked but it's not able to correct it so I'm going to do still the same problem make sure you're using the JavaScript so yeah it failed again so yeah it does generate some code would it work not really you would have to fix some things but if you want fully working code this is not it this is not going to work now let's try to ask the same question right here in chat apt so I'll just make a new chat here and again we'll use 3.5 paste it here and see if we get a better answer so let's see so again it's going to do this date format and I like the way it shows the function name as opposed to the other one which was get month or year let's just format date so we got if the format is a year then it's going to return the year that should be fine and then if the format is a month it's going to get you this array of months which is the right place to declare this array if you're going to do it this way and then get that in that get month position see it didn't do that plus one which will be correct and in all other cases it gives us this return error this function will work just fine so if we copy this go back here and just replace this and apparently I'm not copying the right thing for some reason let's just copy the right thing so using the same function format date now that was created if we run this previous thing it should still work so if I go back here then re-run this code you'll see we got the results no problem so one question here and we got the correct results now just for sake of trying let's also try the same exact question with gpt4 and see if it outputs anything different I guess I think we should understand that even with 3.5 it doesn't mean it's going to write the same code if I ask the same question again but just for the sake of doing it let's just do gpt4 rerun the same thing taking longer but essentially this is exactly the same thing so as you can see even with just standard JavaScript without apps script involved chat GPD gives us the correct code googlebard gives us something that looks like a code that might work but it's not actually accurate and that should do it for this video thanks for watching please subscribe and I'll see you in the next video

Channel: Learn Google Sheets & Excel Spreadsheets

Views: 7,130

Keywords: Chat GPT, Google Bard, JavaScript, Apps Script, Code, vs, compare, versus

Id: 6OufZkQur1Y

Length: 31min 14sec (1874 seconds)

Published: Mon May 22 2023