William Rosener: Hello welcome back in this video you're going to learn you learn how to use javascript to perform string manipulation and Google sheets.
William Rosener: Today, more and more businesses and people are using online cloud based web based technology and applications to store and handle their data.
William Rosener: let's start here by taking a quick look at what will be created here, and here again i'm going to be in Google sheets and when I hit the this command button called convert I can see it's just going to go through.
William Rosener: This case assuming someone enter the data in last name comma first name and where it's going to simply be extracting the person and the last thing there.
William Rosener: And in this case there's only seven names, but if there were 70,000 names I was just doing the math there, and you did about one a second it's still going to take you over 20 hours.
William Rosener: And now it's going to take it's going to take about 20 hours at one per second it's gonna probably take you longer than that but, most likely.
William Rosener: you're going to introduce errors, at least, I know I would be starting off here pretty correctly, some of the first thing has been before long i'm gonna be coming down here and i'm going to be typing in.
William Rosener: Walker for the first name and Kent over here i'm going to kind of getting the MIC first names and the last things possibly mixed up along with some spelling mistakes etc them.
William Rosener: Okay well again here's what we're gonna be doing this, I created a little button called convert which is simply this going to extract those names.
William Rosener: Okay now let's we don't want to do let's get started here, so the very first step i'm going to do is.
William Rosener: That you don't want to do is hopefully you've already got an account on Google it if not you'll have to create a Google account.
William Rosener: And then, what you're going to do is you're going to come up here after you get your account created press on these little dots bullets appear.
William Rosener: In you're going to see you can see, you go to YouTube except here there's gmail but i'm going to go to derive so create an account on Google you haven't go to drive okay now we're going to do is going to come up here and i'm gonna hit to file new and i'm gonna hit Google sheets.
William Rosener: And we've got a untitled blank spreadsheet and just to save a little time here i'm going to copy and paste i'll put this in the same areas you found the video and i'm not sure it's even worse than opening up but they might be.
William Rosener: Again, so good i've just got a few names here.
William Rosener: And we're going to go through these names extracting first name and the last thing.
William Rosener: Okay, so at this point, the next step i'm gonna do is i'm going to start creating the script and then we're going to create a command button that calls that script let's sell start off with a little script here, let me come up here to tools and choose script editor.
William Rosener: In the very first step i'm to do, once this opens is rename this one i'm gonna call this one.
William Rosener: convert names.
William Rosener: Okay i'll tell you what i'm going to go grab grab some code here and place in there.
William Rosener: And i'm deleting this code here, but just to kind of show you and i'll be copying pasting more code here momentarily here.
William Rosener: Okay, so what's going to happen here is i'm just going to simply take the information, this is going to be route two column one i'm going to grab that information.
William Rosener: stored in a temporary variable and then i'm going to be writing it affects setting the value into two but to tell you let's go ahead and i'm gonna go ahead and run this at one time he could save it first and then i'm gonna hit run.
William Rosener: and take a second to hear.
William Rosener: Come on.
William Rosener: Oh okay I almost forgot we're going to have to authorize javascript so i'm going to go ahead and hit the review permissions.
William Rosener: i'm going to click on my name.
William Rosener: i'm gonna hit advanced.
William Rosener: Good untitled project and save.
William Rosener: And we've only got to do this once and just hit a lot.
William Rosener: And you're probably i'll get a little text message on my phone and you may also get an email there.
William Rosener: Okay, so now looks like it started it and completed it let's just go ahead and take a look, all we did is we're going to take initially take this information and then moved it over here.
William Rosener: Okay, and let's take a look at the code here, one more time.
William Rosener: So the first thing up here declared a couple of variables i'm going to have set my sheet i'm now going to get the rain information wrote two column one on the set my range.
William Rosener: i'm going to get the value out of that range stored in temp and then going to set another range, which is row two column two and i'm going to send so this one was a good values, this one is a set value with temp so again when I run that if I run it right here.
William Rosener: Now I come back, I can see, you have executed that okay well, rather than having to go to that script all the time we're going to go ahead and make it easier for user and we're going to create a command button someone come right up here to insert i'm going to go to a drawing.
William Rosener: i'm going to drag out a text box.
William Rosener: To type in convert names.
William Rosener: i'm going to go ahead and let's change the color about go with the sheet of like a shade of green here and then i'm gonna go ahead and maybe that's if I get that Center.
William Rosener: Okay i'm gonna hit save and close.
William Rosener: Okay, there we go now what you can do is actually click on that, and you can move it around notice when i've got it slipped it there's three dots over here so i'm going to press on those three dots and i'm going to go assign script.
William Rosener: And i'm going to type in convert.
William Rosener: name names, remember, I did the porcelain let's see if that worked out.
William Rosener: Okay i'm gonna hit convert name running script and it's not working there.
William Rosener: OK now what we're gonna do is we're going to go ahead and get it to work on the first I wish to people to get it to work on the first row let's i've got it that what's the public place it into a loop so i'm gonna come back and go to my queued up here.
William Rosener: So, if this ever gets closed right up here, I can just come right up here to tools and script editor.
William Rosener: Good and i'm just going to delete this code which I typed in there, and let me know actually there's some code here that's going to get us excited it's gonna all this code is going to work on the first name luminous I go ahead copy and paste all this.
William Rosener: And by you re type in this, and now that you're going to actually maintain retain this information, much more than if you were to write a copy paste.
William Rosener: So let's go ahead and take a look here, the first three rows appear on just declaring variables next one i'm going to get my sheet, in this case i'm just going to need to kind of specify the sheet, so you can see here.
William Rosener: i've got sheet get range or why these 212223 I need to specify the three ranges, so this is going to be row two column one this is row to come to and to to.
William Rosener: I mean row two column three, and that is why I need this three values, right here Okay, so the very first step i'm gonna do is i'm going to get the values from range one.
William Rosener: here's range one i'm going to get those values stored into an array what you're going to find is when you do it get values next three stores in an array well.
William Rosener: In javascript Ray start in zero so i'm going to do 00 because i'm accessing the first piece of data, out of that array okay next step, but they don't know.
William Rosener: well documented documentation right here.
William Rosener: you're not but maybe i'll start off with some numbers.
William Rosener: And then i'll put some my name.
William Rosener: And like so Okay, so now what i'm going to do is this day that's got to be formatted again I mentioned, we mentioned before, in this case we've got a comma space common space.
William Rosener: And it's all carefully and format it there, and again i'll probably mentioned this before I thought I had something like this.
William Rosener: I couldn't do anything with it there's no way I could extract the first thing out of that if it was capitalized stand a chance, I could do that.
William Rosener: there's a space here a tab a colon anything as long as there's something here, in this case i've got a comment in the space we can actually search for the one now searching for the space, though.
William Rosener: Okay, so in this case here i'm going to find the space position and if I take a look.
William Rosener: Look through that font size larger but i'm sorry i'm not sure how to do that larger here in Google sheets here.
William Rosener: i'm going to find it at positions about in that first cell i'm going to find it at position eight that's for the again I want this to work on in it.
William Rosener: So, after this line has been executed the space position would be eight i'm not going to grab the last name well i'm going to start in positions zero.
William Rosener: And I found the space in position eight eight minus one is seven so i'm going to take the first seven characters.
William Rosener: And that's going to be 1234567 and that's going to be the last thing then i'm going to start in so I found the space and position eight eight plus one is not on the starting position nine and extract the rest of that and that's going to be the first step okay so i've extracted.
William Rosener: The first name the last name last two things are going to simply write them out i'm going to set the value in range.
William Rosener: To there's range to row to come to i'm going to write the first name and then range 323 i'm going to ride the last let's go ahead and try this again I can find one I can i'm going to make sure I save it.
William Rosener: And i'm just going to leave this open here i'm not going to come back here and i'm going to hit convert names and Sure enough, is working on the person there.
William Rosener: Okay Now all we need to do is place this into a loop, and not to do that i'm just going to use a.
William Rosener: Little for loop here.
William Rosener: and take a look here, I want to go from two to eight someone come right back here whoops.
William Rosener: Take a look at my code right below the sheets here i'm going to put a little do loop here or for loop and i'm going to go for.
William Rosener: Culture equals to restart in them the second row and then i'm going to go ahead and say well counter is less than or equal to eight.
William Rosener: semi colon and now i'm going to increment my counter not got two choices here, I can either go counter equals counter plus one.
William Rosener: or a lot of people just put the two plus lines and that's going to increment by one i'm now going to start.
William Rosener: notice when I do this I don't want it to close right away so when, as soon as I put that curly bracket it started so go ahead and delete the one.
William Rosener: And then you're going to come down here and close it so i'm going to started here actually only you're starting it not closing it then i'm going to close it down here.
William Rosener: Okay, so the first time through counter has a value of two so now, all we need to do is change these tools to counter.
William Rosener: Okay, just like so so first time through we're gonna go 2345678.
William Rosener: And as long as it's less than eight we're going to keep going through the loop there and we're ready to check this out so i'm going to save it.
William Rosener: Come back and hit convert names.
William Rosener: And there we go.
William Rosener: Okay, it is finished them so that's what you're going to be doing.
William Rosener: and inevitable, you may come across some other problems that's why that's all i'm gonna have you do on this assignment is this one, I think there's some new technologies here, maybe you've got to create an account on Google, maybe you have a little syntax error etc there and.
William Rosener: Finally, the last step is to last steps here.
William Rosener: Okay, I tell you what let's go ahead and give this a name I own and we could come right up here, I believe in name it.
William Rosener: And if you want you to call it convert names, where you could give it the assignment the assignment number now here's for the big thing here, so I want you to do two things one is I want you to share it.
William Rosener: In German ish share it with me.
William Rosener: So you would press share come here, if you want to put a little notification here i'm sharing my assignment with you and then send out.
William Rosener: i'm going to cancel so make sure you.
William Rosener: share it with me and then also I know it's not going to save the javascript but go ahead and download Microsoft excel.
William Rosener: In and save this as a Microsoft excel file and maybe call this assignment 12, for example, instead of convert names and then upload that to your class website.
William Rosener: And when you're done here's what you're going to do notice when you save it it's not going to be it's not going to have any of the macros it's not going to work, but I could.
William Rosener: easily see that you've got the names and you've got your convert button there so anyway that's going to kind of wrap up this video again, one more time here.
William Rosener: If I was to close that and come here to tools and good script editor I can take a look at my code, I can make some changes I can actually run it right here, if I want or.
William Rosener: Again, imagine you're trying to create an interface here, so the average Secretary can handle and they're like what's all this code here so just create them a nice little convert button command, but here that is linked to that kid so that, when they press on it.
William Rosener: Simply goes through there and extracts the appropriate information well I that's going to wrap up this video catch the next video thanks bye.