WEBVTT 1 00:00:01.589 --> 00:00:09.510 William Rosener: hello, this is Dr rosen or welcome back and this instructional video you'll learn about reformatting data using Google sheets and javascript. 2 00:00:09.900 --> 00:00:18.539 William Rosener: will start to take a look at what you learn how to create and then we'll go ahead and walk through it, step by step here so imagine we've got some data we have the address. 3 00:00:19.230 --> 00:00:24.330 William Rosener: We got the city, the state and visit and initiative thought, this is a great idea to bundle this all into. 4 00:00:24.870 --> 00:00:29.820 William Rosener: A single sound, then we start realizing man, this is pretty difficult to sort and search when it's all. 5 00:00:30.150 --> 00:00:43.680 William Rosener: In one cell so we're going to kind of break it up and put the address here city state and zip into their own column so we're incredible man button called extract addresses and when we press it it's going to go ahead and extract that information there. 6 00:00:44.850 --> 00:00:55.620 William Rosener: But know that we don't want to do let's get started here, so the first step i'm gonna do is i'm going to go to Google COM i'm going to press on the Apps i'm going to come down and choose the drive. 7 00:00:57.360 --> 00:00:59.040 William Rosener: i'm gonna come up here to new. 8 00:01:00.990 --> 00:01:02.790 William Rosener: i'm going to choose the Google sheets. 9 00:01:04.530 --> 00:01:11.010 William Rosener: And there we go so God, and if you wanted to go ahead and give this one, a name I think this might be a silent 11. 10 00:01:12.630 --> 00:01:12.870 William Rosener: But. 11 00:01:14.160 --> 00:01:15.690 William Rosener: Go ahead and give it a name, at some point here. 12 00:01:16.920 --> 00:01:28.410 William Rosener: Well, in the same area as you found a link to this video, you should also find some address system simply a copy that and leave this open because we're going to come back and grab some javascript code here in a second. 13 00:01:29.610 --> 00:01:35.760 William Rosener: and paste and now i'm going to go ahead and come right over here type address. 14 00:01:37.710 --> 00:01:39.120 William Rosener: going to type in city. 15 00:01:40.170 --> 00:01:42.390 William Rosener: State and. 16 00:01:43.980 --> 00:01:53.820 William Rosener: Okay, and again taking a look here we've got some format of data we've got in this case we've got commas, as long as it's somewhat formatted we can work with it, it could be dashes. 17 00:01:54.270 --> 00:02:02.190 William Rosener: semi colons etc, we could simply look for the semi colons and some other punctuation mark and do our format in there. 18 00:02:02.910 --> 00:02:10.950 William Rosener: let's start by creating the command, but then we actually won't even connected towards towards the end of the video here it's great the command button on the come right up here to insert. 19 00:02:11.520 --> 00:02:21.750 William Rosener: going to come on down to draw in and start drawing i'm not going to use them text box and drag a little text box and i'm typing extract. 20 00:02:22.800 --> 00:02:24.420 William Rosener: And i'm going to type to addresses. 21 00:02:25.800 --> 00:02:30.990 William Rosener: And I see I go ahead and add a little color here i'll tell you i'm gonna go ahead and send this first of all I had it selected here. 22 00:02:32.940 --> 00:02:34.650 William Rosener: And then add a little color. 23 00:02:37.710 --> 00:02:41.400 William Rosener: They are we go, I think, let me go with the shade of green here. 24 00:02:42.750 --> 00:02:46.560 William Rosener: And maybe i'm i'm gonna ever so slightly resize this. 25 00:02:48.810 --> 00:02:50.040 William Rosener: Okay, sound up too much space. 26 00:02:51.570 --> 00:02:56.670 William Rosener: And i'll go ahead and drag that read over here to the software. 27 00:02:57.690 --> 00:03:12.150 William Rosener: Okay now we're going to go ahead and start working on our javascript so we've got our command and it's actually not even connected to the javascript let's go to probably the last step we do here now i'm gonna come up here to extensions i'm going to go to Apps script. 28 00:03:16.500 --> 00:03:27.300 William Rosener: Okay, and here is it's already kind of ready to go, we can start working on a function within javascript well, you could come along right here and type in extract addresses. 29 00:03:27.990 --> 00:03:37.680 William Rosener: But I tell you what i'm just going to go ahead and in the same area that you found this addresses just go ahead and copy all this, make sure you get that closing curly bracket way down at the bottom. 30 00:03:39.060 --> 00:03:42.390 William Rosener: And i'm just going to highlight this i'm going to leave it and then paste. 31 00:03:43.380 --> 00:03:48.060 William Rosener: So anyway i'm going to give you all this good because one little mistake and your code won't work. 32 00:03:48.480 --> 00:03:54.390 William Rosener: And so anyway we're going to start off with a function called extract addresses notice, I can get a capital a year. 33 00:03:54.840 --> 00:03:59.760 William Rosener: The first two lines are going to simply I declare some variables next line is i'm gonna say sheet. 34 00:04:00.120 --> 00:04:08.400 William Rosener: And what is it, those are we looking at shape here it's simply the act of sheet and javascript is case sensitive So if I put in the lowercase a here and wouldn't work. 35 00:04:09.060 --> 00:04:25.980 William Rosener: The next two lines are just kind of taking a look at that very first we're going to kind of break this up we're going to get this first address to work once we've got that work and we'll put this in a loop and go from two to love him but, again, we could be going to to 20,000 just disease. 36 00:04:28.170 --> 00:04:36.930 William Rosener: Okay, so that's going to just kind of showing you that first line one little difference between a bb and javascript is javascript starts in Position zero. 37 00:04:37.260 --> 00:04:44.490 William Rosener: So we got 0123 the notice we're going to find the first combat position eight and the second one at position 16. 38 00:04:45.420 --> 00:05:04.830 William Rosener: Okay, the next one is going to be i'm just going to be specifying some ranges first one is going to be 02 column one on one we wrote to them column 2345 and six So do I hear is going to be wrote two column one that's the range one range two brains three range for range five. 39 00:05:06.720 --> 00:05:13.950 William Rosener: OK, the next thing that's going to do that information is stored in range Milan, this information is stored an array. 40 00:05:14.250 --> 00:05:20.340 William Rosener: Of it simply break that array up and put it into a string called complete addresses so that's kind of all that line is done. 41 00:05:21.030 --> 00:05:30.000 William Rosener: The next four lines are just going to play some dummy information into the spreadsheet there, so I would say range to is some address. 42 00:05:30.450 --> 00:05:44.910 William Rosener: range three some city for some state and some zip and will be replacing that but just at this point, we should be able to run it so i'm gonna go ahead and press the SAVE icon and it looks like I have to do this more because right now the run is grayed out so when I press the same function. 43 00:05:46.320 --> 00:05:55.020 William Rosener: And now i'm going to press the run and it's just to let you know, the first time you do this it's going to take 15 2030 seconds or more. 44 00:05:55.770 --> 00:06:06.120 William Rosener: Possibly because it's going to need to authorize permission here so just patiently wait when you should hopefully get authorization required here review permissions. 45 00:06:07.650 --> 00:06:16.500 William Rosener: i'm logged in as bill roesner when you share this at the very end you're you're going to share it with a roaster at an issue okay Daddy do somebody go ahead and press on this. 46 00:06:18.120 --> 00:06:29.280 William Rosener: And i'm going to press down here and hit advanced and i'm going to go to an untitled project and save so i'm going to go ahead and know it's unsafe, but we need to press on that. 47 00:06:30.360 --> 00:06:31.800 William Rosener: And allow. 48 00:06:33.060 --> 00:06:42.180 William Rosener: Okay, and as soon as you do that, you probably have just received a notification on your phone they're saying hey some authorization has been allowed them. 49 00:06:42.960 --> 00:06:57.180 William Rosener: Okay now if we come back, we got some address city state zip So hopefully at this point you've at least got something working here it's simply taking this dummy information and throw it in your spreadsheet, though. 50 00:06:58.500 --> 00:07:15.090 William Rosener: Okay now actually start working on getting this thing to work and the first thing we need to do is find out where does this comma occur at once, we find the first comma everything to the left, that is going to be the address someone to come along and type in come up. 51 00:07:16.890 --> 00:07:17.850 William Rosener: position one. 52 00:07:20.250 --> 00:07:25.410 William Rosener: We played to address the string complete address that i'm going to do an index of. 53 00:07:26.490 --> 00:07:30.660 William Rosener: And i'm gonna do is put a comma, and recently called out the Interior. 54 00:07:32.820 --> 00:07:44.760 William Rosener: So, once a common position, one is equal to the complete at the string complete address that index of and that's a capital over here so everything else is lowercase except that oh enough. 55 00:07:45.210 --> 00:07:53.430 William Rosener: And you're going to notice that that's very common like set value kind of the calls camel capitalization when you go from one world to another. 56 00:07:53.880 --> 00:08:04.830 William Rosener: Go ahead and capitalize it and then all we're gonna do is we're searching for the position of that comma I tell you what let's go ahead and come right down here and type in comma position one. 57 00:08:05.550 --> 00:08:20.580 William Rosener: So rather than display in the strength some address we're going to display what's in the variable comma position one so take a look here, if this is the first address, we should find that at position eight So when I go ahead and run this. 58 00:08:22.890 --> 00:08:33.240 William Rosener: And then, after as soon as I run it I go ahead and close my execution log now if I come right back here Sure enough, I have found the first comment and position eight. 59 00:08:34.560 --> 00:08:38.820 William Rosener: Okay, so let's just go ahead and get covered position to where we're right here. 60 00:08:39.960 --> 00:08:48.330 William Rosener: i'm a position to is equal to complete address that index of just like we did a second ago. 61 00:08:50.310 --> 00:08:54.600 William Rosener: And the only difference here, well, we need to get that exact same common. 62 00:08:55.740 --> 00:09:05.790 William Rosener: Now, rather than starting at the beginning of the screen string we want to start in comma position one, and let me get my semi colon at the end. 63 00:09:07.170 --> 00:09:14.430 William Rosener: So what's going to happen is we are going to find the first common position eight now what we want to do is start in position nine. 64 00:09:15.120 --> 00:09:25.710 William Rosener: and find the next comma and we're going to find that position, hopefully find a position 16 I tell you what let's even go ahead and confirm that position to. 65 00:09:29.430 --> 00:09:36.240 William Rosener: Okay, thank you everything's looking good here and let's go ahead and possibly save it and then run it. 66 00:09:40.770 --> 00:09:41.670 William Rosener: Okay oops. 67 00:09:46.080 --> 00:10:03.180 William Rosener: position one plus one Okay, because otherwise i'm sorry I have found the first one in position aid, so I need to go P, plus one that's nine now I can begin to search again sorry about that so come up position one plus one. 68 00:10:05.100 --> 00:10:06.390 William Rosener: And let's see what we get now. 69 00:10:09.780 --> 00:10:15.840 William Rosener: Okay, great so we found the first column position at the second one at position 16. 70 00:10:17.280 --> 00:10:22.860 William Rosener: Okay, now we can begin to extract some data so let's go ahead and first of all we'll get the address. 71 00:10:27.420 --> 00:10:40.980 William Rosener: In we're going to use a function called sub string that's all one word is lowercase here i'm going to start in Position zero and i'm going to go to comma position one and was that. 72 00:10:42.840 --> 00:10:47.730 William Rosener: Okay, so what was common position, one that was eight, so I found. 73 00:10:49.770 --> 00:11:10.590 William Rosener: The first comment deposition eight so i'm going to grab 123 or 5678 so i'm going to grab eight pieces of pieces of information and place that into address, and now I can come along rather than pretty common position want let's go ahead and put in an address how. 74 00:11:12.840 --> 00:11:17.460 William Rosener: We get and let's uh i'm not sure if I need to save that, let me just go ahead and run it. 75 00:11:20.730 --> 00:11:25.950 William Rosener: or come back to our sheets and Sure enough, we have found the address there. 76 00:11:27.090 --> 00:11:29.070 William Rosener: Okay let's go ahead and grab sitting. 77 00:11:40.680 --> 00:11:43.410 William Rosener: So, again we're gonna take the string complete address. 78 00:11:44.430 --> 00:11:45.390 William Rosener: Go sub strength. 79 00:11:49.170 --> 00:11:54.060 William Rosener: Okay, and in this case we're going to start in common position one plus two. 80 00:12:00.810 --> 00:12:07.560 William Rosener: You know, two i'm going to start going go ahead and type in common position to explain why we just type them. 81 00:12:12.000 --> 00:12:21.060 William Rosener: Okay, so what is going to happen now is we're going to go ahead and we're trying to get this city we're going to hit we found the first column deposition eight. 82 00:12:21.630 --> 00:12:31.170 William Rosener: So i'm going to go eight plus two is 10 so we if we hard coded we could put it in there in order to work on this address so we're going to start in position. 83 00:12:32.280 --> 00:12:41.940 William Rosener: 10 and we are going to go to position 16 but it doesn't actually get that it gets one less than that so it's going to stop at position. 84 00:12:43.500 --> 00:12:45.330 William Rosener: And let's go ahead and then come down here. 85 00:12:48.030 --> 00:12:59.040 William Rosener: And if you typed in that sub string and we're not going to do it now, but you're going to see that it's actually stops one character less than Lord says so let's go ahead and save this. 86 00:13:00.210 --> 00:13:01.140 William Rosener: and run it. 87 00:13:04.350 --> 00:13:04.800 William Rosener: A MAC. 88 00:13:08.010 --> 00:13:08.850 William Rosener: and run it. 89 00:13:12.600 --> 00:13:19.650 William Rosener: And come here and sure enough we're getting the address and the city let's get the state and then we'll get this up. 90 00:13:21.030 --> 00:13:22.110 William Rosener: Okay, to get the state. 91 00:13:31.740 --> 00:13:42.870 William Rosener: Something in this case, they get the State, I am going to I found that common position 16 and restart to after that, which is going to be, I wanted to hard code I would put 18 and 20. 92 00:13:44.820 --> 00:13:48.210 William Rosener: So I would I could literally put in 18 comma. 93 00:13:49.560 --> 00:13:55.560 William Rosener: 20 and it would work on this one particular address, but I want to work on any particular address so i'm gonna go ahead and type in. 94 00:13:56.970 --> 00:13:57.630 William Rosener: Common. 95 00:13:59.190 --> 00:14:02.100 William Rosener: position two plus two. 96 00:14:03.360 --> 00:14:03.840 William Rosener: And then. 97 00:14:05.010 --> 00:14:05.550 William Rosener: i'm. 98 00:14:06.900 --> 00:14:18.090 William Rosener: Actually, before you right here, it says the sub string includes the character up to, but not including the character indicated by the end Okay, and then we get comma position. 99 00:14:19.380 --> 00:14:38.910 William Rosener: Two plus four and give a little semi colon at the end, so what is happening is we're going to start in so we found that second column, a position 1616 plus two is 18 so i'm going to start here and position 18 i'm going to go 18 plus four would be. 100 00:14:40.230 --> 00:14:45.120 William Rosener: 16 plus forward be 20 but it doesn't actually get the last one, so it stops at position. 101 00:14:46.680 --> 00:14:48.030 William Rosener: And that should get the state. 102 00:14:52.710 --> 00:14:55.680 William Rosener: And let's say that and run it. 103 00:15:00.120 --> 00:15:09.870 William Rosener: And we'll come right back here and sure enough or getting this team here that he left us get the zip and then we'll put this on a loop of the final walk connected to our command button here. 104 00:15:11.310 --> 00:15:14.370 William Rosener: Okay, the last piece of data is our zip. 105 00:15:16.050 --> 00:15:17.850 William Rosener: ended up with like wine here. 106 00:15:20.370 --> 00:15:23.340 William Rosener: So we're going to take our string complete address. 107 00:15:25.140 --> 00:15:30.900 William Rosener: i'm going to use a slice here, this is going to allow them to extract the last five characters. 108 00:15:31.440 --> 00:15:47.880 William Rosener: Some with a negative it starts at the right and goes back so in this case here, all of these are five, and I know some addresses will have like a nine nine digit zip code there, except for them, but i'm just going to grab the last five another thing to come right down here. 109 00:15:49.230 --> 00:15:50.700 William Rosener: And type in zip. 110 00:15:53.640 --> 00:15:56.910 William Rosener: And we'll go ahead and say that and run up. 111 00:16:00.870 --> 00:16:08.190 William Rosener: And we'll come right back up here and sure enough or grabbing the address the city, the state and zip. 112 00:16:09.630 --> 00:16:24.120 William Rosener: Now, when you do just replace this in the loop and we'll go from two to 11 but just as easily we could be going to 11,000 and now you're going to be saving yourself hours and hours and hours of time in not making a possibility mistakes or. 113 00:16:25.200 --> 00:16:31.800 William Rosener: somebody's got a variable here called counter so i'm going to go ahead and we're going to go for counter. 114 00:16:33.090 --> 00:16:34.740 William Rosener: see my little prick see here. 115 00:16:35.970 --> 00:16:45.000 William Rosener: or counter it goes to summer start off at position to what counter is less than or equal to 11. 116 00:16:46.230 --> 00:16:51.870 William Rosener: or semi colons we would go counter plus plus it's going to increment by one. 117 00:16:52.950 --> 00:16:54.390 William Rosener: will go ahead and start it. 118 00:16:55.650 --> 00:17:01.860 William Rosener: And notice it put the closing on there I don't want to close in there, I want to wind down here at the bottom. 119 00:17:03.660 --> 00:17:07.380 William Rosener: Now, what I want to do I want you to do is go ahead and and then all these lines over. 120 00:17:21.570 --> 00:17:30.150 William Rosener: Okay, so what we just add is we're going to set we're going to put a loop recreate this loop so we're going to start town or equal to, so the first time through counter has a value of two. 121 00:17:31.110 --> 00:17:36.030 William Rosener: As long as counter the next time, as long as it's counter is less than or equal to, we love it. 122 00:17:36.780 --> 00:17:48.930 William Rosener: we're going to continue this is going to simply increment counter by one I could have also type counter equals counter plus one there that the counter plus plus is kind of more than standard Convention they're going to come in and buy a single image of them. 123 00:17:50.190 --> 00:17:58.050 William Rosener: Okay, so now, rather than taking a look counter the person in through is going to have a value of two so let's simply change this to counter. 124 00:18:00.480 --> 00:18:01.560 William Rosener: All these tools here. 125 00:18:14.130 --> 00:18:33.720 William Rosener: Okay, so the first time through counter has a value to so we'll be taking a look at this information, the next time through counter is going to have a value of three and the value 456 all the way through 11 there I think we're ready to check this out let's save it let's run it. 126 00:18:38.400 --> 00:18:44.610 William Rosener: And let's close and take a look there and sure enough it's like everything's working perfect here. 127 00:18:49.230 --> 00:19:00.120 William Rosener: OK now let's go ahead and just kind of confirm in there everything's looking at let's go ahead and delete this and let's connect it to the command button. 128 00:19:00.750 --> 00:19:10.230 William Rosener: So here's the command button there's three little dots up here go ahead and press on the three dots and assign script and we call that extract. 129 00:19:11.400 --> 00:19:12.390 William Rosener: addresses. 130 00:19:13.470 --> 00:19:14.370 William Rosener: And press okay. 131 00:19:16.140 --> 00:19:27.330 William Rosener: And that was simply read up here extract addresses OK, so now, when I press on the command button it's going to go ahead and execute that script. 132 00:19:28.830 --> 00:19:40.320 William Rosener: Well, I think we've got this thing finished here, I think you need a final steps here, make sure you go ahead and share that with me actually could come right up here it share. 133 00:19:42.360 --> 00:19:49.860 William Rosener: Just like you typically do you're going to come right up here you're going to share that with roesner at initio k.edu. 134 00:19:50.940 --> 00:19:57.990 William Rosener: you're going to hit the sand and then you're going to have a copy link eventually to create a hyperlink from your website to. 135 00:19:59.700 --> 00:20:02.040 William Rosener: i'm just going to go ahead and cancel this. 136 00:20:03.840 --> 00:20:11.880 William Rosener: create a hyperlink to this document from your website so when I grade i'm not looking through hundreds of email addresses I can simply go to your website. 137 00:20:12.570 --> 00:20:27.030 William Rosener: redesign 12345 whatever assignments i'm grading for that duration them well hopefully know this video we learned a little bit about reformatting data using javascript and Google sheets our kitchen next video thanks man.