JavaScript Arrays - Programming Like a Grown Up - Google Sheets Apps Scripts - Array Methods Part 5

okay so in this video I'll try to demonstrate how important exactly are a race I'm going to do some basic examples of something you would want to do and we'll see exactly why using a race is very important so let's start with something basic let's try to create a script that will just calculate our profit here in this column over here so basically what we're trying to accomplish this should say profit and it would just be let's take sales and minus cost of goods and get a total and again this would be an example of something we would want to do for the whole column right so let's try to accomplish something like this using a script and then we'll see the difference between doing this the right way and the wrong way so I'm gonna go to my script editor here we go so in this script editor we'll go ahead and create our spreadsheet very both so darkness I'm going to create also variable for our active sheet that's good now we have our variable and what I'm going to do I'm going to create a loop that will just go and calculate sales minus cost of goods and basically place this over here so our loop needs to start from row two as you can see over here and keep going until the end right so for now I'll do something short so we'll go from Row two to Row 15 so that would be a basic for look okay so semicolon here that's good so that's gonna start from the second row here roll to row 15 in here right so let's try to now do the rest so we need to see what the sales value is sales is located 1 2 3 4 5 column number 5 so I'm gonna create a variable and I'll go inside of our active sheet get range and then the row will be I which is the current row we're in and a column will be 5 and we're gonna get the value so let's also get our value for cost of goods I'm just gonna copy this line and I believe it was column 6 yeah so next column so that should get us cost of goods now I'm going to create another variable here we'll call it profit and that would be sales minus our cost of goods that will be our profit and finally we would take that profit and place it in this cell over here and that's column number let's see 9 okay so and we need to set the value to whatever this profit is supposed to be save this this should get us what we need so I'm gonna run this so it took a little bit let's see so you can see how we have all our numbers here so let's also add profit in this first column the actual text profit so to speed this up it should happen before our look I'll just go on top here and that would be the first column and the text will set to text profit good so I'm gonna run this again go back here there we are so now we're gonna run this for the entire spreadsheet right so let's see how many rows we have here so we have four thousand nine hundred and ninety nine rows so I'm gonna go back here and do that number obviously there's probably a better way to get this number we could get the last row but that's not really the point of this video so I'm gonna save this and run and let's just pay attention how long this takes so you can see how it's taking quite a while and I'm not sure how long this is actually gonna take and even I'm not sure if this is actually gonna finish this script because all Google scripts have a time limit how long you can execute a script and if you run out of that time it will just stop the script and just tell you that you can't run a script for that long but so far we're waiting it's been probably at least 30 seconds maybe even more and and I'm running out of patience at this point so I'm just gonna cancel this let's just see how far that got us so I'm gonna go here that's our profit control down and there I am so we're in row 291 so if I do control down again see we have about 5,000 rows all this time we've been running this we were able to get to row 291 and that's pretty much it and this operation is not even that complicated all we're doing we're just grabbing two numbers and we're just subtracting them for about like five thousand rows of data so hopefully that shows you how inefficient so far this is so the reason this is inefficient is that every time you have to use this get range or set value it has to go to the spreadsheet and get that information and that just slows down the script the more times you have to do this so you have to try to limit in your script the number of times you're getting something from the spreadsheet and number of times you're putting something back to the spreadsheet now in this particular case we're getting some information here right well actually we're setting some information here but over here our loop runs from two to four thousand nine hundred eighty nine so what is that about five thousand times a little less than that so we run this about five thousand times and every time out of those five thousand times we have to get this value from this cell get value from the seller sell and set the value to the cell which will make our script incredibly slow so now let's transform the script in the right way of creating scripts and see how using an array will improve this so I'm gonna just delete this entire for loop we'll still use this set value to make our profit so I'm just going to create variable data and my data is going to be simply just get the information from these two columns because I need sales numbers and cost of goods numbers and I'm gonna do that from my active sheet I'm gonna do get range and the starting row is Row to the starting column is 1 2 3 4 5 so 2 & 5 now the number of rows we have to do it's gonna be well the total number of rows here is four thousand nine hundred and eighty nine and we have one row of label so that makes it four thousand nine hundred and eighty eight and then how many columns so I wanted to get sales and cost of goods this too so two columns and we can do get values to get it to an array and the array is gonna be this data so as a result of this we will grab this range starting from here there's two columns and all the way down and put it inside of an array let's now do our operations so we're gonna do our operation using map method and because map method returns a new array we will have to create that variable maybe I'll call this profit so that's this and inside of this map we're going to create a function and since it's going to be a pretty simple operation I'm not going to create a separate function I will do a function with no name right inside of this bracket this function will pass the parameter which will be the row so each row will be that parameter I'm just gonna do our four row and that function is going to return so what does it have to return it has to return sales minus cost of goods and for me that's gonna be an array and the first column in that array is going to be sales numbers and the second column in that array is going to be our cost of goods numbers so to get to the first column I'm gonna do R 0 I'm gonna subtract R 1 which is the second column and we need to return this as an array because we need to return it as a row so I'm gonna do a semicolon here and save this so if this entire map thing isn't clear to you and you're not sure why I have to return an array here then go back and watch the previous part that's where I cover map function and I explain why this works the way it works and this one this is gonna be it I'm gonna return a new array and in that array I'm going to just take the first column and subtract the second one and then finally I have to take that profits and this profit is going to be an array of all those results and I'm gonna have to place it in this column over here in AI column and to do that I'm going to go to my active sheet I'm gonna do get range now we have to first oops extra parentheses there the first row so we're gonna start from the second row over here and this is gonna be let's see column number nine so that's second row and nine and then we have to see how many rows that is and how many columns that is so the number of rows and number of columns we will just calculate automatically by getting the length of this array so I'm gonna do profit dot length and then I'll do profits and I'll get the first element in that array which will be the first row and if I check how many things I have inside of that first row that should give me the number of columns and then I have to use set values property to finally use this profits array and place it on my spreadsheet so I'm gonna save this now let's take a look see it's empty let's try to run this and see what we get so I'm gonna run this so it took probably less than five seconds or so let's go back and see what happened so here control down we're done so that's how much faster it was using an array instead of the regular way of doing this hopefully that gives you an idea of why using arrays is important because once you move out of really small data sets then not using array is not even an option so let's try another one just for the sake of demonstration I'm gonna clear this two columns and we'll try to take this location column and split it into two column we'll make a new column city and another column state and we'll have to take this Columbus Ohio splitted by you know comma and place the state here and city here so go back to this I'm going to start by oh I guess we're not gonna be setting the labels because I already have them in here but that's really not the big deal here so the first thing we need to do we need to just get this locations to an array right so we're gonna do that by taking which column is this column 7 so it's still going to start from the second row it's gonna be column 7 still the same number of rows and now we need one column instead of two columns that's going to be my data and it's gonna be this entire column of values so now that I have the data I will have to create a new variable I'll call this results and for now I'm just gonna clear this so again we're gonna go in that data which is this that column of values and array we're going to use our map function on that and we're going to pass a row as a variable so now that function should do what it should split those values and to get to the variable in that row we're gonna get to R 0 R 0 in here will be the first element so since we have just one column that's the way we're gonna get to it if we have two columns we would do our 1 to get to column 2 and R 2 to get 2 column 3 right but we have only one column so we're gonna have to do R 0 to get to that value in that column now that value that I'm gonna get is gonna be this text and what I need to do in this text I need to split it using the comma and I can do it using a native JavaScript split function and I'm gonna split it by comma and I think there was a space there yeah perfect that's what I'm gonna be looking forward to split this and when I split that it's gonna give me an array and that array is gonna be well 1 is gonna be the first one and the second one is going to be the second one inside of that array the first one is the city so if I want to get the city I will have to do the first one which is the 0 so this entire thing is gonna give me the city so let's just return this and I have to return that if you remember inside of an array so that we have the row structure in Google sheets so I'm gonna create this that's my results and then what I'm gonna do we're gonna place this in here so which column is that that's column 8 still starting in that second row and now we're gonna use variable results we'll calculate the length and the length here and it should figure out how many columns and rows we have there and we're gonna put our results here back to our spreadsheet so that's so far see how well this went I'm gonna run this plate took literally a couple of seconds and I have all of these and then I'm missing the state so to do this state remember this is an array that's what I'm getting and in this array I have this first row that gave me the city now I'm gonna copy this and I'm gonna do a comma because I need a second column and paste that second column here and I'm still getting the same value in this row I'm splitting by this comma space but instead of after splitting the comma space getting the first thing which is gonna be the city I need to get the second thing which is going to be the state so I'm gonna change this zero to one and because I use length here I don't need to recalculate anything or really change anything here I should be able to just run this there it is just a few seconds and this is done now if I try to do this with a for loop again like the previous example with subtracting numbers that would take forever so hopefully that gives you an idea why you use a race and that you need to use a race and that's pretty much all I have for this video thanks for watching please subscribe and I'll see you in the next video

Channel: Learn Google Spreadsheets

Views: 35,184

Keywords: JavaScript Arrays, Programming, Google Sheets Apps Scripts, Array Methods, speed, fast

Id: 985XJOeigpA

Length: 20min 32sec (1232 seconds)

Published: Fri Nov 02 2018